data / sql

Optimisation de requêtes : EXPLAIN et EXPLAIN ANALYZE

Leçon 161 exercice

Explication

Ce que vous allez apprendre

Lire un plan d'exécution avec EXPLAIN avant d'exécuter réellement une requête
Utiliser EXPLAIN ANALYZE pour comparer les estimations aux temps réels
Repérer un Seq Scan problématique face à un Index Scan efficace
Identifier pourquoi une fonction appliquée à une colonne empêche l'usage d'un index
Utiliser pg_stat_statements pour prioriser les requêtes à optimiser en production

Dans quel contexte ?

Un développeur reçoit une alerte : la page "historique de commandes" d'un client met soudain 8 secondes à charger, contre quelques millisecondes auparavant, alors que le code n'a pas changé. La table commandes a simplement grossi. Plutôt que de deviner une cause, il lance EXPLAIN ANALYZE sur la requête en question pour observer précisément ce que fait le moteur - la démarche que détaille cette leçon.

D'abord, la mauvaise habitude à corriger

Face à une requête lente, la tentation est de deviner la cause — "c'est sûrement l'index qui manque" — et de bricoler une solution au hasard. Il existe pourtant un outil pour observer, pas deviner.

L'outil : EXPLAIN

EXPLAIN demande à la base de révéler le plan d'exécution qu'elle compte suivre, un peu comme demander à un GPS de montrer l'itinéraire choisi avant de démarrer, plutôt que deviner s'il a pris le bon chemin.

Mais ce plan reste une prévision

EXPLAIN seul montre le plan prévu, basé sur des statistiques, sans rien exécuter réellement. Pour connaître la vérité, il faut un outil qui exécute vraiment la requête.

EXPLAIN ANALYZE va plus loin

Il exécute réellement la requête — attention si c'est un UPDATE ! — et compare les estimations aux temps et volumes réellement observés. Un grand écart entre "rows estimé" et "actual rows" signale des statistiques obsolètes à rafraîchir.

Deux mots-clés à repérer immédiatement dans le résultat

Seq Scan (parcours complet de la table) est un signal d'alerte sur une grande table filtrée. Index Scan, ou mieux Index Only Scan, indique au contraire qu'un index a été exploité efficacement.

Signal dans le plan	Signification	Action
`Seq Scan` sur grosse table	Parcours complet	Envisager un index sur la colonne filtrée
`Index Scan` / `Index Only Scan`	Index bien exploité	Rien à faire, c'est efficace
`rows=X` très différent de `actual rows=Y`	Statistiques obsolètes	Lancer `ANALYZE nom_table`
Filtre sur `EXTRACT(...)`/fonction	Index probablement inutilisé	Réécrire en comparaison d'intervalle

Un piège subtil, une fois qu'on sait lire un plan

Écrire WHERE EXTRACT(YEAR FROM date_colonne) = 2025 empêche souvent l'utilisation d'un index existant, car la base ne peut pas deviner à l'avance le résultat de la fonction pour chaque ligne indexée.

Piège fréquent

WHERE EXTRACT(YEAR FROM cree_le) = 2025 sur une table commandes de plusieurs millions de lignes force un Seq Scan complet, même si un index existe sur cree_le, car PostgreSQL devrait recalculer la fonction pour chaque ligne avant de pouvoir comparer.

La correction, et le lien avec la suite

Reformuler en comparaison d'intervalle (date_colonne >= '2025-01-01' AND date_colonne < '2026-01-01') redonne à l'index toute son efficacité. Cette leçon prolonge directement celle sur les index en donnant l'outil pour vérifier, plutôt que supposer, leur utilité réelle.

Bonne pratique

Avant d'ajouter un index ou de réécrire une requête "à l'instinct", lance toujours EXPLAIN ANALYZE en premier. La mesure objective évite de perdre du temps sur une fausse piste et confirme le gain réel après correction.

Commandes & code

Optimisation de requêtes

-- EXPLAIN : affiche le plan d'exécution PRÉVU, sans exécuter la requête
EXPLAIN SELECT * FROM commandes WHERE client_id = 42;

-- EXPLAIN ANALYZE : EXÉCUTE réellement la requête et donne les temps réels
EXPLAIN ANALYZE SELECT * FROM commandes WHERE client_id = 42;
-- Lire : "Seq Scan" (parcours complet, mauvais signe sur grosse table)
--    vs  "Index Scan" / "Index Only Scan" (utilise un index, généralement bon signe)
-- "rows=X" estimé vs "actual rows=Y" réel : un grand écart indique des statistiques obsolètes

-- Exemple avant optimisation : full scan car pas d'index sur client_id
-- Seq Scan on commandes (cost=0.00..1834.00 rows=50 width=40) (actual time=0.02..12.4 rows=48)

CREATE INDEX idx_commandes_client_id ON commandes(client_id);

-- Après : Index Scan, coût et temps divisés par un facteur important
-- Index Scan using idx_commandes_client_id on commandes (cost=0.29..8.45 rows=50) (actual time=0.01..0.08)

-- Format JSON pour analyse programmatique / outils
EXPLAIN (ANALYZE, FORMAT JSON, BUFFERS) SELECT * FROM commandes WHERE client_id = 42;
-- BUFFERS montre les accès disque/cache (shared hit/read) : révèle les I/O réels

-- Réécrire une requête lente : éviter les fonctions sur la colonne indexée
-- MAUVAIS : empêche l'utilisation de l'index sur cree_le
SELECT * FROM commandes WHERE EXTRACT(YEAR FROM cree_le) = 2025;
-- BON : plage de valeurs, l'index sur cree_le est utilisable
SELECT * FROM commandes WHERE cree_le >= '2025-01-01' AND cree_le < '2026-01-01';

-- Éviter SELECT * : ne récupérer que les colonnes nécessaires
-- réduit l'I/O et permet potentiellement un index-only scan
SELECT id, montant FROM commandes WHERE client_id = 42;  -- au lieu de SELECT *

-- ANALYZE : met à jour les statistiques utilisées par le planificateur de requêtes
ANALYZE commandes;   -- à lancer après un import massif de données

-- VACUUM (PostgreSQL) : récupère l'espace des lignes mortes (MVCC)
VACUUM ANALYZE commandes;

-- Comparer deux formulations d'une même requête
EXPLAIN ANALYZE
SELECT DISTINCT client_id FROM commandes;         -- souvent plus lent

EXPLAIN ANALYZE
SELECT client_id FROM commandes GROUP BY client_id; -- souvent équivalent ou plus rapide

-- Identifier les requêtes les plus coûteuses en production (extension pg_stat_statements)
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Résumé

EXPLAIN prédit le plan, EXPLAIN ANALYZE l'exécute réellement et mesure les temps.
Un Seq Scan sur une grande table filtrée est un signal d'alerte : envisager un index.
Appliquer une fonction sur une colonne dans WHERE empêche souvent l'utilisation de son index.
ANALYZE/VACUUM maintiennent les statistiques et l'espace disque à jour pour de bons plans.
pg_stat_statements identifie objectivement les requêtes à optimiser en priorité en production.

Exercices pratiques

1 disponible

Mission : diagnostiquer l'historique de commandes qui met 8 secondes à charger

Objectif : Utiliser EXPLAIN ANALYZE pour observer un plan d'exécution réel et corriger une requête qui empêche l'usage d'un index.

Contexte

La page "historique de commandes" d'un client met désormais 8 secondes à charger, contre quelques millisecondes il y a un mois, sans qu'aucun code n'ait changé. La table commandes a simplement grossi. La requête suspecte filtre sur client_id, et une autre filtre sur l'année d'une date via une fonction.

Résoudre l’exercice →