explain

EXPLAIN и чтение плана запроса

Предпосылки: планировщик запросов (оценки, статистика, cost model), B-tree (типы сканирования: Index Scan/Bitmap/Index Only), буферный кеш (страницы, cache hit vs read).

← Подзапросы и CTE | Память и spill →

Запрос может быть логически простым (WHERE, JOIN, ORDER BY), но выполняться очень по‑разному: полный перебор таблицы, точечный доступ через индекс, чтение по битмапу, сортировка, построение хеш‑таблицы, многократные проходы по одной и той же таблице. EXPLAIN нужен, чтобы увидеть выбранный способ выполнения и понять, что именно делает запрос дорогим: CPU, дисковый I/O, ошибочные оценки числа строк (rows) или «не тот» порядок операций.

Два режима: EXPLAIN и EXPLAIN ANALYZE

EXPLAIN показывает план, не выполняя запрос. Это быстро и безопасно, но это только прогноз: планировщик строит план на основе статистики и модели затрат.

EXPLAIN ANALYZE выполняет запрос и печатает фактические числа рядом с оценками. Это главный инструмент диагностики производительности, потому что он показывает, где именно тратится время и насколько планировщик ошибся.

EXPLAIN
SELECT *
FROM users
WHERE email = 'alice@example.com';
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE email = 'alice@example.com';

ANALYZE запускает запрос реально. Для UPDATE/DELETE/INSERT это значит реальные изменения. Если нужно посмотреть план записи, обычно делают так: выполняют EXPLAIN (ANALYZE, BUFFERS) в транзакции и откатывают её, либо проверяют на тестовой базе.

Как читать вывод EXPLAIN: три числа, которые важнее всего

В каждом узле плана почти всегда есть три группы чисел:

1. cost=... — оценка «дорого/дёшево» в единицах cost model (не миллисекунды). По cost планировщик выбирает план.
2. rows=... — сколько строк планировщик ожидает от этого узла. В оптимизации запросов это часто называют оценкой кардинальности (cardinality estimation).
3. actual ... rows=... loops=... (только в ANALYZE) — сколько строк получилось на самом деле и сколько раз узел запускался.

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

Узлы, которые чаще всего определяют стоимость

Ниже — минимальная карта узлов, которые обычно определяют производительность.

Доступ к таблице: Seq Scan, Index Scan, Bitmap

Seq Scan — чтение всех страниц таблицы. Он не «плохой по определению»: если запрос возвращает большую долю строк, последовательное чтение часто дешевле, чем тысячи точечных походов в heap по TID.

Index Scan — точечный доступ: индекс выдаёт TID’ы, дальше идут обращения к heap за строками. Выгоден при высокой селективности (нужна малая доля строк). Цена — random I/O, если нужных страниц нет в кеше.

Bitmap Index Scan + Bitmap Heap Scan — компромисс: сначала собирается битовая карта нужных TID/страниц, потом heap читается пачками. Этот вариант часто выигрывает, когда строк нужно «немало, но не слишком много»: index scan будет слишком случайным, seq scan — слишком широким.

Соединения: Nested Loop, Hash Join, Merge Join

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

• Nested Loop усиливает ошибку в оценках: если внешний ввод в 10 раз больше, чем ожидалось, внутренний узел может выполниться в 10 раз больше раз.
• Hash Join обычно упирается в память (и при нехватке — в temp I/O).
• Merge Join любит отсортированные входы и может «прятать» стоимость сортировки (если сортировка нужна и так, например для ORDER BY).

Сортировки и группировки: Sort, HashAggregate, GroupAggregate

Сортировки и агрегации часто выглядят «дешёво» в cost, пока всё помещается в память. Если вход большой и операция не помещается, появляется temp I/O. Это отдельная тема, но уже на уровне EXPLAIN её видно по секции BUFFERS (temp read/write) и по метаданным узла Sort/Aggregate.

BUFFERS: как отличить CPU от диска

BUFFERS связывает план с реальными страницами.

Пример того, что можно увидеть:

Buffers: shared hit=1200 read=30 dirtied=2 written=0

• shared hit — страницы нашлись в shared_buffers: запрос в основном работал с памятью.
• shared read — страницы пришлось читать с диска: это почти всегда причина скачков latency.
• dirtied/written — запрос модифицировал страницы; запись может быть результатом DML, установки hint bits или фоновых механизмов.

Если на узле с большим actual time почти нет shared read, то проблема часто CPU‑характера: вычисления, сортировки, большое количество строк, тяжёлые выражения. Если shared read большой — проблема I/O: неудачный plan shape, плохая селективность, «попали» в холодные данные.

Типичный сценарий диагностики

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

1. Выполнить EXPLAIN (ANALYZE, BUFFERS) и найти узел, где сосредоточено основное время выполнения.
2. Проверить, насколько rows совпадает с actual rows. Если ошибка большая, первым делом выяснить, почему статистика не описывает реальные данные (корреляции, распределения, устаревшие оценки).
3. Понять, что доминирует: shared read (диск) или CPU‑работа (много строк/тяжёлые вычисления).
4. Уже после этого выбирать действие: индекс, переписывание запроса, изменение формы условий, разбиение запроса на этапы, или пересмотр модели данных.

Sources

• PostgreSQL Documentation (пример: v16): EXPLAIN. https://www.postgresql.org/docs/16/sql-explain.html
• PostgreSQL Documentation (пример: v16): Using EXPLAIN. https://www.postgresql.org/docs/16/using-explain.html

---

← Подзапросы и CTE | Память и spill →