PostgreSQL : diagnostiquer une requête lente avec EXPLAIN

Exécutez les exemples dans psql ou votre client PostgreSQL, sur un environnement de test représentatif. Remplacez la table fictive orders et ses colonnes par les vôtres.

Vérifier si la requête attend

Pendant le ralentissement :

SELECT pid, state, wait_event_type, wait_event,
       clock_timestamp() - query_start AS duree,
       pg_blocking_pids(pid) AS bloque_par,
       left(query, 200) AS requete
FROM pg_stat_activity
WHERE datname = current_database()
  AND state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

bloque_par non vide indique des processus bloquants. Une attente Lock demande d’examiner les transactions concurrentes ; un index ne suffit pas à la résoudre. Une transaction idle in transaction peut bloquer les autres sans apparaître dans ce filtre : inspectez alors le PID signalé séparément.

La visibilité des sessions d’autres utilisateurs dépend des droits. Les textes SQL peuvent contenir des données sensibles : anonymisez-les avant de partager un diagnostic. N’arrêtez pas une session de production sans comprendre sa transaction.

Commencer par le plan estimé

EXPLAIN
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 50;

Sans ANALYZE, PostgreSQL prépare le plan sans exécuter cette requête. Les valeurs cost sont des estimations internes, pas des millisecondes.

Pour mesurer une lecture sur votre environnement de test :

BEGIN;
SET LOCAL statement_timeout = '5s';
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 50;
ROLLBACK;

Le délai de cinq secondes est un exemple à adapter. Si le délai est dépassé, aucun plan complet n’est fourni ; terminez la transaction avec ROLLBACK avant de continuer dans psql.

EXPLAIN ANALYZE exécute la requête. Sur UPDATE, DELETE ou INSERT, il effectue les écritures. Un rollback n’annule pas tous les effets possibles, notamment les séquences ou certains appels externes déclenchés par des fonctions. Analysez ces opérations sur une copie de test maîtrisée.

Lire les informations utiles

Les temps des nœuds incluent le travail de leurs enfants : ne les additionnez pas tous. Le plan ne mesure pas à lui seul tout le trajet jusqu’au navigateur, notamment le transfert et le rendu des résultats.

Tester une correction ciblée

Pour l’exemple filtré par client puis trié par date, cet index est un candidat à mesurer :

CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);

Vérifiez les index existants avant de le créer. Un index prend de la place et ralentit certaines écritures. Sur une table active en production, une création classique bloque les écritures pendant sa construction ; CREATE INDEX CONCURRENTLY réduit ce blocage, mais a ses propres contraintes et ne s’exécute pas dans une transaction explicite.

Si la table a beaucoup changé et que les statistiques sont anciennes, une mise à jour ciblée peut aider :

ANALYZE orders;

Cette opération demande les droits appropriés et consomme des ressources. Vérifiez aussi les conversions de types, fonctions appliquées aux colonnes filtrées, jointures et colonnes inutiles. Un index supplémentaire ne corrige pas une requête lancée 500 fois par l’application.

Confirmer le gain

Reprenez le même plan avec les mêmes paramètres et comparez durée, lignes et buffers. Faites quelques mesures en notant l’effet du cache, puis essayez des paramètres représentatifs, y compris les cas les plus lourds.

Vérifiez que les résultats sont identiques et mesurez aussi le temps côté application. Pour retrouver les requêtes coûteuses à l’échelle d’un service, l’extension pg_stat_statements fournit des statistiques agrégées ; son activation demande une configuration serveur adaptée.

Ressources

• PostgreSQL — lire EXPLAIN
• PostgreSQL — syntaxe et limites d’EXPLAIN
• PostgreSQL — activité des sessions
• PostgreSQL — création d’index et CONCURRENTLY
• PostgreSQL — pg_stat_statements
• PostgreSQL — commandes essentielles