Как получить план, который можно проверить

Представьте, что на собеседовании вам дали запрос истории заказов. Клиент ждёт десять строк. Сколько строк пришлось просмотреть базе, чтобы их найти?

Откройте одно соединение с учебной базой PostgreSQL и выполните команды по порядку. Временная таблица видна только в этом соединении. Остаток от деления распределяет 100 000 заказов поровну между 10 000 клиентами.

CREATE TEMP TABLE demo_orders AS
SELECT n AS order_id, n % 10000 AS customer_id
FROM generate_series(1, 100000) AS orders(n);

ANALYZE demo_orders;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT order_id FROM demo_orders WHERE customer_id = 42;

Отдельная команда ANALYZE demo_orders собирает статистику для планировщика. Параметр ANALYZE внутри EXPLAIN запускает сам запрос. С помощью TIMING OFF мы отключили замеры времени отдельных узлов и можем сосредоточиться на количестве строк.

Как прочитать Seq Scan, rows и Filter

В первом плане найдите три строки. Ниже сокращённый фрагмент, без стоимости и счётчиков буферов.

Seq Scan on demo_orders  …  (actual rows=10.00 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 99990

Seq Scan означает последовательное чтение таблицы. Узел запускался один раз (loops=1), передал выше десять заказов и отбросил 99 990. Теперь можно ответить на вопрос интервьюера: база просмотрела все 100 000 строк.

В полном выводе слово rows встречается дважды. Рядом с cost стоит прогноз числа строк на выходе, рядом с actual — результат выполнения. Даже точный прогноз в десять строк сам по себе ничего не говорит о количестве отброшенных заказов.

Что изменится после создания индекса

В том же соединении создайте индекс по полю из условия и повторите запрос.

CREATE INDEX demo_orders_customer_idx ON demo_orders (customer_id);

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT order_id FROM demo_orders WHERE customer_id = 42;

Ищите Index Cond: (customer_id = 42). Если видите Bitmap Index Scan под Bitmap Heap Scan, читайте снизу вверх. Индекс находит ссылки на подходящие строки, затем верхний узел читает страницы таблицы с этими заказами. Возможен и обычный Index Scan. План зависит от статистики и настроек.

Вот сокращённый второй план из нашего запуска на PostgreSQL 18:

Bitmap Heap Scan on demo_orders  …  (actual rows=10.00 loops=1)
  Heap Blocks: exact=10
  Buffers: local hit=10 read=2
  ->  Bitmap Index Scan on demo_orders_customer_idx
        Index Cond: (customer_id = 42)

Десять заказов оказались на десяти страницах таблицы. В первом плане сканирование дало 448 попаданий в локальные буферы. Во втором верхний узел показал десять попаданий и два прочитанных блока. Числа относятся к этому набору данных и запуску.

Сравните строки Buffers: local у верхних узлов обоих планов. Для временной таблицы PostgreSQL использует локальные буферы. Счётчик hit показывает обращения к блокам, уже находившимся в буферах. Верхний узел включает работу дочерних, поэтому складывать их показатели нельзя.

Как объяснить результат на собеседовании

«Условию отвечали десять заказов. Сначала PostgreSQL нашёл их полным просмотром и отбросил 99 990 строк. После создания индекса условие появилось в Index Cond. Запрос вернул те же заказы, но теперь база могла найти их по ключу клиента».

Проверьте границу этого объяснения сами. Замените равенство условием customer_id < 9000 и заранее посчитайте, сколько заказов подойдёт. Получится 90 000. Посмотрите, какой способ чтения выбрала ваша база: когда нужны почти все строки, последовательный проход может стоить дешевле обхода индекса с обращениями к таблице.

Другие задачи с проверяемым результатом собраны в подборке вопросов по SQL для собеседования.