Optimiser requête PostgreSQL : 10s à 50ms

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 :

-- In postgresql.conf
log_min_duration_statement = 500

-- Or for a single 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_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE 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.email
ORDER BY o.created_at DESC
LIMIT 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 :

Seq Scan on orders o  (cost=0.00..185432.00 rows=892145 width=48)
  Filter: (created_at >= '2025-01-01' AND status = ANY(...))
  Rows Removed by Filter: 3241876
  Buffers: shared hit=12 read=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 :

-- Composite index matching the WHERE clause
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC)
  WHERE status IN ('completed', 'shipped');

-- Index on the foreign key join (often missed!)
CREATE INDEX CONCURRENTLY idx_order_items_order_id
  ON order_items (order_id)
  INCLUDE (quantity, unit_price);  -- covering index

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_total
FROM recent_orders ro
JOIN customers c ON c.id = ro.customer_id
JOIN order_items oi ON oi.order_id = ro.id
GROUP BY ro.id, ro.total, c.name, c.email, ro.created_at
ORDER 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

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