C'est quoi EXPLAIN et comment l'utiliser pour analyser tes requêtes

Imagine que tu développes une appli, et qu'une de tes requêtes devient soudainement la plus coûteuse dans ta base de données. Tu commences à remarquer des bugs et des ralentissements dans ton appli. C'est là que EXPLAIN entre en scène, pour t'aider à piger où ça coince. Optimiser tes requêtes grâce à l'analyse de EXPLAIN te permet d'économiser des ressources, de gagner du temps et d'améliorer l'expérience utilisateur de ton appli.

EXPLAIN, c'est ta façon de jeter un œil à l'intérieur de PostgreSQL et de voir comment la base de données compte exécuter ta requête. Il te montre si un index va être utilisé ou si ça va finir en scan complet de la table, quelles étapes l'optimiseur va faire, dans quel ordre, et à quel point les résultats intermédiaires vont être gros.

En gros, EXPLAIN te permet de comprendre à quoi t'attendre quand tu exécutes une requête : est-ce qu'elle est « lourde », combien de lignes il pense devoir traiter, et quelles ressources seront utilisées. C'est un outil indispensable quand une requête commence à ramer et que tu dois comprendre pourquoi.

EXPLAIN, c'est comme une lampe torche dans le noir : avec, tu vois ce qui se passe sous le capot et où ça part en vrille.

Syntaxe de la commande EXPLAIN

Regardons la syntaxe de base de la commande EXPLAIN :

EXPLAIN ta_requête_SQL;

Exemple de requête :

EXPLAIN SELECT * FROM students WHERE age > 20;

Quand tu lances cette commande, PostgreSQL n'exécutera pas la requête. À la place, il va te montrer le plan d'exécution. C'est comme un croquis avant de construire — c'est utile de voir ce qui va être fait avant de tout casser.

Voilà un exemple de sortie :

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Ce résultat peut faire flipper au début, mais t'inquiète — on va décortiquer les composants principaux.

Décryptage d'un plan d'exécution basique

Décortiquons ce résultat :

1. Seq Scan on students — ça veut dire que PostgreSQL va scanner toute la table students (scan séquentiel). C'est pas toujours mauvais, mais sur des grosses tables, Seq Scan peut être lent.

2. cost=0.00..35.00 — c'est l'estimation du coût de l'opération :

   • Startup Cost : coût de démarrage de l'opération (ici 0.00).
   • Total Cost : coût total pour finir l'opération (ici 35.00).

3. rows=7 — PostgreSQL pense que la condition age > 20 va renvoyer 7 lignes. C'est ce qu'on appelle la "cardinalité". Si tu vois des estimations bizarres, ça peut vouloir dire que les stats de ta table sont périmées.

4. width=37 — c'est la taille moyenne d'une ligne en octets.

5. Filter: (age > 20) — précise que PostgreSQL va appliquer ce filtre en vérifiant chaque ligne.

Du coup, la sortie de EXPLAIN te donne une idée des stratégies et des suppositions de PostgreSQL. Tu peux utiliser ces infos pour optimiser.

Options de la commande EXPLAIN

Même si la sortie de base de EXPLAIN est déjà utile, tu peux la modifier avec ces options :

ANALYZE

Avec cette option, PostgreSQL va non seulement te montrer le plan, mais aussi exécuter la requête et te filer les vraies données. Exemple :

EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20;

Ça te permet de comparer les suppositions de PostgreSQL avec ce qui se passe vraiment, pour voir si ça colle.

VERBOSE

Affiche plus de détails, pratique pour une analyse poussée. Exemple :

EXPLAIN VERBOSE SELECT * FROM students WHERE age > 20;

BUFFERS

Montre l'utilisation des buffers mémoire pendant l'exécution de la requête. À utiliser avec ANALYZE :

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM students WHERE age > 20;

COSTS

Si tu veux masquer ou afficher les infos de coût (cost), utilise cette option :

EXPLAIN (COSTS OFF) SELECT * FROM students WHERE age > 20;

FORMAT

Le plan peut être affiché dans d'autres formats, comme JSON ou XML. Exemple :

EXPLAIN (FORMAT JSON) SELECT * FROM students WHERE age > 20;

Exemple d'utilisation de EXPLAIN

Imaginons une base university avec une table students. Disons que tu veux trouver tous les étudiants de plus de 20 ans :

EXPLAIN SELECT * FROM students WHERE age > 20;

La sortie peut ressembler à ça :

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Comme on l'a déjà dit, c'est un scan séquentiel Seq Scan, qui peut être inefficace sur des grosses tables.

Maintenant, créons un index sur la colonne age et voyons si le plan change :

CREATE INDEX age_index ON students(age);

EXPLAIN SELECT * FROM students WHERE age > 20;

Sortie :

Index Scan using age_index on students  (cost=0.15..4.23 rows=7 width=37)
  Index Cond: (age > 20)

Maintenant, PostgreSQL utilise un scan par index (Index Scan), ce qui est généralement plus rapide, surtout sur des grosses tables.

Questions et erreurs fréquentes

Pourquoi ma requête est lente même avec un index ?

Peut-être que ta requête renvoie trop de lignes, donc l'index n'est pas si utile. L'index peut aussi être de mauvaise qualité ou pas à jour.

Et si la sortie de EXPLAIN est trop dure à comprendre ?

Commence par des requêtes simples et analyse les nœuds du plan un par un.

Comment savoir si les stats de la table sont périmées ?

Lance la commande ANALYZE students

Quand utiliser EXPLAIN sans ANALYZE ?

Si tu veux juste voir le plan sans exécuter la requête (par exemple pour des requêtes qui modifient les données).