Мониторинг медленных запросов в PostgreSQL: pg_stat_statements

На production-базе регулярно появляются запросы, которые незаметно съедают ресурсы: один запрос выполняется 50 мс, но вызывается 10 000 раз в минуту — и суммарно занимает больше времени, чем редкий, но тяжёлый отчёт на 30 секунд. Найти такие запросы вручную невозможно. Для этого в PostgreSQL есть расширение pg_stat_statements.

Включение pg_stat_statements

Расширение поставляется вместе с PostgreSQL, но не активировано по умолчанию. Добавьте его в postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all        # all / top / none
pg_stat_statements.max = 10000        # максимум отслеживаемых запросов
pg_stat_statements.track_utility = on # DDL и COPY тоже

После изменения shared_preload_libraries нужен рестарт PostgreSQL. Затем подключитесь к нужной базе и создайте расширение:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Проверить, что работает
SELECT count(*) FROM pg_stat_statements;

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

Самый полезный запрос для первого анализа — топ по total_exec_time. Это запросы, которые в сумме потребляли больше всего процессорного времени за всё время работы:

SELECT
    round(total_exec_time::numeric, 2)               AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2)                AS mean_ms,
    round(stddev_exec_time::numeric, 2)              AS stddev_ms,
    round((total_exec_time / sum(total_exec_time)
        OVER () * 100)::numeric, 1)                  AS pct_total,
    left(query, 120)                                 AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Обратите внимание на колонку pct_total — если один запрос занимает 60% от всего времени базы, с него и нужно начинать оптимизацию.

Запросы с наибольшим средним временем

Редкие, но тяжёлые запросы лучше искать по mean_exec_time:

SELECT
    calls,
    round(mean_exec_time::numeric, 2)  AS mean_ms,
    round(max_exec_time::numeric, 2)   AS max_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    left(query, 120)                   AS query
FROM pg_stat_statements
WHERE calls > 10  -- исключаем единичные запросы
ORDER BY mean_exec_time DESC
LIMIT 10;

Запросы с проблемами блокировок

Если запросы часто ждут блокировок, это видно через pg_stat_statements в комбинации с pg_stat_activity:

-- Текущие долгие запросы (выполняются прямо сейчас)
SELECT
    pid,
    now() - query_start                   AS duration,
    state,
    wait_event_type,
    wait_event,
    left(query, 120)                       AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '5 seconds'
ORDER BY duration DESC;

Аналог slow query log в PostgreSQL

В MySQL есть slow query log, в PostgreSQL его аналог настраивается через postgresql.conf:

# Логировать запросы, выполняющиеся дольше N миллисекунд
log_min_duration_statement = 1000   # 1 секунда

# Дополнительно можно логировать планы для медленных запросов
auto_explain.log_min_duration = 500
auto_explain.log_analyze = on
# (требует shared_preload_libraries = 'auto_explain')

После этого все медленные запросы будут появляться в pg_log. На production удобнее использовать pg_stat_statements — он не генерирует дополнительный I/O на диск и позволяет делать SQL-запросы к статистике.

Анализ нагрузки по типам операций

-- Запросы с наибольшим числом блочных чтений (cache miss)
SELECT
    calls,
    round(mean_exec_time::numeric, 2)  AS mean_ms,
    shared_blks_hit                    AS cache_hits,
    shared_blks_read                   AS disk_reads,
    round(shared_blks_hit::numeric /
        nullif(shared_blks_hit + shared_blks_read, 0) * 100, 1) AS hit_pct,
    left(query, 100)                   AS query
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY disk_reads DESC
LIMIT 10;

Низкий hit_pct (< 90%) говорит о том, что данные часто читаются с диска, а не из shared_buffers. Это может означать недостаточный размер кэша или неэффективные запросы, считывающие лишние данные.

Сброс статистики

После оптимизации запроса полезно сбросить статистику, чтобы убедиться, что изменения дали эффект:

-- Сбросить всю статистику
SELECT pg_stat_statements_reset();

-- Сбросить статистику только для конкретного запроса (PostgreSQL 14+)
SELECT pg_stat_statements_reset(
    userid => (SELECT oid FROM pg_roles WHERE rolname = 'app_user'),
    dbid   => (SELECT oid FROM pg_database WHERE datname = 'mydb'),
    queryid => 1234567890
);

Практический чеклист для production

1. Включите pg_stat_statements и auto_explain на всех продакшн-базах.
2. Настройте log_min_duration_statement = 500 для фиксации медленных запросов в логах.
3. Раз в неделю запускайте запрос топ-10 по total_exec_time и проверяйте, не появилось ли новых лидеров.
4. Следите за запросами с mean_exec_time > 100ms и calls > 1000 — они кандидаты на оптимизацию индексами.
5. После каждого релиза сравнивайте статистику до и после — pg_stat_statements_reset() перед деплоем помогает изолировать эффект изменений.

Итог

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

Научитесь писать эффективные SQL-запросы на интерактивном тренажёре — задачи с реальными данными и мгновенной проверкой.