Commencer→
Retour au cours

data / sql

Lecture avancée du plan d'exécution

Leçon 261 exercice

Explication

Ce que vous allez apprendre

  • Reconnaître les trois algorithmes de jointure : Nested Loop, Hash Join, Merge Join
  • Identifier quelle stratégie de jointure convient à quelle situation de données
  • Lire l'option BUFFERS pour distinguer une lecture en cache d'une lecture disque
  • Repérer un plan parallélisé (Gather/Parallel Seq Scan)
  • Comprendre l'impact de MATERIALIZED sur l'exécution d'une CTE

Dans quel contexte ?

Un développeur senior doit diagnostiquer pourquoi une requête qui joint clients et commandes est devenue lente après la migration d'une partie du trafic vers une nouvelle région. Le plan EXPLAIN ANALYZE révèle un Nested Loop exécuté des milliers de fois là où un Hash Join serait bien plus adapté, un signal que les statistiques ou les index doivent être revus. Cette dernière leçon du parcours SQL apprend à lire ce niveau de détail dans un plan d'exécution.

D'abord, ce qu'on savait déjà faire

La leçon 16 a introduit EXPLAIN ANALYZE avec une distinction simple : parcours complet (Seq Scan) contre utilisation d'un index (Index Scan). Un vrai plan d'exécution en production contient bien plus d'informations : comment deux tables sont jointes, si le travail est réparti sur plusieurs processeurs, et où sont réellement lues les données.

Étape 1 : comprendre le Nested Loop

Le planificateur choisit entre trois grandes stratégies de jointure selon la situation. Le Nested Loop parcourt une table externe et, pour CHAQUE ligne, va chercher la correspondance dans l'autre table : excellent si l'une des deux tables est petite ou très filtrée, catastrophique sinon.

Étape 2 : le Hash Join pour deux grands ensembles

Quand aucun index n'est exploitable et que les deux tables sont grandes, le Hash Join construit une table de hachage en mémoire à partir de la plus petite table, puis parcourt l'autre une seule fois — bien plus efficace qu'un Nested Loop dans ce cas.

Étape 3 : le Merge Join pour des données déjà triées

Si les deux entrées sont déjà triées sur la clé de jointure (souvent grâce à un index), le Merge Join les fusionne directement, un peu comme fusionner deux piles de cartes déjà triées — sans jamais construire de structure intermédiaire.

AlgorithmeIdéal quandCoût typique
Nested LoopUne table petite ou très filtréeCatastrophique si les deux tables sont grandes
Hash JoinDeux grands ensembles, pas d'index exploitableConstruit une table de hachage en mémoire
Merge JoinLes deux entrées déjà triées sur la clé de jointureTrès efficace, pas de structure intermédiaire

Prérequis

Cette leçon suppose une bonne maîtrise d'EXPLAIN ANALYZE de base (leçon 16) et des jointures (leçon 5) : elle approfondit ce que le planificateur fait concrètement derrière un JOIN.

Une fois les jointures comprises : d'où viennent réellement les données ?

L'option BUFFERS révèle si les données lues venaient du cache (shared hit, rapide) ou du disque (shared read, plus lent). Un taux élevé et répété de lectures disque signale que les données utiles dépassent la mémoire allouée au cache.

Pour aller plus loin : le parallélisme et les CTE

Un plan Gather/Parallel Seq Scan montre que PostgreSQL a réparti le travail sur plusieurs workers. Et depuis PostgreSQL 12, une CTE simple est fusionnée dans la requête plutôt que calculée séparément, sauf si MATERIALIZED est explicitement demandé — un détail qui peut radicalement changer la performance d'une requête vue en leçon 11.

Piège à connaître pour clore le cours

Un grand écart entre le nombre de lignes estimé et le nombre réel dans le plan signale des statistiques obsolètes : il suffit souvent de relancer ANALYZE pour que le planificateur reprenne de meilleures décisions. Ce réflexe de lecture fine du plan résume l'esprit de tout ce parcours SQL : comprendre précisément ce que fait la base, pas seulement écrire une requête qui "marche".

Bonne pratique

Face à une requête de production devenue lente, résiste à la tentation de changer du code au hasard. Lis d'abord le plan complet (EXPLAIN (ANALYZE, BUFFERS)), identifie l'opération la plus coûteuse (Nested Loop répété, lecture disque massive, statistiques obsolètes), puis corrige précisément cette cause avant de mesurer à nouveau.

Commandes & code

Lecture avancée du plan d'exécution

Aller au-delà de "Seq Scan vs Index Scan" : algorithmes de jointure, parallélisme et matérialisation des CTE.

sql
-- Lire les algorithmes de jointure choisis par le planificateur
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.nom, co.montant
FROM clients c JOIN commandes co ON co.client_id = c.id
WHERE c.region = 'EU';

-- Nested Loop : efficace si une des deux tables est petite ou fortement filtrée
--   -> Nested Loop
--        -> Index Scan using idx_clients_region on clients
--        -> Index Scan using idx_commandes_client_id on commandes (exécuté N fois, une par ligne externe)

-- Hash Join : efficace pour joindre deux grands ensembles sans index exploitable
--   -> Hash Join
--        Hash Cond: (co.client_id = c.id)
--        -> Seq Scan on commandes co
--        -> Hash  -- construit une table de hachage en mémoire sur la table la plus petite (clients)

-- Merge Join : efficace si les deux entrées sont déjà triées (ou triables à bas coût) sur la clé de jointure
--   -> Merge Join
--        Merge Cond: (c.id = co.client_id)
--        -> Index Scan using clients_pkey on clients
--        -> Index Scan using idx_commandes_client_id on commandes

-- Forcer temporairement un plan pour comparer (débogage uniquement, jamais en production)
SET enable_hashjoin = off;
EXPLAIN ANALYZE SELECT c.nom, co.montant FROM clients c JOIN commandes co ON co.client_id = c.id;
RESET enable_hashjoin;

-- Lire BUFFERS : distingue cache (shared hit) et disque (shared read)
-- Buffers: shared hit=842 read=12  -- 842 pages depuis le cache, seulement 12 depuis le disque
-- Un ratio "read" élevé et répété indique un working set trop grand pour shared_buffers

-- Exécution parallèle : PostgreSQL peut répartir un Seq Scan/agrégat sur plusieurs workers
EXPLAIN ANALYZE SELECT COUNT(*) FROM commandes WHERE montant > 50;
--  Finalize Aggregate
--    -> Gather (workers planned: 2, workers launched: 2)
--         -> Partial Aggregate
--              -> Parallel Seq Scan on commandes

SET max_parallel_workers_per_gather = 4;  -- ajuster le nombre de workers pour une requête coûteuse

-- CTE : MATERIALIZED (isolée, calculée une fois) vs inlinée (fusionnée dans la requête)
-- Depuis PostgreSQL 12, une CTE simple est inlinée par défaut, sauf si :
--  - elle est référencée plusieurs fois, ou
--  - elle contient un effet de bord, ou
--  - MATERIALIZED est explicitement demandé
WITH stats AS MATERIALIZED (
    SELECT client_id, COUNT(*) AS nb FROM commandes GROUP BY client_id
)
SELECT * FROM stats WHERE nb > 10;
-- MATERIALIZED force PostgreSQL à calculer "stats" une seule fois avant de filtrer

-- Repérer une sous-requête corrélée coûteuse : elle apparaît comme "SubPlan" réexécuté par ligne
EXPLAIN ANALYZE
SELECT c.nom, (SELECT COUNT(*) FROM commandes co WHERE co.client_id = c.id) AS nb
FROM clients c;
--  -> SubPlan 1 (exécuté une fois PAR ligne de clients -- coût potentiellement O(n*m))

-- Comparer coût estimé et coût réel pour détecter des statistiques obsolètes
-- rows=100 (estimé) vs actual rows=45000 (réel) -> les stats ne reflètent plus les données
ANALYZE commandes;

Résumé

  • Nested Loop, Hash Join, Merge Join ont chacun un profil de coût différent selon la taille des tables et les index disponibles.
  • BUFFERS distingue les pages lues depuis le cache (shared hit) de celles lues sur disque (shared read).
  • Un plan Gather/Parallel Seq Scan indique une exécution répartie sur plusieurs workers.
  • WITH ... AS MATERIALIZED force le calcul isolé d'une CTE, utile pour éviter une réévaluation coûteuse à chaque référence.
  • Un grand écart entre rows estimé et actual rows signale des statistiques obsolètes : relancer ANALYZE.

Exercices pratiques

1 disponible
1

Mission : diagnostiquer une jointure devenue lente après une migration

Objectif : Lire un plan EXPLAIN ANALYZE détaillé pour identifier un mauvais choix d'algorithme de jointure causé par des statistiques obsolètes, et proposer une correction.

Contexte

Depuis la migration d'une partie du trafic vers une nouvelle région, la requête qui joint clients et commandes sur co.client_id = c.id est devenue beaucoup plus lente. Le plan EXPLAIN (ANALYZE, BUFFERS) montre un Nested Loop où la boucle interne (Index Scan sur commandes) est exécutée des milliers de fois, et affiche rows=120 en estimation contre actual rows=48000 en réalité sur le Seq Scan de clients. Les deux tables contiennent chacune plusieurs millions de lignes.

Résoudre l’exercice →