Как читать EXPLAIN ANALYZE в PostgreSQL: система, а не шпаргалка

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

EXPLAIN vs EXPLAIN ANALYZE: что на самом деле происходит

Режимы выполнения и побочные эффекты

EXPLAIN строит план и показывает оценки стоимости (cost), строк (rows) и среднюю ширину (width) без выполнения запроса. Это безопасно, но говорит только о том, как оптимизатор думает выполнить запрос.

EXPLAIN ANALYZE, напротив, выполняет запрос по-настоящему. Планировщик уже принял окончательное решение, и теперь мы видим не только его прогнозы, но и фактические замеры времени и количества строк для каждого узла. Именно это сравнение - основа senior-подхода к диагностике.

Критический нюанс: EXPLAIN ANALYZE реально применяет все побочные эффекты - вставляет строки, удаляет, вызывает функции, изменяет последовательности. Результат выборки отбрасывается, но изменения в базе остаются. Поэтому для запросов с INSERT, UPDATE, DELETE или любыми изменяющими вызовами обязательна обёртка:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM ...;
ROLLBACK;

Без этого вы рискуете потерять данные или исказить production-окружение. Все рекомендации по оптимизации, полученные из плана, должны быть проверены в аналогичном окружении с гарантией отсутствия необратимых последствий.

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

Главный источник расхождений - несоответствие статистики таблиц реальному распределению данных. PostgreSQL оценивает rows через гистограммы и оценки селективности, но любой из этих компонентов может устареть. Другие причины: коррелированные предикаты (статистика по умолчанию считает условия независимыми), неучтённые функциональные зависимости, перекошенная выборка из-за некорректного размера выборки default_statistics_target. Когда фактическое количество строк на узле в разы превышает прогнозируемое, страдает не только этот узел: вышестоящие узлы получают неожиданно много данных, и вся цепочка деградирует.

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

Структура строки плана в text-формате

Текстовый формат по умолчанию представляет дерево отступами. Каждая строка содержит тип операции, в скобках - оценки планировщика, а при использовании ANALYZE - фактические замеры. На верхнем уровне всегда стоит итоговый узел, который даёт общее время выполнения и суммарное количество строк. Умение быстро находить «дыру» начинается с чтения этой структуры снизу вверх и сверху вниз одновременно.

Оценки планировщика: что означают cost, rows, width

Параметр cost - условная безразмерная мера стоимости, где за единицу принято время чтения одной страницы из дискового кеша (seq_page_cost). Первое число - стартовая стоимость до момента выдачи первой строки, второе - полная стоимость завершения узла. Сравнение узлов по cost имеет смысл только в рамках одного запроса: оно показывает относительную дороговизну операций, но не точное время.

rows - прогнозируемое количество строк, которое узел вернёт. Чем выше этот показатель, тем больше данных пойдёт на обработку к узлам-верхушкам. width - средний размер одной строки в байтах. Эти три числа вместе определяют выбор алгоритмов соединения и объём оперативной памяти, выделяемой для сортировок и хеш-таблиц.

Фактические метрики: actual time, rows, loops – ключ к поиску проблем

actual time=startup..total показывает замеры в миллисекундах: сколько времени прошло до готовности первой строки и до завершения узла. Если узел многократно выполняется (например, внутренний узел Nested Loop), времена усредняются для одного вызова, а количество повторений отражается в loops. Реальное число возвращённых строк показывает, сколько данных прошло через узел, и это число необходимо сравнить с rows из плана.

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

BUFFERS: как увидеть работу с памятью и диском

Зачем смотреть на буферы и как их включить

Без BUFFERS вы видите только время, но не знаете, упирается ли запрос в дисковый ввод‑вывод или, наоборот, всё находится в кеше. Это различие принципиально для выбора между добавлением памяти и оптимизацией запроса.

В версиях ниже PostgreSQL 18 буферы включаются явно: EXPLAIN (ANALYZE, BUFFERS) SELECT .... Начиная с 18-й версии статистика буферов автоматически добавляется к выводу EXPLAIN ANALYZE, отдельная опция больше не требуется.

Интерпретация shared hit, read, dirtied: отличаем кеш от диска

Поля:

shared hit - страницы найдены в буферном кеше PostgreSQL. Затраты минимальны.
shared read - страницы прочитаны с диска или из кеша ОС, но не из буферного кеша СУБД. Это потенциальный источник задержек.
dirtied - количество страниц, изменённых в процессе (грязные страницы). Полезно при анализе DML.
Также встречаются local hit/read/dirtied для временных объектов и temp read/written для временных файлов на диске при сбросе сортировок или хеш-таблиц.

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

Продвинутые опции EXPLAIN для senior-аналитики

SETTINGS, WAL, TIMING – когда и зачем

SETTINGS выводит значения параметров, отличные от умолчаний и влияющие на планировщик (например, work_mem, random_page_cost). Без этой информации нельзя понять, в каком контексте был построен план: та же стоимость может привести к разным решениям при разных настройках. Анализируя чужой отчёт, всегда требуйте вывод с SETTINGS.

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

TIMING (включён по умолчанию в ANALYZE) замеряет время каждого узла. Отключать его имеет смысл только на системах с высокими накладными расходами на системные вызовы gettimeofday() - такие случаи крайне редки в современных сборках PostgreSQL.

FORMAT JSON/YAML: машинная обработка и дополнительные детали

Вывод в FORMAT JSON или YAML отдаёт полное дерево плана в структурированном виде. При анализе вручную это менее наглядно, но незаменимо для автоматизированной обработки: сравнения разных прогонов, интеграции с мониторингом или извлечения детальных метрик программным способом. JSON-формат возвращает, например, точные объёмы использованной памяти для хеш-таблиц и сортировок, которые в текстовом формате могут быть скрыты.

Типовые узлы и как их читать на практике

Сканирование таблиц: Seq Scan, Index Scan, Index Only Scan, Bitmap Scan

Seq Scan: последовательное чтение всей таблицы. Хорош для выборки значительной доли строк, ужасен для точечных запросов. Смотрите на rows removed by filter - если отбрасывается большинство кортежей, план неадекватен для такого фильтра.
Index Scan: обход индекса с заходом в кучу за каждой подходящей строкой. Heap Fetches покажет, сколько обращений к таблице произошло; если их значительно меньше rows - многие строки читались из индексных записей (visibility map). Если Heap Fetches близко к rows, возможно, индекс содержит много неактуальных версий или таблица плохо обслужена VACUUM.
Index Only Scan: читает только индекс, без захода в кучу, за исключением случаев, когда нужна проверка видимости и карта видимости не подтверждает её. Здесь Heap Fetches - сигнал, что таблица нуждается в VACUUM либо что доля записей, выходящих за рамки карты видимости, велика.
Bitmap Scan: сначала индексное сканирование строит битовую карту расположения строк, затем читает таблицу в физическом порядке. Эффективен для промежуточной селективности, экономит случайный ввод‑вывод.

Методы соединений: Nested Loop, Hash Join, Merge Join

Nested Loop: для каждой строки внешнего источника выполняется внутренний узел. Критично смотреть на loops внутреннего узла: если их сотни тысяч, даже быстрый Index Scan даёт суммарное время, пропорциональное количеству циклов. Часто мигает красным там, где отсутствует подходящий индекс для внутреннего условия.
Hash Join: строит хеш-таблицу по внутреннему набору, затем проверяет внешние строки. Много памяти, требует work_mem. Если в выводе видна строка Buckets: ... Batches: ... с Batches > 1, хеш-таблица не поместилась в память целиком и данные обрабатываются партиями с дисковым сбросом - это всегда дополнительная нагрузка.
Merge Join: оба входа предварительно сортируются или читаются из индекса, затем сливаются. Хорош для больших объёмов данных, когда оба набора упорядочены. Смотрите на наличие Sort узлов перед ним - они добавят времени и дисковых операций, если work_mem мал.

Агрегации и сортировки: HashAggregate, Sort, GroupAggregate

HashAggregate: строит хеш-таблицу для группировки. Если work_mem недостаточен, уходит в сброс на диск с несколькими проходами - в плане отражается как Batches. Это главный сигнал увеличить work_mem локально для конкретного запроса.
Sort: быстрая сортировка в памяти или использование временных файлов. Факт использования диска (external merge) виден в детализации. Если происходит сброс на диск, внимательно смотрите на work_mem и объём данных.
GroupAggregate: требует предварительно отсортированных данных, поэтому часто идёт в паре с Sort или чтением по индексу. Становится проблемным, когда сортировка не может уместиться в памяти.

Senior-методика анализа: от симптомов к решению

Что обычно врет и где искать расхождения

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

Начало плана: если на самом нижнем скане rows в разы меньше фактического, расходятся все вышестоящие оценки. Причина почти всегда в устаревших статистиках (ANALYZE таблицы).
Соединения: сравнивайте прогноз строк на выходе Hash Join или Merge Join с реальностью. Если реальных строк значительно больше, критично проверьте оценки на входах.
Узлы с фильтрацией: Rows Removed by Filter показывает объём «мусора», который пришлось прочитать и отбросить. Если он велик при Seq Scan, вероятно, нужен индекс; если при Index Scan - условия плохо соответствуют индексу.

Как не навредить: анализ DML через транзакции

Любой EXPLAIN ANALYZE для INSERT, UPDATE, DELETE или MERGE должен выполняться внутри явного блока транзакции с немедленным ROLLBACK. Даже при анализе на тестовой копии привычка к такой обёртке страхует от случайного изменения данных. Никогда не копируйте полученный запрос в production без предварительной проверки окружения и нагрузки.

Интерпретация Rows Removed by Filter, Heap Fetches и других скрытых метрик

Rows Removed by Filter - строки, прошедшие проверку условия доступа на уровне индекса или таблицы, но отброшенные наложенным условием. Появится, например, при составном индексе и фильтре, использующем только левую часть. Показывает, насколько точно индекс соответствует запросу: чем больше удалено, тем «шире» лишнего читается.
Heap Fetches - при каждом Index Only Scan это сигнал, что пришлось идти в кучу для проверки видимости. Если доля таких заходов велика, VACUUM давно не проводился или таблица часто изменяется. Результат: Index Only Scan теряет своё главное преимущество и по накладным расходам приближается к Index Scan.
Lossy pages в Bitmap Scan - при дефиците памяти work_mem битовая карта теряет точность, и при чтении страница обрабатывается целиком (все кортежи). Это увеличивает число просматриваемых строк без дополнительных фильтрующих условий.

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

Часто задаваемые вопросы

В чём разница между EXPLAIN и EXPLAIN ANALYZE? EXPLAIN показывает только прогнозы планировщика, не исполняя запрос. EXPLAIN ANALYZE выполняет запрос и добавляет реальные замеры по каждому узлу, включая фактическое время и количество строк.

Почему планы одного и того же запроса могут меняться при повторном запуске? При повторном EXPLAIN ANALYZE данные могут оказаться в буферном кеше, что изменит время. Также статистика таблицы могла обновиться, изменив оценки планировщика и, следовательно, выбранный план. Чтобы увидеть план с холодного старта, сбрасывайте кеш в тестовом окружении или перезапускайте экземпляр PostgreSQL.

Влияет ли work_mem на вывод EXPLAIN? Да, work_mem влияет на оценки стоимости и выбор алгоритмов (например, предпочтение Hash Join) даже для EXPLAIN, а для EXPLAIN ANALYZE определяет количество сбросов на диск, что отражается в плановых метриках Batches или external merge.

Как защититься от реального выполнения DML при анализе? Всегда оборачивайте запросы с изменением данных в блок транзакции: BEGIN; EXPLAIN (ANALYZE, BUFFERS) DELETE ...; ROLLBACK;. Даже на тестовой среде это хорошая привычка, исключающая необратимые изменения.

Что делать, если actual time растёт только на одном узле, а остальные работают быстро? Вероятно, узел испытывает узкое место - например, отсутствует индекс для фильтра, либо внутренний цикл вызывает дорогую операцию множество раз. Вычислите долю времени узла от общего и проверьте loops. Если этот узел внутри Nested Loop и loops велико, подумайте о добавлении индекса или изменении стратегии соединения.

Нужно ли изучать план в JSON, если текстовый формат устраивает? Для ручного анализа текстового формата достаточно. JSON пригождается, когда нужно автоматизировать сбор метрик, сравнивать планы программно или извлекать детали (точный объём памяти, статистику JIT), которые текстовый формат не показывает явно.

Можно ли полностью доверять оценкам стоимости? Нет. Оценки cost - это условные числа, настроенные параметрами стоимости (seq_page_cost и т.д.). Они предназначены для сравнения альтернативных планов в рамках одного запроса, но не для точного предсказания времени. Решающее слово всегда за фактическими замерами.

Вывод

Senior-уровень чтения EXPLAIN ANALYZE - это не запоминание всех полей, а выработка привычки сопоставлять три слоя: что планировщик ожидал, что случилось на самом деле и как расходовались системные ресурсы (буферы, память, временные файлы). Начинайте с поиска максимального расхождения между прогнозом и фактом по количеству строк, затем уточняйте причину через буферы и дополнительные метрики, и только потом формулируйте решение - индекс, обновление статистики, изменение work_mem или переписывание запроса. И никогда не забывайте про страховочную транзакцию для DML: настоящий профессионализм начинается с безопасных практик.

Как читать EXPLAIN ANALYZE в PostgreSQL на senior-уровне: не шпаргалка, а система