Как читать дерево плана EXPLAIN

Лена ворвалась в кабинет Макса с горящими глазами:

— Я сделала 20 EXPLAIN’ов! И теперь у меня ещё больше вопросов! Почему тут Seq Scan, а тут Index Scan? Что за цифры в скобках? И почему это похоже на дерево?

Макс смахнул крошки от печенья с клавиатуры:

— О, ты уже заметила главное! План запроса — это действительно дерево. Но не простое, а перевёрнутое.

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

Макс открыл пример:

EXPLAIN SELECT * FROM orders JOIN users ON users.id = orders.user_id;

Hash Join  (cost=100.50..150.20 rows=500 width=200)
  Hash Cond: (orders.user_id = users.id)
  ->  Seq Scan on orders  (cost=0.00..50.00 rows=1000 width=100)
  ->  Hash  (cost=70.50..70.50 rows=2000 width=100)
        ->  Seq Scan on users  (cost=0.00..70.50 rows=2000 width=100)

— Видишь эту лесенку? Читаем снизу вверх:

Листья (низ): Сначала сканируем таблицы (Seq Scan)

Ветки (середина): Хешируем данные (Hash)

Ствол (верх): Соединяем результаты (Hash Join)

Лена постучала пальцем по экрану:

— То есть PostgreSQL сначала выполняет то, что глубже в дереве?

— Для чтения плана начинай с самых глубоких узлов, — ответил Макс. — Но выполнение устроено как конвейер: родитель запрашивает строки у дочерних узлов. Одни узлы могут отдавать строки сразу, а блокирующие операции вроде Sort или построения Hash сначала накапливают вход.

Сначала достаём булочки и котлету (читаем таблицы)

Потом собираем (JOIN, сортировка)

В конце подаём (возвращаем результат)

Что значит cost=100.50..150.20

— Первое число — оценка стоимости до первой строки, второе — до получения всех строк этого узла. Стоимость родителя уже включает работу его поддерева.

Лена кивнула:

— То есть 150.20 уже включает дочернюю работу и собственную оценку Hash Join?

— Да, но не пытайся просто складывать все числа, напечатанные в плане: общая работа уже включена в родительские оценки, а узлы могут выполняться несколько раз. Для фактического плана смотри ещё на loops.

Лена нахмурилась:

— А почему иногда cost=0.00..50.00?

— А, это особенность расчётов! — махнул рукой Макс. — Postgres не учитывает подготовку к Seq Scan как затраты.

Но на самом деле:

• 0.00 — не значит «бесплатно», просто нет дополнительных расходов как у индексов

• 50.00 — полная цена чтения всей таблицы

Четыре важных типа узлов

Макс нарисовал на доске:

Seq Scan (Трактор) 🚜

— Читает таблицу от начала до конца, как трактор пашет поле.

— Когда встречается: Нет подходящего индекса / читается вся таблица.

Index Scan (Спорткар) 🏎

— Быстро находит данные по индексу, но если строк много — делает много «поездок» туда-сюда.

— Когда встречается: WHERE по индексированному полю.

Sort (Конвейерная лента) 🔀

— Сортирует данные перед выдачей (ORDER BY) или для соединения.

— Когда встречается: ORDER BY без индекса / GROUP BY / DISTINCT.

Hash Join (Фабрика) 🏭

— Сначала строит хеш-таблицу из одной таблицы, потом ищет совпадения.

— Когда встречается: JOIN больших таблиц по равенству (=).

Практика: разбираем план

Макс открыл заготовленный пример:

Limit  (cost=150.21..150.21 rows=1)
  ->  Sort  (cost=150.20..155.20 rows=2000)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (cost=0.00..50.00 rows=2000)

— Видишь? — ткнул он в Sort. — Без индекса по created_at Postgres:

Сначала трактором (Seq Scan) читает все заказы,

Потом конвейером (Sort) сортирует,

И только потом обрезает (Limit).

— Подходящий индекс по created_at DESC может дать строки в нужном порядке и убрать отдельный Sort, — Макс хитро улыбнулся. — Но планировщик выберет его только если полная стоимость такого пути окажется ниже.

Что запомнить

• План изображён как дерево: начинайте чтение с глубоких узлов, но учитывайте конвейерное выполнение

• cost=старт..конец — оценка работы до первой и до всех строк узла

• Seq Scan и Index Scan — альтернативные пути, а не «плохой» и «хороший» узел сами по себе

Лена потянулась за ноутбуком:

— Окей, я попробую еще «почитать» планы. Если у меня будут еще вопросы?

— Присылай скриншоты, — Макс вручил ей шоколадку. — Разберём вместе. А завтра я покажу, как заставить Postgres использовать индекс!

Практическое задание

Сделайте EXPLAIN для любого запроса с JOIN

Попробуйте «прочитать» дерево плана снизу вверх

В EXPLAIN ANALYZE сопоставьте actual time, rows и loops. Не ищите виновника только по самому большому cost: стоимость родителя включает дочерние узлы.