Витрина статусов заказов в админке Brew открывалась мгновенно — пока таблица событий кассы была маленькой. К миллиону строк та же страница стала грузиться по две-три секунды, а под нагрузкой вечерних часов вис весь раздел. Бэкенд-разработчик смотрел на запрос — SELECT * FROM events WHERE ref_no = ? — и не понимал: запрос-то тривиальный, одна строка на выходе. Проблема была не в запросе, а в том, как база его выполняла: без индекса по ref_no ей приходилось читать и проверять каждую из миллиона строк, чтобы найти одну. Увидеть это можно было одной командой — EXPLAIN ANALYZE.

Цель юнита — научиться читать план запроса и видеть в нём ту самую разницу: «прочитали весь миллион» против «сходили точно в нужную строку». Это первый юнит модуля про индексы, и он про инструмент, которым мы будем пользоваться до конца модуля. Конкретику индексов (составные, частичные, GIN) разберём дальше; здесь — алфавит: какой узел плана что значит, где смотреть число обработанных строк и почему в PG18 под планом сразу видны буферы.

EXPLAIN, EXPLAIN ANALYZE и узлы плана

EXPLAIN <запрос> показывает план — что планировщик собирается делать, с оценками стоимости и числа строк, но запрос не выполняет. EXPLAIN ANALYZE <запрос> запрос реально выполняет и дописывает к каждому узлу фактические числа: сколько строк прошло, сколько раз узел отработал, сколько времени занял. Оценки врут, факты — нет, поэтому для разбора «почему медленно» всегда берут ANALYZE.

⚠️ EXPLAIN ANALYZE выполняет запрос по-настоящему. С SELECT это безопасно, но EXPLAIN ANALYZE DELETE ... реально удалит строки. Чтобы посмотреть план меняющего запроса без последствий — оберни в транзакцию и откати: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;.

План — это дерево узлов, читается изнутри наружу и снизу вверх: нижние узлы отдают строки верхним. Нас сейчас интересуют два листовых узла-источника:

• Seq Scan (sequential scan) — последовательное чтение всей таблицы, строка за строкой, с проверкой условия. Если в плане под фильтром по одной строке стоит Seq Scan на большой таблице — это и есть «прочитали весь миллион».
• Index Scan — спуск по индексу прямо к нужным строкам. Под ним стоит Index Cond — условие, по которому индекс отобрал строки, не трогая остальные.

Строки, буферы и время

В строке узла EXPLAIN ANALYZE смотрим три вещи:

• actual rows — сколько строк узел реально вернул (в PG18 печатается с двумя знаками: rows=1.00). Рядом, у сканов, бывает Rows Removed by Filter — сколько строк узел прочитал и выкинул фильтром. Миллион выброшенных строк ради одной найденной — точная мера лишней работы.
• Buffers — сколько 8-КБ страниц узел тронул: shared hit — найдены в кэше, shared read — прочитаны с диска. В PostgreSQL 18 EXPLAIN ANALYZE печатает Buffers по умолчанию — раньше для этого нужен был явный EXPLAIN (ANALYZE, BUFFERS). Буферы — самая честная мера «сколько данных перелопатили»: она не зависит от того, насколько загружен процессор в момент замера.
• actual time — время на узел в миллисекундах (first_row..last_row), и итоговое Execution Time внизу.

Время и буферы зависят от железа и прогрева кэша, поэтому в ## Запуск ниже мы их намеренно глушим и показываем только форму плана и число строк — то, что воспроизводится дословно на любой машине. А вот как выглядит полный вывод EXPLAIN (ANALYZE) на нашей машине (у тебя числа будут другие):

 Seq Scan on events_lab  (cost=0.00..19853.00 rows=1 width=25) (actual time=15.189..19.830 rows=1.00 loops=1)
   Filter: (ref_no = 762312)
   Rows Removed by Filter: 999999
   Buffers: shared hit=7353
 Planning Time: 0.059 ms
 Execution Time: 19.839 ms

7353 страницы в кэше, миллион выброшенных строк, ~20 мс — против индексного варианта:

 Index Scan using events_lab_ref_no_idx on events_lab  (...) (actual time=0.035..0.036 rows=1.00 loops=1)
   Index Cond: (ref_no = 762312)
   Index Searches: 1
   Buffers: shared hit=4 read=3
 Execution Time: 0.044 ms

7 страниц вместо 7353, ноль выброшенных строк, ~0.04 мс. Это та же разница, что видела админка Brew. (Index Searches: 1 — тоже новинка PG18: сколько раз индекс пришлось «искать» заново.)

План — это дерево: читаем изнутри наружу

Узлы плана вложены друг в друга. Лист (скан таблицы) отдаёт строки родителю, тот — своему родителю, и так до корня. Поэтому план читают изнутри наружу, снизу вверх: сначала откуда взялись строки, потом что с ними сделали. Вот форма типичного плана с соединением — не наш запуск (у него дерево из одного узла), а структура, чтобы виден был порядок чтения:

Aggregate                          ← ③ свернул строки в группы
  ->  Hash Join                    ← ② соединил два источника
        ->  Seq Scan on events_lab ← ① прочитал всю таблицу       (лист)
        ->  Index Scan on shops    ← ① достал строки по индексу   (лист)
 
Читаем снизу вверх: ① листья-сканы → ② join → ③ агрегат.
Время и буферы родителя уже включают детей.

Наш демо-запрос — простейшее дерево из одного узла: Seq Scan (или после индекса — Index Scan). Навык тот же: найти листья, подняться к корню. Что значит каждое поле под узлом:

Что показывает наш код

Урок — в demo.sql. Он создаёт лабораторный стол events_lab на миллион строк (базовые таблицы Brew не трогаем), собирает статистику через ANALYZE и дважды объясняет один и тот же запрос — до и после CREATE INDEX:

-- 1) без индекса
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT * FROM events_lab WHERE ref_no = 762312;     -- → Seq Scan, Rows Removed by Filter: 999999
 
CREATE INDEX events_lab_ref_no_idx ON events_lab (ref_no);
ANALYZE events_lab;
 
-- 2) с индексом
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT * FROM events_lab WHERE ref_no = 762312;     -- → Index Scan, Index Cond

Опции (... TIMING OFF, BUFFERS OFF) срезают всё машинозависимое — остаётся форма плана и фактические строки. Параллелизм мы выключаем (max_parallel_workers_per_gather = 0), чтобы план читался в один столбец, а не дробился на Gather + воркеры (Postgres умеет параллелить большие сканы — но это тема не первого урока).

Запуск

docker compose up -d
make lecture L=06-indexing-and-explain/06-01-reading-explain-analyze-buffers

Вывод:

== 1) БЕЗ индекса: запрос идёт Seq Scan по всему миллиону строк ==
                    QUERY PLAN                     
---------------------------------------------------
 Seq Scan on events_lab (actual rows=1.00 loops=1)
   Filter: (ref_no = 762312)
   Rows Removed by Filter: 999999
 
 
== создаём индекс по ref_no и пересобираем статистику ==
 
== 2) С индексом: тот же запрос — Index Scan точно в одну строку ==
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Index Scan using events_lab_ref_no_idx on events_lab (actual rows=1.00 loops=1)
   Index Cond: (ref_no = 762312)
   Index Searches: 1

Без индекса — Seq Scan и Rows Removed by Filter: 999999: база прочитала миллион строк, чтобы вернуть одну. После CREATE INDEX тот же запрос идёт Index Scan'ом с Index Cond: (ref_no = 762312) — индекс отобрал строку напрямую, выкидывать ничего не пришлось. Время и буферы здесь срезаны ради воспроизводимости (полный вывод с ними — в разделе выше).

Заборчик

Что мы упростили:

• Идеальная селективность. Мы выключили параллелизм и показали запрос, который возвращает одну строку из миллиона — на нём индекс выигрывает всегда. В жизни селективность любая: запрос, отдающий половину таблицы, планировщик осознанно пустит Seq Scan'ом — читать пол-таблицы прыжками по индексу дороже, чем подряд. Это правильное решение, а не «индекс не сработал».
• Один замер, а не диагноз. Числа из ANALYZE — это один прогон на конкретной машине с конкретным состоянием кэша. «Холодный» (первый после старта) и «горячий» прогоны дают разные Buffers/time, поэтому в проде на план смотрят несколько раз и сравнивают форму, а не отдельные миллисекунды.
• EXPLAIN — про запрос, не про базу. Он отвечает «как выполнился ЭТОТ запрос», а не «здорова ли база в целом». Системные вьюхи (pg_stat_statements — кто суммарно съедает больше всего времени), автовакуум, раздувание таблиц, кэш-хит по всей базе — это приборная панель, которую держит твой DBA.

Граница курса: твоя задача — уметь объяснить свой запрос и увидеть лишнюю работу в плане; тюнинг сервера и мониторинг кластера за её пределами.

Что забрать с собой

• EXPLAIN показывает план (оценки), EXPLAIN ANALYZE — выполняет запрос и даёт факты; для разбора «почему медленно» всегда бери ANALYZE.
• План читается изнутри наружу; Seq Scan = «прочитали всю таблицу», Index Scan + Index Cond = «сходили точно к строкам».
• Главные числа узла: actual rows, Rows Removed by Filter (лишняя работа) и Buffers (тронутые страницы; в PG18 печатаются по умолчанию).
• Время и буферы машинозависимы — сравнивай форму плана и число строк, а не отдельные миллисекунды.
• EXPLAIN ANALYZE реально выполняет запрос: меняющие команды смотри в BEGIN; ... ROLLBACK;.

Дальше — 06-02 «B-tree и порядок столбцов в составном индексе»: почему индекс по (a, b) помогает запросу по a и по a AND b, но не всегда по одному b — и что в PG18 с этим меняет skip-scan.