EXPLAIN и план запроса на собеседовании Data Engineer

Зачем спрашивают на собесе DE

Все DE-задачи в проде сводятся к «запрос медленный — что делать». Без EXPLAIN это гадание на гуще. На собесе обязательно: «прочитай план», «почему здесь Hash Join, а не Nested Loop», «что значит Rows Removed by Filter: 1M».

Главная боль без понимания — DE добавил индекс «и не помогло». А не помог потому, что планировщик не выбрал индекс, а это видно только в EXPLAIN.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN SELECT * FROM users WHERE email = 'a@b';
-- предсказание плана, реально не выполняет

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b';
-- выполняет запрос и показывает реальные тайминги

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
-- + информация о страницах из cache vs disk

Ключевые опции:

• ANALYZE — реальное выполнение, реальные rows и timings.
• BUFFERS — сколько страниц прочитано из shared buffers (cache) и из диска.
• VERBOSE — больше деталей.
• FORMAT JSON — машиночитаемо, удобно скриптами парсить.
• WAL — сколько WAL-записей породил запрос (для INSERT/UPDATE/DELETE).
• SETTINGS — какие настройки повлияли.

Опасность ANALYZE: реально выполняет запрос. Не запускай EXPLAIN ANALYZE на UPDATE/DELETE без BEGIN; ROLLBACK;.

BEGIN;
EXPLAIN ANALYZE DELETE FROM big_table WHERE created_at < '2020-01-01';
ROLLBACK;

Структура плана

План — дерево узлов. Читать снизу вверх и изнутри наружу.

Sort  (cost=10.5..10.6 rows=10 width=40) (actual time=0.2..0.2 rows=12 loops=1)
  Sort Key: u.created_at
  ->  Hash Join  (cost=2.0..10.4 rows=10 width=40)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (cost=0..6.0 rows=100)
        ->  Hash  (cost=1.5..1.5 rows=10)
              ->  Index Scan on users u  (cost=0.1..1.5 rows=10)
                    Index Cond: (status = 'active')

В каждой строке:

• cost=A..B — оценка планировщика. A — стартовая стоимость (до первой строки), B — общая. Условные единицы.
• rows — оценка количества строк.
• width — средний размер строки в байтах.
• actual time=A..B — реальное время в мс (только в ANALYZE).
• rows actual — реальное количество строк.
• loops — сколько раз узел выполнялся (важно для Nested Loop).

Главное правило: сравнивай оценочные rows с фактическими. Если расхождение в 100×+ — планировщик ошибается, нужен ANALYZE TABLE или хинты.

Scan-методы

Seq Scan — последовательное чтение всей таблицы. Не плохо: на маленькой или при выборке > 5-20% строк часто оптимально.

Index Scan — поиск по индексу + чтение нужных строк из heap. Хорош на низкой селективности (несколько строк).

Index Only Scan — данные взяты из индекса (covering index или visibility map подсказал). Не идём в heap.

Bitmap Heap Scan + Bitmap Index Scan — для средней селективности и OR. Сначала в индексе строится битмап нужных страниц, потом читаем страницы. Снижает random IO.

Tid Scan — поиск по физическому tid (ctid). Редко, но бывает.

-- быстрый
Index Scan using idx_user_id (cost=0.1..8.5 rows=1)

-- если ANALYZE показывает rows=1M вместо rows=1
-- → проблема: статистика плохая или вы выбрали индекс ошибочно

Join-методы

Nested Loop — для каждой строки внешней таблицы перебираем внутреннюю (часто через индекс).

Nested Loop
  ->  Seq Scan on a (rows=10)
  ->  Index Scan on b using idx_b_a_id (rows=1, loops=10)

Хорош, когда внешняя таблица маленькая (десятки строк) и есть индекс на join-колонке внутренней.

Hash Join — по внутренней таблице строится хеш-таблица в памяти, по внешней пробегает с lookup.

Hash Join
  ->  Seq Scan on a (rows=1M)
  ->  Hash
        ->  Seq Scan on b (rows=10K)

Хорош для больших равенств (=). Плохо, если хеш не влезает в work_mem — fallback на disk → batch.

Merge Join — обе таблицы предварительно отсортированы (или индексы дают порядок), идём двумя курсорами.

Merge Join
  ->  Index Scan on a using idx_a_id
  ->  Sort
        ->  Seq Scan on b

Хорош на больших отсортированных данных без подходящего хеша.

Подсказка для собеса: если запрос с join тормозит — обычно неправильный join-метод из-за плохой оценки rows. Проверяй EXPLAIN ANALYZE и сравнивай rows estimated vs actual.

Сортировка и агрегация

Sort. Может быть in-memory (quicksort) или external on-disk (external merge). Если видишь external merge Disk — мало work_mem или подходящего индекса нет. Решение: подними work_mem или сделай ORDER BY по индексированной колонке.

Hash Aggregate — GROUP BY через хеш в памяти. Быстрее, но требует work_mem.

Group Aggregate — GROUP BY после Sort. Медленнее, но не требует памяти.

Limit — раннее завершение. С индексной сортировкой ORDER BY ... LIMIT 10 берёт первые 10 без полного сканирования.

Типичные проблемы и как искать

Rows Removed by Filter: огромное число.

Seq Scan on big_table (rows=10)
  Filter: (status = 'active')
  Rows Removed by Filter: 9990

Прочитали 10000 строк, отфильтровали 9990. Нужен индекс на status или partial index.

Лживые оценки.

... rows=1 ... (actual rows=1000000)

Планировщик оценил 1, получилось миллион → ANALYZE, проверить pg_stats, иногда — CREATE STATISTICS на функциональные/корреляционные зависимости.

Hash batches на диск.

Hash Cond: ...
  Buckets: 32768  Batches: 4  Memory Usage: 256MB

Batches > 1 = на диск. Подними work_mem для запроса (SET LOCAL work_mem = '512MB').

Nested Loop на больших данных.

Nested Loop  (loops=10000000)

Loops > 100k и нет индекса → план плохой, попробуй Hash Join (хинт через pg_hint_plan или переписать запрос).

Index Only Scan + Heap Fetches. Heap Fetches > 0 означает, что visibility map не пересекает все страницы → VACUUM помогает.

Частые ошибки

Анализировать EXPLAIN без ANALYZE. Это только оценка. Реальные тайминги нужны.

Запускать EXPLAIN ANALYZE на DELETE/UPDATE на проде. Запрос реально выполнится. Только в BEGIN; ... ROLLBACK;.

Игнорировать loops в Nested Loop. Время = actual time × loops. Маленький actual time, но 1М loops = десятки секунд.

Считать, что Seq Scan — всегда плохо. Для маленькой таблицы или selectivity > 30% это оптимально.

Не делать ANALYZE после bulk load. Планировщик использует устаревшую статистику и выбирает плохой план.

Не использовать BUFFERS. Это разрыв между «горячим» и «холодным» планом. Buffers: shared hit=10000 — всё в кэше; read=10000 — диск.

Полагаться на форматированный TEXT для парсинга. Используй FORMAT JSON для скриптов.

Связанные темы

• Индексы БД на собесе DE
• Партиционирование таблиц для DE
• Транзакции и MVCC на собесе DE
• SQL для Data Engineer: собеседование
• Подготовка к собесу Data Engineer

FAQ

Что такое cost в EXPLAIN?

Условные единицы планировщика. Грубо: 1.0 = одно случайное чтение страницы. Параметризуется через seq_page_cost, random_page_cost, cpu_tuple_cost. Сравнивать стоит планы между собой, не абсолютное значение.

Как заставить Postgres использовать индекс?

Лучший способ — нормальная статистика (ANALYZE) и подходящие индексы. Если очень нужно — pg_hint_plan или ручные SET enable_seqscan = OFF (ужасный паттерн на проде).

Что значит external merge Disk: 200000kB?

Сортировка не влезла в work_mem и пишется в temp-файлы. Для серьёзных запросов поднимай SET LOCAL work_mem = '512MB'.

Как читать план в ClickHouse?

EXPLAIN PIPELINE или EXPLAIN PLAN показывает stages MergeTree-движка. Совсем другая модель, чем в Postgres — там «pipeline of streams», а не дерево узлов.

Чем отличается auto_explain от ручного?

auto_explain логирует план для запросов > N мс автоматически. Полезно для разбора долгих запросов в проде без воспроизведения вручную.

Это официальная информация?

Нет. Статья основана на документации Postgres 14+ и материалах по оптимизации запросов.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.