Найти самые дорогие запросы (pg_stat_statements: общее и среднее время)

Команда

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

Что делает

Выводит нормализованные запросы, потребляющие больше всего серверного времени в текущей базе данных, с их долей от общего; второй запрос выводит самые медленные в расчёте на вызов.

Когда использовать

Первое, куда смотреть при росте нагрузки CPU/IO или жалобах пользователей 'база тормозит'; чтобы решить, с чего начинать настройку.

Внимание

pg_stat_statements ничего не собирает, пока не добавлен в shared_preload_libraries, сервер не перезапущен и в базе не выполнен CREATE EXTENSION. Значения накапливаются с момента последнего сброса. stddev_ms, высокий относительно mean_ms, указывает, что план или распределение данных меняются от вызова к вызову. mean_plan_time заполняется только при pg_stat_statements.track_planning = on (по умолчанию off; включение добавляет накладные расходы). Для просмотра текстов запросов других пользователей нужен суперпользователь или роль pg_read_all_stats.