Encontrar las consultas más costosas (pg_stat_statements: tiempo total y medio)

Comando

-- 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;

Qué hace

Lista las consultas normalizadas que consumen más tiempo del servidor en la base de datos actual, con su porcentaje del total; la segunda consulta lista las más lentas por llamada.

Cuándo usarlo

Primera parada cuando sube la carga de CPU/IO o los usuarios dicen que 'la base de datos va lenta'; para decidir por dónde empezar el tuning.

Precaución

pg_stat_statements no recopila nada hasta que se añade a shared_preload_libraries, se reinicia el servidor y se ejecuta CREATE EXTENSION en la base de datos. Los valores son acumulados desde el último reset. Un stddev_ms alto en relación con mean_ms indica que el plan o la distribución de los datos varían entre llamadas. mean_plan_time solo se rellena con pg_stat_statements.track_planning = on (off por defecto; habilitarlo añade sobrecarga). Ver los textos de consulta de otros usuarios requiere superusuario o el rol pg_read_all_stats.