EXPLAIN ANALYZE: читать план запроса, а не угадывать индекс

Симптом: в тикет кладут скриншот EXPLAIN и пишут «нужен индекс», хотя ни параметров, ни фактических строк, ни времени там нет. Цена такой диагностики — исправление не того узла: добавляют ключ, когда ошибается оценка, либо гоняются за Seq Scan, который является самым дешёвым вариантом. План — не приговор и не рецепт. Это дерево предположений о том, как получить результат; EXPLAIN ANALYZE позволяет положить рядом часть предположений и факт выполнения.

Разберём один механизм: как из плана сделать короткую цепочку «где возникла ошибка → чем её проверить → что менять». Уровень автора 2019 года здесь намеренно прикладной: SQL, параметры, server-side plan и базовые счётчики, без обещаний универсального тюнинга. Мы не называем один узел лучшим. Мы ищем первый узел, где оценённое число строк перестало быть правдоподобным, и отличаем его от узла, который просто виден вверху дерева.

Два режима EXPLAIN отвечают на разные вопросы

Обычный EXPLAIN строит выбранный план и показывает оценки: стоимость, количество строк и ширину строки. Он безопаснее для тяжёлого или изменяющего statement, потому что сам запрос не выполняет. EXPLAIN ANALYZE выполняет statement и добавляет фактические строки и время. Поэтому он нужен, когда мы проверяем оценку, но требует той же осторожности, что и исходный запрос: SELECT может нагрузить базу, а INSERT/UPDATE/DELETE действительно изменят данные, если не выполнить их в защищённой транзакции и не сделать rollback.

Добавка BUFFERS делает разбор полезнее для медленных случаев. Она сообщает количество буферов, затронутых на узлах, и помогает увидеть разницу между «мы нашли мало строк, но прочитали много страниц» и «мы почти ничего не читали, но много раз повторили вычисление». Это не счётчик запросов приложения и не замер диска в чистом виде: это статистика буферов PostgreSQL. Читаем её вместе с rows и loops, а не отдельно как ещё одно большое число.

План читается от верхнего оператора к его входам, но расследование часто начинает снизу: с scan, который создаёт объём данных. Отступы показывают, чей результат подаётся родителю. Если верхний Aggregate медленный, это ещё не означает, что агрегирование виновато. Он мог дождаться миллионов строк от ребёнка. И наоборот, красивый Index Scan ниже может не спасать, если следующий Filter выбрасывает почти всё найденное.

Контракт одной фикстуры и одного снимка

Для чтения плана недостаточно сократить SQL до «примерно такого». Нужны точные параметры, версия сервера и форма предиката. Используем ту же минимальную схему p17_sql_index_fixture.work_orders: редкий waiting, частый ready и диапазон по created_at. Выполнить её можно только в disposable-базе PostgreSQL 11. В этой задаче такого запуска не было: psql не установлен и подключения нет. Поэтому ниже только команды снятия данных, а не искусственно сочинённые строки actual time.

-- Для уже созданной p17_sql_index_fixture.work_orders.
-- План фиксируем вместе с реальными значениями параметров.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, amount
FROM p17_sql_index_fixture.work_orders
WHERE state = 'waiting';

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, amount
FROM p17_sql_index_fixture.work_orders
WHERE state = 'ready';

-- В изменяющем сценарии не оставляем изменения ради плана.
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE p17_sql_index_fixture.work_orders
SET amount = amount + 1
WHERE state = 'waiting';
ROLLBACK;

Последние три строки показывают границу, а не призыв гонять UPDATE в production. Документация PostgreSQL прямо отмечает, что EXPLAIN ANALYZE исполняет statement; транзакция с ROLLBACK защищает данные от учебного изменяющего примера. Для реального расследования договоритесь о безопасном окне и о том, можно ли вообще исполнять тяжёлый запрос. Измерение, которое само создаёт инцидент, не становится качественным от слова ANALYZE.

Estimate против actual: ищем первую развилку

Главная пара чисел — rows и actual rows на одном узле. Малое отклонение естественно: статистика приблизительна, распределение меняется, стоимость не является физическим секундомером. Но кратное расхождение важно, особенно если оно начинается на нижнем scan и затем размножается через join. Планировщик выбирает join order, способ соединения и размер промежуточных наборов по оценке. Если он ожидает десять строк, а получает сто тысяч, последующий nested loop или sort может стать дорогим не потому, что этот оператор «плохой», а потому, что вход в него оказался другим.

Не надо исправлять каждый верхний узел по очереди. Отмечаем первый узел снизу, где estimate заметно ушёл от факта, и задаём узкий вопрос. Предикат слишком широкий? Статистика старая? Два столбца коррелируют, но известны планировщику по одному? Тип параметра ведёт к cast? Условие спрятано за функцией? Это уже проверяемые гипотезы. Фраза «оптимизатор тупит» не говорит, какую из них можно опровергнуть.

Есть ещё ловушка loops. PostgreSQL показывает actual time и actual rows как средние за один запуск узла, чтобы сравнивать их с оценками. Внутренний узел nested loop с actual time=0.15..0.20 может казаться невинным, но при loops=10000 его вклад нельзя оценивать как две десятые миллисекунды. Умножаем среднее на loops, затем смотрим Buffers: это связывает повторение логики с количеством затронутых страниц.

Index Cond, Filter и цена поздней проверки

План с названием индекса не равен плану с точным поиском. У Index Cond видно условие, которое ограничивает доступ через индекс. У Filter видно условие, применённое к строкам после выбранного доступа. Это нормальная конструкция: один индекс может сузить набор, второй предикат проверяется позже. Но если индекс отдаёт большую часть таблицы, а filter выбрасывает почти всё, проверяем порядок ключей, форму выражения и статистику, а не объявляем любой Index Scan успехом.

Пример: есть индекс (state), а запрос одновременно выбирает диапазон времени. Если state = 'ready' почти ничего не отсекает, он может быть плохой точкой старта даже при наличии Index Cond. Если реальный пользовательский путь всегда просит редкий state и короткий диапазон, исследуем составной индекс и порядок его колонок на подтверждённых запросах. Если путь выбирает почти всё, возможно, правильнее остаться на последовательном чтении. Индекс проектируют от устойчивой нагрузки, не от одного названия поля.

Статистика, корреляция и честная проверка

Стандартная статистика хранится по отдельным столбцам. Планировщик обычно предполагает независимость условий, а в реальных таблицах столбцы часто связаны: город и почтовый индекс, тип заказа и статус, страна и валюта. PostgreSQL 11 поддерживает расширенную статистику, но это не кнопка «собрать всё». Сначала нужен доказанный плохой estimate на конкретном сочетании условий. Потом можно рассмотреть CREATE STATISTICS для этой группы и снова выполнить ANALYZE.

-- Пример исследовательского шага для реально коррелирующих условий.
-- Не создавайте статистику на все пары колонок без плана и симптома.
CREATE STATISTICS work_orders_state_created_stats (dependencies)
ON state, created_at
FROM p17_sql_index_fixture.work_orders;

ANALYZE p17_sql_index_fixture.work_orders;

SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'p17_sql_index_fixture'
  AND tablename = 'work_orders';

Этот пример не обещает, что зависимости помогут диапазону по времени: документация PostgreSQL 11 прямо ограничивает functional dependencies простыми equality-условиями с константами. Поэтому в журнале расследования фиксируем не «создали CREATE STATISTICS», а исходный estimate, форму условия, ожидание от статистики и результат повторного плана. Если условие не попадает в границы механизма, не надо приписывать ему эффект.

Порядок чтения, который можно повторить

1. Сохранить точный SQL и параметры. Отдельно записать, что болит: время ответа, чтение буферов, неверный join order или рост после изменения данных.
2. Снять plain EXPLAIN, затем безопасный EXPLAIN (ANALYZE, BUFFERS). Не запускать write-statement без транзакционной границы и разрешения на нагрузку.
3. Прочитать дерево от результата к входам и отметить scan/join, который создаёт крупный поток строк. Не считать верхний узел причиной только потому, что он напечатан первым.
4. На каждом ключевом узле сверить rows с actual rows; при loops > 1 учесть повторения.
5. Разобрать Index Cond отдельно от Filter и посмотреть Buffers. Это отделяет доступ к строкам от позднего отбора и повторного I/O.
6. Изменить одну подтверждённую причину: статистику, форму предиката, ключ индекса или сам объём работы. Переснять такой же план и сравнить данные, а не только имя scan.

Границы вывода

• Фактическое время из EXPLAIN ANALYZE включает выполнение под его профилированием; не переносите одно значение как SLA для приложения.
• Одинаковый SQL может получить другой план после изменения данных, настроек стоимости, памяти, версии или параметров. Снимок надо хранить с контекстом.
• Принудительное отключение планов может помочь увидеть альтернативу, но не заменяет статистику и не является автоматическим production-fix.
• Расширенная статистика требует конкретной корреляции и конкретного неверного estimate. Собирать её на все комбинации означает добавлять стоимость без диагноза.

Итог

Хорошее чтение EXPLAIN ANALYZE не начинается с охоты на Index Scan. Оно начинается с точного SQL, затем сопоставляет estimate и actual на узлах, учитывает loops, различает Index Cond и Filter, читает Buffers и ищет первую неверную предпосылку. После этого индекс становится одним из вариантов действия, а не ответом, выбранным до вопроса.

Проверяемые источники

• PostgreSQL 11: Using EXPLAIN — стоимости, дерево плана, реальные строки и время в EXPLAIN ANALYZE; значения actual time и rows даны в среднем на один loop
• PostgreSQL 11: EXPLAIN — ANALYZE действительно выполняет statement, BUFFERS выводит статистику буферов; для изменяющих запросов документация рекомендует транзакцию с rollback
• PostgreSQL 11: Statistics Used by the Planner — селективность оценивается по приблизительной статистике; pg_stats удобнее прямого чтения pg_statistic, а корреляцию столбцов не ловят обычные одноколоночные статистики
• PostgreSQL 11: ANALYZE — ANALYZE собирает приблизительную выборку, обновляет статистику для планировщика и позволяет повышать target ценой времени и места
• PostgreSQL 11: Introduction to Indexes — индекс ускоряет поиск небольшого числа строк, но имеет цену на изменениях; после создания может требоваться актуальная статистика