Interprétation du plan d'exécution : lecture et analyse des nœuds (`Seq Scan`, `Index Scan`, `Hash Join`)

Aujourd'hui, on va voir ce que sont les nœuds d'un plan d'exécution PostgreSQL, comment les lire et, surtout, comment piger quand un truc cloche. Tu vas comprendre pourquoi ta base de données préfère parfois utiliser un Seq Scan bien lourd, alors qu'il y a déjà un index (voire plusieurs).

Quand PostgreSQL construit le plan d'exécution d'une requête, il la découpe en étapes, qu'on appelle des nœuds. Chaque nœud, c'est un peu comme une "étape" que le serveur de base de données exécute pour traiter ta requête. Les principaux types de nœuds incluent :

Sequential Scan (Seq Scan)

Seq Scan ou scan séquentiel, c'est la façon la plus basique de récupérer des données d'une table. PostgreSQL prend littéralement la table, lit chaque ligne une par une et vérifie si elles correspondent aux conditions de ta requête.

Quand est-ce que Seq Scan est utilisé ?
Seq Scan est utilisé si :

• Il n'y a pas d'index adapté sur la table pour accélérer la requête.
• La condition de filtrage est trop large pour que l'index soit utile (genre, tu récupères plus de 50% des données).
• PostgreSQL estime que lire la table en séquentiel sera plus rapide que d'utiliser un index (ça arrive parfois sur des toutes petites tables).

EXPLAIN SELECT * FROM students WHERE age > 18;

Exemple de résultat :

Seq Scan on students  (cost=0.00..35.50 rows=10 width=50)
  Filter: (age > 18)

Regarde bien le Seq Scan on students — ça veut dire que PostgreSQL va lire toute la table "students".

Problèmes avec Seq Scan : Si la table est énorme, le scan séquentiel peut prendre un temps fou.

Index Scan

Index Scan, c'est le scan des données en utilisant un index. Quand tu crées un index dans PostgreSQL, c'est un peu comme faire une table des matières pour ta table. Si la requête peut utiliser l'index, PostgreSQL ne parcourt pas toute la table, mais juste les parties qui l'intéressent.

Quand est-ce que Index Scan est utilisé ?

• La requête a des conditions de filtrage sur une colonne indexée (genre WHERE).
• Tu utilises des opérateurs de comparaison comme =, <, >, BETWEEN, etc.

CREATE INDEX idx_students_age ON students(age);

EXPLAIN SELECT * FROM students WHERE age = 18;

Exemple de résultat :

Index Scan using idx_students_age on students  (cost=0.15..8.27 rows=1 width=50)
  Index Cond: (age = 18)

Ici, Index Scan using idx_students_age montre que PostgreSQL utilise l'index idx_students_age. Au lieu de lire la table ligne par ligne, il va direct à l'essentiel grâce à l'index.

Avantages de Index Scan :

• Ça accélère grave les requêtes sur les grosses tables.
• Ça réduit la quantité de données lues sur le disque.

Problèmes de Index Scan :
Si ta requête ramène trop de données (genre plus de la moitié de la table), utiliser l'index peut être plus lent qu'un Seq Scan.

Hash Join

Hash Join est utilisé pour joindre deux tables selon une condition (genre ON students.course_id = courses.id). PostgreSQL crée une table de hachage pour l'une des tables (la plus petite) et s'en sert pour trouver les correspondances dans l'autre table.

Quand est-ce que Hash Join est utilisé ?

• Quand tu fais un INNER JOIN, LEFT JOIN, etc.
• Quand PostgreSQL pense que Hash Join sera plus efficace que d'autres méthodes de jointure.

EXPLAIN
SELECT * 
FROM students 
JOIN courses ON students.course_id = courses.id;

Exemple de résultat :

Hash Join  (cost=25.00..50.00 rows=10 width=100)
  Hash Cond: (students.course_id = courses.id)
  -> Seq Scan on students  (cost=0.00..20.00 rows=10 width=50)
  -> Hash  (cost=15.00..15.00 rows=10 width=50)
       -> Seq Scan on courses  (cost=0.00..15.00 rows=10 width=50)

Ici, Hash Join fait la jointure entre deux tables. Remarque que PostgreSQL fait d'abord un Seq Scan sur les deux tables, puis construit la table de hachage (Hash).

Avantages de Hash Join :

• Rapide pour les tables de taille moyenne.
• Efficace pour joindre des tables avec beaucoup de lignes.

Problèmes de Hash Join :
Si la table de hachage est trop grosse pour tenir en mémoire, PostgreSQL va utiliser le disque pour la stocker, et là, ça rame sévère.

Exemple d'analyse d'un plan d'exécution

On va décortiquer un exemple concret.

Requête :

EXPLAIN ANALYZE
SELECT *
FROM students
JOIN courses ON students.course_id = courses.id
WHERE students.age > 18;

Résultat :

Hash Join  (cost=35.00..75.00 rows=5 width=100) (actual time=1.00..2.50 rows=5 loops=1)
  Hash Cond: (students.course_id = courses.id)
  -> Seq Scan on students  (cost=0.00..40.00 rows=10 width=50) (actual time=0.50..1.00 rows=7 loops=1)
        Filter: (age > 18)
        Rows Removed by Filter: 3
  -> Hash  (cost=25.00..25.00 rows=5 width=50) (actual time=0.30..0.30 rows=5 loops=1)
       -> Seq Scan on courses  (cost=0.00..20.00 rows=5 width=50) (actual time=0.20..0.25 rows=5 loops=1)
Planning Time: 0.50 ms
Execution Time: 3.00 ms

Interprétation :

1. Hash Join : C'est le nœud principal. PostgreSQL fait la jointure entre students et courses.
   • actual time : de 1.00 à 2.50 ms.
   • rows=5 : la requête a renvoyé 5 lignes.
2. Nœuds imbriqués :
   • Seq Scan on students : lit la table students en séquentiel et applique le filtre (age > 18).
   • Rows Removed by Filter = 3 : 3 lignes ne correspondaient pas à la condition.
   • Hash : PostgreSQL crée une table de hachage pour la table courses.

Comparaison et choix des nœuds

Quand tu analyses un plan d'exécution, le truc, c'est de piger pourquoi PostgreSQL a choisi telle ou telle méthode pour traiter les données. Parfois, tu dois intervenir pour corriger le tir, genre ajouter un index ou réécrire la requête. Quelques conseils :

• Si tu vois un Seq Scan sur une grosse table, pense aux index.
• Si un Hash Join rame, vérifie la mémoire dispo pour PostgreSQL.
• Utilise EXPLAIN ANALYZE pour comparer les valeurs estimées et réelles des métriques (rows, time).

À ce stade, t'as déjà une bonne base pour lire les plans d'exécution et comprendre leurs nœuds. Dans les prochaines leçons, on parlera des problèmes d'optimisation classiques et comment les résoudre.