Comment nous avons réduit le temps d'exécution d'une requête de base de données de 10 secondes à 50 ms

Le problème : un tableau de bord qui mettait 10 secondes à se charger

Le tableau de bord analytique d'un client mettait plus de 10 secondes à se charger. Les utilisateurs le quittaient. Le directeur technique était en panique. L'équipe backend affirmait : « Ça fonctionne très bien en local. » Ça vous dit quelque chose ?

Voici exactement comment nous avons identifié le problème, l'avons résolu et avons réduit le temps de réponse à 50 ms. Pas de miracle : juste un débogage systématique.

Étape 1 : Identifier la requête lente

Avant de nous lancer dans des conjectures, nous avons activé le journal des requêtes lentes de PostgreSQL afin de détecter toute requête dépassant 500 ms :

-- Dans PostgreSQL : 
log_min_duration_statement = 

500 -- Ou pour une seule session : 
SET log_min_duration_statement = 500;

Le coupable s'est immédiatement imposé : une seule requête joignant quatre tables prenait 10,2 secondes :

SELECT o.id, o.total, c.name, c.email,      
 COUNT(oi.id) AS item_count,      
 SUM(oi.quantity * oi.unit_price) as 
calculated_totalFROM orders 
oJOIN customers c ON c.id = o.
customer_idJOIN order_items oi ON oi.order_id = o.
idJOIN products p ON p.id = oi.
product_idWHERE o.created_at >= '2025-01-01'
  AND o.status IN ('completed', 'shipped')
  AND c.region = 
'asia-pacific'GROUP BY o.id, o.total, c.name, c.
emailORDER BY o.created_at 
DESCLIMIT 50;

Étape 2 : EXPLAIN ANALYZE — Découvrez ce que fait réellement PostgreSQL

L'exécution de la commande EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) a mis en évidence le véritable problème :

Recherche séquentielle sur les commandes o  (coût = 0,00..185 432,00 lignes = 892 145 largeur = 48)
  Filtre : (created_at >= '2025-01-01' ET status = ANY(...))
  Lignes supprimées par le filtre : 3241876
  Tampons : hit partagé=12 lecture=142891

Analyse séquentielle sur 4 millions de lignes. PostgreSQL lisait l'intégralité de la table « orders » à partir du disque, car il n'y avait pas d'index sur les colonnes « created_at » ou « status ». La valeur « Buffers: read=142891 » confirme un volume considérable d'E/S disque.

Étape 3 : La solution — Index composite + index partiel

Un index à colonne unique sur `created_at` serait utile, mais un index composite ciblant précisément ce type de requête est bien plus efficace :

-- Index composite correspondant à la 
clause WHERE CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC)
  WHERE status IN ('completed', 'shipped');

-- Index sur la jointure de clé étrangère (souvent oublié !)
CREATE INDEX CONCURRENTLY idx_order_items_order_id
  ON order_items (order_id)
  INCLUDE (quantity, unit_price);  -- index couvrant

La clause WHERE status IN (...) sur l'index en fait un index partiel : il n'indexe que les lignes sur lesquelles porte effectivement la requête, ce qui lui permet de rester compact et rapide. La clause INCLUDE sur le deuxième index en fait un index couvrant : PostgreSQL peut répondre à la requête à partir de l'index seul, sans avoir à consulter la table.

Étape 4 : Restructuration de la requête à l'aide d'une CTE

La requête initiale calculait les agrégats sur l'ensemble des résultats avant d'appliquer la clause LIMIT. Nous l'avons restructurée pour effectuer d'abord le filtrage, puis l'agrégation :

WITH recent_orders AS (
  SELECT o.id, o.total, o.customer_id, o.created_at
  FROM orders o
  WHERE o.created_at >= '2025-01-01'
    AND o.status IN ('completed', 'shipped')
  ORDER BY o.created_at DESC
  LIMIT 50
)
SELECT ro.id, ro.total, c.name, c.email,      
 COUNT(oi.id) as item_count,      
 SUM(oi.quantity * oi.unit_price) as 
calculated_totalFROM recent_orders 
roJOIN customers c ON c.id = ro.
customer_idJOIN order_items oi ON oi.order_id = ro.
idGROUP BY ro.id, ro.total, c.name, c.email, ro.
created_atORDER BY ro.created_at DESC;

Cette requête récupère d'abord seulement 50 commandes, puis effectue la jointure. Au lieu d'agréger des millions de lignes, nous effectuons la jointure sur 50 lignes.

Le résultat

Système métrique	Avant	Après
Durée de la requête	10 200 ms	47 ms
Lignes analysées	4 134 021	50
Lectures sur disque	142 891 tampons	12 tampons
Index utilisé	Aucun (balayage séquentiel)	idx_statut_des_commandes_créées

Enseignements tirés

1. Vérifiez toujours EXPLAIN ANALYZE en production, et pas seulement en environnement local. Les bases de données locales disposent de caches récentes et contiennent des ensembles de données de petite taille.

2. Les colonnes de clés étrangères doivent être indexées. Django et Rails les créent automatiquement, mais elles font souvent défaut dans les schémas SQL bruts.

3. L'opérateur LIMIT n'empêche pas les balayages complets de la table lorsqu'il est associé à GROUP BY. Reformulez la requête pour effectuer d'abord un filtrage.

4. Utilisez le mode CONCURRENT lors de la création d'index en production afin d'éviter le verrouillage de la table.

La requête la plus coûteuse est celle qui n'a pas fait l'objet d'un profilage. Intégrez l'utilisation de `EXPLAIN ANALYZE` à votre processus de révision du code pour toute requête portant sur plus de 10 000 lignes.
— alokknight Ingénierie