EXPLAIN ANALYZE в PostgreSQL: как читать план запроса

Медленный запрос в PostgreSQL может выглядеть невинно, а план — показывать сотни тысяч прочитанных строк вместо ожидаемой сотни. В PostgreSQL 16 команда EXPLAIN ANALYZE запускает запрос и выводит фактические показатели: по ним можно найти узкое место, не гадая по одному лишь времени ответа.

Сначала выберите безопасный режим

Обычный EXPLAIN строит план, но не выполняет запрос. EXPLAIN ANALYZE выполняет его, измеряет работу и сравнивает факт с оценкой планировщика. Для запроса чтения это обычно удобно, но для записи важно помнить: команда действительно выполнит INSERT, UPDATE или DELETE.

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

Получите план на конкретном запросе

Ниже — воспроизводимый пример для PostgreSQL 16. Он создаёт временную таблицу, заполняет её строками и строит индекс. Вы можете менять условие запроса, но не ждите, что план обязательно совпадёт с примером: выбор зависит от статистики, настроек и стоимости операций на конкретном сервере.

CREATE TEMP TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    account_id integer NOT NULL,
    payload text NOT NULL
);

INSERT INTO events (account_id, payload)
SELECT (n % 1000)::integer, repeat('x', 100)
FROM generate_series(1, 100000) AS s(n);

CREATE INDEX events_account_id_idx ON events (account_id);
ANALYZE events;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload
FROM events
WHERE account_id = 42;

ANALYZE events обновляет статистику таблицы, чтобы планировщик мог опираться на сведения о данных. Сам EXPLAIN ANALYZE выполняет SELECT. В выводе ищите узел, который читает таблицу, а затем сопоставляйте ожидаемое и фактическое число строк.

Читайте план снизу вверх

План — дерево операций. Сначала PostgreSQL выполняет дочерние узлы, затем передаёт их результат родительским. Поэтому начинайте с нижних строк и поднимайтесь к узлу, который выдаёт итоговый результат.

В каждой строке обычно указаны тип узла и параметры в скобках. Например, Seq Scan означает последовательное чтение таблицы, Index Scan — чтение через индекс, а Bitmap Heap Scan — работу через битовую карту адресов строк. Название само по себе не говорит, хорош ли план: последовательное чтение небольшой таблицы может быть дешевле поиска по индексу.

Узлы могут содержать фрагменты условия:

• Index Cond — условие, которое использует индекс для поиска;
• Filter — проверка уже найденных строк; отсеянные строки показываются как Rows Removed by Filter;
• Recheck Cond — условие, которое PostgreSQL повторно проверяет для строк, найденных через битовую карту.

Большое число Rows Removed by Filter — повод проверить, можно ли использовать условие фильтрации при поиске по индексу. Но это не готовый рецепт «добавьте индекс»: сначала проверьте, сколько строк проходит через узел, насколько часто он запускается и не дешевле ли таблицу прочитать целиком.

Сравните оценку с фактом

В записи cost=0.00..18.25 rows=10 поля cost — оценка планировщика в условных единицах, а не миллисекунды. Первое число — предполагаемая стоимость до получения первой строки, второе — стоимость выполнения узла целиком. В rows указано ожидаемое количество строк.

У actual time=0.010..0.035 rows=100 loops=1 время измеряется в миллисекундах, а rows и loops показывают фактический результат и число запусков узла. Если узел выполнялся несколько раз, число строк в выводе — среднее за один запуск, а не общий итог: для примерного общего количества умножьте rows на loops. Значения округляются, поэтому это ориентир.

Ищите не просто расхождение, а его масштаб. Если планировщик ожидал десяток строк, а узел фактически выдал десятки тысяч, причина может быть в устаревшей статистике, неравномерном распределении значений или выражении в условии, которое мешает оценке. Сначала проверьте актуальность статистики с помощью ANALYZE, затем повторите измерение на том же запросе и тех же параметрах.

Самый полезный вопрос к плану — не «почему тут Seq Scan?», а «на каком узле реальная работа разошлась с оценкой и сколько ресурсов это стоило?»

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

Проверьте буферы и повторные запуски

Опция BUFFERS помогает увидеть обращения к страницам данных — блокам фиксированного размера, с которыми работает PostgreSQL.^* shared hit означает, что блок нашёлся в буферном кеше PostgreSQL; shared read — что серверу пришлось загрузить его в этот кеш. Это не обязательно чтение с физического диска: данные могли оставаться в кеше операционной системы.

Если есть temp read или temp written, операция использовала временные файлы — например, сортировка не поместилась в доступную ей память. Это повод проверить объём данных и сам план, а не сразу менять настройки памяти для всего сервера.

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

^* Буфер — область памяти, в которой PostgreSQL хранит страницы таблиц и индексов. Счётчики буферов помогают оценить объём обращений, но сами по себе не измеряют задержку физического диска.

Когда какой режим выбирать

• Нужно увидеть план без выполнения запроса — начните с EXPLAIN.
• Запрос безопасно выполнять, а проблема проявляется на конкретных параметрах — используйте EXPLAIN (ANALYZE, BUFFERS) и сравните оценки с фактом.
• Время узлов мешает читать вывод или создаёт заметные накладные расходы — в диагностическом запуске можно отключить замер времени узлов через параметр TIMING OFF. Фактические строки и циклы останутся, но таймингов отдельных узлов не будет.

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

Что часто ломает интерпретацию

• Вы считаете любой Seq Scan ошибкой. Для маленькой таблицы или запроса, который возвращает значительную её часть, полный проход может оказаться дешевле индекса.
• Вы смотрите только на верхнюю строку. Итоговое время важно, но причина часто ниже — например, в узле с большим числом обработанных строк.
• Вы складываете времена узлов. Время родителя включает работу дочерних, поэтому сумма даст завышенное значение.
• Вы принимаете shared read за доказательство чтения с диска. Счётчик показывает загрузку в кеш PostgreSQL, но не исключает кеш операционной системы.
• Вы сравниваете два плана после разных условий запуска. Нагрузка, параметры запроса и состояние кеша способны изменить и план, и время выполнения.

Если после обновления статистики оценки всё ещё сильно расходятся с фактом, проверьте распределение значений и повторите замер с параметрами, на которых запрос медленный. А уже затем решайте, нужен ли другой индекс или изменение самого запроса.

Что делать дальше

Возьмите один медленный запрос и сохраните для него вывод EXPLAIN (ANALYZE, BUFFERS) вместе с параметрами, временем и примерной нагрузкой. Отметьте узел с наибольшим расхождением между оценкой и фактом, а также объём прочитанных буферов. После точечного изменения повторите измерение в тех же условиях. Инвалидация кеша и подбор параметров сервера — отдельные темы: сначала установите, что именно показывает план.