Concepts de base du plan d'exécution : cost, rows, width

Quand tu écris une requête SQL, PostgreSQL ne l'exécute pas direct. D'abord, il active son "cerveau" — l'optimiseur de requêtes, qui construit un plan d'exécution. Ce plan, c'est comme un itinéraire sur une carte : PostgreSQL calcule quelles actions faire et dans quel ordre pour choper les données.

L'optimiseur de requêtes évalue tous les chemins possibles pour exécuter ta requête : scan séquentiel de la table, utilisation des index, filtrage, tri, etc. Il essaie de trouver la façon la moins coûteuse (niveau ressources) d'exécuter ta requête. En gros, il cherche un compromis entre le temps d'exécution et les ressources du serveur.

Paramètres clés du plan d'exécution

Bon, passons à la partie la plus "fun" — l'analyse des paramètres que PostgreSQL te montre après la commande EXPLAIN. On commence avec un exemple simple :

EXPLAIN
SELECT * FROM students WHERE age > 20;

Tu vas obtenir un résultat du genre :

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

Allez, on va décoder ces mots et chiffres mystérieux.

1. cost (coût d'exécution)

cost — c'est une estimation des ressources nécessaires pour exécuter la requête. Ce paramètre a deux parties :

• Startup Cost : coût pour démarrer l'opération (genre préparer un index).
• Total Cost : coût total pour finir toute l'opération.

Exemple :

cost=0.00..35.00

• 0.00 — c'est le Startup Cost.
• 35.00 — c'est le Total Cost.

Plus la valeur de cost est basse, plus ce plan est cool pour PostgreSQL. Mais attention, cost c'est relatif. Ce n'est pas en secondes ou millisecondes, c'est juste une estimation interne de PostgreSQL.

2. rows (nombre estimé de lignes)

rows indique combien de lignes PostgreSQL pense retourner ou traiter à cette étape du plan. Dans notre exemple :

rows=7

Ça veut dire que PostgreSQL pense que le filtre age > 20 va renvoyer 7 lignes. Ces infos viennent des stats que PostgreSQL collecte sur la table. Si les stats sont périmées, la prévision peut être à côté de la plaque. Et ça peut donner un plan moins optimal.

3. width (largeur de la ligne en octets)

width — c'est la taille moyenne de chaque ligne retournée à cette étape, en octets. Dans notre exemple :

width=72

Ça veut dire que chaque ligne retournée fait en moyenne 72 octets. width prend en compte la taille des colonnes et les petits trucs en plus, genre les identifiants de ligne ou les infos système.

C'est un peu comme quand tu télécharges une appli. Si elle "pèse" lourd (genre width élevé), ça prend plus de temps à charger, même si t'as la fibre (genre cost bas).

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

Regardons un exemple concret. Imaginons qu'on a une table students :

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    major VARCHAR(50)
);

Et on lance cette requête :

EXPLAIN
SELECT * FROM students WHERE age > 20 AND major = 'CS';

Le résultat peut ressembler à ça :

Seq Scan on students  (cost=0.00..42.50 rows=3 width=164)
  Filter: ((age > 20) AND (major = 'CS'))

• Seq Scan : PostgreSQL fait un scan séquentiel de la table students. Donc il parcourt chaque ligne.
• cost=0.00..42.50 : Coût de l'opération. Startup Cost est 0.00, le coût total est 42.50.
• rows=3 : PostgreSQL pense que le filtre age > 20 AND major = 'CS' va sortir 3 lignes.
• width=164 : Chaque ligne fait en moyenne 164 octets.

Maintenant tu piges comment PostgreSQL prend ses décisions, et tu peux repérer les points faibles dans tes requêtes. Par exemple, si tu vois un cost élevé, c'est peut-être que ta requête est trop lourde. Ou si tu vois beaucoup de lignes dans rows, tu devrais revoir ton filtre.

Comment cost marche en vrai ?

Ajoutons un index sur la colonne age :

CREATE INDEX idx_age ON students(age);

On relance la requête :

EXPLAIN
SELECT * FROM students WHERE age > 20 AND major = 'CS';

Le résultat peut changer :

Bitmap Heap Scan on students  (cost=4.37..20.50 rows=3 width=164)
  Recheck Cond: (age > 20)
  Filter: (major = 'CS')
  ->  Bitmap Index Scan on idx_age  (cost=0.00..4.37 rows=20 width=0)
        Index Cond: (age > 20)

Qu'est-ce qui a changé ?

• Au lieu de Seq Scan, on a maintenant un Bitmap Heap Scan : PostgreSQL cherche d'abord les lignes dans l'index idx_age, puis les récupère dans la table.
• cost a bien baissé : maintenant Startup Cost est 4.37, et Total Cost est 20.50.
• L'opération est plus efficace grâce à l'index.

Visualisation : différence entre Seq Scan et Index Scan

Voilà un petit tableau comparatif pour que ce soit plus clair :

Pièges et erreurs classiques

Quand tu utilises les paramètres du plan d'exécution, attends-toi à quelques surprises. Par exemple, un cost bas n'est pas toujours synonyme de meilleure perf. Si les stats de la base sont obsolètes (genre après une grosse mise à jour de la table), le plan peut être à côté de la plaque. Mets à jour les stats avec la commande ANALYZE. On en parle plus dans la prochaine leçon.

Assure-toi d'utiliser des index là où il faut. Mais abuse pas des index : ça prend de la place et ça ralentit les opérations d'écriture.