PostgreSQL EXPLAIN ANALYZE : comprendre les plans de requêtes

PostgreSQL EXPLAIN présente le plan choisi pour une requête. EXPLAIN ANALYZE l’exécute et ajoute les observations de cette exécution. Lire le plan permet de distinguer une estimation de lignes inexacte, du travail répété et un accès coûteux avant de choisir une modification.

Ce guide utilise une table jetable sur PostgreSQL 18.6. Nous comparons une requête sélective avant et après l’ajout d’un index, puis modifions le filtre et examinons un sous-plan répété. Les résultats décrivent des observations sur des données synthétiques, sans promesse de performance en production.

Choisir une requête et délimiter le test

Partez d’observations de la charge, par exemple avec pg_stat_statements, pour sélectionner une requête utile à examiner. Relevez ses vrais paramètres, son rôle et son contexte de base. Un paramètre inventé peut produire un plan peu pertinent pour la demande lente.

Utilisez une base jetable distincte. Notre guide de l’hébergement PostgreSQL (en anglais) distingue les responsabilités d’un service géré de celles d’un serveur administré par vos soins. Ne copiez pas les commandes de création ou de nettoyage dans une base de production.

La référence EXPLAIN précise qu’ANALYZE exécute l’instruction. Les résultats d’un SELECT ne sont pas renvoyés, mais son exécution et ses effets ont lieu. Un rollback n’annule pas tout : les avancées de séquences PostgreSQL ne sont pas annulées. Examinez les fonctions et effets externes avant d’analyser du SQL inconnu.

Créer un jeu de données reproductible

Connectez-vous directement avec psql à une nouvelle base de test. Gardez la session ouverte et vérifiez son identité avant d’appliquer ces paramètres locaux au laboratoire :

SELECT version(), current_database(), current_user;
SET statement_timeout = '10s';
SET lock_timeout = '2s';
SET max_parallel_workers_per_gather = 0;

Les délais limitent les instructions et les attentes de verrous pour cet exercice ; ce ne sont pas des recommandations de production. La documentation des délais PostgreSQL en précise la portée. Les workers parallèles sont désactivés pour simplifier la lecture des plans, pas pour accélérer les requêtes.

Arrêtez-vous si plan_lab existe déjà. Créez une seule fois ce jeu de données dans la base jetable :

CREATE SCHEMA plan_lab;
CREATE TABLE plan_lab.events (
    id integer PRIMARY KEY,
    account_id integer NOT NULL,
    amount integer NOT NULL,
    note text NOT NULL
);
INSERT INTO plan_lab.events
SELECT n, n % 1000, n % 100, repeat('x', 80)
FROM generate_series(1, 200000) AS g(n);
ANALYZE plan_lab.events;

La table contient 200 000 lignes et 1 000 identifiants de compte, soit 200 lignes par compte. La clé primaire indexe id ; aucun index ne porte encore sur account_id. La commande ANALYZE collecte les statistiques du planificateur. Elle diffère de l’option ANALYZE d’EXPLAIN, qui exécute la requête.

Lire d’abord le plan estimé

EXPLAIN
SELECT id, amount
FROM plan_lab.events
WHERE account_id = 42;

Notre sortie EXPLAIN était la suivante :

Seq Scan on events  (cost=0.00..5577.00 rows=199 width=8)
  Filter: (account_id = 42)

Le planificateur choisit un parcours séquentiel et estime 199 lignes en sortie. width=8 indique la largeur moyenne estimée d’une ligne de sortie, en octets. Les deux valeurs cost représentent le coût de démarrage et le coût total du plan, pas des millisecondes. Ces unités dépendent des paramètres de coût.

rows désigne les lignes émises par le nœud, pas toutes celles qu’il examine. Un petit résultat estimé peut donc exiger le parcours de toute la table. Un parent comme LIMIT peut aussi cesser de demander des lignes avant que l’enfant termine son travail estimé.

Comparer les estimations aux lignes observées

BEGIN READ ONLY;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SETTINGS)
SELECT id, amount
FROM plan_lab.events
WHERE account_id = 42;
ROLLBACK;

La transaction en lecture seule ajoute une limite au SELECT mesuré. Elle ne supprime ni l’utilisation du processeur, ni les entrées-sorties, ni les verrous. Le laboratoire a aussi confirmé que le mode lecture seule rejette un UPDATE analysé sur cette table. Les plans SELECT mesurés utilisaient un rôle disposant uniquement du droit SELECT.

Cet extrait reprend les lignes du nœud et des buffers de notre première capture texte ; les paramètres et les résumés de temps sont omis :

Seq Scan on events  (cost=0.00..5577.00 rows=199 width=8) (actual rows=200.00 loops=1)
  Filter: (account_id = 42)
  Rows Removed by Filter: 199800
  Buffers: shared hit=3077

L’estimation de 199 était proche des 200 lignes observées. Le nœud a parcouru la table et rejeté 199 800 lignes par le filtre. Le chemin d’accès constitue donc la piste utile dans cet exemple ; une forte erreur d’estimation n’était pas le problème.

TIMING OFF retire les mesures d’horloge par nœud tout en conservant les nombres de lignes réels et le temps global d’exécution. SETTINGS affiche les paramètres pertinents du planificateur qui diffèrent des valeurs par défaut. Nous avons conservé les captures texte et JSON complètes ; l’article privilégie les comptes et la structure plutôt qu’un ratio de temps.

Modifier une seule chose : ajouter un index de compte

Terminez la transaction en lecture seule, puis créez cet index dans la base de test :

CREATE INDEX events_account_idx ON plan_lab.events (account_id);

Relancez le même bloc EXPLAIN ANALYZE. Notre plan est devenu :

Bitmap Heap Scan on events  (cost=5.84..635.32 rows=199 width=8) (actual rows=200.00 loops=1)
  Recheck Cond: (account_id = 42)
  Heap Blocks: exact=200
  Buffers: shared hit=200 read=2
  ->  Bitmap Index Scan on events_account_idx  (cost=0.00..5.79 rows=199 width=0) (actual rows=200.00 loops=1)
        Index Cond: (account_id = 42)
        Index Searches: 1
        Buffers: shared read=2

Le Bitmap Index Scan trouve les entrées correspondantes dans events_account_idx. Le Bitmap Heap Scan récupère les lignes de la table. Le nœud supérieur émet toujours 200 lignes ; une comparaison séparée des résultats triés a confirmé leur égalité avant et après la création de l’index.

L’observation montre que l’index a changé le chemin d’accès de cette requête. Elle ne prouve pas que toutes les requêtes sont devenues plus rapides. Un index occupe de l’espace et ajoute du travail aux écritures. Un CREATE INDEX ordinaire implique aussi des verrous sur une table active. Son déploiement en production exige un examen distinct.

Lire les buffers sans compter deux fois

Sans index, notre capture indique 3 077 accès satisfaits par les buffers partagés. Avec l’index, le parent indique 200 hits et deux lectures. Un hit signifie que le bloc était déjà dans les buffers partagés de PostgreSQL. Une lecture signifie qu’il a fallu l’y charger ; le système d’exploitation pouvait encore le conserver en cache.

Les compteurs du parent incluent ceux des enfants. Ajouter les deux lectures de l’enfant aux deux du parent compterait le même travail deux fois. Les compteurs représentent des accès, pas un nombre de blocs physiques distincts. Séparez aussi les buffers de planification de l’arbre d’exécution.

Le guide de lecture des plans explique ces statistiques inclusives. Remontez des sources de lignes vers les nœuds qui les consomment, puis cherchez où le travail augmente ou où les estimations divergent. Additionner tous les temps des parents et des enfants ne donne pas le temps total de la requête.

Tester un filtre plus large

BEGIN READ ONLY;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SETTINGS)
SELECT id, amount
FROM plan_lab.events
WHERE account_id < 900;
ROLLBACK;

L’index était toujours présent, mais cette requête a émis 180 000 lignes avec un parcours séquentiel. L’estimation était de 179 403 lignes. Le filtre sélectionne 90 % du jeu de données : parcourir un index puis récupérer les lignes dispersées pose un autre compromis que rechercher un seul compte.

Un parcours séquentiel ne suffit pas à diagnostiquer un problème. Les conseils PostgreSQL sur les index recommandent des tests avec des données et statistiques représentatives. Ne désactivez pas les parcours séquentiels simplement pour faire apparaître un index dans le plan.

Lire les lignes avec le nombre de boucles

Cette sous-requête corrélée rend visible le travail répété :

BEGIN READ ONLY;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SETTINGS)
SELECT a.account_id,
       (SELECT sum(e.amount)
        FROM plan_lab.events AS e
        WHERE e.account_id = a.account_id) AS total_amount
FROM (VALUES (42), (43), (44)) AS a(account_id);
ROLLBACK;

Notre capture texte contient :

Values Scan on "*VALUES*"  (cost=0.00..1916.19 rows=3 width=12) (actual rows=3.00 loops=1)
  Buffers: shared hit=606
  SubPlan 1
    ->  Aggregate  (cost=638.71..638.72 rows=1 width=8) (actual rows=1.00 loops=3)
          Buffers: shared hit=606
          ->  Bitmap Heap Scan on events e  (cost=5.84..638.21 rows=200 width=4) (actual rows=200.00 loops=3)
                Recheck Cond: (account_id = "*VALUES*".column1)
                Heap Blocks: exact=600
                Buffers: shared hit=606
                ->  Bitmap Index Scan on events_account_idx  (cost=0.00..5.79 rows=200 width=0) (actual rows=200.00 loops=3)
                      Index Cond: (account_id = "*VALUES*".column1)
                      Index Searches: 3
                      Buffers: shared hit=6

Le Bitmap Heap Scan interne indique actual rows=200.00 loops=3 : une moyenne de 200 lignes émises par exécution, soit 600 sur les trois exécutions. Aggregate émet une ligne par exécution, tandis que Values Scan en émet trois. Ces nombres décrivent des positions différentes dans le même arbre.

Les 606 hits de buffers couvrent déjà le travail répété ; ne les multipliez pas par trois. Les moyennes par boucle, les compteurs cumulés de buffers et les statistiques inclusives des parents demandent des lectures différentes. L’exemple les distingue sans imposer une stratégie de jointure.

Examiner le premier écart significatif

Si les lignes estimées et réelles divergent fortement, remontez depuis les feuilles pour localiser le premier écart important. Vérifiez la fraîcheur des statistiques, les distributions déséquilibrées et les filtres corrélés avant de modifier la mémoire ou les options du planificateur. La documentation des statistiques de planification explique les limites des statistiques par colonne. Une augmentation de leur précision ou l’ajout de statistiques étendues doit répondre à une observation précise.

Reproduisez aussi le paramétrage de l’application. PostgreSQL peut choisir un plan préparé générique ou personnalisé ; une requête littérale copiée dans psql peut suivre un autre plan. Notre guide PgBouncer (en anglais) couvre les contraintes distinctes des sessions et requêtes préparées lors du pooling.

Testez ensuite une seule modification, conservez des résultats équivalents et répétez dans des conditions comparables. Le temps EXPLAIN ne mesure pas la latence applicative complète : transfert réseau et traitement applicatif en sont exclus, et le comportement par défaut ne sérialise pas le résultat écarté. Les exécutions répétées alimentent aussi les caches. Conservez versions, paramètres, statistiques et conditions de cache avec le plan.

Nettoyer le jeu de données

DROP TABLE plan_lab.events;
DROP SCHEMA plan_lab;
RESET statement_timeout;
RESET lock_timeout;
RESET max_parallel_workers_per_gather;

Exécutez ce nettoyage uniquement dans la même base jetable. Il supprime la table et le schéma nommés, puis réinitialise les paramètres de session. Notre laboratoire PostgreSQL 18.6 a passé dix contrôles portant sur les lignes, les chemins d’accès, l’égalité des résultats, les sous-plans répétés, la lecture seule et le nettoyage. Le serveur a ensuite été arrêté. Aucun débit de production, déploiement cloud, comportement de plan préparé ni charge concurrente n’a été testé.