Utiliser EXPLAIN ANALYZE pour mesurer le temps d'exécution réel des requêtes

Si la commande EXPLAIN te permet de jeter un œil dans la boule de cristal et de voir comment PostgreSQL “prévoit” d’exécuter ta requête, alors EXPLAIN ANALYZE te transforme en vrai détective qui découvre ce qui s’est vraiment passé.

Différences clés entre EXPLAIN et EXPLAIN ANALYZE :

EXPLAIN – c’est la théorie, ça montre comment PostgreSQL prévoit d’exécuter la requête. Tu vois des valeurs estimées comme le nombre de lignes (rows) et le coût d’exécution (cost).

EXPLAIN ANALYZE – c’est la pratique. PostgreSQL exécute vraiment la requête et te montre :

• Le nombre réel de lignes traitées à chaque étape.
• Le temps d’exécution réel de chaque opération.
• Une comparaison avec les estimations du plan (rows et cost).

Exemple : si ta requête est censée traiter 100 lignes mais en traite en vrai 10 000, EXPLAIN ANALYZE va te balancer ce fait pas très joli direct !

Syntaxe de base et utilisation

Comme EXPLAIN, EXPLAIN ANALYZE est super simple à utiliser. Tu ajoutes juste le mot ANALYZE à ta commande EXPLAIN.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Voilà ce que fait PostgreSQL :

• Il exécute la requête.
• Il enregistre chaque opération dans le plan d’exécution, avec les vraies stats.
• Il te renvoie une description complète du process d’exécution de la requête.

Quelles infos donne EXPLAIN ANALYZE ?

Le temps d’exécution réel des opérations :

• Actual Start Time : quand l’opération a commencé.
• Actual End Time : quand l’opération s’est terminée.

Le nombre total de lignes traitées :

Ça aide à voir si les estimations du plan (rows) sont justes ou à côté de la plaque.

Infos sur les buffers :

Comment les buffers disque et mémoire ont été utilisés.

Exemple d’utilisation de EXPLAIN ANALYZE

Regardons un exemple concret. On a une table students qui contient des infos sur les étudiants :

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

INSERT INTO students (name, age, grade)
VALUES
('Alice', 22, 4.1),
('Bob', 19, 3.8),
('Charlie', 23, 4.5),
('Diana', 20, 3.9);

On lance une requête pour récupérer les étudiants de plus de 20 ans :

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Exemple de résultat :

Seq Scan on students  (cost=0.00..14.00 rows=2 width=116) (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (age > 20)
  Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.048 ms

Décryptons le résultat :

• Seq Scan – ça veut dire que PostgreSQL fait un scan séquentiel de la table.
• cost=0.00..14.00 – c’est le coût estimé de l’opération.
• rows=2 – PostgreSQL pense que la requête va renvoyer 2 lignes (et il a raison !).
• actual time=0.025..0.026 – le temps réel d’exécution de l’opération (en millisecondes).
• Rows Removed by Filter: 2 – deux lignes ont été filtrées car elles ne matchaient pas la clause WHERE.

Comparer théorie et pratique

Voilà la magie de EXPLAIN ANALYZE : il te montre comment la requête a vraiment été exécutée, et tu peux comparer ça au plan théorique.

Regardons un exemple un peu plus costaud.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20 AND grade > 4.0;

Exemple de résultat :

Seq Scan on students  (cost=0.00..14.00 rows=1 width=116) (actual time=0.026..0.027 rows=1 loops=1)
  Filter: ((age > 20) AND (grade > 4.0))
  Rows Removed by Filter: 3
Planning Time: 0.035 ms
Execution Time: 0.057 ms

Ce qu’on voit :

1. PostgreSQL a exécuté la requête en 0.057 millisecondes.
2. Une seule ligne (rows=1) correspond aux conditions WHERE.
3. Trois lignes ont été filtrées (Rows Removed by Filter: 3).

Résumé

Utiliser EXPLAIN ANALYZE te permet de trouver les goulots d’étranglement et de piger comment optimiser tes requêtes. Par exemple :

• Si Seq Scan est trop “lourd”, c’est peut-être le moment d’ajouter un index.
• Si les estimations de PostgreSQL sont très différentes des vraies données, il faut checker les stats de la table (ANALYZE) ou la structure des index.