Картина знакома почти каждому, кто отвечает за базу. Приложение начинает тормозить, пользователи жалуются на таймауты, дашборды краснеют, а в голове крутится один вопрос: какой именно запрос всё это устроил. Соблазн велик сразу полезть в код и навешивать индексы по наитию, но это путь в никуда. Правильный порядок действий превращает панику в спокойную инженерную работу, которая укладывается в тридцать минут. Сначала находим виновников по статистике, затем разбираем каждого через план выполнения и только потом что-то меняем.
Почему один инструмент не закрывает задачу и зачем их связка
Главная ошибка новичка состоит в поиске одного волшебного инструмента, который покажет всё разом. Такого нет, и в этом нет ничего страшного. У PostgreSQL есть три разных способа смотреть на запросы, и каждый отвечает на свой вопрос. Представление pg_stat_activity показывает, что выполняется прямо сейчас, какие сессии висят и кто кого блокирует. Расширение pg_stat_statements ранжирует запросы по совокупной стоимости за период, отвечая на вопрос, кто съедает ресурсы в долгую. А команда EXPLAIN ANALYZE отвечает на вопрос, почему конкретный уже найденный запрос медленный.
Порядок здесь не произвольный. EXPLAIN ANALYZE великолепен, когда уже известно, какой запрос исследовать, но сам по себе он не подскажет, кого именно из тысяч запросов стоит изучать. Сначала нужен список подозреваемых, и его дают мониторинг и статистика. Запускать разбор плана наугад на первом попавшемся запросе означает потратить полчаса впустую. Поэтому связка работает так: pg_stat_statements выдаёт короткий список реальных обжор, а EXPLAIN ANALYZE по очереди допрашивает каждого из них.
Как поднять pg_stat_statements и не пропустить ни одного запроса
Расширение не включено по умолчанию, его нужно подгрузить как разделяемую библиотеку и настроить пару параметров. Это требует перезапуска сервера, поэтому момент стоит выбрать заранее. Конфигурация минимальна и понятна.
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000 # сколько разных запросов отслеживать
pg_stat_statements.track = all # учитывать в том числе вложенные запросыПосле перезапуска расширение создаётся в нужной базе одной командой, и с этого момента оно начинает копить статистику по каждому уникальному шаблону запроса.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;Здесь скрывается тонкость, о которой многие узнают слишком поздно. Долгое время бытовало мнение, что метрики живут вечно, пока сервер не перезапущен или пока их явно не сбросили. Это оказалось неправдой. Расширение хранит ограниченное число шаблонов, заданное параметром максимума, и при переполнении вытесняет наименее значимые записи. Запрос, который выполнялся когда-то давно, вполне может уже отсутствовать в представлении. Поэтому для прицельного разбора инцидента есть надёжный приём: сбросить статистику в начале наблюдения, дать системе поработать несколько минут под боевой нагрузкой и только потом снять показания. Так в выборку попадёт именно текущее поведение, а не усреднённая история за месяцы.
-- В начале наблюдения обнуляем накопленное
SELECT pg_stat_statements_reset();Какой запрос к статистике выдаёт настоящих обжор
Когда статистика накопилась, дело за одним запросом. Он сортирует шаблоны по суммарному времени выполнения и забирает первую десятку. Именно суммарное время, а не среднее, остаётся главной метрикой при охоте на узкие места. Запрос, который выполняется доли секунды, но вызывается миллион раз в час, нагружает базу сильнее, чем тяжёлый отчёт, запускаемый дважды в день.
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;Эта выборка уже многое рассказывает. Колонка с числом вызовов отделяет редкие тяжёлые запросы от частых лёгких. Среднее время показывает стоимость одного выполнения. А процент попаданий в кэш намекает на природу проблемы. Если запрос почти всё читает с диска, а не из памяти, виноват может быть не сам запрос, а нехватка памяти или раздувшаяся таблица. Высокое суммарное время при высокой частоте вызовов почти всегда указывает на классическую проблему лишних обращений, когда приложение в цикле дёргает базу вместо одного пакетного запроса.
Что показывает EXPLAIN ANALYZE и чем он отличается от простого EXPLAIN
Получив список подозреваемых, берём каждого и прогоняем через разбор плана. Здесь важно понимать разницу между двумя режимами. Обычный EXPLAIN показывает только намерения планировщика: какой путь он собирается выбрать, во сколько оценивает стоимость и сколько строк ожидает. Ничего при этом не выполняется. Добавление ключевого слова ANALYZE меняет дело кардинально: запрос реально выполняется, и к оценкам добавляются фактические цифры времени и числа строк по каждому шагу.
Из этого следует предупреждение, которое стоит вытатуировать на запястье. ANALYZE действительно выполняет запрос со всеми побочными эффектами. На запросе изменения данных это означает реальное удаление или обновление строк. Чтобы безопасно разобрать такой запрос, его оборачивают в транзакцию с откатом.
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM orders WHERE created_at < '2024-01-01';
ROLLBACK;Опция BUFFERS добавляет к плану бесценную информацию о том, сколько восьмикилобайтных блоков каждый шаг прочитал из памяти, прочитал с диска и записал. Именно по ней видно, какой узел делает больше всего черновой работы с данными. На чтении запросов эту опцию стоит включать всегда, ведь она почти бесплатна, а пользы даёт много.
Как читать план и где прячется настоящий тормоз
План выполнения часто пугает новичков стеной непонятного текста, но читать его проще, чем кажется, если знать, на что смотреть. Главная ошибка состоит в том, чтобы воспринимать план как табло с очками и гнаться за самым большим числом стоимости. План это не табло, а модель того, как база собирается получить результат, плюс запись того, что произошло на самом деле. Вся ценность в сравнении этих двух историй.
Рассмотрим реальный пример плана для запроса с фильтром по таблице заказов.
Bitmap Heap Scan on big_orders (cost=35.61..10480.72 rows=2690 width=57)
(actual time=3.263..1133.014 rows=2207 loops=1)
Recheck Cond: (customer_id = 42)
Filter: (status = 'open'::text)
Rows Removed by Filter: 236
Heap Blocks: exact=2413
Buffers: shared hit=219 read=2199
I/O Timings: shared read=1084.008
-> Bitmap Index Scan on big_orders_customer_id_idx (cost=0.00..34.94 rows=3000 width=0)
(actual time=2.067..2.068 rows=2443 loops=1)
Index Cond: (customer_id = 42)
Planning Time: 2.845 ms
Execution Time: 1134.558 msЧто здесь читается с первого взгляда. PostgreSQL печатает оценочные и фактические значения рядом, и число строк единственное, что показано в обеих частях. Это позволяет мгновенно ловить ошибки в оценке количества строк. В примере планировщик ждал около 2690 строк, а получил 2207, расхождение небольшое, значит со статистикой по этой таблице порядок. А вот строка с таймингом ввода-вывода кричит о проблеме: больше тысячи миллисекунд из общего времени ушло на чтение 2199 блоков с диска. Узкое место не в логике запроса, а в том, что данные не помещаются в кэш и читаются с медленного хранилища.
Алгоритм чтения плана сводится к нескольким простым вопросам. Сначала ищется не самая большая оценка стоимости, а самое большое фактическое время. Затем по каждому узлу сравнивается оценочное и фактическое число строк. Расхождение в десять и более раз означает, что планировщик работал с устаревшей статистикой и оптимизировал запрос под несуществующую реальность. Лечится это обновлением статистики командой ANALYZE по таблице. Дальше стоит искать последовательное сканирование больших таблиц с избирательным фильтром, особенно когда под ним красуется большое число отброшенных фильтром строк. Это первый кандидат на индекс.
Какие узоры в плане выдают типовые болезни запросов
Опытный глаз цепляется за повторяющиеся узоры. Строка про удалённые фильтром строки сразу под последовательным сканированием это почти всегда сигнал, что нужен индекс. Хэш-соединение с числом пакетов больше единицы говорит, что соединение не уместилось в память и вылилось на диск, и тогда стоит подумать об увеличении рабочей памяти для сессии. Вложенный цикл с огромным числом повторений выдаёт ту самую проблему частых мелких обращений.
Особенно коварен случай, когда узел оценил двести строк, а выдал два миллиона. Всё, что стоит в плане выше этого узла, начинает работать с гораздо большим объёмом данных, чем рассчитывал планировщик. Один такой промах в оценке объясняет медленный план лучше, чем любая отдельная цифра стоимости. Поэтому стратегия отладки звучит так: найти первый крупный промах оценки, понять, откуда он взялся, из формы запроса, логики соединения, вида предиката или устаревшей статистики, и менять по одной вещи за раз, каждый раз сравнивая планы заново.
Отдельно стоит держать в голове частую ловушку с функциями над колонками. Когда колонка обёрнута в функцию, индекс по ней использовать нельзя.
-- Плохо: индекс по created_at не работает
SELECT * FROM orders WHERE DATE(created_at) = '2025-01-15';
-- Хорошо: диапазонное условие по сырой колонке использует индекс
SELECT * FROM orders
WHERE created_at >= '2025-01-15' AND created_at < '2025-01-16';Если фильтрация по результату функции неизбежна, спасает выражательный индекс ровно по тому же выражению. Это типовой приём, который превращает медленное сканирование в быстрый поиск по индексу.
Почему разбирать нужно реальный запрос, а не его упрощённую версию
Последний, но критичный нюанс касается того, какой именно текст подавать в EXPLAIN ANALYZE. Разбирать нужно настоящий запрос с реальными значениями параметров, а не с заглушками вида подстановочных знаков и не упрощённую версию, которую кажется удобнее. Реальные значения параметров напрямую влияют на выбор плана. Запрос с редким значением фильтра пойдёт через индекс, а с частым через сканирование, и план будет совершенно разным. Полезно также выставлять имя приложения в параметрах соединения, чтобы по статистике было видно, какой сервис или фоновый обработчик породил запрос.
После того как изменения внесены, есть смысл сбросить статистику расширения и понаблюдать заново, чтобы убедиться, что правка действительно помогла, а не переложила нагрузку на соседний запрос. Оптимизация базы это не разовый проект, а постоянная дисциплина, отделяющая системы, которые работают предсказуемо, от тех, что вечно тушат пожары в продакшене. Связка из ранжирования по статистике и прицельного разбора плана превращает мутную жалобу на тормоза в конкретный список действий, и весь цикл от первого симптома до понятого диагноза реально проходится за полчаса. Дальше остаётся самое приятное: внести точечную правку и увидеть, как суммарное время верхнего запроса в списке падает в разы.
Канал сайта в Telegram
Канал сайта на YouTube