Requêtes lentes PostgreSQL : causes, détection et correction

Qu'est-ce qu'une requête lente, exactement ?

Il n'existe pas de seuil universel : une requête de 200 ms peut être parfaitement normale pour un traitement analytique nocturne, et catastrophique pour un endpoint appelé à chaque clic d'un utilisateur. Ce qui définit une requête lente, c'est l'écart entre son temps d'exécution habituel et son temps d'exécution constaté à un instant donné — pas une valeur absolue figée dans le temps.

Deux angles complémentaires permettent de la repérer : le temps déjà écoulé pour une requête encore en cours d'exécution (utile pour détecter un blocage en direct), et le temps total moyen d'exécution d'une requête une fois terminée, agrégé sur de nombreux appels (utile pour repérer une dérive progressive plutôt qu'un incident isolé).

Les causes les plus fréquentes

Un index manquant ou inutilisé reste la cause la plus courante : PostgreSQL doit alors parcourir (seq scan) une grande partie de la table au lieu de cibler directement les lignes recherchées. Un tri (ORDER BY) sur une colonne non indexée aggrave encore le problème, en particulier combiné à un filtre sur une autre colonne — c'est exactement le scénario détaillé dans notre étude de cas de diagnostic en 5 minutes.

Les statistiques du planificateur obsolètes (autovacuum en retard ou désactivé) sont une seconde cause fréquente : PostgreSQL choisit son plan d'exécution à partir d'estimations de cardinalité — si ces estimations sont fausses, il peut choisir un scan complet là où un index aurait été bien plus rapide.

Viennent ensuite : un verrou détenu par une autre transaction (la requête n'est pas lente, elle attend), un work_mem insuffisant qui force un tri ou un hash join à déborder sur le disque temporaire, et une explosion du volume de données traitées par une requête qui restait rapide tant que la table était petite.

Détecter les requêtes lentes avec pg_stat_statements

L'extension pg_stat_statements agrège, pour chaque requête normalisée (les valeurs littérales sont remplacées par des paramètres), le nombre d'appels, le temps d'exécution cumulé et moyen, les lignes retournées et les blocs lus depuis le cache ou le disque. C'est la source de données de référence pour identifier, sur la durée, quelles requêtes coûtent le plus cher à votre instance.

La requête ci-dessous classe les requêtes par temps moyen d'exécution décroissant — un bon point de départ pour toute investigation de lenteur.

SELECT query, calls, mean_exec_time, total_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Lire un plan d'exécution avec EXPLAIN ANALYZE

Une fois la requête suspecte identifiée, EXPLAIN (ANALYZE, BUFFERS) l'exécute réellement et affiche le plan choisi par PostgreSQL ainsi que le temps effectif de chaque étape (scan, tri, jointure) et les blocs lus en cache ou sur disque. C'est l'outil qui confirme — ou infirme — l'hypothèse d'un index manquant.

Les signaux à surveiller dans la sortie : un Seq Scan sur une grande table alors qu'un filtre sélectif est appliqué, un écart important entre les lignes estimées (rows=) et les lignes réelles retournées (signe de statistiques obsolètes), et un tri ou un hash join qui bascule sur disque ("external merge" ou "Disk" dans la sortie) faute de work_mem suffisant.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM pgbench_accounts
WHERE aid <= 1000
ORDER BY abalance;

Comment PWR identifie automatiquement les requêtes lentes

Plutôt que d'attendre qu'un utilisateur signale une lenteur, PWR surveille en continu deux niveaux : en direct, le Live Health Banner compte les requêtes actives dont le temps déjà écoulé dépasse 1, 5 ou 10 secondes ; sur la durée, chaque snapshot capture l'état de pg_stat_statements et permet de comparer deux instants pour isoler précisément quelle requête a commencé à dériver.

Le rapport PWR classe automatiquement les requêtes par temps d'exécution, par consommation CPU et par volume d'appels, et propose un historique par requête (Query History) qui montre son évolution snapshot après snapshot — de quoi dater exactement l'apparition d'une régression, sans avoir à rejouer manuellement des EXPLAIN ANALYZE un par un.

Corriger une requête lente : la méthode courte

Dans l'ordre : confirmer la cause avec EXPLAIN ANALYZE, vérifier si un index composite adapté au filtre et au tri résoudrait le scan complet, s'assurer que les statistiques sont à jour (ANALYZE manuel si l'autovacuum est en retard), puis ajuster work_mem si un tri ou une jointure déborde sur disque. Chaque changement doit être revérifié avec un nouveau plan d'exécution pour confirmer le gain réel.

C'est exactement la méthode appliquée dans notre étude de cas concrète : un index composite bien choisi a fait chuter la charge CPU de 32% à 8% en quelques minutes.