Un guide systématique pour déboguer les requêtes PostgreSQL lentes

Les requêtes PostgreSQL lentes sont plus faciles à corriger lorsque vous cessez de les traiter comme des mystères. La base de données peut généralement vous montrer le chemin qu'elle a choisi, le nombre de lignes qu'elle attendait, le nombre de lignes qu'elle a réellement touchées, si elle a lu depuis le cache ou le disque, et si elle a attendu une autre session.

L'erreur que je vois le plus souvent est de passer directement de "ce point de terminaison est lent" à "créer un index". Parfois, cela fonctionne. Parfois, l'index est ignoré car le prédicat est écrit d'une manière qui ne peut pas l'utiliser. Parfois, la requête est correcte mais bloquée derrière une transaction ouverte depuis vingt minutes. Une approche systématique fait gagner du temps car elle sépare la forme de la requête, les estimations du planificateur, les E/S, la mémoire et le verrouillage.

Comprendre les goulots d'étranglement des performances des requêtes

Avant de plonger dans les outils, il est essentiel de reconnaître les raisons courantes pour lesquelles une requête PostgreSQL peut avoir de mauvaises performances. Ces problèmes se répartissent généralement en quelques catégories clés :

• Index manquants ou inefficaces : La base de données est obligée d'effectuer des analyses séquentielles sur de grandes tables alors qu'un index aurait pu fournir un accès rapide.
• Structure de requête sous-optimale : Des jointures complexes, des sous-requêtes inutiles ou une mauvaise utilisation des fonctions peuvent confondre le planificateur.
• Statistiques obsolètes : PostgreSQL s'appuie sur des statistiques pour construire des plans d'exécution efficaces. Si les statistiques sont obsolètes, le planificateur peut choisir un chemin inefficace.
• Contention de ressources : Problèmes tels que des temps d'attente E/S élevés, un verrouillage excessif ou une mémoire insuffisante allouée à PostgreSQL.

Étape 1 : Identifier la requête lente

Avant de pouvoir corriger une requête lente, vous devez l'identifier avec précision. Se fier aux plaintes des utilisateurs est inefficace ; vous avez besoin de données empiriques provenant de la base de données elle-même.

Utilisation de pg_stat_statements

La méthode la plus efficace pour suivre les requêtes gourmandes en ressources dans un environnement de production consiste à utiliser l'extension pg_stat_statements. Ce module suit les statistiques d'exécution pour toutes les requêtes exécutées sur la base de données.

Activation de l'extension (nécessite des privilèges de superutilisateur et un rechargement de la configuration) :

-- 1. Assurez-vous qu'elle est listée dans postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'

-- 2. Connectez-vous à la base de données et créez l'extension
CREATE EXTENSION pg_stat_statements;

Interrogation des principaux contrevenants :

Pour trouver les requêtes consommant le plus de temps total, utilisez la requête suivante :

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

Sur les anciennes versions de PostgreSQL, ces colonnes peuvent être nommées total_time et mean_time. Utilisez les noms exposés par votre serveur.

Le temps total et le temps moyen répondent à des questions différentes. Une requête qui prend en moyenne 20 millisecondes mais s'exécute un million de fois peut être le plus grand coût de votre base de données. Une requête qui s'exécute une fois par heure pendant 40 secondes peut être pénible pour un utilisateur mais moins importante pour l'ensemble du système. Regardez les deux.

Si vous avez besoin de la requête actuellement lente, et non de celle historiquement coûteuse, vérifiez les sessions actives :

SELECT pid, now() - query_start AS age, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

Étape 2 : Analyser le plan d'exécution avec EXPLAIN ANALYZE

Une fois qu'une requête lente est isolée, l'étape critique suivante consiste à comprendre comment PostgreSQL l'exécute. La commande EXPLAIN montre le plan prévu, mais EXPLAIN ANALYZE exécute réellement la requête et rapporte le temps réel pris pour chaque étape.

Syntaxe et utilisation

Enveloppez toujours votre requête lente avec EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) pour la sortie la plus détaillée. L'option BUFFERS est cruciale car elle montre l'activité d'E/S disque.

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * 
FROM large_table lt 
JOIN other_table ot ON lt.id = ot.lt_id
WHERE lt.status = 'active' AND lt.created_at > NOW() - INTERVAL '1 day';

Interprétation de la sortie

La sortie se lit de bas en haut et de droite à gauche, car les nœuds les plus internes sont exécutés en premier. Les métriques clés sur lesquelles se concentrer incluent :

1. cost= : Le coût estimé par le planificateur, pas le temps réel. Utilisez-le pour comparer les choix de plan, pas comme des millisecondes.
2. rows= : Le nombre estimé de lignes traitées par ce nœud.
3. actual time= : Le temps réel passé en millisecondes sur cette opération spécifique.
4. rows= (Réel) : Le nombre réel de lignes retournées par ce nœud.
5. loops= : Combien de fois ce nœud a été exécuté (souvent élevé dans les boucles imbriquées).

Repérer les inefficacités :

• Analyses séquentielles sur de grandes tables : Si l'accès à une grande table utilise Seq Scan au lieu d'un Index Scan ou Bitmap Index Scan, vous avez probablement besoin d'un meilleur index.
• Grande divergence entre les lignes estimées et réelles : Si le planificateur a estimé 10 lignes mais que le nœud en a réellement traité 1 000 000, les statistiques sont obsolètes ou le planificateur a fait un mauvais choix.
• actual time élevé sur les jointures/tris : Un temps excessif passé dans les opérations Hash Join, Merge Join ou Sort indique souvent une mémoire insuffisante (work_mem) ou une incapacité à utiliser efficacement les index.

Surveillez également les lignes Buffers. shared hit signifie que PostgreSQL a trouvé des pages dans le cache. shared read signifie qu'il a dû lire des pages depuis le stockage. Une requête peut être lente parce que le plan est mauvais, ou parce que le plan est raisonnable mais qu'il lit une grande quantité de données froides depuis le disque.

Astuce : Pour les plans complexes, utilisez des outils en ligne comme explain.depesz.com ou le visualiseur de plan d'explication graphique de pgAdmin pour interpréter les résultats graphiquement.

Étape 3 : Résoudre les goulots d'étranglement courants

En fonction de vos conclusions EXPLAIN ANALYZE, appliquez des correctifs ciblés.

Optimisation des index

Si Seq Scan domine une grande table et que la requête est sélective, envisagez des index sur les colonnes utilisées dans les clauses WHERE, JOIN et ORDER BY. Une analyse séquentielle n'est pas automatiquement mauvaise ; PostgreSQL peut la choisir correctement lorsque la plupart des lignes sont nécessaires.

Exemple : Si la requête filtre par status puis joint sur user_id :

-- Créez un index composé pour des recherches et jointures plus rapides
CREATE INDEX idx_large_table_status_user_id ON large_table (status, user_id);

Pour les systèmes de production, utilisez CREATE INDEX CONCURRENTLY lorsque vous devez éviter de bloquer les écritures, et rappelez-vous qu'il ne peut pas s'exécuter dans un bloc de transaction normal :

CREATE INDEX CONCURRENTLY idx_large_table_status_user_id
ON large_table (status, user_id);

Mise à jour des statistiques (VACUUM ANALYZE)

Si le planificateur fait des estimations très inexactes (décalage entre les lignes estimées et réelles), forcez une mise à jour des statistiques de la table.

ANALYZE VERBOSE table_name;
-- Pour les tables très actives, envisagez des seuils d'autovacuum/analyse par table plus bas.

Réglage de la mémoire

Si les opérations de tri ou de hachage débordent sur le disque (souvent indiqué par des E/S élevées dans la sortie BUFFERS ou un tri lent), augmentez la mémoire de travail disponible de PostgreSQL.

-- Augmentez work_mem au niveau de la session pour le test de requête spécifique
SET work_mem = '128MB'; 
-- Ou globalement dans postgresql.conf pour des améliorations de performances durables

Avertissement : Augmenter work_mem globalement trop haut peut épuiser la mémoire système si de nombreuses requêtes complexes s'exécutent simultanément. Réglez cela avec soin en fonction de la capacité du serveur.

Recherchez les détails de débordement de tri ou de hachage dans le plan. La sortie PostgreSQL plus récente peut montrer des méthodes de tri telles que external merge Disk: ..., ce qui est un signe clair que l'opération a dépassé la mémoire disponible et a écrit des fichiers temporaires.

Réécriture de requête

Parfois, la structure elle-même est le problème. Évitez les prédicats non SARGables (conditions qui empêchent l'utilisation de l'index), comme l'application de fonctions aux colonnes indexées dans la clause WHERE :

Inefficace (empêche l'utilisation de l'index) :

WHERE DATE(created_at) = '2023-10-01'

Efficace (permet l'utilisation de l'index) :

WHERE created_at >= '2023-10-01 00:00:00' AND created_at < '2023-10-02 00:00:00'

Un autre modèle courant consiste à sélectionner beaucoup plus de colonnes que l'application n'en a besoin. SELECT * rend les plans plus difficiles à optimiser, augmente l'utilisation de la mémoire et peut forcer des lectures de tas supplémentaires alors qu'une analyse d'index uniquement pourrait autrement fonctionner. Pour les chemins chauds, listez les colonnes délibérément.

Vérifications de verrouillage

Si EXPLAIN ANALYZE est rapide dans votre session mais que l'application est lente, la requête peut attendre avant même d'obtenir un temps d'exécution utile. Vérifiez les attentes de verrouillage :

SELECT pid, wait_event_type, wait_event, now() - query_start AS age, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

Ensuite, trouvez le bloqueur :

SELECT blocked.pid AS blocked_pid,
       blocker.pid AS blocker_pid,
       blocked.query AS blocked_query,
       blocker.query AS blocker_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks
  ON blocked_locks.pid = blocked.pid AND NOT blocked_locks.granted
JOIN pg_locks blocker_locks
  ON blocker_locks.locktype = blocked_locks.locktype
 AND blocker_locks.database IS NOT DISTINCT FROM blocked_locks.database
 AND blocker_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocker_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 AND blocker_locks.pid <> blocked_locks.pid
JOIN pg_stat_activity blocker
  ON blocker.pid = blocker_locks.pid;

La correction peut être au niveau de l'application : transactions plus courtes, déplacement des appels API externes lents en dehors de la transaction, évitement de SELECT ... FOR UPDATE inutile, ou changement de l'ordre dans lequel les tables sont mises à jour afin que les transactions concurrentes ne se bloquent pas mutuellement.

Un petit exemple : La requête lente du tableau de bord

Supposons qu'un tableau de bord exécute cette requête toutes les quelques secondes :

SELECT *
FROM orders
WHERE DATE(created_at) = CURRENT_DATE
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

La table a des millions de lignes. EXPLAIN ANALYZE montre une analyse séquentielle, un grand nombre de lignes supprimées par le filtre, et un tri. Le premier réflexe pourrait être d'indexer created_at, mais le prédicat enveloppe la colonne dans DATE(created_at), donc un index normal sur created_at est moins utile.

Réécrivez le filtre de date comme une plage :

SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day'
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Ensuite, envisagez un index qui correspond au filtre et au tri :

CREATE INDEX CONCURRENTLY idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'paid';

Ce n'est pas une recette d'index universelle. Cela fonctionne lorsque paid est un filtre courant du tableau de bord et lorsque les dernières commandes payées sont ce que l'application demande habituellement. Si l'application filtre également beaucoup par compte, le meilleur index pourrait commencer par account_id. Le but est de concevoir l'index autour du modèle d'accès réel, pas autour d'une seule colonne mentionnée dans la requête.

Après le changement, le plan devrait montrer moins de lignes analysées et idéalement éviter un tri explicite. Si le plan choisit toujours une analyse séquentielle, vérifiez si la plage de dates est trop large, si les statistiques sont obsolètes, ou si les paramètres de requête en production diffèrent de votre cas de test.

Étape 4 : Vérification et surveillance

Après avoir implémenté un changement, réexécutez EXPLAIN ANALYZE sur la même requête avec des paramètres comparables. L'objectif n'est pas toujours de voir une analyse d'index. L'objectif est de voir moins de travail : moins de lignes supprimées par les filtres, moins de tampons lus, pas de débordement disque, de meilleures estimations de lignes, ou moins de temps passé dans le nœud coûteux.

Continuez à surveiller pg_stat_statements pour confirmer que la requête modifiée n'apparaît plus dans la liste des principaux contrevenants, garantissant que le correctif a un impact global positif.

Surveillez également le coût d'écriture après l'ajout d'index. Chaque nouvel index doit être maintenu lors des insertions, mises à jour et suppressions. Un index de lecture parfait pour un tableau de bord peut être un mauvais compromis s'il ralentit un chemin d'ingestion à volume élevé. Pour les tables importantes, vérifiez les deux côtés : la requête lente s'est-elle améliorée, et la latence d'écriture ou le gonflement de la table se sont-ils aggravés par la suite ?

Une autre habitude aide lors des incidents réels : testez avec des valeurs de paramètres réalistes. PostgreSQL peut choisir différents plans pour un client avec dix lignes et un client avec dix millions de lignes. Si l'application utilise des instructions préparées, les plans génériques peuvent également se comporter différemment de la requête unique que vous collez dans psql. Lorsque le problème de production affecte un locataire, un compte ou une plage de dates, reproduisez cette forme aussi fidèlement que possible dans un environnement sûr.

Si la requête est destructive ou trop coûteuse à exécuter avec EXPLAIN ANALYZE, commencez par un simple EXPLAIN, exécutez-la sur un environnement de staging, ou enveloppez le test dans une transaction que vous annulez. Pour UPDATE et DELETE, vous pouvez encore apprendre beaucoup de la partie analyse et jointure du plan avant d'apporter toute modification en production.

Gardez une courte note avec le plan avant et après, le timing et la raison du changement. Cette habitude évite les régressions accidentelles plus tard et donne à la personne suivante une explication réelle au lieu d'un nom d'index mystérieux dans le schéma.