Тяжёлые запросы в PostgreSQL: поиск через pg_stat_statements

Запрос, который выполняется 20 мс, может съесть больше ресурсов, чем запрос на секунду, если запускается десятки тысяч раз. В PostgreSQL 16 расширение pg_stat_statements помогает найти обе проблемы: показывает накопленное время, среднюю длительность, число вызовов и работу с блоками. Ниже — практический способ настроить сбор и выбрать запросы для разбора, не сбрасывая статистику наугад.

Сначала включите сбор статистики

Инструкция рассчитана на PostgreSQL 16. pg_stat_statements входит в поставку PostgreSQL, но серверу нужно загрузить модуль при старте. Для этого проверьте параметр shared_preload_libraries в конфигурации: добавьте pg_stat_statements, сохранив уже перечисленные библиотеки. Изменение требует перезапуска сервера, а не только перезагрузки конфигурации.

После перезапуска подключитесь к нужной базе данных и создайте расширение:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Расширение нужно создать в каждой базе, статистику которой вы хотите смотреть. Для чтения чужих записей могут понадобиться права суперпользователя или роль с правом pg_read_all_stats. Если доступа не хватает, попросите администратора выдать нужные права — не пытайтесь обходить ограничения.

Базовая настройка собирает статистику для запросов верхнего уровня. Параметр pg_stat_statements.track можно установить в all, если важно видеть также запросы, запускаемые внутри функций. Это добавит детализации, но увеличит объём собираемых данных и может усложнить интерпретацию: один запрос приложения способен породить несколько записей.

Начните с общего времени, а не с самого медленного вызова

Выполните запрос ниже в той базе, которую анализируете. Он выводит 20 записей с наибольшим суммарным временем выполнения и долю каждой записи среди запросов этой базы в текущей статистике.

SELECT
    queryid,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    rows,
    round(
        (100 * total_exec_time / sum(total_exec_time) OVER ())::numeric,
        1
    ) AS share_pct,
    shared_blks_read,
    temp_blks_written,
    left(query, 500) AS query_sample
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
  AND calls > 0
ORDER BY total_exec_time DESC
LIMIT 20;

Сначала смотрите на total_ms и calls. Высокое суммарное время означает, что запись заметно участвовала в общей нагрузке за период накопления статистики. Высокое mean_ms при небольшом числе вызовов — повод проверить отдельный медленный сценарий, но не обязательно причина общей загрузки сервера. Колонка query_sample показывает пример нормализованного запроса; это не журнал с исходными значениями параметров.

Если ваш аккаунт видит несколько баз, фильтр по dbid помогает не смешивать их записи. Само представление группирует статистику не только по тексту, но и по базе, пользователю, идентификатору запроса и признаку верхнего уровня. Поэтому queryid в одиночку не всегда достаточно, чтобы сопоставить запись с конкретным действием приложения.

Три рейтинга — три разных вопроса

Не сортируйте список по одной колонке при каждом разборе. Выбирайте рейтинг под симптом: общий расход времени, медленные отдельные выполнения или работу с временными файлами.

Чтобы проверить временные блоки, повторите выборку, замените ORDER BY total_exec_time на ORDER BY temp_blks_written DESC и оставьте те же столбцы. Высокое значение — сигнал исследовать запрос, а не доказательство ошибки само по себе. Для поиска медленных в среднем записей сортируйте по mean_exec_time DESC и задайте минимальное число вызовов, например calls >= 10, чтобы единичный запуск не возглавил список. Этот порог — фильтр для анализа, а не универсальная рекомендация.

Как выбрать кандидата

• Если база постоянно занята, начните с total_exec_time: частые запросы могут быть важнее редких долгих.
• Если пользователи жалуются на долгий конкретный экран или операцию, ищите высокий mean_exec_time и сверяйте результат с логами приложения.
• Если растут временные файлы, изучите temp_blks_written и план выполнения: запрос может сортировать или обрабатывать больше данных, чем ожидалось.
• Если важна нагрузка на чтение, смотрите shared_blks_read вместе с попаданиями в буферный кеш и доступными метриками времени чтения.

shared_blks_read — число блоков, которые запрос прочитал через механизм общих буферов PostgreSQL. Это не прямой счётчик чтений с физического диска: нужные данные могли оказаться в кеше операционной системы. Время чтения и записи PostgreSQL собирает только при включённом track_io_timing; включайте его осознанно и оценивайте накладные расходы в вашей среде.

Самый долгий запрос — не всегда главный виновник. Ищите тот, который совпадает с симптомом: суммарной загрузкой, задержкой конкретного действия или ростом временных данных.

Статистика не равна временному окну

Счётчики pg_stat_statements накапливаются, а не показывают автоматически последние пять минут. После сброса статистики или перезапуска период наблюдения изменится; отдельные записи могут вытесняться, когда достигнут лимит, заданный конфигурацией. Поэтому колонка share_pct в запросе — доля среди текущих записей в текущем наборе накопленных данных, а не доля нагрузки за конкретный интервал.

Чтобы понять, что изменилось после релиза, снимите значения до и после него и сравните прирост calls и total_exec_time за одинаковый интервал. Для регулярного наблюдения используйте систему мониторинга, которая сохраняет снимки. Не вызывайте сброс статистики просто ради «чистого списка»: вы потеряете точку сравнения и можете скрыть предшествовавшую проблему.

В PostgreSQL 16 состояние сборщика можно проверить так:

SELECT stats_reset, dealloc
FROM pg_stat_statements_info;

stats_reset показывает время последнего сброса статистики, а dealloc — число вытесненных записей. Рост dealloc означает, что часть исторических записей могла исчезнуть из представления; при разборе учитывайте настроенный размер набора.

Что может исказить вывод

Нормализованный текст и queryid

Нормализованный запрос — представление, в котором конкретные литералы заменяются параметрами, чтобы одинаковые по структуре запросы с разными значениями учитывались вместе. Например, поиск по разным идентификаторам может попасть в одну запись. Это удобно для рейтинга, но скрывает разницу между параметрами: один тип значения иногда приводит к существенно более тяжёлому плану. Сверяйте статистику с логами приложения и реальными условиями вызова.

queryid вычисляется по структуре запроса и не стоит считать его постоянным идентификатором между крупными версиями PostgreSQL или разными сборками. При сравнении после обновления сопоставляйте также текст запроса, базу и пользователя. Если текст обрезан, администратор может проверить параметр track_activity_query_size и актуальную документацию PostgreSQL.

Счётчики не объясняют причину

pg_stat_statements помогает выбрать кандидатов, но не заменяет анализ плана. Возьмите конкретный запрос, проверьте его с теми же условиями и данными, которые характерны для проблемы, затем изучите план выполнения. Не запускайте EXPLAIN ANALYZE без понимания последствий на запросах, которые меняют данные: такой разбор действительно выполняет запрос.

Таблица показывает агрегаты, а не пики задержки и не процентиль для каждого вызова. Среднее может выглядеть приемлемо, хотя часть пользователей регулярно ждёт дольше. Для таких случаев сопоставляйте агрегаты с метриками приложения и журналами медленных запросов.

Что делать дальше

Выберите из рейтинга одну запись, связанную с реальным симптомом, и зафиксируйте период наблюдения, число вызовов и среднее время. Затем сопоставьте её с конкретным действием приложения, получите план выполнения и только после этого меняйте запрос или индексы. После изменения сравните снимки за сопоставимые интервалы: сколько стало вызовов, как изменилось суммарное время и не выросло ли потребление временных блоков.

Инвалидация статистики, настройка журнала медленных запросов и анализ планов — отдельные темы; здесь мы рассматриваем только поиск кандидатов через pg_stat_statements.