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

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

(Это продолжение гайда про EXPLAIN ANALYZE. Если не читали — советуем начать оттуда.)

pg_stat_statements: что нагружает систему

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

По умолчанию pg_stat_statements недоступен пользователям PostgreSQL. Чтобы активировать его, необходимо выполнить несколько шагов:

1. Открыть на редактирование конфигурационный файл postgresql.conf. Я использую PostgreSQL 13, и команда выглядит так:

sudo nano /etc/postgresql/13/main/postgresql.conf

2. Найти и раскомментировать (или добавить) в конфигурационном файле строку:

shared_preload_libraries = 'pg_stat_statements'

3. Перезагрузить сервис:

sudo systemctl restart postgresql

4. Расширение включается для каждой базы данных отдельно:

CREATE EXTENSION pg_stat_statements;

Если всё настроено правильно, вы увидите, что расширение активно, используя команду:

\dx pg_stat_statements

Рассмотрим три запроса к pg_stat_statements, которые помогут быстро найти проблемные места. Для каждого разберём цель, SQL-код и ключевые колонки результата.

Пример 1. Самые частые запросы

Даже быстрый запрос становится проблемой, если выполняется тысячи раз в секунду: нагружает CPU, генерирует сетевой трафик и создаёт очереди на блокировки. Этот запрос покажет 10 самых частых обращений к базе*

SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

Как читать результат:

• query — текст SQL-запроса.

• calls — общее количество выполнений с момента последнего сброса статистики.

*Примечание: все примеры совместимы с PostgreSQL 13+. В более ранних версиях названия колонок pg_stat_statements отличаются — см. документацию. При необходимости в пример можно добавить столбцы rows и mean_exec_time в SELECT для полноты.

Если в топе много запросов с calls > 10 000 за короткий период (после сброса статистики), которые возвращают мало данных (в среднем < 10–100 строк на вызов: rows / calls), вероятно, приложение генерирует лишние обращения. Это типично для проблемы N+1 (сначала выбирается список записей, а затем для каждой отдельным запросом загружаются связанные данные) или отсутствия кеширования. В таких случаях запросы стоит кешировать на уровне приложения (Redis/Memcached) или объединять в пакетные запросы (batching). Оптимизация логики приложения обычно даёт самый быстрый прирост производительности, так как напрямую снижает нагрузку на СУБД, сеть и CPU сервера.

Пример 2. Самые медленные запросы (по суммарному времени)

Этот запрос найдет 10 запросов, которые в сумме отнимают больше всего процессорного времени. Даже если такой запрос выполняется редко, он может блокировать другие транзакции и замедлять отклик системы.

SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Как читать результат:

• total_exec_time — суммарное время выполнения запроса в миллисекундах.

• calls — сколько раз запрос был выполнен.

Обратите внимание внимание на запросы с total_exec_time > 300 000 мс (5 минут суммарно) при calls < 100 — каждый вызов в среднем занимает > 3 секунд: это явные кандидаты на оптимизацию.

Пример 3. Запросы с низким уровнем кеширования (дисковые чтения)

Чтение с диска — самая медленная операция в PostgreSQL. Этот запрос выявит информацию о топ-10 SQL-запросах в PostgreSQL с низким уровнем кеширования.

SELECT query,
   	calls,
   	rows,
   	shared_blks_hit,
   	shared_blks_read,
   	ROUND(
     	100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0)::NUMERIC,
     	2
   	) AS hit_percent,
   	ROUND(mean_exec_time::NUMERIC, 2) AS avg_time_ms
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 100
ORDER BY hit_percent ASC NULLS LAST
LIMIT 10;

Как читать результат:

shared_blks_hit — количество блоков, взятых из оперативной памяти (кеша).

shared_blks_read — количество блоков, прочитанных с диска.

hit_percent — процент попаданий в кеш. Чем ближе к 100%, тем лучше.

avg_time_ms — среднее время выполнения одного вызова в мс.

Оценивайте hit_percent в контексте объёма дисковых чтений: если он ниже 70% при shared_blks_read > 500, запрос создаёт заметную нагрузку на диск. Порог условный: для OLTP-нагрузки hit_percent < 90% часто уже тревожный сигнал, тогда как для аналитики или больших Seq Scan 50–70% может быть нормой. Всегда анализируйте метрики в связке с общим числом вызовов (calls) и планом выполнения (EXPLAIN). Чтобы оценить влияние на один запуск, разделите shared_blks_read на calls (или добавьте колонку в запрос). Низкий процент попаданий в кеш — сигнал проверить индексы (влияют на эффективность поиска, о них — в следующей части цикла), shared_buffers (достаточно ли памяти для кеширования) и автовакуум (не засоряют ли устаревшие версии строк кеш и не увеличивают ли объём чтений).

Я показал три готовых примера запросов с pg_stat_statements. Их можно сразу использовать в работе с базой. 

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

Используйте инструмент как для регулярной диагностики, так и в качестве основы для автоматического мониторинга трендов производительности.

auto_explain: разбираемся, почему запрос «завис»

Модуль auto_explain — это автоматизированный EXPLAIN ANALYZE. Не нужно вручную оборачивать SQL: модуль сам ловит медленные запросы и пишет их планы в лог. С включёнными log_analyze и log_buffers вы получаете тот же детальный вывод. Подробности — в документации.

Для подключения выполните несколько шагов:

1. Откройте на редактирование конфигурационный файл postgresql.conf:

sudo nano /etc/postgresql/13/main/postgresql.conf

2. Добавьте библиотеку в параметр shared_preload_libraries. Если там уже есть другие библиотеки (например, pg_stat_statements), добавьте через запятую. Сразу настройте другие параметры.

shared_preload_libraries = 'pg_stat_statements, auto_explain'

Настройки параметров auto_explain добавьте в файл конфигурации, если их там нет:

#Настройка времени логирования
auto_explain.log_min_duration = 1000  
 #Включает фактическое выполнение
auto_explain.log_analyze = on      	
#Включает работу с буфером
auto_explain.log_buffers = on

3. Перезагрузите сервис:

sudo systemctl restart postgresql

4. Проверьте, куда записываются логи. Найдите параметр logging_collector в postgresql.conf (файл мы открывали в Шаге 1). По умолчанию он закомментирован (#) и выключен, поэтому PostgreSQL отправляет логи в stderr, который перехватывает системный журнал. В этом случае ищите записи через journalctl.

Команда выводит последние 20 записей журнала:

sudo journalctl -u postgresql -n 20 --no-pager

Команда используется для просмотра журнала в режиме реального времени:

sudo journalctl -u postgresql -f

Если вы хотите, чтобы логи сохранялись в отдельные файлы, раскомментируйте строку logging_collector в файле конфигурации (Шаг 1) и установите значение on. Важно: этот параметр применяется только после перезапуска сервиса. Для этого повторите Шаг 3. После перезапуска логи начнут писаться в папку, указанную в параметре log_directory. Узнать подробнее о работе с логами сервера можно в нашей базе знаний.

С включенным “auto_explain.log_analyze = on” лог выглядит как вывод команды EXPLAIN ANALYZE — дерево плана выполнения с реальным временем, количеством строк, циклов*.

*Примечание: При использовании prepared statements в лог попадает только шаблон запроса ($1, $2 вместо реальных значений), так как параметры передаются отдельным сообщением протокола. Подробнее в документации и по тематике на habr.

При настройке auto_explain уделите внимание параметру auto_explain.log_min_duration. Правильный выбор этого порога критически важен, так как auto_explain генерирует значительный объём записей, особенно при работе со сложными запросами или секционированными таблицами.

Чтобы избежать перегрузки журнала и не влиять на производительность сервера (из-за дополнительных затрат на логирование планов), не включайте модуль глобально, а активируйте его только для конкретной сессии или временного интервала диагностики. Для этого выполните в нужной сессии следующие SQL-команды:

-- Загрузка модуля в текущую сессию
LOAD 'auto_explain';
-- Установка порога логирования (например, 50 мс)
SET auto_explain.log_min_duration = '50';
-- Включение детального плана (аналог EXPLAIN ANALYZE)
SET auto_explain.log_analyze = on;
-- Логирование информации о буферах
SET auto_explain.log_buffers = on;

Параметр log_analyze = off — это режим EXPLAIN. Он показывает предполагаемый план выполнения (оценки планировщика). Это безопасно для продакшена и практически не замедляет запросы.

Параметр log_analyze = on — это режим EXPLAIN ANALYZE, который выполняет запрос. Это полезно для отладки, так как показывает реальные задержки. Однако для тяжёлых запросов это может замедлить работу базы, так как сервер полностью выполнит запрос, соберёт статистику выполнения и запишет план в журнал. В этом случае также помним, если через базу пройдут запросы UPDATE, DELETE или INSERT, они реально изменят данные.

Основная область применения auto_explain — обнаружение проблем в реальном времени без модификации кода или остановки сервисов. Он не заменяет ручной анализ. Для сравнения планов до и после оптимизации по-прежнему используйте EXPLAIN ANALYZE напрямую.

log_min_duration_statement: тонкая настройка логов

Параметр log_min_duration_statement задаёт порог: все SQL-запросы, выполняющиеся дольше указанного времени, попадают в лог. Подробности — в документации.

По умолчанию 'log_min_duration_statement' не активен. Чтобы активировать его, необходимо выполнить несколько шагов:

1. Открыть на редактирование конфигурационный файл 'postgresql.conf'. Я использую 13 версию PostgreSQL, и команда на сервере выглядит так:

sudo nano /etc/postgresql/13/main/postgresql.conf

Узнать путь к файлу можно SQL-командой:

SHOW config_file;

2. Найти и раскомментировать (или добавить) параметр. Значение указывается в миллисекундах. Например, для логирования запросов медленнее 1 секунды:

'log_min_duration_statement = 1000'

3. Перезагрузить сервис для применения настроек:

sudo systemctl restart postgresql

4. Проверить логи. Аналогично настройке логов для auto_explain (См. шаг 4) нужно узнать состояние сборщика логов logging_collector. Также разобраться в этом вам помогут следующие SQL-команды:

Узнать текущий порог логирования медленных запросов:

SHOW log_min_duration_statement;

Узнать, включен ли сборщик логов:

SHOW logging_collector;

Узнать, где находится директория с данными (внутри папка log):

SHOW data_directory;

Узнать, какой файл лога активен прямо сейчас:

SELECT pg_current_logfile();

Медленные запросы будут помечены меткой ‘duration’ и имеют следующий вид в журнале:

2026-03-20 14:30:45 MSK [127624] postgres@myproject LOG: duration: 235.988 ms statement: SELECT * FROM customers c WHERE email LIKE '%@example.com' ; 2026-03-20 14:30:46 MSK [127624] postgres@myproject: duration: 1828.775 ms statement: SELECT c.name, o.total_amount, o.status FROM customers c JOIN orders o ON c.id = o.customer_id ; 
2026-03-20 14:30:47 MSK [127800] postgres@myproject: duration: 1509.881 ms statement: SELECT pg_sleep(1.5), COUNT(*) FROM customers;

Как и в случае с auto_explain, важно правильно выбрать пороговое значение:

• Слишком низкое значение: приведет к избыточному логированию и росту объема журналов («будет много шума»).

• Слишком высокое значение: может скрыть часть медленных запросов, которые требуют оптимизации.

При высокой нагрузке избыточное логирование может привести к перегрузке дисковой подсистемы (I/O wait) и увеличению нагрузки на службу systemd-journald. При сохранении логов в файл обязательно следите за размером журнала. Если логи растут чрезмерно, настройте автоматическую ротацию, используя параметры ‘log_rotation_size’ и ‘log_rotation_age’. Увеличение дискового пространства — временная мера, приоритетом должна быть правильная настройка хранения логов.

Краткое резюме по инструментам мониторинга

Можно не включать все инструменты одновременно — это приведёт к дублированию данных и лишней нагрузке. Выбирайте набор в зависимости от среды и задач.

Например, auto_explain и log_min_duration_statement частично дублируют друг друга. Если уже настроен auto_explain с порогом логирования и включён auto_explain.log_statement¹, то log_min_duration_statement обычно не требуется — вы и так получите текст запроса, длительность и план выполнения. Держать оба включёнными одновременно обычно нет смысла.

pg_stat_statements — рекомендуется включать его в любой среде. Нагрузка на CPU минимальна (обычно 1–2%), так как расширение работает через разделяемую память и не пишет на диск при каждом запросе².

Для логирования медленных запросов выбор зависит от окружения:

• В среде разработки и тестирования (Dev / Staging): используйте auto_explain. Он показывает полный план выполнения (при опции log_analyze = on), что помогает понять причины замедления. Нагрузка может быть заметной (~5–20% и более к времени выполнения), но в разработке это допустимо. 

• В рабочей среде (Production): используйте log_min_duration_statement. Нагрузка близка к нулю, пока запрос укладывается в заданный порог. При превышении времени запроса в журнал записывается только текст и длительность, создавая минимальную нагрузку (при условии правильной настройки порога, без создания избыточного логирования). Использование auto_explain в продакшене допустимо, например, для кратковременной отладки: с высоким порогом (> 500 мс), включённым семплированием (auto_explain.log_sample_rate < 1) или активацией только для конкретных сессий/пользователей через SET. Иначе auto_explain сам станет узким местом именно для тех тяжёлых запросов, которые вы пытаетесь отследить.

Этот подход можно адаптировать под конкретный проект. Собранная статистика и логи станут надёжной базой для перехода от простого наблюдения к проактивному управлению БД.

¹ auto_explain.log_statement = on доступен с PostgreSQL 14. Без этого параметра модуль логирует только план выполнения, без текста самого запроса.

² Нагрузка может возрастать при очень большом значении параметра pg_stat_statements.max или при экстремально высокой конкуренции запросов. В типовых сценариях (до нескольких тысяч запросов в секунду) оверхед остаётся в пределах 1–2%.

Заключение

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

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

Автор текста Макаренков Вячеслав

НЛО прилетело и оставило здесь промокод для читателей нашего блога:

-15% на заказ любого VDS (кроме тарифа Прогрев) — HABRFIRSTVDS