Trouver les requêtes les plus coûteuses (pg_stat_statements : temps total et moyen)

Commande

-- By total time: queries consuming the most server time
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct_total,
       rows / NULLIF(calls, 0) AS rows_per_call,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

-- By mean time per call: individually slow queries
SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(max_exec_time::numeric, 2) AS max_ms,
       round(mean_plan_time::numeric, 2) AS mean_plan_ms,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS query
FROM pg_stat_statements
WHERE calls >= 50
ORDER BY mean_exec_time DESC
LIMIT 20;

Ce que fait la commande

Liste les requêtes normalisées qui consomment le plus de temps serveur dans la base courante, avec leur part du total ; la seconde requête liste les plus lentes par appel.

Quand l'utiliser

Premier réflexe lorsque la charge CPU/E-S augmente ou que les utilisateurs signalent que 'la base est lente' ; pour décider par où commencer le tuning.

Attention

pg_stat_statements ne collecte rien tant qu'il n'est pas ajouté à shared_preload_libraries, que le serveur n'est pas redémarré et que CREATE EXTENSION n'est pas exécuté dans la base. Les valeurs sont cumulées depuis la dernière réinitialisation. Un stddev_ms élevé par rapport à mean_ms indique que le plan ou la distribution des données varie d'un appel à l'autre. mean_plan_time n'est renseigné qu'avec pg_stat_statements.track_planning = on (off par défaut ; l'activer ajoute une surcharge). Voir le texte des requêtes des autres utilisateurs exige un superutilisateur ou le rôle pg_read_all_stats.