EXPLAIN ANALYZE в PostgreSQL: от seq scan до hash join

Зачем читать план, а не гадать

Запрос идёт пять секунд вместо пяти миллисекунд. Первый рефлекс у большинства — «добавим индекс, авось поможет». Это шаманство: вы дёргаете ручки, не понимая, что физически делает СУБД. EXPLAIN ANALYZE превращает шаманство в инженерию. Он показывает дерево операций, которое планировщик выбрал для исполнения вашего запроса, и — главное — что на самом деле произошло во время выполнения.

Ментальная модель, которую надо держать в голове на протяжении всей статьи: PostgreSQL не «выполняет SQL». Он компилирует ваш декларативный запрос в физический план — дерево узлов, где каждый узел умеет одно: читать таблицу, соединять два потока строк, сортировать, агрегировать. Ваша работа при чтении плана — пройти это дерево и найти узел, который врёт о размере данных или делает заведомо дорогую операцию. Если хочется сразу трогать руками, у нас есть бесплатный курс по SQL с runnable-песочницей PostgreSQL прямо в браузере; но статья самодостаточна.

Дерево плана: снизу вверх, изнутри наружу

Вывод EXPLAIN — это перевёрнутое дерево. Корень — самая верхняя строка, листья — самые вложенные по отступу. Данные текут снизу вверх: листья читают таблицы, родители их соединяют, фильтруют и агрегируют. Читать план надо так же — от самых глубоко вложенных узлов к корню.

EXPLAIN ANALYZE
SELECT c.full_name, count(o.id) AS orders_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'DE'
GROUP BY c.full_name;

HashAggregate  (cost=2310.5..2330.5 rows=2000 width=40)
               (actual time=18.2..18.9 rows=1980 loops=1)
  Group Key: c.full_name
  ->  Hash Join  (cost=120.0..2010.0 rows=24000 width=36)
                 (actual time=1.1..12.4 rows=23910 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Seq Scan on orders o  (cost=0..1500 rows=80000 width=12)
                                   (actual time=0.01..4.2 rows=80000 loops=1)
        ->  Hash  (cost=95.0..95.0 rows=2000 width=28)
                  (actual time=1.0..1.0 rows=1980 loops=1)
              ->  Seq Scan on customers c
                    (cost=0..95.0 rows=2000 width=28)
                    (actual time=0.02..0.7 rows=1980 loops=1)
                  Filter: (country = 'DE'::text)

Самый вложенный узел здесь — Seq Scan on customers с фильтром по стране. Он выполняется первым, его строки строят хеш-таблицу (узел Hash), затем Seq Scan on orders прогоняется через этот хеш в Hash Join, и результат агрегируется. Корень HashAggregate отдаёт финальные строки.

Cost против actual time

В каждой строке две группы чисел.

cost=start..total — оценка планировщика в абстрактных единицах стоимости (по умолчанию единица — это seq_page_cost, чтение одной страницы при последовательном сканировании). Это не миллисекунды и не сравнивается с реальным временем. Первое число — стоимость до выдачи первой строки (startup cost), второе — до выдачи последней.
actual time=start..total — реальное время в миллисекундах, измеренное при выполнении. Тоже startup и total.

Startup cost важен для понимания блокирующих узлов. У Seq Scan startup близок к нулю: первая строка отдаётся почти сразу. У Sort или Hash startup большой: эти узлы должны прочитать весь вход, прежде чем отдать первую строку наружу. Это объясняет, почему LIMIT 10 поверх Seq Scan дёшев, а поверх Sort — нет.

loops: число надо умножать

Критическая деталь, на которой спотыкаются почти все. Если узел исполняется несколько раз (типично внутри Nested Loop), actual time и rows показаны на одну итерацию. Реальная стоимость — это значение, умноженное на loops. Узел с actual time=0.05..0.08 rows=1 loops=200000 на самом деле сжёг около 16 секунд и выдал 200 тысяч строк. Всегда смотрите на loops, прежде чем делать вывод «этот узел дешёвый».

Estimated rows против actual rows — главный диагностический сигнал

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

Откуда берётся расхождение:

Устаревшая статистика. PostgreSQL хранит гистограммы и n_distinct в pg_statistic, обновляемые командой ANALYZE (и автовакуумом). После массовой загрузки или удаления данных статистика врёт. Лечение — ANALYZE имя_таблицы;.
Коррелированные предикаты. Планировщик по умолчанию считает условия независимыми и перемножает их селективности. Для WHERE city = 'Берлин' AND country = 'DE' (город однозначно задаёт страну) это даёт заниженную оценку. Лечение — CREATE STATISTICS с dependencies.
Выражения и функции. WHERE lower(email) = ... — у планировщика нет статистики по результату функции, он берёт грубую константу селективности.

Запомните правило: ищите узел, где estimated и actual rows расходятся сильнее всего, — он почти всегда виновник плохого плана.

Методы доступа: как читается одна таблица

Прежде чем что-то соединять, строки надо достать со страниц. Узел доступа — лист дерева, и от его выбора зависит всё остальное.

Узел	Как работает	Когда планировщик выбирает	Слабое место
Seq Scan	Читает все страницы таблицы подряд	Нет подходящего индекса; или строк так много, что индекс не выгоден	Линейно по размеру таблицы
Index Scan	Спуск по B-tree, затем поход в heap за каждой строкой	Высокая селективность (мало строк), нужны не-индексные колонки	Случайные I/O в heap при многих строках
Index Only Scan	Только обход индекса, heap не трогается	Все нужные колонки есть в индексе и страница «видима» в visibility map	Падает в `Heap Fetches` при свежих изменениях
Bitmap Heap Scan	Строит битовую карту страниц по индексу, потом читает heap по порядку страниц	Среднее число строк: слишком много для Index Scan, слишком мало для Seq	Расход памяти на bitmap, потеря если `work_mem` мал

Seq Scan — не всегда зло

Последовательное чтение всех страниц. На большой таблице, где надо вернуть единичные строки, это красный флаг. Но если запрос всё равно затрагивает значительную долю таблицы (скажем, больше 5-10 процентов), Seq Scan объективно быстрее индекса: последовательный I/O дешевле случайного, а индекс на большой выборке вынуждает прыгать по heap случайным образом. Планировщик это знает через seq_page_cost против random_page_cost. Видеть Seq Scan на маленькой таблице (пара тысяч строк) — нормально и оптимально.

Index Scan и Index Only Scan

Index Scan спускается по B-tree, находит подходящие TID (идентификаторы строк) и идёт в heap за полными строками. Каждый такой поход — потенциально случайный I/O.

Index Only Scan — оптимизация: если все запрошенные колонки лежат в самом индексе, в heap ходить не нужно. Но есть подвох — MVCC требует проверять видимость строки. Поэтому в EXPLAIN ANALYZE смотрите на строку Heap Fetches: если она большая, индекс «only» по факту всё равно ходит в heap (страницы ещё не помечены в visibility map после изменений). Лечение — VACUUM, который обновляет visibility map.

Bitmap Heap Scan — компромисс

Двухфазная операция. Сначала Bitmap Index Scan обходит индекс и строит битовую карту нужных страниц heap. Затем Bitmap Heap Scan читает эти страницы в физическом порядке, превращая случайный I/O в почти последовательный. Это выбор планировщика для «средней» селективности и для комбинирования нескольких индексов через BitmapAnd / BitmapOr. Признак проблемы — строка Recheck Cond с большим Rows Removed by Index Recheck или пометка lossy: bitmap не влез в work_mem и огрубился до уровня страниц.

Методы join: как соединяются два потока

Когда таблиц несколько, узел доступа становится входом для join-узла. Алгоритмов три, и выбор между ними — половина искусства оптимизации.

Nested Loop

Для каждой строки внешнего входа пройти по внутреннему. Без индекса это O(m * n) — катастрофа на больших таблицах. Но если внутренний вход — это Index Scan по ключу соединения, сложность падает до O(m * log n), и на маленьком внешнем входе это самый быстрый вариант: ноль накладных расходов на построение структур. Красный флаг — Nested Loop, где внешний вход выдаёт сотни тысяч строк (помните про loops): это значит, что планировщик недооценил размер из-за плохой статистики.

Hash Join

Берёт меньший вход, строит из него хеш-таблицу в памяти (узел Hash), затем прогоняет больший вход, пробивая каждую строку по хешу. Сложность — O(m + n), не нужны отсортированные или индексированные данные. Работает только для equi-join (условие вида a.x = b.y). Это рабочая лошадка для соединения двух больших неиндексированных таблиц. Критический сигнал в плане — Batches: если значение больше единицы, хеш-таблица не влезла в work_mem и PostgreSQL разбил её на пакеты с записью на диск. Смотрите строку с Disk: в выводе Hash — это спилл, и он резко замедляет узел.

Merge Join

Оба входа должны быть отсортированы по ключу соединения; дальше — слияние двух упорядоченных потоков за один проход, O(m + n). Выгоден, когда данные уже отсортированы (например, оба читаются Index Scan по нужному ключу) или когда сортировка всё равно нужна выше по дереву. Если же планировщику приходится вставлять явный Sort под Merge Join, и этот Sort спиллит на диск, такой план обычно проигрывает Hash Join.

Эвристика выбора:

маленький внешний вход и индекс на внутреннем — Nested Loop;
два больших входа, equi-join, есть память — Hash Join;
входы уже отсортированы по ключу — Merge Join.

BUFFERS: попадание в кэш

Время в миллисекундах обманчиво — оно зависит от того, лежат ли данные в кэше. EXPLAIN (ANALYZE, BUFFERS) показывает, сколько страниц узел взял из shared buffers (кэш PostgreSQL) против тех, что пришлось читать с диска (или из page cache ОС).

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using orders_customer_id_idx on orders
    (cost=0.42..18.3 rows=6 width=64)
    (actual time=0.03..0.05 rows=6 loops=1)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=4 read=0
Planning:
  Buffers: shared hit=12

Ключевые поля:

shared hit — страницы найдены в кэше PostgreSQL (быстро, без I/O).
shared read — страницы прочитаны с диска (на самом деле — мимо shared buffers; могли попасть в page cache ОС, но для PostgreSQL это «промах»).
shared dirtied / written — страницы изменены или вытеснены на диск во время запроса.

Зачем это нужно: BUFFERS даёт метрику, не зависящую от прогрева кэша. Первый запуск с холодным кэшем покажет много read и большое время; повторный — почти всё hit и время в разы меньше. Если вы сравниваете два плана, сравнивайте суммарное число обработанных буферов, а не только миллисекунды. Узел, который трогает десятки тысяч буферов ради десятка строк на выходе, неэффективен независимо от того, попал он в кэш или нет.

Чек-лист красных флагов

При разборе плана идите по списку — это ловит подавляющее большинство проблем.

Rows расходятся на порядки. estimated 100 против actual 200000. Корень почти всех бед. Сначала ANALYZE таблицы, потом думайте дальше.
Seq Scan на большой таблице с селективным фильтром. Если возвращается малая доля строк, но идёт полное сканирование — нет индекса, либо индекс есть, но не используется (несовпадение типов, функция над колонкой, отключённый индекс).
Спилл сортировки на диск. В узле Sort строка Sort Method: external merge Disk: 48000kB вместо quicksort Memory. Поднимайте work_mem для этой сессии или уменьшайте объём сортируемых данных.
Hash Join с Batches > 1 и Disk: — хеш не влез в память, тот же рецепт.
Nested Loop с огромным loops. Внутренний узел гоняется сотни тысяч раз — обычно следствие пункта 1.
Index Only Scan с большим Heap Fetches — нужен VACUUM, иначе «only» не экономит.
Lossy bitmap в Bitmap Heap Scan — мало work_mem.

Маленький практический приём для пунктов 3-4: посмотрите спилл, временно поднимите память и перепроверьте план.

SET work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ORDER BY ...;
RESET work_mem;

Если спилл исчез и время упало — вы нашли причину. Только не оставляйте глобально огромный work_mem: он выделяется на каждый сортирующий узел каждого соединения, и при высокой конкуренции это съест всю память сервера.

Куда дальше

Чтение плана — это навык, который ставится практикой: один и тот же запрос на холодном и прогретом кэше, с индексом и без, до и после ANALYZE. Лучшее, что можно сделать после этой статьи, — открыть EXPLAIN ANALYZE на своих реальных запросах и пройтись по чек-листу красных флагов.

Если хочется системно, от B-tree и устройства индекса до устройства самого планировщика, у нас есть бесплатный курс по SQL — с runnable-песочницей PostgreSQL прямо в браузере, где можно гонять EXPLAIN без установки чего-либо. Он входит в бесплатное направление по дата-инженерии, которое ведёт от SQL и Python до Spark, Kafka и архитектуры платформ. Всё открыто и бесплатно.