Reading Query Plans: EXPLAIN and EXPLAIN (ANALYZE, BUFFERS)

Когда запрос внезапно тормозит, гадать бесполезно — нужно спросить у самой базы, что она собирается делать. В PostgreSQL для этого есть EXPLAIN: команда показывает план выполнения, который выбрал планировщик. А EXPLAIN (ANALYZE, BUFFERS) идёт дальше и реально выполняет запрос, показывая фактическое время и обращения к диску. В этой статье разберём, как читать план, чем Seq Scan отличается от Index Scan, почему оценки расходятся с реальностью и как по плану понять, что не хватает индекса.

Будем работать со схемой интернет-магазина: таблицы users, orders и employees.

EXPLAIN против EXPLAIN ANALYZE

EXPLAIN только оценивает план, не запуская запрос. EXPLAIN ANALYZE запрос выполняет (осторожно с INSERT/UPDATE/DELETE!) и добавляет реальные числа.

-- только план и оценки, запрос не выполняется
EXPLAIN
SELECT * FROM orders WHERE user_id = 42;

-- реальное выполнение + время + чтения из кэша/диска
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42;

Типичная верхняя строка плана:

Seq Scan on orders  (cost=0.00..1834.00 rows=120 width=64)
                    (actual time=0.015..12.4 rows=118 loops=1)

Что здесь важно:

• cost=0.00..1834.00 — оценка стоимости в условных единицах: до первой строки и до последней. Это не миллисекунды.
• rows=120 — сколько строк планировщик ожидает.
• actual time=... и actual rows=118 — что произошло на самом деле.
• loops=1 — сколько раз узел выполнялся (важно во вложенных циклах).

Чтобы выполнить EXPLAIN ANALYZE на изменяющем запросе без последствий, оберните его в транзакцию:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK;  -- ничего реально не удалится

Seq Scan против Index Scan

Seq Scan (последовательное сканирование) читает всю таблицу строка за строкой. Это не всегда плохо: если нужно вернуть большую часть таблицы, читать её целиком дешевле, чем прыгать по индексу.

Index Scan использует индекс, чтобы найти нужные строки точечно. Он выигрывает, когда условие отбирает небольшую долю строк (селективный фильтр).

-- селективно: один пользователь из миллиона -> Index Scan
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

-- неселективно: половина таблицы -> Seq Scan ожидаем и оправдан
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'paid';

Есть ещё Bitmap Heap Scan — компромисс: PostgreSQL собирает совпадения по индексу в битовую карту, а потом читает таблицу пачками по физическому порядку. Его выбирают для «средней» селективности — сотни-тысячи строк.

Пожалуйста, не «оптимизируйте» планировщик хинтами — лучше дайте ему свежую статистику:

ANALYZE orders;  -- пересобрать статистику распределения данных

Подводный камень: в MySQL команда называется EXPLAIN ANALYZE (с 8.0), но формат вывода другой и измеряется в реальном времени по узлам. В ClickHouse используется EXPLAIN PLAN / EXPLAIN PIPELINE, а понятия «индекс» там нет — вместо него разреженный primary key и пропуск гранул.

Оценки против реальности

Самый ценный навык — сравнивать rows (оценка) с actual rows (факт). Большое расхождение означает, что у планировщика плохая статистика, и он мог выбрать неоптимальный план.

EXPLAIN ANALYZE
SELECT *
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.country = 'DE' AND o.created_at > '2026-01-01';

Если увидите что-то вроде:

Nested Loop  (rows=5 ... actual rows=48000 loops=1)

— это красный флаг. Планировщик думал, что строк будет 5, и выбрал Nested Loop, а их оказалось 48000. Для такого объёма выгоднее был бы Hash Join. Причины расхождения:

• устаревшая статистика — лечится ANALYZE;
• коррелированные столбцы (например, city и country) — помогает CREATE STATISTICS;
• сложные выражения в фильтре, по которым нет статистики.

Смотрите на оценки снизу вверх: ошибка в листовом узле раздувается по всему дереву соединений.

Как поймать недостающий индекс

Недостающий индекс выглядит в плане очень узнаваемо: Seq Scan по большой таблице с селективным фильтром в Filter, где Rows Removed by Filter огромен.

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

Seq Scan on employees  (cost=0.00..21000 rows=1 ...)
  Filter: (email = 'anna@example.com')
  Rows Removed by Filter: 499999
  Buffers: shared read=8200

Сигналы тревоги:

• Seq Scan там, где вы ожидали найти одну строку (rows=1);
• Rows Removed by Filter: 499999 — база перебрала полтаблицы впустую;
• Buffers: shared read=8200 — много блоков прочитано с диска.

Лечение — индекс по столбцу из фильтра:

CREATE INDEX idx_employees_email ON employees (email);

После этого тот же план превращается в Index Scan с Buffers: shared read=4 и временем в микросекундах. Для запросов, которым нужно лишь несколько столбцов, рассмотрите покрывающий индекс:

CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at) INCLUDE (status);

Подводный камень: индекс не используется, если столбец завёрнут в функцию (WHERE lower(email) = ...) — планировщик не видит совпадения. Либо стройте функциональный индекс (CREATE INDEX ... ON employees (lower(email))), либо убирайте функцию из условия.

Привыкайте запускать EXPLAIN (ANALYZE, BUFFERS) на любом медленном запросе до того, как тянуться к индексу или переписывать SQL. План скажет, где именно теряется время — а дальше это уже инженерия, а не гадание.