PostgreSQL помнит, что именно выполнялось: расширение pg_stat_statements агрегирует текст запроса, число вызовов, суммарное и среднее время, а также счётчики чтения со страниц и записи во временные файлы. Это первый инструмент, к которому стоит обращаться, когда база «стала медленной»: он показывает не отдельный неудачный запрос, а те, что съедают время в сумме. Разберём установку, разбор колонок и типичные диагнозы.

Установка и включение расширения

Расширение подключается на уровне всего кластера, поэтому требует загрузки библиотеки до старта постгреса. В Debian и Ubuntu конфигурация лежит в каталоге версии, в Fedora и CentOS — в каталоге данных кластера.

grep -E "shared_preload_libraries|pg_stat_statements" /etc/postgresql/16/main/postgresql.conf
echo "shared_preload_libraries = 'pg_stat_statements'" | sudo tee -a /etc/postgresql/16/main/postgresql.conf
sudo systemctl restart postgresql@16-main
sudo -u postgres psql -c "CREATE EXTENSION pg_stat_statements;"

Если не перезапустить сервис до создания расширения, вы получите ошибку про отсутствующую библиотеку. Проверить успешность просто: в таблице pg_extension должна появиться строка, а в базах, где нужно вести учёт, придётся выполнить CREATE EXTENSION отдельно — расширение не переносится между базами автоматически.

sudo -u postgres psql -c "SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_stat_statements';"
sudo -u postgres psql -c "SHOW shared_preload_libraries;"
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_statements;"

Параметры сбора статистики

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

pg_stat_statements.max = 5000
pg_stat_statements.track = all
pg_stat_statements.track_io_timing = on
pg_stat_statements.track_planning = on
shared_preload_libraries = 'pg_stat_statements,auto_explain'

Значение track = all учитывает и вложенные вызовы функций, а top — только внешний запрос. Параметр max задаёт размер кольцевого буфера: при переполнении самые редкие записи вытесняются, поэтому значение в несколько тысяч обычно достаточно. Изменения вступают в силу после перезапуска.

Как устроено представление

Одна строка — один нормализованный запрос: литералы заменены на параметры, комментарии отброшены. Именно поэтому один и тот же SQL с разными числами попадает в одну строку со счётчиком вызовов.

SELECT substring(query, 1, 60) AS q, calls, rows, left(query, 200)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Основные колонки: calls — число выполнений, total_exec_time и mean_exec_time — суммарное и среднее время исполнения в миллисекундах, rows — строк, shared_blks_hit и shared_blks_read — попадания и промахи в буферный кэш, temp_blks_written — блоки, ушедшие во временные файлы при сортировках и хеш-соединениях. В новых версиях появилось время планирования в колонках plan_total_time и plan_mean_time.

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

Суммарное время отвечает на вопрос «куда уходит вся база», среднее — на вопрос «что тормозит в отдельном вызове». Оба списка полезно смотреть подряд: часто всплывают разные запросы.

SELECT substring(query, 1, 70) AS q, calls, round(total_exec_time::numeric, 1) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 10;

Фильтр по dbid нужен, потому что расширение агрегирует статистику всех баз кластера сразу. Если в списке оказались запросы с огромным calls и крошечным mean_ms, проблема в количестве обращений, а не в плане выполнения: это типичный сценарий, когда приложение дергает данные в цикле.

Проблемы ввода-вывода и сортировки на диске

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

SELECT substring(query, 1, 60) AS q, calls, shared_blks_hit, shared_blks_read, temp_blks_read, temp_blks_written,
round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct
FROM pg_stat_statements
WHERE shared_blks_read + temp_blks_written > 0
ORDER BY shared_blks_read DESC
LIMIT 10;

Доля попаданий ниже девяноста процентов говорит о недостатке разделяемой памяти или о запросах, читающих таблицы целиком. Большие значения temp_blks_written означают, что сортировка или хеш не поместились в work_mem: либо поднимите её точечно для сессии, либо уберите лишнюю сортировку планом.

SELECT relname, seq_scan, idx_scan, pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10;

Парный запрос к pg_stat_user_tables показывает таблицы, которые постоянно читаются последовательно. Если индекс существует, а idx_scan равен нулю, планировщик его не видит: проверьте статистику, селективность условия и актуальность ANALYZE.

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

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

sudo -u postgres psql -c "SELECT pg_stat_statements_reset();"
journalctl -u postgresql@16-main --since "10 minutes ago" | grep -i "duration:"
psql -c "SELECT mean_exec_time, calls, substring(query,1,50) FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;"

Для разбора плана конкретного медленного запроса удобен модуль auto_explain: он пишет план в журнал, не меняя сам SQL. Включите его в shared_preload_libraries вместе с основным расширением и задайте порог auto_explain.log_min_duration = '500ms', чтобы в лог попадали только действительно долгие выполнения. Полезно также включить auto_explain.log_buffers и auto_explain.log_nested_statements: первое показывает, сколько страниц читалось, второе — вложенные вызовы функций, которые в общей статистике выглядят одним запросом. Сбрасывать счётчики сразу после правки не стоит: дайте базе поработать хотя бы час под привычной нагрузкой, иначе сравнение будет сделано на выборке из нескольких вызовов.

Чек-лист регулярного анализа

  • раз в неделю смотрите топ по total_exec_time за прошедшие дни;
  • отдельно ищите запросы с большим числом вызовов и малым числом строк;
  • проверяйте долю попаданий в кэш и объём временных блоков;
  • сверяйте список запросов с индексами: есть ли покрытие для самых частых условий;
  • сбрасывайте статистику после миграций и сравнивайте результат до и после;
  • следите за заполнением буфера: если pg_stat_statements.max исчерпан, часть запросов не учитывается.

Итог

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

Насколько публикация полезна?

Нажмите на звезду, чтобы оценить!

Средняя оценка 0 / 5. Количество оценок: 0

Оценок пока нет. Поставьте оценку первым.

Поделиться: Telegram ВКонтакте