Sortie SQL EXPLAIN N'a pas besoin de ressembler à du texte alien

Vous exécutez EXPLAIN sur une requête lente. La base de données vous renvoie quelque chose de type :

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000

Vous regardez cela pendant dix secondes. Vous ajoutez un index au hasard. Vous redémarrez l'application et vous espérez. Ce n'est pas une stratégie de débogage — c'est de la superstition avec des étapes supplémentaires.

EXPLAIN vous dit en réalité quelque chose de précis et d'actif. Voici comment le lire.

EXPLAIN vs EXPLAIN ANALYZE : Quelle option utiliser

Plaine EXPLAIN vous montre ce que le planificateur intend de faire. Il ne exécute pas la requête — il affiche simplement le plan avec des coûts estimés. Rapide, mais les estimations peuvent être erronées.

EXPLAIN ANALYZE exécute réellement la requête et ajoute les temps réels. Vous obtenez à la fois le plan et les données réelles :

-- PostgreSQL: run the query and show real timings
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

-- MySQL equivalent
EXPLAIN ANALYZE SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

Le BUFFERS option est disponible uniquement dans PostgreSQL et ajoute les comptes de touches/buts dans le tampon — utile pour diagnostiquer les problèmes d'E/S, mais ignorez-la pour l'instant.

Une réserve : EXPLAIN ANALYZE exécute la requête en réalité. Sur un DELETE ou UPDATE, enveloppez-la dans une transaction et annulez-la :

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'pending';
ROLLBACK;

Les nombres de coût : ce qu'ils signifient et ce qu'ils ne signifient pas

Chaque nœud du plan montre (cost=X..Y rows=N width=W). Les gens voient ces nombres et supposent qu'ils sont en millisecondes. Ce n'est pas le cas.

• coût=X..Y — X est le coût de démarrage (travail effectué avant la première ligne renvoyée), Y est le coût total pour traiter toutes les lignes. L'unité est arbitraire « coût de page » — proportionnel aux lectures de pages de disque, mais calibré par les constantes de coût de PostgreSQL. Un coût de 1,0 correspond à une lecture séquentielle d'une page.
• lignes=N — le nombre d'éléments estimé par le planificateur. Peut être très erroné si les statistiques des tables sont obsolètes.
• largeur=W — taille moyenne d'une ligne en octets. Des lignes plus larges ralentissent les jointures et les triages.

Lorsque vous exécutez EXPLAIN ANALYZE, chaque nœud obtient également (actual time=X..Y rows=N loops=L). Ces sont en millisecondes. Un écart important entre les estimations et les valeurs réelles est le signal le plus fiable selon lequel le planificateur a fait une mauvaise décision. millisecondes. Un grand écart entre les lignes estimées et les lignes réelles est le signal le plus fiable selon lequel le planificateur a fait une mauvaise décision.

Une analyse détaillée d'EXPLAIN ANALYZE annotée

Voici la même requête de l'introduction, cette fois avec ANALYZE et chaque ligne expliquée :

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
           (actual time=2.451..12.879 rows=3168 loops=1)
-- Top-level node. Joins orders and customers via hashing.
-- cost estimate: 145..578 | actual: 2.4ms startup, 12.9ms total
-- rows estimate: 3200 | actual: 3168 — pretty close here

  Hash Cond: (o.customer_id = c.id)
  -- This is the join key. The planner built a hash table on customers.id
  -- then probed it with every row from orders.

  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
                          (actual time=0.019..3.125 rows=16000 loops=1)
  -- Full table scan on orders. 16,000 rows scanned.
  -- No filter here — we pull all orders and join them below.
  -- This is expected: we need all orders, so no index would help here.

  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
            (actual time=2.412..2.413 rows=1000 loops=1)
            Buckets: 1024  Batches: 1  Memory Usage: 64kB
  -- Builds the in-memory hash table from the customers result.
  -- 1 batch = fits in work_mem. Multiple batches = spilling to disk (bad).

        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
                                   (actual time=0.012..1.345 rows=1000 loops=1)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000
        -- Scanned all 10,000 customer rows. Kept 1,000 where country='US'.
        -- *** This is the expensive part. An index on customers.country
        --     would let PostgreSQL skip the 9,000 discarded rows entirely. ***

Planning Time: 0.187 ms
Execution Time: 13.451 ms

L'action clé apparaît immédiatement : Rows Removed by Filter: 9000 sur une lecture complète de la table est le modèle le plus courant pour « vous avez besoin d'un index ici. »

Les types de nœuds que vous rencontrerez réellement

Seq Scan

Lit chaque ligne de la table, dans l'ordre de la pile. Les gens voient cela et veulent immédiatement ajouter un index. Mais un Seq Scan n'est pas toujours mauvais — si votre filtre correspond à 20%+ des lignes, le planificateur décide correctement que des recherches aléatoires via un index seraient plus lentes. Un Seq Scan sur une table de 500 lignes est acceptable. Un Seq Scan sur une table de 10 millions de lignes avec un filtre très sélectif est un problème.

Index Scan

Utilise une index B-tree pour rechercher les lignes correspondantes, puis récupère chaque ligne à partir de la pile. La récupération de la pile est la partie coûteuse — chaque ligne est une lecture aléatoire. Bonne lorsque le filtre est sélectif (c'est-à-dire que vous récupérez une petite fraction des lignes).

Index Only Scan

La requête peut être résolue entièrement à partir de l'index sans toucher la pile. Cela suppose que chaque colonne dans votre SELECT et WHERE clause est couverte par l'index. Type le plus rapide de lecture — si vous voyez cela, la conception de l'index fonctionne bien.

Bitmap Heap Scan

Un compromis. Le planificateur construit un bitmap des pages de la pile contenant les lignes correspondantes (via Bitmap Index Scan), puis récupère uniquement ces pages dans l'ordre, réduisant les lectures aléatoires. Courant lorsque l'Index Scan aurait trop de lectures aléatoires mais que le Seq Scan aurait lu trop de lignes inutiles.

Hash Join

Construit une table de hachage à partir de l'entrée plus petite, puis la scanne avec chaque ligne de l'entrée plus grande. Bon pour de grands joints sans ordre de tri utile. Regardez pour Batches: > 1 dans le nœud Hash — cela signifie que la table de hachage a débordé sur le disque parce qu'elle a dépassé work_mem. Augmenter work_mem pour cette session corrige souvent ce problème.

Nested Loop

Pour chaque ligne du côté externe, scanne le côté interne (généralement via un index). Excellent lorsque l'un des côtés est petit — O(n) au lieu de O(n log n). Terrible lorsque les deux côtés sont grands, car il exécute le scan interne une fois par ligne externe. Si vous voyez un Nested Loop avec loops=50000, ce scan interne est exécuté 50 000 fois.

Merge Join

Les deux entrées arrivent triées sur la clé de jointure ; le planificateur les parcourt en parallèle. Éfficace mais nécessite le tri au départ. Vous verrez cela lorsque les deux côtés ont déjà un index sur la clé de jointure, ou lorsque le planificateur décide qu'un nœud de tri est plus économique que le hachage.

Pourquoi le planificateur choisit un Seq Scan au lieu d'un Index Scan

Cela confond les gens le plus souvent. Vous avez un index. La requête est lente. EXPLAIN montre un Seq Scan tout de même.

Le planificateur utilise les statistiques des colonnes — stockées dans pg_statistic, mises à jour par ANALYZE — pour estimer le nombre de lignes que le filtre retournera. Si l'estimation est de 30% de la table, un Seq Scan est vraiment plus économique que 300 000 recherches aléatoires dans un index. Les lectures aléatoires sont coûteuses.

Le seuil où le planificateur préfère un Seq Scan est d'environ 10 à 20% de sélectivité, selon votre matériel et les constantes de coût. Vous pouvez vérifier l'estimation du planificateur par rapport à la réalité :

-- Check the planner's selectivity estimate
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Compare with actual count
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders;

Si le planificateur estime 8 000 lignes mais qu'il n'y en a que 80, c'est un problème de statistiques obsolètes. Exécutez ANALYZE orders; et vérifiez à nouveau le plan. Cela corrige souvent des mauvais plans plutôt que d'ajouter des index.

Lecture de l'output EXPLAIN au format JSON

PostgreSQL peut afficher le plan au format JSON, ce qui est plus facile à parser ou à explorer dans une vue arborescente :

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

L'output JSON est dense. Si vous le collez dans IO Tools’ JSON Formatter, il devient une arborescence navigable — utile lorsqu'on gère des plans profondément imbriqués provenant de requêtes complexes avec plusieurs sous-requêtes ou CTEs.

Un workflow pratique de débogage

Lorsqu'une requête est lente, suivez cette séquence plutôt que de deviner :

1. Exécutez EXPLAIN (ANALYZE, BUFFERS) sur la requête exacte avec des paramètres réels de production (pas $1 de placehouders — utilisez les valeurs réelles).
2. Trouvez le nœud le plus coûteux. Recherchez le plus haut actual time, pas le plus haut coût estimé. Ces valeurs peuvent différer.
3. Comparez les lignes estimées et réelles à ce nœud. Un écart de 10x ou plus signifie que le planificateur travaillait avec des informations mauvaises. Exécutez d'abord ANALYZE <table>; .
4. Recherchez des Seq Scans sur de grandes tables avec des filtres sélectifs. Rows Removed by Filter: <large number> directement en dessous d'un Seq Scan est votre candidat à l'index.
5. Vérifiez les nœuds Hash pour Batches > 1. Si présent, le jointure a débordé sur le disque. Augmentez work_mem pour cette session et réexécuter.
6. Vérifiez les Nested Loop avec de grands nombres de boucles. Un nombre de boucles dans les milliers signifie que le scan interne est sous pression. Un index sur la colonne de jointure de la table interne le corrige généralement.

Avant d'ajouter de nouveaux index, exécutez également vos requêtes à l'aide de IO Tools’ SQL Formatter — les requêtes lisibles sont plus faciles à analyser, et parfois une réécriture élimine le problème entièrement.

Comprendre le plan est la première étape, pas l'index. Une fois que vous pouvez lire l'output EXPLAIN, vous passerez moins de temps à deviner et plus de temps à faire des modifications ciblées qui fonctionnent réellement.

Vous aimerez peut-être aussi