Requêtes lentes : méthode complète

Prérequis : cours précédents (index, EXPLAIN).

Pourquoi

Les lenteurs reviennent par vagues (volume qui grandit) : il faut une méthode reproductible, pas du flair. Voici la procédure, dans l'ordre, à chaque fois.

Concepts : la méthode en 5 temps

1. Mesurer : log_min_duration_statement = 500ms en prod + pg_stat_statements (l'extension qui classe les requêtes par temps total — à installer partout).
2. Reproduire : rejouer la requête lente sur une copie (jamais d'EXPERIMENTS en prod).
3. EXPLAIN (ANALYZE, BUFFERS) : trouver le nœud coupable (scan, jointure, tri).
4. Corriger : index (partiel/composite), réécriture (EXISTS vs IN, CTE vs sous-requête), projection/CQRS si structurel.
5. Vérifier : avant/après chiffré, puis monitorer (la requête revient-elle ?).

Exemple : pg_stat_statements

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
-- Les 10 requêtes qui coûtent le plus AU TOTAL : là est le travail rentable

Cas réel

« La base est lente » : pg_stat classe → UNE requête de reporting (lancée toutes les 5 min par un cron oublié) = 70 % du temps total. Supprimée (dashboard passé en matérialisée) : charge divisée par 3. Sans la méthode : upgrade serveur inutile à 200 €/mois.

Pièges

• Optimiser la requête la plus lente au lieu de la plus coûteuse (temps × fréquence !).
• Tester sur 1000 lignes de dev : les plans changent avec le volume. Toujours un jeu réaliste.
• Oublier le réseau/applicatif : parfois la requête est fine, c'est le N+1 applicatif (1000 allers-retours) qui tue.

À retenir

Mesurer (pg_stat) → reproduire → EXPLAIN → corriger → vérifier ; temps × fréquence.