Formations — 1 037 leçons en français

Accueil › SQL & bases de données › Diagnostiquer une requête lente

Diagnostiquer une requête lente

Lire un plan d'exécutionintermediaire

Optimiser sans mesurer, c'est deviner. La démarche tient en quatre étapes, dans cet ordre.

1. Trouver la vraie cible — pg_stat_statements, trié par temps total :

SELECT calls,
       round(total_exec_time::numeric, 0)  AS temps_total_ms,
       round(mean_exec_time::numeric, 2)   AS moyen_ms,
       rows,
       left(query, 80)                     AS requete
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Une requête de 2 ms appelée un million de fois coûte bien plus qu'une requête de 3 s appelée dix fois.

2. Capturer le cas réel — log_min_duration_statement = '500ms' journalise les requêtes lentes avec leurs paramètres ; auto_explain y ajoute le plan.

3. Analyser — EXPLAIN (ANALYZE, BUFFERS) sur la requête, avec les vraies valeurs de paramètres.

4. Corriger, puis re-mesurer. Dans l'ordre de rentabilité : réécrire un prédicat non indexable, ajouter l'index manquant, lancer ANALYZE, réduire le nombre d'appels (N+1), et seulement ensuite toucher aux paramètres.

Et quand la base est bloquée maintenant, une seule vue compte :

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS duree, left(query, 60)
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duree DESC;

Les termes à connaître

pg_stat_statements

Extension qui agrège les statistiques d'exécution de toutes les requêtes : nombre d'appels, temps total, temps moyen, lignes. C'est par elle qu'on trouve les requêtes à optimiser, avant d'ouvrir EXPLAIN.

Temps total contre temps moyen

Une requête à 2 ms appelée un million de fois coûte plus cher qu'une requête à 3 s appelée dix fois. Classer par temps total désigne la vraie cible.

log_min_duration_statement

Paramètre qui journalise toute requête dépassant une durée : log_min_duration_statement = '500ms'. La façon la plus simple de capturer les requêtes lentes réelles, avec leurs paramètres.

auto_explain

Module qui journalise automatiquement le plan des requêtes lentes. Précieux pour les lenteurs intermittentes, impossibles à reproduire à la demande.

pg_stat_activity

Vue des sessions en cours : requête exécutée, état, durée, verrous attendus. Le premier endroit à regarder quand la base est bloquée maintenant.

Requête N+1

Une requête pour la liste, puis une par élément. Invisible dans le code, très visible dans pg_stat_statements : une requête minuscule appelée des dizaines de milliers de fois.

Lire l'explication détaillée et faire le quiz →

Cette page présente la partie essentielle de la leçon. Les exemples commentés, les pièges courants et les trois questions de quiz sont dans l'application.