Lire EXPLAIN ANALYZE dans PostgreSQL pour les débutants

1. Aperçu

EXPLAIN ANALYZE est l’outil le plus important pour diagnostiquer les requêtes lentes dans PostgreSQL. Cet article vous aide à lire chaque partie de la sortie : type de nœud, cost, rows, actual time, et savoir quand une requête a un problème. Après avoir lu ceci, vous serez en confiance pour lire le plan d’exécution et saurez s’il faut ajouter un index ou modifier la requête.

2. EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN affiche seulement le plan prévu (estimated plan) basé sur les statistiques, sans exécuter la requête. EXPLAIN ANALYZE exécute réellement la requête et mesure le temps réel.

-- Voir seulement le plan prévu, sans exécuter la requête
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Exécuter vraiment, mesurer le temps réel
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Attention : EXPLAIN ANALYZE exécute réellement la requête, donc si c’est un UPDATE ou DELETE, les données seront modifiées. Utilisez une transaction et ROLLBACK si vous voulez juste tester :

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'SHIPPED' WHERE id = 1;
ROLLBACK;

3. Structure d’un nœud dans le plan

Chaque ligne de la sortie est un nœud — une étape de traitement des données. Exemple avec la table orders :

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=8 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 99992
Planning Time: 0.120 ms
Execution Time: 12.465 ms

Explication de chaque partie :

• Seq Scan on orders : type de nœud, ici un parcours séquentiel de toute la table.
• cost=0.00..1834.00 : coût estimé (coût de démarrage .. coût total), unité relative, pas en ms.
• rows=10 : nombre de lignes estimé que le planificateur pense retourner.
• width=72 : taille moyenne de chaque ligne (en octets).
• actual time=0.015..12.430 : temps réel (ms) — du début jusqu’à la première ligne .. jusqu’à la fin du nœud.
• rows=8 loops=1 : nombre de lignes réelles retournées, et combien de fois ce nœud a été exécuté (loops).
• Rows Removed by Filter : nombre de lignes parcourues mais ne correspondant pas au filtre — un grand nombre ici est un signe de manque d’index.

4. Comparer Seq Scan et Index Scan

4.1. Quand il n’y a pas d’index

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=8 loops=1)
  Filter: (customer_id = 42)
Execution Time: 12.465 ms

PostgreSQL doit lire toute la table puis filtrer ligne par ligne. Avec une grande table, c’est la cause principale de ralentissement.

4.2. Après avoir ajouté un index

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=10 width=72)
  (actual time=0.018..0.025 rows=8 loops=1)
  Index Cond: (customer_id = 42)
Execution Time: 0.045 ms

Comparaison : le coût baisse de 1834 à 8.31, le temps d’exécution baisse de 12.465 ms à 0.045 ms. C’est un exemple classique montrant comment un index permet à PostgreSQL de sauter directement aux lignes nécessaires, sans parcourir toute la table.

Remarque : PostgreSQL n’utilise pas toujours un index même s’il existe. Si la requête retourne une grande partie de la table (par exemple > 20-30% des lignes), le planificateur choisit généralement Seq Scan car c’est moins coûteux que les accès aléatoires via l’index puis le retour à la table (random I/O).

5. Lire un plan avec JOIN

Quand une requête contient un join, le plan aura plusieurs nœuds imbriqués. Le nœud enfant s’exécute en premier, son résultat remontant au nœud parent.

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'VN';

Hash Join  (cost=45.50..520.75 rows=200 width=40)
           (actual time=1.200..8.900 rows=195 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o
        (cost=0.00..420.00 rows=10000 width=12)
        (actual time=0.010..3.500 rows=10000 loops=1)
  ->  Hash  (cost=40.00..40.00 rows=440 width=36)
            (actual time=1.100..1.100 rows=440 loops=1)
        ->  Seq Scan on customers c
              (cost=0.00..40.00 rows=440 width=36)
              (actual time=0.005..0.700 rows=440 loops=1)
              Filter: (country = 'VN'::text)
Execution Time: 9.050 ms

Comment lire ceci : Hash Join est le nœud racinaire (root), il reçoit les données de deux nœuds enfants (Seq Scan on orders et Hash contenant Seq Scan on customers). PostgreSQL construit une table de hachage à partir de la table customers filtrée, puis parcourt orders et le confronte à la table de hachage. Le temps actual time du nœud parent inclut déjà le temps des nœuds enfants à l’intérieur.

6. Signaux d’alerte à surveiller

6.1. L’estimation s’écarte largement de la réalité

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=9500 loops=1)

Le planificateur estime rows=10 mais en réalité il retourne 9500. Un écart aussi important est généralement dû à des statistiques obsolètes. Pour résoudre ce problème :

ANALYZE orders;

Cette commande met à jour les statistiques de distribution des données pour que le planificateur fasse des estimations plus précises.

6.2. Loops anormalement grand (Nested Loop)

Nested Loop  (cost=0.43..5000.00 rows=1 width=40)
             (actual time=0.020..450.000 rows=1 loops=1)
  ->  Seq Scan on customers c (actual time=0.010..0.015 rows=1 loops=1)
  ->  Index Scan on orders o
        (actual time=0.002..0.450 rows=1 loops=1000)

loops=1000 signifie que le nœud Index Scan on orders est appelé 1000 fois (correspondant à 1000 lignes du nœud externe). Si chaque appel consomme du temps, le coût total sera très élevé bien que le actual time du nœud considéré isolément semble petit. Multipliez actual time par loops pour estimer le temps réel total du nœud.

6.3. Utiliser BUFFERS pour voir les I/O

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=10 width=72)
  (actual time=0.018..0.025 rows=8 loops=1)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=4
Execution Time: 0.045 ms

shared hit=4 signifie que 4 blocs ont été lus depuis le cache (mémoire), sans accès disque. Si vous voyez shared read large, cela signifie que les données doivent être lues depuis le disque — signe de cache miss, peut-être dû à shared_buffers trop petit ou à une requête qui parcourt trop de données froides.

7. Utiliser EXPLAIN dans du code Java/Spring

Lors du debug de performance dans une application Spring Boot, vous pouvez logger le plan directement via JdbcTemplate :

@Service
public class QueryDiagnosticsService {

    private final JdbcTemplate jdbcTemplate;

    public QueryDiagnosticsService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<String> explainAnalyze(String sql) {
        String query = "EXPLAIN (ANALYZE, FORMAT TEXT) " + sql;
        return jdbcTemplate.queryForList(query, String.class);
    }
}

explainAnalyze prend le SQL original, l’enveloppe avec EXPLAIN (ANALYZE, FORMAT TEXT) et retourne chaque ligne du plan sous forme de String. Utilisez-le dans l’environnement dev/staging pour logger le plan quand vous détectez une requête lente.

@Test
void explainAnalyze_shouldReturnPlanWithExecutionTime() {
    QueryDiagnosticsService service = new QueryDiagnosticsService(jdbcTemplate);

    List<String> plan = service.explainAnalyze(
        "SELECT * FROM orders WHERE customer_id = 42"
    );

    assertThat(plan)
        .isNotEmpty()
        .anyMatch(line -> line.contains("Execution Time"));
}

Ce test n’affirme pas de chiffres concrets (car le temps varie selon la machine), il vérifie seulement que le plan retourné contient la ligne Execution Time — suffisant pour confirmer que la commande s’est exécutée correctement et a retourné un plan valide.

Attention : Ne pas exécuter EXPLAIN ANALYZE sur la production pour des requêtes avec INSERT/UPDATE/DELETE sans envelopper dans une transaction avec ROLLBACK, car elle s’exécutera réellement.

8. Quand vous n’avez PAS besoin d’EXPLAIN ANALYZE

• La requête est déjà rapide et stable (quelques ms, peu de données) — pas besoin d’optimiser davantage, l’effort ne vaut pas le coup.
• Vous êtes au stade précoce de la conception du schéma, vous n’avez pas assez de données réelles pour que les statistiques reflètent la réalité (peu de lignes signifie que Seq Scan gagne toujours contre Index Scan).
• La requête s’exécute sur une table temporaire (TEMP TABLE) petite une seule fois — l’overhead d’analyse ne vaut pas les gains.

9. Conclusion

EXPLAIN ANALYZE vous montre la différence entre ce que PostgreSQL pense qu’il va se passer (coût, rows estimés) et ce qui se passe réellement (actual time, rows réels). Quand vous lisez un plan, faites attention à l’ordre d’exécution (de l’intérieur vers l’extérieur), au type de nœud (Seq Scan ou Index Scan), et particulièrement à l’écart entre estimation et réalité — c’est la clé pour savoir si votre requête a besoin d’un index, d’une commande ANALYZE sur la table, ou d’une logique réécrite.