Une requête qui tournait en 20 millisecondes en développement met soudain 4 secondes en production, sur une table qui a dépassé le million de lignes. Le réflexe classique est d'ajouter un index sur la colonne du WHERE et d'espérer que ça suffise. Parfois ça fonctionne, souvent non, parce que la vraie cause est ailleurs : un mauvais ordre de colonnes dans un index composite, des statistiques obsolètes, ou une requête qui empêche structurellement PostgreSQL d'utiliser l'index existant. EXPLAIN ANALYZE est l'outil qui permet de sortir de la devinette.
Ce que fait réellement le planificateur
PostgreSQL ne choisit pas un plan d'exécution au hasard. Pour chaque requête, le planificateur estime le coût de plusieurs stratégies possibles (scan séquentiel, scan d'index, scan d'index bitmap, jointures en nested loop, hash join, merge join...) à partir de statistiques qu'il maintient sur chaque table : nombre de lignes estimé, cardinalité distincte des colonnes, distribution des valeurs (histogrammes). Ces statistiques sont mises à jour par ANALYZE, lancé automatiquement par autovacuum mais pas toujours à la fréquence qu'on croit.
Le point essentiel à comprendre : un index existant n'est jamais une garantie d'utilisation. Si le planificateur estime qu'un scan séquentiel coûte moins cher qu'un scan d'index (par exemple parce que la condition du WHERE matche 40% des lignes de la table), il choisira le scan séquentiel, et il aura probablement raison. Un index n'est rentable que lorsque la sélectivité de la condition est suffisamment forte.
Lire un plan EXPLAIN ANALYZE
La commande à lancer en diagnostic est :
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
AND o.created_at > now() - interval '30 days'
ORDER BY o.created_at DESC
LIMIT 50;
ANALYZE exécute réellement la requête (attention sur un UPDATE/DELETE, encapsuler dans une transaction annulée) et affiche les temps réels à côté des estimations. BUFFERS ajoute le nombre de blocs lus en cache (shared hit) versus sur disque (shared read), ce qui est souvent plus révélateur que le temps lui-même.
Un plan dégradé ressemble typiquement à ceci :
Limit (cost=15234.12..15234.25 rows=50 width=48) (actual time=812.441..812.498 rows=50 loops=1)
-> Sort (cost=15234.12..15298.67 rows=25820 width=48) (actual time=812.439..812.460 rows=50 loops=1)
Sort Key: o.created_at DESC
Sort Method: top-N heapsort Memory: 28kB
-> Hash Join (cost=892.00..14560.33 rows=25820 width=48) (actual time=4.102..798.215 rows=26104 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (cost=0.00..13201.00 rows=25820 width=24) (actual time=0.015..742.330 rows=26104 loops=1)
Filter: ((status = 'pending') AND (created_at > (now() - '30 days'::interval)))
Rows Removed by Filter: 473896
-> Hash (cost=612.00..612.00 rows=22000 width=32) (actual time=3.890..3.891 rows=22000 loops=1)
-> Seq Scan on customers c (cost=0.00..612.00 rows=22000 width=32)
Planning Time: 0.412 ms
Execution Time: 814.102 ms
Les signaux à repérer dans l'ordre :
Seq ScansurordersavecRows Removed by Filter: 473896: la base lit 500 000 lignes pour n'en garder que 26 000. C'est le cœur du problème.- Lire le plan de bas en haut : l'exécution commence par les nœuds les plus internes.
- Comparer
rowsestimé etactual rows: un écart important (par exemple estimé à 500, réel à 26 000) signale des statistiques obsolètes et doit faire lancer unANALYZE ordersmanuel avant toute autre action. Execution Timevs somme des temps des nœuds : unSortou unHashqui dépassework_membascule sur disque (Sort Method: external merge), ce qui coûte beaucoup plus cher qu'un tri en mémoire.
Le correctif : un index adapté au filtre réel
La requête filtre sur status et created_at, puis trie sur created_at. Un index composite qui respecte l'ordre d'usage (égalité d'abord, intervalle/tri ensuite) permet un scan d'index direct sans tri supplémentaire :
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
WHERE status = 'pending';
Ici, l'index est partiel (WHERE status = 'pending') parce que seule cette valeur de status est interrogée fréquemment dans ce chemin de code ; un index partiel reste petit et rapide à maintenir même quand la table grossit. CONCURRENTLY évite de verrouiller les écritures pendant la construction, au prix d'une création plus lente et de la nécessité de gérer un éventuel échec (l'index reste alors marqué INVALID et doit être supprimé puis recréé).
Après création, le plan devient :
Limit (cost=0.43..892.31 rows=50 width=48) (actual time=0.052..1.845 rows=50 loops=1)
-> Nested Loop (cost=0.43..5102.88 rows=286 width=48) (actual time=0.051..1.832 rows=50 loops=1)
-> Index Scan using idx_orders_status_created_at on orders o
(cost=0.43..1890.21 rows=286 width=24) (actual time=0.028..0.912 rows=50 loops=1)
Index Cond: (created_at > (now() - '30 days'::interval))
-> Index Scan using customers_pkey on customers c
(cost=0.29..0.35 rows=1 width=32) (actual time=0.015..0.016 rows=1 loops=50)
Index Cond: (id = o.customer_id)
Execution Time: 2.103 ms
Le tri a disparu (l'index fournit déjà l'ordre created_at DESC), le scan séquentiel a disparu, et le temps passe de 814 ms à 2 ms.
Les pièges qui empêchent un index d'être utilisé
Même avec le bon index créé, plusieurs erreurs courantes le rendent invisible au planificateur :
- Mismatch de type implicite : comparer une colonne
varcharà un littéral numérique, ou une colonnetimestampà une chaîne mal castée, force PostgreSQL à caster chaque ligne, ce qui annule l'index. - Fonction appliquée sur la colonne indexée :
WHERE lower(email) = 'x@y.com'ignore un index classique suremail. Il faut soit un index d'expression (CREATE INDEX ON customers (lower(email))), soit changer la requête. ORentre colonnes différentes :WHERE status = 'pending' OR priority = 'high'ne peut pas exploiter un seul index composite efficacement ; envisager deux index séparés combinés par unBitmapOr, que le planificateur choisit automatiquement si les deux index existent.- Ordre des colonnes dans un index composite : un index
(created_at, status)n'aide pas une requête qui filtre surstatusseul, pour la même raison qu'un annuaire téléphonique trié par ville puis par nom n'aide pas à chercher par nom seul.
Quand l'indexation ne suffit pas
Ajouter des index a un coût qu'on oublie trop souvent : chaque INSERT/UPDATE doit maintenir tous les index de la table, donc une table avec 15 index sur un flux d'écriture intense peut voir ses latences d'écriture exploser alors que les lectures volent. Dans ce cas, les options sérieuses sont :
- Le partitionnement de la table (par plage de dates typiquement) quand le volume dépasse plusieurs dizaines de millions de lignes et que les requêtes filtrent naturellement sur la clé de partition.
- Une vue matérialisée rafraîchie périodiquement si la requête sert un tableau de bord qui tolère quelques minutes de retard.
- Revoir le modèle applicatif lui-même (dénormalisation ciblée, cache Redis en amont) quand la requête est appelée à très haute fréquence et que même 2 ms cumulés sur des milliers d'appels par seconde pèsent sur l'infrastructure.
À retenir
EXPLAIN ANALYZE avec BUFFERS donne toujours un fait, jamais une supposition : il faut lire de bas en haut, comparer rows estimé à actual rows, et chercher les Rows Removed by Filter élevés. Un index n'est une solution que s'il correspond exactement à la sélectivité et à l'ordre de tri de la requête réelle, pas à une intuition sur "la colonne du WHERE". Avant de créer un index en production, tester son effet avec EXPLAIN ANALYZE sur un jeu de données représentatif, et le créer avec CONCURRENTLY.
