Trouver les requêtes lentes avec pg_stat_statements

Avant d'optimiser, mesurez : pg_stat_statements agrège les statistiques de toutes les requêtes exécutées, normalisées (paramètres remplacés).

-- postgresql.conf : shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;

SELECT round(total_exec_time::numeric, 0)  AS temps_total_ms,
       calls,
       round(mean_exec_time::numeric, 2)   AS moyenne_ms,
       rows,
       left(query, 80)                     AS requete
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

Comment prioriser#

• Temps total élevé : la requête qui pèse le plus sur la base (souvent un calls énorme avec une durée moyenne modeste).
• Moyenne élevée : une requête lente qui pénalise l'utilisateur.
• shared_blks_read élevé : beaucoup de lecture disque, index manquant ou cache trop petit.

Complétez avec log_min_duration_statement (journaliser les requêtes plus lentes qu'un seuil) et auto_explain (journaliser automatiquement les plans des requêtes lentes).

Astuce : optimiser la requête qui représente 40 % du temps total rapporte plus que dix requêtes « lentes » rarement appelées.