PostgreSQL : déboguer une requête lente et l'optimiser

C'est vendredi 18h30. Ta requête met 4 secondes à répondre en prod. Le client t'a envoyé un screenshot de son écran de chargement. Ton téléphone sonne. Tu as une bière froide qui t'attend. Voilà le contexte dans lequel tu vas déboguer une requête PostgreSQL lente.

Bonne nouvelle : 80% des problèmes de performance PostgreSQL ont une cause identifiable en moins de 10 minutes avec les bons outils. Ce guide suit l'ordre logique d'une vraie investigation — pas un catalogue exhaustif de features PG, une méthode qui marche.

EXPLAIN vs EXPLAIN ANALYZE : l'un prédit, l'autre exécute

La première erreur classique : utiliser EXPLAIN seul et se demander pourquoi les chiffres ne correspondent pas à ce qu'on observe en prod.

EXPLAIN affiche le plan d'exécution que PostgreSQL pense utiliser, basé sur les statistiques qu'il a en mémoire. Il n'exécute pas la requête. Les coûts sont des estimations, les rows sont des prédictions.

EXPLAIN ANALYZE exécute réellement la requête et enrichit le plan avec les valeurs mesurées : temps réel, nombre de lignes réelles traitées, nombre de boucles. C'est lui qu'on veut. Attention : il exécute vraiment la requête — ne pas lancer sur un DELETE ou UPDATE sans BEGIN/ROLLBACK.

-- Sécurisé pour les requêtes d'écriture
BEGIN;
EXPLAIN ANALYZE
    UPDATE orders SET status = 'processed' WHERE created_at < NOW() - INTERVAL '30 days';
ROLLBACK;

-- Pour un SELECT, pas de précautions particulières
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
    SELECT u.email, COUNT(o.id) AS order_count
    FROM users u
    JOIN orders o ON o.user_id = u.id
    WHERE u.created_at >= '2025-01-01'
    GROUP BY u.email
    ORDER BY order_count DESC
    LIMIT 20;

L'option BUFFERS est précieuse : elle indique combien de blocs ont été lus depuis le cache (shared hit) vs depuis le disque (read). Une requête avec beaucoup de lectures disque sur une table censée être en cache est un signal fort.

Ce qu'on cherche dans la sortie

Trois choses à repérer immédiatement :

• Seq Scan sur une grosse table : PostgreSQL parcourt toutes les lignes une par une. Sur une table de 50 000 lignes, c'est souvent acceptable. Sur 10 millions, non.
• Écart énorme entre rows estimated et rows actual : si PostgreSQL pensait filtrer 50 lignes et en a traité 80 000, ses statistiques sont obsolètes ou la requête est mal écrite.
• Nœud avec un temps disproportionné : le plan est un arbre. Le nœud le plus lent est le coupable. Le temps affiché est cumulatif — soustraire le temps des enfants pour isoler le coût propre d'un nœud.

-- Exemple de sortie EXPLAIN ANALYZE problématique
Seq Scan on orders  (cost=0.00..48520.00 rows=2000 width=120)
                    (actual time=0.042..3841.223 rows=1847291 loops=1)
  Filter: (status = 'pending' AND created_at > '2024-01-01')
  Rows Removed by Filter: 152709
Planning Time: 1.2 ms
Execution Time: 4102.8 ms

-- Ce qu'on voit :
-- 1. Seq Scan sur "orders" → pas d'index utilisé
-- 2. rows estimated = 2 000, rows actual = 1 847 291 → statistiques catastrophiques
-- 3. 4 secondes → correspond exactement à ce que le client voit

Les coupables habituels

1. Index manquant sur une colonne filtrée

Le cas le plus fréquent. Une table orders avec 2 millions de lignes, une requête qui filtre sur user_id, pas d'index. PostgreSQL fait un Seq Scan complet. Le fix prend 30 secondes.

-- La requête qui rame
SELECT id, total, status
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;

-- EXPLAIN ANALYZE montre :
-- Seq Scan on orders  (actual time=0.031..2341.5 rows=47 loops=1)
-- Filter: (user_id = 42)
-- Rows Removed by Filter: 1999953

-- Le fix
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
-- CONCURRENTLY : crée l'index sans locker la table en écriture

-- Après création, la même requête :
-- Index Scan using idx_orders_user_id on orders
-- (actual time=0.041..0.189 rows=47 loops=1)
-- 2,3 secondes → 0,2 ms

2. Index existant mais pas utilisé

Situation plus traîtresse : l'index existe, mais PostgreSQL ne l'utilise pas. Trois raisons principales.

Fonction dans le WHERE : dès qu'on applique une fonction à une colonne indexée, l'index est inutilisable — PostgreSQL ne peut pas parcourir l'index sur la valeur transformée.

-- Index sur email, mais inutilisé
CREATE INDEX idx_users_email ON users(email);

-- L'index ne sert à rien ici
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- Index sur expression — résout le problème
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Ou réécrire la requête si les données sont déjà en minuscules
SELECT * FROM users WHERE email = 'alice@example.com';

LIKE avec wildcard en préfixe : LIKE '%dupont' ne peut pas utiliser un index B-tree, car il faut parcourir toutes les valeurs pour trouver celles qui se terminent par "dupont". LIKE 'dupont%' en revanche, oui.

-- Seq Scan garanti, même avec un index sur last_name
SELECT * FROM customers WHERE last_name LIKE '%martin';

-- Utilise l'index B-tree
SELECT * FROM customers WHERE last_name LIKE 'martin%';

-- Pour la recherche "contient", utiliser pg_trgm + GIN
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_customers_last_name_trgm
    ON customers USING GIN(last_name gin_trgm_ops);

-- Maintenant ce LIKE utilise l'index
SELECT * FROM customers WHERE last_name LIKE '%martin%';

Mauvaise cardinalité : si une colonne a très peu de valeurs distinctes (ex. status avec 3 valeurs possibles sur 2 millions de lignes), PostgreSQL peut estimer qu'un Seq Scan est plus rapide qu'un Index Scan suivi de 600 000 accès aléatoires. Il a souvent raison. La solution : index partiel.

-- 2 millions d'orders, 95% ont status = 'completed', 5% status = 'pending'
-- Un index simple sur status est peu utile pour 'completed'
-- Mais très utile pour 'pending' (100 000 lignes sur 2 millions)

-- Index partiel : n'indexe que les pending
CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

-- Cette requête utilise maintenant l'index partiel
SELECT id, user_id, total
FROM orders
WHERE status = 'pending'
  AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

3. Le problème N+1 via ORM

Pas spécifique à PostgreSQL, mais PostgreSQL en souffre autant que les autres. L'ORM charge 100 articles, puis pour chaque article fait une requête séparée pour charger l'auteur. Résultat : 101 requêtes au lieu d'une.

-- Ce que l'ORM génère (N+1)
SELECT * FROM articles LIMIT 100;
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;
-- ... 98 fois encore

-- Ce qu'il devrait faire
SELECT a.id, a.title, a.body, a.published_at,
       u.id AS author_id, u.name AS author_name, u.avatar_url
FROM articles a
JOIN users u ON u.id = a.author_id
ORDER BY a.published_at DESC
LIMIT 100;

La détection se fait dans les logs ou via pg_stat_statements (voir plus bas) : des dizaines de requêtes identiques avec juste un paramètre qui change, exécutées en rafale. En Go : SQL classique avec un seul JOIN. En Laravel/Django : eager loading.

4. Statistiques obsolètes

PostgreSQL maintient des statistiques sur la distribution des données dans chaque table (via l'autovacuum). Si ces statistiques sont périmées — après un gros import, un DELETE massif — le planner prend de mauvaises décisions. Symptôme : écart énorme entre rows estimated et rows actual dans EXPLAIN ANALYZE.

-- Mettre à jour les statistiques sans bloquer les écritures
ANALYZE orders;

-- Après un gros chargement ou purge de données
VACUUM ANALYZE orders;

-- Vérifier la fraîcheur des stats
SELECT schemaname, tablename, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE tablename = 'orders';

5. Trop de lignes retournées

Parfois la requête est correctement indexée mais ramène 50 000 lignes alors qu'on en affiche 20. Ajouter LIMIT et paginer correctement. Pour des listes triées sur de gros volumes, la pagination par offset devient elle-même un problème au-delà de quelques milliers de pages : OFFSET 100000 LIMIT 20 force PG à parcourir 100 020 lignes pour en retourner 20.

-- Pagination par offset — lente sur les grandes pages
SELECT id, title, published_at
FROM articles
ORDER BY published_at DESC
LIMIT 20 OFFSET 10000;

-- Pagination par curseur (keyset pagination) — performante quelle que soit la page
SELECT id, title, published_at
FROM articles
WHERE published_at < '2024-06-15 10:23:00'  -- dernière valeur de la page précédente
ORDER BY published_at DESC
LIMIT 20;

Choisir le bon type d'index

PostgreSQL propose plusieurs types d'index. En pratique, on en utilise trois.

B-tree (défaut)

Convient à 95% des cas : égalité, inégalité, tri, plage de valeurs. C'est le défaut quand on écrit CREATE INDEX sans préciser le type. Efficace sur les colonnes à haute cardinalité (identifiants, emails, dates, montants).

CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_users_email ON users(email);

-- Index composite : l'ordre des colonnes compte.
-- Utile pour les requêtes qui filtrent sur user_id ET trient sur created_at.
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

GIN — Full-text search et JSONB

Pour les recherches dans du texte (tsvector), dans des tableaux, ou dans des colonnes JSONB. Un index B-tree sur un champ JSONB n'aide pas pour des requêtes comme WHERE metadata @> '{"role": "admin"}'.

-- Index GIN pour requêtes JSONB
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

-- Maintenant efficace
SELECT * FROM users WHERE metadata @> '{"role": "admin", "active": true}';

-- Index GIN pour full-text search en français
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector =
    to_tsvector('french', COALESCE(title, '') || ' ' || COALESCE(body, ''));

CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

SELECT title FROM articles
WHERE search_vector @@ plainto_tsquery('french', 'postgresql optimisation index');

BRIN — Timestamps séquentiels

BRIN (Block Range INdex) est minuscule en taille et très efficace pour les colonnes dont les valeurs croissent naturellement avec l'insertion : timestamps, IDs séquentiels. Principe : au lieu d'indexer chaque valeur, il stocke les valeurs min/max par bloc de données. Sur une table de logs de plusieurs centaines de Go, il peut réduire le temps de requête drastiquement avec un index de quelques Mo seulement.

-- Table de logs avec 500 millions de lignes.
-- Un index B-tree sur created_at pèserait ~15 Go.
-- Un index BRIN pèse quelques Mo.
CREATE INDEX idx_logs_created_at_brin ON access_logs USING BRIN(created_at);

-- Efficace pour les plages temporelles
SELECT COUNT(*), path
FROM access_logs
WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01'
GROUP BY path
ORDER BY count DESC;

pg_stat_statements : trouver les requêtes coûteuses en prod

Jusqu'ici on savait quelle requête analyser. En prod, souvent on ne sait pas. pg_stat_statements accumule des statistiques sur toutes les requêtes exécutées : temps total, nombre d'appels, temps moyen. C'est là que se cachent les requêtes qui s'exécutent 10 000 fois par heure et qui prennent 200ms chacune — moins visibles qu'une requête à 4 secondes, mais bien plus impactantes au total.

-- Activer l'extension (nécessite un redémarrage de PostgreSQL)
-- Dans postgresql.conf : shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Les 10 requêtes les plus coûteuses en temps total
SELECT
    LEFT(query, 100) AS query_short,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    ROUND(mean_exec_time::numeric, 2) AS mean_ms,
    ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Les requêtes avec le plus grand écart-type (instabilité des temps)
-- Symptôme possible : plan différent selon les paramètres, ou cache miss aléatoire
SELECT
    LEFT(query, 100) AS query_short,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS mean_ms,
    ROUND(stddev_exec_time::numeric, 2) AS stddev_ms
FROM pg_stat_statements
WHERE calls > 100
ORDER BY stddev_exec_time DESC
LIMIT 10;

-- Remettre à zéro les stats (utile après un déploiement ou une optimisation)
SELECT pg_stat_statements_reset();

pg_stat_statements n'interrompt pas le service et n'a pas d'impact mesurable sur les performances. Il n'y a aucune raison de ne pas l'activer en prod sur tous tes clusters PostgreSQL.

Astuces rapides

Forcer PostgreSQL à montrer le plan avec index

Parfois PostgreSQL choisit un Seq Scan alors qu'un index existe, et on veut savoir ce que donnerait le plan avec l'index. SET enable_seqscan = off force le planner à préférer les index scans pour la session courante. Utile pour diagnostiquer, pas pour corriger définitivement.

-- Dans ta session uniquement — pas de modification globale
SET enable_seqscan = off;

EXPLAIN ANALYZE
    SELECT * FROM orders WHERE user_id = 42;

-- Remettre en état normal
SET enable_seqscan = on;

-- Interprétation :
-- Si le plan avec index est plus rapide → créer l'index ou lancer ANALYZE (stats obsolètes).
-- Si le plan avec index est plus lent → PostgreSQL avait raison de faire le Seq Scan.
--   Peut-être que la requête ramène trop de lignes pour qu'un index aide.

work_mem pour les tris et hash joins

Quand tu vois Sort Method: external merge Disk dans EXPLAIN ANALYZE, PostgreSQL a manqué de mémoire et a dû écrire sur disque pour trier. Augmenter work_mem pour la session peut résoudre le problème immédiatement.

-- Par défaut work_mem = 4MB — insuffisant pour les grosses agrégations
SET work_mem = '64MB';

EXPLAIN ANALYZE
    SELECT user_id, SUM(total) AS revenue
    FROM orders
    WHERE created_at >= '2025-01-01'
    GROUP BY user_id
    ORDER BY revenue DESC;

-- Avec work_mem suffisant :
-- Sort Method: quicksort  Memory: 12kB  ← tout en RAM, rapide

-- Sans work_mem suffisant :
-- Sort Method: external merge  Disk: 48MB  ← écrit sur disque, lent

-- Attention : ne pas augmenter work_mem globalement sans calcul.
-- Il s'applique PAR opération de tri ET par connexion simultanée.
-- 100 connexions × 5 tris × 64MB = 32 Go consommés potentiellement.
-- Le réserver aux sessions qui en ont besoin, ou ajuster max_connections.

VACUUM ANALYZE après un gros chargement

-- Après un import ou un DELETE massif
VACUUM ANALYZE orders;

-- VACUUM récupère l'espace des lignes mortes (dead tuples après UPDATE/DELETE)
-- ANALYZE met à jour les statistiques du planner
-- Les deux ensemble : indispensable après toute modification massive des données

-- Vérifier l'état de bloat d'une table
SELECT n_dead_tup, n_live_tup,
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE tablename = 'orders';
-- Si dead_pct > 10-20%, un VACUUM s'impose

La méthode en résumé

Quand une requête PostgreSQL est lente, voilà l'ordre d'investigation qui résout la quasi-totalité des cas :

1. EXPLAIN (ANALYZE, BUFFERS) sur la requête incriminée. Identifier le nœud le plus lent. Chercher Seq Scan sur grande table, écart rows estimated / actual.
2. Index manquant ? Créer avec CONCURRENTLY en prod. Vérifier que la requête est écrite pour en profiter — pas de fonction sur la colonne indexée, pas de LIKE '%...'.
3. Statistiques obsolètes ? VACUUM ANALYZE sur la table concernée.
4. En prod, pas de requête précise à cibler ? pg_stat_statements pour identifier les coupables par temps total ou nombre d'appels.

80% des problèmes de performance PostgreSQL se règlent avec EXPLAIN ANALYZE et un index bien placé. Les 20% restants impliquent du tuning de configuration (shared_buffers, work_mem, max_connections), de la réécriture de requêtes complexes, ou du partitionnement de tables — mais ça, c'est pour un autre article.

La bière peut attendre encore 10 minutes. Le client, non.