Как читать план
План — это дерево операций; корень возвращает результат, а каждый узел забирает строки у своих потомков. EXPLAIN сам по себе показывает оценки планировщика (единицы стоимости и ожидаемые строки); EXPLAIN ANALYZE реально выполняет запрос и добавляет настоящее время и число строк — именно это делает план читаемым. BUFFERS добавляет, сколько страниц по 8 КБ пришло из кеша (hit) или с диска (read). Обычный путь по плану: найти узел с наибольшим исключительным временем, затем понять, почему он там: неверная оценка строк выше по дереву, отсутствующий индекс или настройка памяти.
Числа на узле
| Поле | Значение |
|---|---|
| cost=0.29..8.31 | Оценка планировщика в условных единицах: стоимость получения первой строки .. стоимость получения всех строк. Сопоставимо только внутри одного плана. |
| rows= | Оценочное число строк на один цикл. Фактическое значение тоже на цикл; умножьте на loops для итога. |
| actual time=0.05..12.3 | Миллисекунды до первой строки .. до последней строки, на цикл, включая потомков. Инструмент вычитает потомков, чтобы показать собственное время каждого узла. |
| loops= | Сколько раз выполнялся узел. Внутренняя сторона nested loop выполняется по разу на каждую внешнюю строку; под Gather это число равно количеству воркеров. |
| Buffers: shared hit=.. read=.. | Страницы, найденные в shared_buffers, против прочитанных из ОС/с диска. Чтения — это то, куда уходит время I/O; track_io_timing показывает его как I/O Timings. |
| Rows Removed by Filter | Строки, которые узел прочитал и затем отбросил: самый явный признак отсутствующего индекса. |
Пять типичных проблем
- Неверные оценки. Если узел ожидает 10 строк, а получает 100 000, планировщик выбрал nested loop или сортировку, имевшие смысл только для 10. Выполните
ANALYZE; для предикатов по коррелированным столбцам создайте расширенную статистику; для CTE и функций планировщик ничего не знает и использует значения по умолчанию. - Последовательное сканирование с селективным фильтром. Нормально на маленьких таблицах. На больших решение — индекс по фильтруемым столбцам, в идеале соответствующий WHERE и ORDER BY.
- Сбросы на диск. Sort external merge, Hash Batches > 1, HashAggregate на диске, lossy-битмапы: операция не уместилась в
work_mem. Повысить его для сессии или запроса дёшево; повышение глобально умножается на число соединений. - Nested loop над сканированием. Внутренняя сторона выполняется по разу на каждую внешнюю строку; если это Seq Scan или Materialize большого набора, время взрывается. Внутренней стороне нужен индекс, либо планировщику нужно разрешить hash join.
- Index-only scan, которые таковыми не являются. Heap Fetches, близкие к числу строк, означают, что карта видимости устарела: выполните VACUUM таблицы.
Как получить план
-- PostgreSQL: самая полезная форма
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT JSON) SELECT …;
-- для записи — внутри транзакции, которую вы откатите
BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE …; ROLLBACK;
-- автоматически логировать медленные планы: auto_explain
LOAD 'auto_explain'; SET auto_explain.log_min_duration = '200ms'; SET auto_explain.log_analyze = on;
-- MySQL 8.0.18+
EXPLAIN ANALYZE SELECT …; -- дерево с фактическим временем
EXPLAIN FORMAT=JSON SELECT …; -- оценки с деталями стоимостиВопросы
Почему время узлов не складывается в общее?
Фактическое время каждого узла включает его потомков, поэтому инструмент показывает исключительное время (узел минус потомки). Параллельные воркеры работают одновременно, поэтому узел под Gather с loops=3 считается один раз, а не три; внутренние стороны nested loop умножаются на loops. Время планирования и время триггеров указаны отдельно.
Какой план считается хорошим?
Тот, в котором оценки отличаются от фактических значений не более чем в десять раз, ни один узел не сбрасывается на диск, самый дорогой узел — тот, которого вы ожидаете (большая таблица, которую действительно нужно прочитать), а буферов, прочитанных с диска, мало по сравнению с попаданиями в кеш. Для такого плана список замечаний пуст.
Поддерживаются ли секционированные таблицы и параллельные планы?
Да: Append / Merge Append по секциям, Gather и Gather Merge с Workers Planned/Launched, агрегаты Partial и Finalize. Разбивка по воркерам (VERBOSE) разбирается, но показывается как часть узла.
Можно ли вставить план из pgAdmin, DataGrip или psql с рамками таблицы?
Да. Строки очищаются от символов рамки psql, заголовка QUERY PLAN и подвала «(n rows)». JSON, скопированный из GUI как одна строка в кавычках, нужно сначала освободить от кавычек.
Насколько полная поддержка MySQL?
Дерево EXPLAIN ANALYZE в MySQL 8 имеет ту же форму, что и текстовый план PostgreSQL, и поддерживается полностью; EXPLAIN FORMAT=JSON (только оценки) отображается на то же дерево: типы доступа становятся видами сканирования, nested_loop — узлом соединения, а флаги filesort / временной таблицы — замечаниями. ANALYZE FORMAT=JSON из MariaDB пока не поддерживается.