Réunion sur les performances : « PostgreSQL ne tient pas la charge. » On discute d'une instance plus puissante. Quelqu'un projette EXPLAIN ANALYZE sur la recherche produits :
Seq Scan on products (cost=0..84291 rows=1 width=120) (actual time=412..412 ms rows=50)
Filter: (category_id = 7 AND price < 100)
Rows Removed by Filter: 890000
412 millisecondes pour 50 lignes — 890 000 lignes filtrées. Index composite (category_id, price) absent. Ce n'est pas un problème de processeur cloud : c'est un problème de plan d'exécution.
EXPLAIN transforme « c'est lent » en mécanisme lisible. Sans lui, vous optimisez au hasard — ou payez une instance plus grosse pour scanner plus vite la même table entière.
Lire un plan : vocabulaire essentiel
| Nœud | Signification | Alerte |
|---|---|---|
| Seq Scan | Lecture de la table entière | Grande table, peu de lignes retournées |
| Index Scan | Parcours d'index | Bon si sélectif |
| Bitmap Index Scan | Plusieurs index combinés | Compromis intermédiaire |
| Nested Loop | Boucle de jointure | Correct petit × indexé ; mauvais si inner en scan séquentiel |
| Hash Join | Table hashée en mémoire | Surveiller la consommation mémoire |
| Sort | Tri explicite | Coûteux si le volume est important |
Métriques clés dans EXPLAIN ANALYZE :
- actual time début..fin par nœud
- rows estimé versus réel — mauvaises statistiques entraînent un mauvais plan
- Buffers shared hit/read — cache chaud versus froid
Un écart estimé/réel multiplié par cent sur le nombre de lignes → lancez
ANALYZE tableavant de créer un index inutile.
Workflow de diagnostic en quinze minutes
- Activez pg_stat_statements :
- Copiez la requête coupable →
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) ... - Identifiez le nœud le plus coûteux (temps × lignes).
- Formulez une hypothèse : index, réécriture de jointure, pagination par curseur.
- Re-mesurez ; comparez mean_exec_time sous charge réelle.
``sql SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; ``
Exemples web courants
Pagination OFFSET lourde
SELECT * FROM articles ORDER BY published_at DESC OFFSET 50000 LIMIT 20;
→ Pagination par curseur : (published_at, id) < ($last, $id).
*COUNT() sur un tableau de bord marketing**
Scan complet — vue matérialisée ou compteur dénormalisé.
JSONB sans index GIN
WHERE data->>'status' = 'active' → index GIN ou colonne générée indexée.
Statistiques et maintenance
VACUUM ANALYZErégulier avec réglage de l'autovacuum.default_statistics_targetplus élevé sur les colonnes filtrées avec distribution asymétrique.- Après une migration massive : statistiques obsolètes = plans temporairement catastrophiques.
PostgreSQL managé (RDS, Scaleway, OVH) : pg_stat_statements souvent activable ; EXPLAIN fonctionne de la même façon qu'en auto-hébergé. Pour choisir entre managé et VPS, voir PostgreSQL managé.
Le sommet : le plan ment quand les statistiques mentent — mais il ment moins que l'intuition
Voici ce que les réunions « on upgrade l'instance » oublient de montrer.
Culture d'équipe : aucun ticket « requête lente » clos sans plan avant/après — même si la correction tient en une ligne d'index.
Décider et avancer sans angle mort
Sur une demi-journée, vous pouvez instaurer une culture de diagnostic PostgreSQL :
- Activez pg_stat_statements en production.
- Analysez les cinq requêtes les plus coûteuses avec EXPLAIN ANALYZE en préproduction.
- Créez un index ou réécrivez la requête — mesurez le gain chiffré.
- Lancez ANALYZE après chaque import massif de données.
- Corrélez les routes applicatives lentes avec les requêtes SQL via votre outil de supervision.
Commencez par la requête qui cumule le plus de temps total — pas celle qui est la plus lente en une seule exécution. Voir Réplication base de données et Index MySQL manquant pour le parallèle côté MySQL.
Questions fréquentes
Quelle différence entre EXPLAIN et EXPLAIN ANALYZE ?
EXPLAIN estime le plan sans exécuter la requête. EXPLAIN ANALYZE l'exécute réellement et affiche les temps mesurés — à utiliser en préproduction ou sur des SELECT prudents, jamais sur un DELETE massif en production.
Seq Scan est-il toujours mauvais ?
Non. Sur une petite table ou quand la majorité des lignes est retournée, le scan séquentiel peut être optimal. C'est un mauvais signe sur une grande table avec peu de lignes retournées et beaucoup de lignes filtrées.
Comment repérer un index manquant ?
Cherchez un scan séquentiel avec un nombre élevé de lignes filtrées et un temps d'exécution dominant. Créez l'index, relancez EXPLAIN ANALYZE et comparez les temps avant et après.
pg_stat_statements suffit-il ?
Non seul. pg_stat_statements identifie quelles requêtes consomment le plus de temps agrégé ; EXPLAIN explique pourquoi. Les deux outils se complètent pour un diagnostic actionnable.
Arrêtez de deviner pourquoi PostgreSQL rame — laissez le plan vous accuser la bonne ligne.