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

Медленный запрос редко объясняется одной строкой плана. Seq Scan может быть правильным выбором, а Index Scan — дорогим из-за тысяч повторных запусков. Диагностика начинается не с поиска «плохого» названия узла, а с четырёх вопросов: сколько строк ожидал планировщик, сколько получил исполнитель, сколько раз сработал узел и какие буферы он затронул.

Если задача пока сводится к выбору структуры доступа, сначала прочитайте статью про индексы PostgreSQL. Здесь не повторяем каталог B-tree, GIN и других методов. Разберём, как читать уже полученный реальный план и находить место, где оценка расходится с выполнением.

Сначала — безопасность

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

Если нужен план DML без сохранения изменений, выполняйте его в транзакции и откатывайте:

BEGIN;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
UPDATE orders
SET status = 'archived'
WHERE created_at < date '2024-01-01';

ROLLBACK;

Откат не делает запуск бесплатным: запрос всё равно берёт блокировки, меняет страницы, может писать WAL и долго мешать другим транзакциям. На production сначала оцените план через EXPLAIN без ANALYZE. Реальное выполнение запускайте только когда понятны объём данных, допустимая нагрузка и последствия блокировок.

Рабочая команда и пример плана

Для ручной диагностики удобен текстовый формат с фактическими метриками и именами объектов:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT *
FROM tenk1 AS t1
JOIN tenk2 AS t2 ON t2.unique2 = t1.unique2
WHERE t1.unique1 < 10;

Ниже сокращённый фрагмент плана на тестовых таблицах PostgreSQL. VERBOSE также печатает списки Output, но для разбора чисел они опущены:

Nested Loop  (cost=4.65..118.50 rows=10 width=488)
             (actual time=0.017..0.051 rows=10.00 loops=1)
  Buffers: shared hit=29 read=11 written=4
  ->  Bitmap Heap Scan on public.tenk1 t1
        (cost=4.36..39.38 rows=10 width=244)
        (actual time=0.009..0.017 rows=10.00 loops=1)
        Recheck Cond: (t1.unique1 < 10)
        Heap Blocks: exact=10
        Buffers: shared hit=5 read=5 written=4
        ->  Bitmap Index Scan on tenk1_unique1
              (cost=0.00..4.36 rows=10 width=0)
              (actual time=0.004..0.004 rows=10.00 loops=1)
              Index Cond: (t1.unique1 < 10)
              Buffers: shared hit=2
  ->  Index Scan using tenk2_unique2 on public.tenk2 t2
        (cost=0.29..7.90 rows=1 width=244)
        (actual time=0.003..0.003 rows=1.00 loops=10)
        Index Cond: (t2.unique2 = t1.unique2)
        Buffers: shared hit=24 read=6
Planning:
  Buffers: shared hit=15 dirtied=9
Planning Time: 0.485 ms
Execution Time: 0.073 ms

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

SQL / 01koddo

План читается от листьев к итоговому узлу

Как читать дерево снизу вверх

Отступ показывает зависимость: строка с большим отступом — дочерний узел строки над ней. Дочерний узел производит строки, родитель принимает их и выполняет следующую операцию. Поэтому практичный порядок чтения такой:

1. Найдите самые глубокие узлы: сканы таблиц, индексов или другие источники строк.
2. Посмотрите, сколько строк каждый узел отдаёт вверх и сколько раз запускается.
3. Поднимайтесь по отступам к соединениям, сортировкам и агрегациям.
4. Закончите корневым узлом: его результат становится результатом всего запроса.

В примере Bitmap Index Scan находит адреса десяти строк. Bitmap Heap Scan читает соответствующие страницы таблицы и отдаёт десять строк. Для каждой из них Nested Loop запускает внутренний Index Scan по tenk2, поэтому у него loops=10. Корень возвращает те же десять соединённых строк.

«Снизу вверх» — способ анализа дерева, а не буквальная временная шкала. Исполнитель запрашивает строки у дочерних узлов по мере необходимости; LIMIT, например, может остановить ветку до полного выполнения.

Estimated: cost, rows и width

Первая группа чисел существует до запуска запроса. Это прогноз планировщика:

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

Actual: time, rows и loops

Группа actual появляется только с ANALYZE:

• actual time=0.017..0.051 — среднее время от запуска узла до первой строки и до завершения одного запуска, в миллисекундах;
• rows=10.00 — среднее число строк, отданных за один запуск;
• loops=1 — сколько раз узел выполнялся.

Если loops больше единицы, time и rows в текстовом плане усреднены на один запуск. У внутреннего скана из примера actual time=0.003..0.003 rows=1.00 loops=10. Значит, он вернул примерно 1 × 10 = 10 строк, а суммарное время его полных запусков — около 0.003 × 10 = 0.030 мс. Значение приблизительное: вывод округлён, а разные запуски могли занимать разное время.

Умножение не даёт время всего запроса. Время узла включает работу его дочерних узлов, поэтому суммирование всех строк плана задвоит затраты. Для общего времени смотрите Execution Time, а умножение на loops используйте, чтобы оценить накопленную работу конкретного повторяемого узла.

BUFFERS: hit, read, dirtied и written

BUFFERS показывает обращения к блокам таблиц и индексов. В PostgreSQL 18 параметр ANALYZE включает эту статистику автоматически; явный BUFFERS оставляет намерение команды понятным.

hit не означает чтение с физического диска — наоборот, PostgreSQL нашёл блок в собственном кэше. Но и read не доказывает обращение к накопителю: операционная система могла отдать страницу из своего файлового кэша. Поэтому по одной строке BUFFERS нельзя разделить RAM и физический I/O. При включённом track_io_timing план дополнительно показывает время чтения и записи, но результаты всё равно нужно оценивать в контексте кэшей и параллельной нагрузки.

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

Кроме shared, план может показать local для временных таблиц и индексов, а также temp для временных рабочих данных сортировок, хешей и похожих операций.

Rows Removed by Filter

Строка Rows Removed by Filter показывает, сколько уже прочитанных строк узел отверг своей проверкой Filter. Например:

Seq Scan on orders
  (actual time=0.020..42.600 rows=120 loops=1)
  Filter: (status = 'failed'::text)
  Rows Removed by Filter: 799880

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

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

Расхождение оценок: где планировщик ошибся в rows

Сравнивайте estimated rows и actual rows в каждом узле, начиная снизу. Небольшое отличие нормально: статистика приблизительна. Опасен разрыв на порядки, особенно в нижней ветке. Если планировщик ожидал одну строку, получил 50 000 и на этой оценке выбрал вложенный цикл, ошибка размножается в родительских узлах.

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

SELECT
  relname,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'users');

Если автоанализ ещё не успел обновить изменившуюся таблицу, статистику можно собрать явно:

ANALYZE public.orders;

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

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

Planning Time и Execution Time

Planning Time — время построения и оптимизации плана после разбора и переписывания запроса; сами разбор и переписывание в него не входят. Большое время планирования встречается у сложных запросов с множеством вариантов соединения и секционированных таблиц, но само по себе ничего не говорит о скорости выполнения.

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

Сравнивайте одинаковые величины. Для запроса, который запускается один раз, обычно важнее Execution Time. Для очень короткого подготовленного запроса, выполняемого тысячи раз, цена планирования и повторного планирования уже может быть значимой.

Чек-лист диагностики плана

1. Зафиксируйте текст запроса, параметры, настройки сессии и объём данных: другой параметр может дать другой план.
2. Для DML начните с EXPLAIN без ANALYZE; реальное выполнение запускайте в безопасном окне, при необходимости внутри BEGIN/ROLLBACK.
3. Читайте дерево от самых глубоких узлов к корню и следуйте отступам.
4. В каждом узле сравните estimated rows с actual rows.
5. При loops > 1 умножьте средние rows и полное время одного запуска на число циклов; не складывайте времена родителей и детей.
6. Найдите большие Rows Removed by Filter и проверьте, нельзя ли сократить поток строк раньше.
7. Проследите, в какой ветке растут shared read, temp и общее число обращений к буферам.
8. Отдельно сравните Planning Time и Execution Time, не принимая их сумму за клиентскую задержку.
9. Если оценки расходятся на порядки, проверьте свежесть статистики до изменения запроса или схемы.
10. Меняйте одну вещь за раз и снимайте тот же план в сопоставимых условиях.

Хороший разбор заканчивается не фразой «здесь Seq Scan», а проверяемым диагнозом: какой узел произвёл лишние строки, сколько раз повторился, какие блоки затронул и на какой неверной оценке построены решения выше по дереву.