EXPLAIN и EXPLAIN ANALYZE: читаем план

В прошлом уроке мы подбирали индексы и проверяли через EXPLAIN, что сервер их берёт. Но EXPLAIN показывает только намерения: как сервер собирается выполнить запрос и сколько это, по его мнению, будет стоить. Сегодня выполняем запрос по-настоящему и сверяем ожидания с фактом. А заодно учимся читать планы длиннее двух строк.

Навигатор и трек поездки

Навигатор перед поездкой строит маршрут: «через мост, 25 минут». Это прогноз по картам и средней скорости. А после поездки в приложении остаётся трек: где реально стоял в пробке, сколько ехал каждый участок. Прогноз и трек иногда сильно расходятся, и самое интересное как раз в расхождениях.

EXPLAIN это маршрут навигатора. EXPLAIN ANALYZE это трек: сервер выполняет запрос и к каждому шагу плана приписывает, сколько он на деле занял и сколько строк вернул.

Таблица продаж

Сгенерируем продажи магазина: сто тысяч строк, по книге из учебной базы в каждой.

CREATE TABLE sales AS
SELECT g AS id,
       (g % 9) + 1 AS book_id,
       (g % 4) + 1 AS customer_id,
       date '2026-01-01' + (g % 280) AS sold_on,
       (g % 3) + 1 AS qty
FROM generate_series(1, 100000) AS g;

ALTER TABLE sales ADD PRIMARY KEY (id);
ANALYZE sales;
ANALYZE books;

Первый трек

EXPLAIN ANALYZE SELECT * FROM sales WHERE sold_on = date '2026-03-01';

                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on sales  (cost=0.00..1890.00 rows=357 width=20) (actual time=0.026..13.535 rows=357.00 loops=1)
   Filter: (sold_on = '2026-03-01'::date)
   Rows Removed by Filter: 99643
   Buffers: shared hit=640
 Planning Time: 0.118 ms
 Execution Time: 14.171 ms
(6 rows)

Первые скобки знакомы по EXPLAIN, это прогноз:

• cost=0.00..1890.00: стоимость первой строки и всех строк в условных единицах.
• rows=357: сколько строк сервер ожидает.
• width=20: средний размер строки в байтах.

Вторые скобки это факт:

• actual time=0.026..13.535: в миллисекундах, когда шаг отдал первую строку и когда закончил.
• rows=357.00: сколько строк вернул на деле. Здесь прогноз попал точно.
• loops=1: сколько раз шаг выполнялся. Почему бывает больше одного, увидим ниже.

Ниже идут подробности. Rows Removed by Filter: 99643 честно говорит, что ради 357 строк сервер перебрал и выбросил почти сто тысяч. Это первый признак, что запросу нужен индекс. Planning Time это время на выбор плана, Execution Time время выполнения. У тебя миллисекунды будут другими и будут меняться от запуска к запуску: время зависит от загрузки машины. Сравнивай порядок величин, а не точные числа.

Буферы

Строка Buffers: shared hit=640 говорит, сколько страниц таблицы прочитано. PostgreSQL читает данные страницами по 8 КБ, 640 страниц это 5 МБ, вся таблица. Слово hit значит, что страницы нашлись в памяти сервера. На настоящем сервере при первом запуске рядом появится read=: страницы пришлось читать с диска. В песочнице база целиком живёт в памяти, поэтому там всегда hit.

С 18-й версии PostgreSQL показывает буферы в EXPLAIN ANALYZE сам. В старых версиях их просили явно: EXPLAIN (ANALYZE, BUFFERS). А в примерах ниже мы их, наоборот, выключим через BUFFERS OFF, чтобы планы были короче.

Осторожно: запрос выполняется

EXPLAIN ANALYZE не притворяется. DELETE под ним удалит строки. Поэтому изменяющие запросы проверяют внутри транзакции и откатывают:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS OFF) DELETE FROM sales WHERE customer_id = 4;
ROLLBACK;

План покажет, что удаление прошло по 25000 строкам, а ROLLBACK вернёт их на место. Как работает откат, рассказано в уроке про транзакции.

План как дерево

Соединим продажи с книгами:

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT b.title, s.qty FROM sales s JOIN books b ON b.id = s.book_id;

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1.20..2027.31 rows=100000 width=30) (actual time=0.286..103.199 rows=100000.00 loops=1)
   Hash Cond: (s.book_id = b.id)
   ->  Seq Scan on sales s  (cost=0.00..1640.00 rows=100000 width=8) (actual time=0.105..33.671 rows=100000.00 loops=1)
   ->  Hash  (cost=1.09..1.09 rows=9 width=30) (actual time=0.118..0.121 rows=9.00 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 5kB
         ->  Seq Scan on books b  (cost=0.00..1.09 rows=9 width=30) (actual time=0.028..0.057 rows=9.00 loops=1)
 Planning Time: 1.416 ms
 Execution Time: 125.630 ms
(8 rows)

План это дерево, нарисованное отступами. Каждая строка со стрелкой -> это узел, а его дети стоят ниже с бо́льшим отступом. Данные текут снизу вверх: листья читают таблицы, а каждый узел получает строки от детей и передаёт выше.

Читаем наш план снизу:

1. Seq Scan on books: прочитали девять книг.
2. Hash: сложили их в хеш-таблицу в памяти, 5 КБ.
3. Seq Scan on sales: прочитали сто тысяч продаж.
4. Hash Join: для каждой продажи нашли её книгу в хеш-таблице.

Время узла включает время его детей. Hash Join закончил на 103 мс, из них 34 мс ушло на чтение продаж. Поэтому самый дорогой узел ищут не по верхней строке, а по разнице между узлом и его детьми.