pg_stat_statements: включаем, читаем и улучшаем запросы PostgreSQL по данным факта

Инструменты оптимизации работают лучше всего, когда они опираются на фактические данные, а не на интуицию. В экосистеме PostgreSQL таким источником истины является расширение pg_stat_statements: оно собирает агрегированную статистику по нормализованным запросам, позволяет быстро выделить «тяжелые» места и принять решения — где индекс, где переписать запрос, а где поменять план и настройки.

Зачем именно pg_stat_statements

В отличие от логов, которые нужно парсить, хранить и нормализовать, pg_stat_statements дает готовую модель данных с ключевыми метриками по каждому шаблону запроса: суммарное и среднее время, количество вызовов, число возвращенных строк, I/O по буферам, время на чтение/запись, использование временных файлов, запись WAL и многое другое. Вы получаете ответы быстро и без сложных интеграций.

Главная ценность — нормализация: различные конкретные значения подставляются как параметры $1, $2 и т.д., поэтому статистика агрегируется по шаблонам, а не по миллионам вариантов одного и того же запроса.

Подготовка и включение

Расширение входит в стандартный комплект «contrib», но для сбора статистики нужно подготовить конфигурацию и перезапустить сервер. Минимальный план таков:

1. Включить расширение в shared_preload_libraries.
2. На PostgreSQL 14+ включить compute_query_id (расширение может использовать ядровой идентификатор запросов).
3. Включить track_io_timing, чтобы видеть стоимость чтения/записи.
4. Настроить параметры самого расширения: pg_stat_statements.max, pg_stat_statements.save, pg_stat_statements.track и при необходимости pg_stat_statements.track_utility.
5. Перезапустить кластер и создать расширение в нужных базах.

Базовые команды

Проверяем, загружено ли расширение:

SHOW shared_preload_libraries;

Включаем через ALTER SYSTEM (или редактируем postgresql.conf):

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET compute_query_id = 'on';
ALTER SYSTEM SET track_io_timing = 'on';
ALTER SYSTEM SET pg_stat_statements.max = '10000';
ALTER SYSTEM SET pg_stat_statements.save = 'on';
ALTER SYSTEM SET pg_stat_statements.track = 'top';
ALTER SYSTEM SET pg_stat_statements.track_utility = 'off';

Пояснения по ключевым пунктам:

• compute_query_id=on на версиях 14+ позволяет задействовать ядровой queryid. Если параметр недоступен (версия ниже), расширение продолжит работать со своей логикой идентификации.
• track_io_timing=on добавляет метрики blk_read_time/blk_write_time. Оверхед есть, но на практике он небольшой и окупается диагностикой I/O.
• pg_stat_statements.max — размер хэша уникальных шаблонов. Если он маловат, разные запросы будут вытеснять друг друга.
• pg_stat_statements.track=top — собираем только верхнеуровневые запросы (без вложенных). Включение all увеличивает детализацию и оверхед.
• pg_stat_statements.track_utility=off — не засоряем статистику командами DDL/maintenance. При необходимости включайте.

Если вы активно анализируете тексты запросов, свойство track_activity_query_size имеет значение: оно ограничивает длину текста, попадающего в статистику (косвенно влияет и на pg_stat_statements). Обычно хватает 2048–4096 байт. Этот параметр требует перезапуска.

После изменения настроек перезапустите кластер. Затем в каждой целевой базе выполните:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Проверить готовность можно простейшей выборкой:

SELECT * FROM pg_stat_statements LIMIT 1;

Что именно меряет pg_stat_statements

Каждая строка — агрегированные данные по «шаблону» запроса (нормализованному тексту query, где литералы заменены на $1, $2, ...). Важные колонки:

• queryid — идентификатор шаблона.
• query — нормализованный SQL.
• calls — количество вызовов.
• total_time (мс), mean_time, min_time, max_time, stddev_time.
• rows — строк, возвращенных суммарно.
• shared_blks_read/hit/dirtied/written — доступы к буферам.
• temp_blks_read/written — пролития во временные файлы (признак тяжелых сортировок/хэшей).
• blk_read_time/blk_write_time — время I/O, доступно при track_io_timing=on.
• На новых версиях также: wal_records, wal_fpi, wal_bytes — полезно для оценки «болезненности» записей.

Статистика хранится глобально и принадлежит всем базам кластера. Вьюха pg_stat_statements показывает записи по всем БД, поэтому удобно присоединять pg_database, чтобы видеть распределение по базам.

Быстрые выборки для приоритезации

Вот готовый набор фильтров, которыми я пользуюсь в первую очередь. Они помогают быстро построить бэклог оптимизаций по принципу 80/20.

Топ по суммарному времени

SELECT d.datname,
       s.queryid,
       s.calls,
       ROUND(s.total_time/1000, 1) AS total_s,
       ROUND(s.mean_time, 2) AS mean_ms,
       s.rows,
       s.query
FROM pg_stat_statements s
JOIN pg_database d ON d.oid = s.dbid
ORDER BY s.total_time DESC
LIMIT 20;

Суммарное время — главный приоритет: запрос может быть даже «не очень медленным», но если он вызывается миллионы раз, именно он сжигает процессор и I/O.

Топ по среднему времени

SELECT d.datname,
       s.queryid,
       s.calls,
       ROUND(s.mean_time, 2) AS mean_ms,
       ROUND(s.total_time/1000, 1) AS total_s,
       s.query
FROM pg_stat_statements s
JOIN pg_database d ON d.oid = s.dbid
WHERE s.calls >= 10
ORDER BY s.mean_time DESC
LIMIT 20;

Фокус на «штучных» тяжеловесах. Важно проверять, нет ли разовых аналитических выборок или админских запросов.

Запросы, проливающие во временные файлы

SELECT d.datname,
       s.queryid,
       s.calls,
       s.temp_blks_read + s.temp_blks_written AS temp_blks,
       ROUND(s.total_time/1000, 1) AS total_s,
       s.query
FROM pg_stat_statements s
JOIN pg_database d ON d.oid = s.dbid
WHERE (s.temp_blks_read + s.temp_blks_written) > 0
ORDER BY temp_blks DESC
LIMIT 20;

Проливы во временные файлы — верный признак того, что сортировки/хэш-агрегации не поместились в рабочую память. Здесь возможны два направления: индексы и увеличение профилей памяти для сортировки/хэша (например, work_mem точечно на сессию).

Подозрительно мало строк при большом времени

SELECT d.datname,
       s.queryid,
       s.calls,
       s.rows,
       ROUND(s.mean_time, 2) AS mean_ms,
       s.query
FROM pg_stat_statements s
JOIN pg_database d ON d.oid = s.dbid
WHERE s.calls >= 100
  AND s.rows <= 1
  AND s.mean_time >= 20
ORDER BY s.mean_time DESC
LIMIT 20;

Частая причина — отсутствие селективного индекса, несоответствие типов или фильтр с функцией, отрезающий индексирование.

Лидеры по записи WAL

SELECT d.datname,
       s.queryid,
       s.calls,
       s.wal_bytes,
       ROUND(s.total_time/1000, 1) AS total_s,
       s.query
FROM pg_stat_statements s
JOIN pg_database d ON d.oid = s.dbid
WHERE s.wal_bytes IS NOT NULL
ORDER BY s.wal_bytes DESC
LIMIT 20;

Высокие wal_bytes часто указывают на массовые UPDATE/INSERT, неэффективные батчи или лишние индексы, которые удорожают запись. При активном использовании репликации и PITR полезно освежить методику по PITR и резервному копированию WAL.

Разбор и улучшения: практический цикл

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

EXPLAIN (ANALYZE, BUFFERS), план и буферы

Выбираем один проблемный запрос (из топа по total_time или mean_time) и исполняем его с замером плана:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...
FROM ...
WHERE ...
ORDER BY ...
LIMIT ...;

Ищем:

• Последовательные сканы больших таблиц там, где ожидали индекс (Seq Scan вместо Index Scan).
• Дорогие join-операции с неправильным порядком соединений или плохими селективностями.
• Сортировки/агрегации, выходящие в temp (смотри BUFFERS и temp_blks_* в статистике).
• Несоответствие типов (явные/неявные касты), функции по колонке в WHERE, мешающие использованию индекса.

Пример: дата и функция в фильтре

Имеем:

SELECT count(*)
FROM orders
WHERE created_at::date = DATE '2025-10-01';

Конструкция created_at::date обламывает потенциальный индекс по created_at. Переписываем диапазоном и проверяем, что есть индекс:

CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders (created_at);

SELECT count(*)
FROM orders
WHERE created_at >= TIMESTAMP '2025-10-01 00:00:00'
  AND created_at <  TIMESTAMP '2025-10-02 00:00:00';

После деплоя снова смотрим pg_stat_statements: должно упасть mean_time и/или total_time (если запрос массовый).

OFFSET против keyset-пагинации

Большие OFFSET приводят к линейной цене. В статистике это часто видно как немалое mean_time у запросов со множеством вызовов и сортировкой по одной и той же колонке. Лекарство — пагинация по курсору/ключу («keyset»), когда вместо OFFSET используем условие по последнему ключу и индекс.

N+1 и «шумные» частые запросы

Если у запроса огромный calls при крошечных rows и заметном total_time, проверьте N+1 в приложении. Часто помогает объединение в один запрос с JOIN или батчевый IN. Цель — уменьшить количество вызовов без потери функциональности.

Запись и WAL

Если метрики wal_bytes и blk_write_time высокие, проверьте:

• Слишком частые коммиты в цикле — объединяйте в разумные батчи.
• Лишние индексы на таблицах с интенсивной записью — каждый индекс удорожает INSERT/UPDATE/DELETE.
• Триггеры и каскады, делающие дополнительную работу.

Подтвердить «I/O-bound» поможет системная телеметрия: диагностика дисковой подсистемы (iostat, iotop, fio).

Управление жизненным циклом статистики

Статистика живет до сброса и может сохраняться между рестартами (pg_stat_statements.save=on). Полезные операции:

• Сделать срез перед изменениями: снимите топы и сохраните их как артефакты.
• Сбросить статистику после внедрения оптимизаций, чтобы видеть чистый эффект.

SELECT pg_stat_statements_reset();

Иногда удобнее не сбрасывать глобально, а держать в виду временной контекст: «с момента N». Для этого просто фиксируйте отметки времени и сравнивайте дельты метрик.

Для безопасных экспериментов удобно использовать отдельный стенд на VDS, чтобы проверять индексы и планировщик без влияния на прод.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

Доступ и безопасность

Создавать расширение может суперпользователь. Для чтения статистики достаточно прав на селект вьюхи; в новых версиях достаточно роли pg_read_all_stats. Хорошая практика — выделить сервисную роль «чтения метрик» и не раздавать лишнего.

Версионные нюансы

• PostgreSQL 14+: появился compute_query_id — используйте on, чтобы обеспечить устойчивую идентификацию запросов ядром.
• Состав колонок расширяется: на новых версиях доступны поля по WAL. При миграции проверяйте SELECT * FROM pg_stat_statements LIMIT 0, чтобы увидеть актуальные колонки.
• Если вы видите много «одинаковых» запросов с разными текстами — возможно, нормализация не происходит из-за особенностей клиента. Проверьте, действительно ли запросы параметризуются, а не собираются строками.

Типичные ошибки эксплуатации

• Забыли про shared_preload_libraries: без него расширение не соберет статистику. Проверьте после рестарта, что оно реально подхватилось.
• Отключенный track_io_timing: без него сложно отличать CPU-bound от I/O-bound запросов.
• Маленький pg_stat_statements.max: новые шаблоны вытесняют старые, искажается картина. Увеличьте лимит.
• Сельдь под шубой метрик: не смешивайте разовый бэкап/миграцию с пользовательским трафиком при анализе. Снимайте срезы в сопоставимых условиях.
• Упор только в «медленные»: запрос с mean_time=5 ms может быть топ-«пожирателем» по total_time из-за частоты вызовов.

Встраиваем в процесс

Хороший поток оптимизации выглядит так:

1. Раз в неделю/итерацию — срез топов по total_time, mean_time, проливам и WAL.
2. Выбор 2–3 кандидатов с максимальной отдачей.
3. EXPLAIN, гипотезы, эксперимент: индекс, переписывание, настройки планировщика, изменение батчей.
4. Деплой, сброс статистики, повторный замер.
5. Постоянная гигиена: следить за регрессией при релизах.

Если у вас есть метрики инфраструктуры, полезно сопоставлять пики total_time с CPU, IOPS и задержками диска, чтобы быстро разделять «узкое место БД» и «узкое место диска/сети».

Чеклист запуска pg_stat_statements

• shared_preload_libraries='pg_stat_statements' включен.
• compute_query_id=on (на 14+).
• track_io_timing=on.
• pg_stat_statements.max ≥ 10000 (или больше, по нагрузке).
• pg_stat_statements.save=on.
• pg_stat_statements.track=top для старта.
• Опционально pg_stat_statements.track_utility=off.
• Увеличить track_activity_query_size до 2048–4096 при длинных SQL.
• Перезапуск и CREATE EXTENSION в нужных БД.
• Быстрые топы — в бэклог оптимизаций.

Итоги

pg_stat_statements — это «профайлер по факту», который превращает оптимизацию из гадания в инженерную практику. Включите его один раз правильно, заведите привычку смотреть на топы по total_time и mean_time, и вы быстро найдёте самые «дорогие» запросы. Дальше — дело техники: план, индекс, переписывание и замер результата. Повторяйте цикл — и база будет становиться быстрее без лишней магии.