Trouver les requêtes lentes Postgres avec pg_stat_statements et EXPLAIN ANALYZE.

pg_stat_statements est livré avec Postgres, mais reste éteint tant qu’on ne le charge pas. Il a besoin de mémoire partagée : il doit figurer dans shared_preload_libraries, ce qui exige un redémarrage du serveur. Ensuite, un CREATE EXTENSION dans chaque base où vous voulez lire la vue. Il enregistre chaque instruction avec ses constantes remplacées par $1, $2 : WHERE id = 7 et WHERE id = 8 comptent pour une seule requête.

# postgresql.conf (needs a restart)
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on

-- then, in each database where you want the view
CREATE EXTENSION pg_stat_statements;

Sur un serveur où vous pouvez lire la vue vous-même, commencez par celle-ci. Triez d’abord par temps total. Une requête de 2 ms appelée 400 000 fois par jour coûte plus cher qu’un rapport de 3 secondes lancé deux fois.

-- Where the time goes: total time, with calls and the average
SELECT round(total_exec_time) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows / nullif(calls, 0) AS rows_per_call,
       round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct,
       left(query, 120) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 15;

-- Start a fresh measurement after a fix
SELECT pg_stat_statements_reset();

pg_stat_statements affiche $1 à la place des valeurs. Remettez de vraies valeurs : le plan pour un client qui a 40 commandes peut différer de celui d’un client qui en a 400 000. Demandez ensuite à Postgres comment il a exécuté la requête.

-- Postgres 18 adds buffer counts to ANALYZE by itself; on 17, ask for them
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 4821 ORDER BY created_at DESC LIMIT 20;

-- ANALYZE really runs the statement. For writes, roll it back:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'paid' WHERE id = 991;
ROLLBACK;

Un plan est un arbre. Chaque ligne est une étape, en retrait sous l’étape qui consomme ses lignes; les lignes les plus profondes s’exécutent en premier. Avec ANALYZE, chaque étape montre deux séries de chiffres : l’estimation du planificateur (cost, rows) et ce qui s’est réellement passé (actual time, rows, loops).

-- Before (Postgres 17 output, trimmed)
Limit  (actual time=88.420..88.426 rows=20 loops=1)
  Buffers: shared hit=2113 read=6221
  ->  Sort  (actual time=88.418..88.421 rows=20 loops=1)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (cost=0.00..20834.00 rows=51 width=97)
                                (actual time=0.381..88.301 rows=48 loops=1)
              Filter: (customer_id = 4821)
              Rows Removed by Filter: 999952
Execution Time: 88.471 ms

-- The fix, as its own migration (CONCURRENTLY cannot run in a transaction)
CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

-- After
Limit  (actual time=0.032..0.061 rows=20 loops=1)
  Buffers: shared hit=24
  ->  Index Scan using orders_customer_created_idx on orders
        (actual time=0.031..0.057 rows=20 loops=1)
        Index Cond: (customer_id = 4821)
Execution Time: 0.083 ms

Construisez les index avec CREATE INDEX CONCURRENTLY sur une table en service pour que les écritures continuent, dans une migration à part, hors transaction. Où exécuter les migrations explique comment Rails et Django s’en occupent.

• Index manquant sur une colonne de filtre ou de jointure. Les clés étrangères sont l’oubli habituel : Postgres indexe la clé primaire, pas la colonne qui pointe vers elle. Chez Canner, canner db check liste les clés étrangères sans index et les index en double, sur tous les forfaits, à partir du catalogue seulement.
• Requêtes N+1. Une requête pour la liste, puis une par ligne. Le correctif est dans l’ORM : includes ou preload dans Rails, select_related/prefetch_related dans Django, include dans Prisma, ou une jointure écrite à la main.
• Pagination par `OFFSET` profond. OFFSET 50000 lit puis jette 50 000 lignes. Paginez plutôt par clé : WHERE id < $dernier_vu ORDER BY id DESC LIMIT 20.
• Une fonction sur la colonne. WHERE lower(email) = $1 ne peut pas utiliser un index sur email. Indexez l’expression : CREATE INDEX ON users (lower(email)).
• Trop de données par ligne. Un SELECT * sur une table aux grosses colonnes JSON ou texte transporte tout. Sélectionnez les colonnes dont la page a besoin.
• Statistiques périmées après un chargement massif. Lancez ANALYZE nom_de_table et refaites l’EXPLAIN.

Chez Canner, les compteurs partent du moment où vous avez activé la supervision ou de la dernière remise à zéro, et le journal garde sept jours. Il n’y a pas d’historique des requêtes à long terme, ni de traçage d’une requête HTTP lente jusqu’à la requête SQL derrière. Si vous en avez besoin, un outil d’APM dans votre appli le fait mieux.

Sur une base sans instance dédiée, pg_stat_statements n’est pas offert. EXPLAIN ANALYZE fonctionne partout où vous pouvez lancer du SQL, par exemple psql par l’accès externe, et canner db check trouve quand même les index de clés étrangères manquants.