EXPLAIN показывает оценку планировщика. EXPLAIN ANALYZE выполняет запрос и даёт фактические цифры. Читают actual rows против planned, тип узла и буферы, а не «стрелочки красивого дерева».
PostgreSQL postgres собеседование
Когда запрос «медленный», догадка «надо индекс» часто ошибочна. Сначала смотрят план. EXPLAIN строит дерево узлов и пишет оценочную стоимость и оценочное число строк. Запрос при этом не выполняется. EXPLAIN ANALYZE выполняет его по-настоящему и пишет actual time и actual rows.
Для SELECT это обычно безопасно. Для UPDATE/DELETE вы реально меняете данные, если не обернёте в транзакцию с rollback и не учтёте триггеры и sequences.
Стоимость — абстрактные единицы (seq_page_cost, random_page_cost, cpu_tuple_cost), не миллисекунды. Сравнивать cost двух планов полезно. Переводить cost в «это будет 200 мс» — нет. Для времени нужны ANALYZE и трассы.
Пример. План на пустой таблице врёт: Seq Scan «дешёвый», потому что строк почти нет. План с литералами может отличаться от плана с prepared statement (generic vs custom plan). Снимайте план на реалистичном объёме и с типичными параметрами.
Как читать дерево
Читают снизу вверх и изнутри наружу: сначала листья (сканы), потом join, потом сортировка и limit. Сравнивают planned rows и actual rows. Расхождение на порядки — врёт статистика: пора ANALYZE, проверить n_distinct, возможно создать expression statistics.
Filter против Index Cond: условие в Index Cond сужает индексный скан. Условие в Filter применяется уже к прочитанным строкам — вы сделали лишнюю работу. LIMIT может внезапно сделать Nested Loop выгодным: внешняя сторона маленькая, внутренний индекс бьёт точно.
Узлы, которые спрашивают
- Seq Scan — куча целиком, таблица с начала до конца. Нормально, если дёшево.
- Index Scan — спуск по индексу плюс обращения в heap по ctid (случайный I/O).
- Index Only Scan — куча не нужна, если visibility map помечает страницу all-visible; иначе Heap Fetches.
- Bitmap Index Scan + Bitmap Heap Scan — много попаданий: сначала битмап страниц, потом куча читается пачками ближе к sequential.
- Nested Loop / Hash Join / Merge Join — вложенный цикл хорош при малом внешнем наборе; hash — на больших равенствах; merge — если оба входа уже упорядочены.
BUFFERS показывает shared hit и shared read: страницы из кэша Postgres против чтения с диска (часто это всё же page cache ОС, не «голый HDD»). Много read при холодном кэше — не тот же диагноз, что много read на прогретом сервере. VERBOSE и WAL подключают по ситуации.
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;
-- Seq Scan vs Index Scan, actual time, rows, Heap Fetches, shared readСнимают фактический план с буферами. Смотрят тип скана, actual rows против planned, Heap Fetches и shared hit/read.
Пример. В плане planned rows = 1, а actual rows = 2 000 000. Тип узла уже не главное: статистика врёт. Сначала ANALYZE, потом новый индекс.
Типичная ошибка
Зубрить «Seq Scan всегда плохо». На маленькой таблице или при низкой селективности он дешевле Index Scan. Вторая ловушка — гонять EXPLAIN ANALYZE на DML в проде без понимания, что запрос выполняется. Третья — смотреть только cost и игнорировать, что planned rows = 1, а actual = 2 миллиона.
На собеседовании
Не спорьте со стрелочками. Сравните planned и actual rows, тип узла и buffers. Вслух отличите Index Scan (ходит в heap по ctid) от Index Only Scan (индекс плюс visibility map). Если статистика врёт — сначала ANALYZE, потом новый индекс.