EXPLAIN ANALYZE в PostgreSQL: как читать план запроса аналитику

Коротко

Если нужен один совет: запускайте explain (analyze, buffers) и сравнивайте в каждом узле оценку rows с фактической.

• explain только строит план и показывает оценки. explain analyze выполняет запрос по-настоящему и добавляет фактическое время и число строк.
• Для update, delete и insert explain analyze меняет данные. Оборачивайте его в begin и rollback.
• План — дерево. Данные текут от самых глубоких узлов к верхнему, поэтому читать удобнее снизу вверх.
• cost — условные единицы для сравнения вариантов, а не миллисекунды. Время показывает только actual time, и оно указано на один проход узла: умножайте на loops.
• Когда оценка строк расходится с фактом в разы, планировщик выбирает план вслепую. Первое, что стоит сделать, — analyze имя_таблицы.
• Функция над колонкой в фильтре отключает обычный индекс: на миллионе событий date(event_time) = ... читает 8 974 страницы за 22 мс, а диапазон по event_time — 17 страниц за 3 мс.

На каких данных сняты планы

Учебная база курса небольшая: 35 341 событие. На такой таблице любой запрос выполняется за миллисекунды, и планы выглядят скучно. Поэтому таблицы учебной базы размножены в 30 раз со сдвигом идентификаторов: 138 390 пользователей, 1 060 230 событий, 37 530 оплат. Распределения внутри остались теми же.

Сервер — PostgreSQL 16.14 в Docker с настройками по умолчанию: work_mem 4 МБ, random_page_cost 4. JIT выключен, чтобы не удлинять вывод. Индексы: первичные ключи, events(event_time), events(user_id) и payments(user_id). Длинные планы в статье сокращены: убраны строки, не нужные для разбора, порядок и отступы сохранены.

В песочнице SQL-курса explain выполнить не получится: она работает на DuckDB и принимает только select и with. У DuckDB есть свой explain analyze, но он рисует дерево сверху вниз в рамках, с операторами HASH_JOIN, HASH_GROUP_BY и TABLE_SCAN. Логика та же, формат другой. Дальше речь только о PostgreSQL.

Чем EXPLAIN отличается от EXPLAIN ANALYZE

explain показывает, как планировщик собирается выполнять запрос, и его оценки: стоимость и ожидаемое число строк. Запрос не выполняется, поэтому explain безопасен даже для тяжёлого запроса на проде.

explain analyze выполняет запрос целиком, выбрасывает результат и печатает план с фактическими числами. Если запрос считается десять минут, explain analyze тоже займёт десять минут. Для изменяющих команд это означает настоящее изменение: explain analyze delete from events where event_time < '2026-06-02' удалил 2 100 строк. Внутри транзакции с rollback строки вернулись — проверка после отката снова находит 2 100.

Опции пишут в скобках: explain (analyze, buffers, settings). buffers добавляет чтение страниц, settings — параметры сервера, отличные от стандартных, format json — машиночитаемый вывод для визуализаторов планов. timing off убирает замеры времени по узлам: на запросах с миллионами мелких операций сами замеры заметно замедляют выполнение.

begin;

explain analyze
delete from events
where event_time < '2026-06-02';

rollback;

-- Delete on events  (actual time=78.647..78.648 rows=0 loops=1)
--   ->  Bitmap Heap Scan on events  (rows=2011) (actual ... rows=2100 loops=1)
-- Execution Time: 85.329 ms

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

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

Ниже план подсчёта событий за 15 июля, снятый до создания индексов и до сбора статистики. Снизу вверх: Parallel Seq Scan читает таблицу целиком и отбрасывает строки фильтром, Partial Aggregate считает частичный count(*) в каждом процессе, Gather собирает три частичных результата, Finalize Aggregate складывает их в один.

Под узлом идут подробности: Filter — условие, которое проверялось на каждой прочитанной строке, Rows Removed by Filter — сколько строк оно отбросило. 348 650 отброшенных строк на процесс ради 4 760 нужных — первый признак, что не хватает индекса.

Finalize Aggregate  (cost=15699.91..15699.92 rows=1 width=8) (actual time=288.354..291.479 rows=1 loops=1)
  ->  Gather  (cost=15699.69..15699.90 rows=2 width=8) (actual time=288.188..291.464 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=14699.69..14699.70 rows=1 width=8) (actual time=278.079..278.080 rows=1 loops=3)
              ->  Parallel Seq Scan on events  (cost=0.00..14694.92 rows=1907 width=0) (actual time=0.332..277.589 rows=4760 loops=3)
                    Filter: ((event_time >= '2026-07-15 00:00:00'::timestamp without time zone) AND (event_time < '2026-07-16 00:00:00'::timestamp without time zone))
                    Rows Removed by Filter: 348650
Planning Time: 0.389 ms
Execution Time: 291.557 ms

Что значат cost, rows, actual time и loops

В первых скобках — оценка планировщика. cost=0.00..14694.92 — стоимость до первой строки и стоимость до последней. Единицы условные: чтение одной страницы подряд стоит 1 (seq_page_cost), чтение страницы вразброс — 4 (random_page_cost), обработка одной строки — 0,01 (cpu_tuple_cost). Стоимость нужна, чтобы сравнивать варианты одного запроса, а не чтобы предсказывать секунды. rows — ожидаемое число строк на выходе узла, width — средний размер строки в байтах.

Во вторых скобках — факт, он появляется только с analyze. actual time=0.332..277.589 — миллисекунды до первой и до последней строки, rows — сколько строк узел реально отдал. Оба числа указаны на один проход узла. loops=3 означает, что узел выполнился трижды — здесь в двух фоновых процессах и в основном. Всего Parallel Seq Scan нашёл 4 760 × 3 = 14 280 событий.

Про loops забывают чаще всего. В Nested Loop внутренний узел может выполниться тысячи раз, и скромные actual time=0.05 при loops=40000 дают две секунды.

Итоговая строка Execution Time включает всё выполнение. Одно число ничего не доказывает: этот же запрос в первый раз занял 291 мс, во второй — 138 мс, дальше около 25 мс, когда страницы таблицы уже лежали в памяти. Сравнивайте варианты на прогретом кэше и по нескольку раз.

Почему оценка строк расходится с фактом

Планировщик не знает, сколько строк вернёт условие, пока не выполнит его. Он угадывает по статистике: гистограммам значений, списку самых частых значений, числу уникальных. Статистику собирает команда analyze, обычно её запускает autovacuum после заметных изменений таблицы.

Таблицу событий здесь специально создали без autovacuum, и в плане выше видно, чем это кончается. Для фильтра по дате планировщик ждал 1 907 строк на процесс, для фильтра event_name = 'export_completed' — тоже 1 907, хотя фактически было 4 760 и 9 880. Без статистики он подставляет одну и ту же догадку для любых условий. После analyze events оценки стали 5 146 и 11 692 — в пределах 20% от факта.

На запросе с соединениями ошибка оценки решает всё: планировщик, который ждёт 10 строк вместо 100 000, выберет вложенный цикл и выполнит внутренний узел сто тысяч раз. В незнакомом медленном плане ищите узел, где оценка и факт rows отличаются на порядок, — проблема обычно в нём или под ним.

Типичные причины у аналитика: таблицу только что залили и не проанализировали; временная таблица, которую autovacuum не видит; условие по выражению над колонкой, для которого нет статистики; связанные условия, которые планировщик считает независимыми.

Seq Scan, Index Scan, Index Only Scan и Bitmap: что означает каждый узел

Seq Scan читает всю таблицу страницу за страницей. Это не приговор: если запрос забирает заметную долю строк, последовательное чтение дешевле прыжков по индексу. count(*) по всей таблице из миллиона событий PostgreSQL выполнил параллельным Seq Scan за 50 мс, и так и должно быть.

Index Scan идёт по индексу и за каждой найденной записью ходит в таблицу. Он выгоден, когда строк мало или нужен порядок индекса: order by event_time desc limit 10 выполнился через Index Scan Backward за 1 мс — прочитал десять записей с конца индекса и остановился.

Index Only Scan отвечает из одного индекса, не заглядывая в таблицу, если в индексе есть все нужные колонки. Подсчёт событий за день по индексу на event_time так и выполнился: Heap Fetches: 0.

Bitmap Index Scan + Bitmap Heap Scan — компромисс для средних выборок. Сначала по индексу строится карта страниц, где есть подходящие строки, потом эти страницы читаются по порядку. Строка Recheck Cond под ним нормальна. События за час, 1 050 строк, PostgreSQL прочитал именно так: 997 страниц, 1,7 мс.

Bitmap Heap Scan on events  (cost=22.57..3717.62 rows=1380 width=30) (actual time=0.593..1.589 rows=1050 loops=1)
  Recheck Cond: ((event_time >= '2026-07-15 10:00:00'::timestamp without time zone) AND (event_time < '2026-07-15 11:00:00'::timestamp without time zone))
  Heap Blocks: exact=997
  ->  Bitmap Index Scan on events_event_time_idx  (cost=0.00..22.23 rows=1380 width=0) (actual time=0.505..0.505 rows=1050 loops=1)
        Index Cond: (...)
Execution Time: 1.652 ms

Почему date(event_time) = … не использует индекс

Вернёмся к отчёту из начала. Индекс на event_time хранит значения колонки, отсортированные по порядку. Условие event_time >= '2026-07-15' and event_time < '2026-07-16' — это отрезок в этом порядке, и планировщик спускается к его началу. Условие date(event_time) = '2026-07-15' сравнивает результат функции, которого в индексе нет. Остаётся посчитать функцию для каждой строки таблицы.

В плане разница видна в первом же узле. Диапазон — Index Only Scan, 17 страниц, 3,1 мс. Функция — Parallel Seq Scan с Filter: (date(event_time) = ...), 8 974 страницы, то есть вся таблица, 22,2 мс. Приведение event_time::date ведёт себя так же. Оценка строк тоже портится: для выражения без статистики планировщик ждал около 5 300 строк — это стандартная доля 0,5% от таблицы — при факте 14 280.

Исправление — переписать фильтр полуоткрытым интервалом и приводить к дате только в select и group by. Почему здесь не подходит between, разобрано в статье про BETWEEN. Альтернатива — индекс по выражению create index on events ((event_time::date)), но его имеет смысл просить, только если такой фильтр стоит в десятках отчётов.

-- where event_time >= '2026-07-15' and event_time < '2026-07-16'
Aggregate  (actual time=2.612..2.612 rows=1 loops=1)
  Buffers: shared hit=1 read=16
  ->  Index Only Scan using events_event_time_idx on events  (rows=18382) (actual ... rows=14280 loops=1)
        Heap Fetches: 0
Execution Time: 3.134 ms

-- where date(event_time) = '2026-07-15'
Finalize Aggregate  (actual time=20.233..22.192 rows=1 loops=1)
  Buffers: shared hit=8974
  ...
        ->  Parallel Seq Scan on events  (rows=2209) (actual ... rows=4760 loops=3)
              Filter: (date(event_time) = '2026-07-15'::date)
              Rows Removed by Filter: 348650
Execution Time: 22.235 ms

Nested Loop, Hash Join и Merge Join: когда появляется каждый

Nested Loop для каждой строки внешнего узла выполняет внутренний. Он хорош, когда снаружи мало строк, а внутри есть индекс по ключу соединения. Три пользователя и их события — классический случай: внешний Index Scan по первичному ключу вернул 3 строки, внутренний поиск по индексу events(user_id) выполнился loops=3 раза, всё заняло 1,2 мс. Тот же узел на 100 000 внешних строк — главный источник медленных запросов из-за ошибки в оценке.

Hash Join строит хеш-таблицу по меньшему входу (узел Hash) и прогоняет через неё больший. Это выбор по умолчанию для соединения больших наборов без полезного порядка. Выручка по каналам — 37 530 оплат и 138 390 пользователей — так и посчиталась: хеш по оплатам занял 2 МБ, Batches: 1 означает, что он поместился в память.

Merge Join сливает два входа, уже отсортированных по ключу, например идущих из индексов. В нашем запросе планировщик оценил его дороже: 6 888 против 4 641 у хеша. Когда хеш-соединение запретили через set enable_hashjoin = off, Merge Join по двум индексам отработал за 44–49 мс, а Hash Join в разных прогонах занимал от 31 до 58 мс. Стоимость — модель, время — факт, и между ними всегда есть зазор. Флаги enable_* годятся для такого эксперимента, но не для рабочих запросов.

Nested Loop  (cost=4.79..136.47 rows=23 width=27) (actual time=0.178..1.125 rows=36 loops=1)
  ->  Index Scan using users_pkey on users u  (rows=3) (actual ... rows=3 loops=1)
        Index Cond: (user_id = ANY ('{42,43,44}'::integer[]))
  ->  Bitmap Heap Scan on events e  (rows=9) (actual ... rows=12 loops=3)
        Recheck Cond: (u.user_id = user_id)
        ->  Bitmap Index Scan on events_user_id_idx  (actual ... rows=12 loops=3)
Execution Time: 1.208 ms

Где в плане видно, что JOIN размножил строки

Ошибка, из-за которой аналитика чаще всего переспрашивают, — выручка, умноженная соединением. Запрос соединяет пользователей с оплатами и с событиями, чтобы заодно отфильтровать активных, и суммирует amount. На исходной учебной базе сумма оплат — 30 639, а после такого JOIN — 284 913, в 9,3 раза больше: каждая оплата повторилась столько раз, сколько событий у её владельца.

В плане размножение видно без знания данных. Внутренний Hash Join пользователей и оплат отдаёт 37 530 строк — по числу оплат. Следующий Hash Join с событиями отдаёт rows=116670 loops=3, то есть 350 010 строк. Если после соединения по ключу строк стало больше, чем в самой детальной таблице, значит ключ не уникален хотя бы с одной стороны. Суммы после такого узла считать нельзя: сначала агрегируйте события до одной строки на пользователя, потом соединяйте.

select u.channel, sum(p.amount) as revenue
from users as u
join payments as p on p.user_id = u.user_id
join events   as e on e.user_id = u.user_id   -- много строк на пользователя
group by u.channel;

--  Hash Join  (rows=146468) (actual ... rows=116670 loops=3)   <- 350 010 строк
--    Hash Cond: (e.user_id = p.user_id)
--    ->  Parallel Seq Scan on events e  (actual ... rows=353410 loops=3)
--    ->  Hash  (actual ... rows=37530 loops=3)
--          ->  Hash Join  (actual ... rows=37530 loops=3)      <- по числу оплат

Что добавляет BUFFERS

Время зависит от кэша, загрузки сервера и соседних запросов. Число прочитанных страниц от этого почти не зависит, поэтому buffers — самая устойчивая мера работы. shared hit — страницы, найденные в памяти PostgreSQL, shared read — прочитанные с диска или из кэша ОС, temp read и temp written — временные файлы, когда операции не хватило памяти. Страница — 8 КБ.

На фильтре по дате разница — 17 страниц против 8 974. На расчёте пауз между событиями через lag по всей таблице план пошёл по индексу events(user_id) и сделал больше миллиона обращений к страницам на 1 060 230 строк (shared hit=1059707 read=1298) — почти по одному на строку, потому что события одного пользователя разбросаны по таблице. Время в таких случаях прыгает от прогона к прогону, а число буферов — нет.

В PostgreSQL 16 buffers нужно просить явно. С версии 18 он включён в explain analyze по умолчанию.

Когда сортировка уходит на диск

Сортировка, хеш-таблица и группировка получают память в пределах work_mem на каждую операцию. Если данных больше, PostgreSQL пишет промежуточные части во временные файлы. В плане это видно по строке Sort Method.

Расчёт пауз между событиями за июль через lag сортирует 382 560 строк по пользователю и времени. При стандартных 4 МБ план показывает external merge Disk: 9752kB и temp written=1225. После set work_mem = '64MB' в той же сессии — quicksort Memory: 27232kB и ни одного временного файла.

Время в этом прогоне почти не изменилось: 278 мс с диском и 292 мс без него, потому что временные файлы остались в кэше операционной системы. На нагруженном сервере с медленным диском разница будет заметнее. Строка Disk: — повод посмотреть на запрос: можно ли сортировать меньше строк, отфильтровав раньше, или поднять work_mem для своей сессии. Поднимать его глобально без администратора не стоит: лимит действует на каждую операцию каждого запроса.

-- work_mem = 4MB
->  Sort  (actual time=121.967..157.754 rows=382560 loops=1)
      Sort Key: events.user_id, events.event_time
      Sort Method: external merge  Disk: 9752kB
      Buffers: shared hit=9316, temp read=1219 written=1225

-- work_mem = 64MB
->  Sort  (actual time=147.276..177.002 rows=382560 loops=1)
      Sort Key: events.user_id, events.event_time
      Sort Method: quicksort  Memory: 27232kB
      Buffers: shared hit=9316

Во что обходится count(distinct)

Уникальных пользователей считают постоянно, и про count(distinct) ходит совет: переписать через подзапрос с select distinct, так быстрее. На наших данных вышло наоборот. count(distinct user_id) по миллиону событий прочитал индекс events(user_id) через Index Only Scan — значения там уже отсортированы — и уложился в 102–217 мс за несколько прогонов.

select count(*) from (select distinct user_id from events) выбрал HashAggregate. Планировщик ждал 113 194 уникальных пользователя, а их 138 390, хеш-таблица не поместилась в память (Batches: 5 Disk Usage: 7048kB), и запрос занял 451–470 мс. Совет зависит от индексов, версии и данных, поэтому проверяйте его на своём плане.

С чего начинать разбор медленного запроса

Когда отчёт тормозит, а доступа к настройкам сервера нет, этот порядок закрывает большинство случаев.

• Снимите explain (analyze, buffers) на прогретом кэше. Для изменяющих команд — внутри begin и rollback.
• Найдите узел с наибольшим собственным временем: actual time узла минус время детей, умноженное на loops.
• Сравните оценку rows с фактом по всему дереву. Расхождение на порядок — повод запустить analyze или переписать условие.
• Ищите Seq Scan с большим Rows Removed by Filter и проверьте, не обёрнута ли колонка фильтра в функцию или приведение типа.
• Смотрите, растёт ли число строк после JOIN сильнее, чем позволяет самая детальная таблица.
• Проверьте строки Disk: и temp written: сортировку или хеш можно уменьшить ранним фильтром.
• Меняйте одно условие за раз и снимайте план заново. Время сравнивайте только между прогонами на одном сервере.