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

EXPLAIN показывает план, который планировщик собирается выполнить, а EXPLAIN ANALYZE действительно выполняет запрос и показывает, сколько на самом деле заняло каждое действие.

План запроса — дерево операций (узлов), по которому PostgreSQL получит результат: какие таблицы как читаются, в каком порядке соединяются, где фильтруются и сортируются. EXPLAIN печатает этот план, не выполняя запрос; EXPLAIN ANALYZE — выполняет и добавляет реальные замеры.

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

Зачем это на практике

EXPLAIN — главный инструмент диагностики медленных запросов. Он отвечает на ключевые вопросы: используется ли индекс или идёт полное сканирование; сколько строк планировщик ожидал и сколько получил на самом деле (большое расхождение — признак устаревшей статистики); на какой узел уходит основное время; сколько данных читается с диска. Любая серьёзная работа над производительностью начинается с чтения плана и им же заканчивается — чтобы убедиться, что стало лучше.

Seq Scan против Index Scan

Два базовых узла чтения таблицы. Seq Scan (sequential scan, последовательное сканирование) читает таблицу целиком, строку за строкой, и проверяет условие для каждой. Index Scan сначала находит подходящие строки через индекс, потом читает только их.

-- без подходящего индекса: читаем всю таблицу
EXPLAIN SELECT * FROM users WHERE age = 30;

                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on users  (cost=0.00..1693.00 rows=210 width=72)
   Filter: (age = 30)

Здесь Seq Scan означает: PostgreSQL пройдёт все строки и оставит те, у кого age = 30. Добавим индекс — и план меняется.

CREATE INDEX idx_users_age ON users (age);
EXPLAIN SELECT * FROM users WHERE age = 30;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_users_age on users  (cost=0.29..8.31 rows=210 width=72)
   Index Cond: (age = 30)

Теперь Index Scan using idx_users_age: СУБД спускается по индексу прямо к нужным строкам. Обратите внимание на Index Cond — это условие, которое отрабатывает сам индекс (в отличие от Filter, который проверяется уже после чтения строки). И заметьте, как упала верхняя оценка стоимости: с 1693 до 8.31.

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

Что значат cost, rows и width

В скобках у каждого узла стоят три оценки, которые планировщик вычислил заранее, ещё не выполняя запрос.

Поле	Что означает
`cost=0.29..8.31`	оценка «стоимости» в условных единицах: первое число — затраты до выдачи первой строки, второе — до выдачи последней. Чем больше — тем дороже узел.
`rows=210`	сколько строк планировщик ожидает получить от этого узла.
`width=72`	средний размер строки в байтах — нужен для оценки объёма данных.

«Стоимость» — безразмерная величина, в которую сведены чтения страниц с диска и обработка строк процессором. Сравнивать её осмысленно только между планами одного и того же запроса: план с меньшим итоговым cost планировщик и выберет. Абсолютное число «дорого/дёшево» само по себе ни о чём не говорит — важно соотношение.

EXPLAIN ANALYZE: план плюс реальность

EXPLAIN показывает только предсказание. EXPLAIN ANALYZE по-настоящему выполняет запрос и дописывает к каждому узлу фактические замеры: actual time (реальное время) и actual rows (реально полученные строки).

EXPLAIN ANALYZE SELECT * FROM users WHERE age = 30;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_users_age on users
     (cost=0.29..8.31 rows=210 width=72)
     (actual time=0.025..0.140 rows=198 loops=1)
   Index Cond: (age = 30)
 Planning Time: 0.110 ms
 Execution Time: 0.190 ms

Читаем: actual time=0.025..0.140 — реальное время до первой и до последней строки в миллисекундах; rows=198 — фактически найдено 198 строк (планировщик предполагал 210 — оценка близка, это хорошо); loops=1 — узел выполнялся один раз. Внизу — итоговое Execution Time.

Главный диагностический приём: сравните оценочные rows и фактические actual rows. Если расхождение в разы или на порядки (план думал «10 строк», а пришло «100000») — у планировщика устаревшая или неполная статистика, и из-за этого он мог выбрать плохой план. Лечится командой ANALYZE (о ней — в уроке про неработающие индексы).

Важное предупреждение: EXPLAIN ANALYZE реально выполняет запрос. Для SELECT это безопасно, но для UPDATE/DELETE/INSERT данные действительно изменятся — оборачивайте такие проверки в транзакцию с ROLLBACK.

BUFFERS: сколько читали с диска

Время в миллисекундах зависит от того, лежат ли данные в оперативной памяти. Более стабильная метрика — сколько страниц (блоков по 8 КБ) запрос прочитал. Её показывает опция BUFFERS.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE age = 30;

 Index Scan using idx_users_age on users  (actual time=0.03..0.15 rows=198 loops=1)
   Index Cond: (age = 30)
   Buffers: shared hit=5 read=2
 Execution Time: 0.21 ms

shared hit=5 — пять страниц нашлись в кеше (быстро), read=2 — две пришлось читать с диска (медленно). Высокое число read у тяжёлого запроса прямо указывает: он перелопачивает много данных. Сравнивая Buffers до и после добавления индекса, вы видите ускорение в страницах, а не в зависящих от кеша миллисекундах — это надёжнее.

Bitmap-сканирование: третий путь

Иногда в плане встречается пара Bitmap Index Scan + Bitmap Heap Scan. Это компромисс между Seq Scan и Index Scan: индекс собирает битовую карту подходящих страниц, а потом таблица читается этими страницами по порядку. PostgreSQL выбирает такой план, когда строк под условие много для обычного Index Scan, но мало для полного Seq Scan, — частый случай для условий с OR и для умеренно селективных фильтров.

Как это работает под капотом

За планом стоит стоимостный оптимизатор. Для одного и того же запроса существует множество способов выполнения: какие индексы задействовать, в каком порядке соединять таблицы, каким алгоритмом (Nested Loop, Hash Join, Merge Join). Планировщик перебирает варианты, для каждого оценивает cost по формуле, опирающейся на статистику (сколько в таблице строк, сколько различных значений в столбце, как они распределены), и выбирает самый дешёвый. Статистику собирает команда ANALYZE и хранит в системном каталоге. Поэтому два слагаемых хорошего плана — это адекватные индексы и свежая статистика: ошибётся в оценке числа строк — выберет неоптимальный план, даже когда идеальный индекс есть.

Частые ошибки

Доверять только EXPLAIN без ANALYZE. Голый EXPLAIN показывает оценки планировщика; реальность видна лишь в EXPLAIN ANALYZE. Расхождение оценок и факта — главный сигнал проблемы.
Считать любой Seq Scan ошибкой. На маленьких таблицах и на запросах, возвращающих большую долю строк, последовательное сканирование быстрее индексного — и планировщик прав.
Гнаться за абсолютным значением cost. Это условные единицы; сравнивать их имеет смысл только между планами одного запроса, а не с «нормой».
Запускать EXPLAIN ANALYZE на изменяющих запросах вне транзакции. UPDATE/DELETE при этом выполнятся по-настоящему — оборачивайте в BEGIN ... ROLLBACK.

Итоги

EXPLAIN печатает план (оценки), EXPLAIN ANALYZE выполняет запрос и добавляет реальные actual time и actual rows.
Seq Scan читает всю таблицу; Index Scan идёт через индекс. Seq Scan нормален на больших выборках и маленьких таблицах.
cost — условная стоимость (первая строка..последняя), rows — ожидаемое число строк, width — байты на строку.
Сравнивайте оценочные rows с фактическими: большое расхождение означает устаревшую статистику — нужен ANALYZE.
Опция BUFFERS показывает прочитанные страницы (hit из кеша, read с диска) — метрика стабильнее миллисекунд.