EXPLAIN PLAN: разбор плана выполнения запроса

Прямой ответ

План выполнения показывает операции запроса и их время, поэтому узкое место определяется по фактическому измерению, а не по предположению о медленной операции.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE user_id = 42;

Вариант ANALYZE выполняет запрос и подставляет фактическое время, поэтому длительность каждой операции измеряется, а не оценивается. Об оптимизации запроса — в статье про оптимизацию запросов БД.

Варианты плана и фактическое время

Вариант EXPLAIN определяет, показывает ли план оценку либо факт, поэтому вывод читается с учётом варианта.

Оценка без выполнения. План без ANALYZE показывает оценку планировщика, поэтому фактическое время в выводе отсутствует.

Фактическое время. План с ANALYZE выполняет запрос и подставляет фактическую длительность, поэтому операция с основной долей времени видна явно.

Число прочитанных блоков. Вывод с BUFFERS показывает объём чтения из буфера и с диска, поэтому источник времени определяется по объёму чтения.

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

Отдельный аспект — выполнение запроса при ANALYZE. Вариант ANALYZE выполняет запрос, поэтому для запроса с изменением данных требуется откат, иначе изменение применяется.

Операции чтения и соединения

Операция чтения определяет, как строки выбираются из таблицы, поэтому доля времени зависит от выбранной операции.

Последовательное чтение. Таблица читается целиком, поэтому время растёт с объёмом таблицы независимо от числа подходящих строк.

Чтение по индексу. Строки выбираются по индексу, поэтому время определяется числом подходящих строк, а не объёмом таблицы.

Соединение по хешу либо по вложенному циклу. Способ соединения определяется объёмом данных, поэтому оценка объёма влияет на выбор способа.

Практическое следствие: последовательное чтение на таблице большого объёма занимает основную долю времени, поэтому замена на чтение по индексу даёт наибольшее сокращение.

Оценки против факта и проверка после изменения

Расхождение оценки и фактического числа строк указывает на устаревшую статистику, поэтому план выбирается против неверной оценки.

Расхождение оценок. Оценка числа строк, отличающаяся от фактического в разы, указывает на устаревшую статистику таблицы.

Обновление статистики. Обновление статистики возвращает оценку к фактическому объёму, поэтому план выбирается против верной оценки.

Повторный план. План после изменения сравнивается с планом до изменения, поэтому смена операции видна явно.

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

Об индексах под условие фильтра — в статье про индексирование БД; о статистике запросов — в статье про pg_stat_statements.

Практика: разбор плана выполнения

1. Снимайте план с ANALYZE. Фактическое время показывает длительность операции, поэтому узкое место определяется по измерению.

2. Добавляйте BUFFERS для оценки объёма чтения. Число блоков показывает, откуда читались данные, поэтому источник времени виден.

3. Находите операцию с основной долей времени. Доля времени указывает на узкое место, поэтому изменение делается против него.

4. Сравнивайте оценку и фактическое число строк. Расхождение указывает на устаревшую статистику, поэтому план выбирается против неверной оценки.

5. Обновляйте статистику при расхождении оценок. Оценка возвращается к фактическому объёму, поэтому план выбирается верно.

6. Откатывайте запрос с изменением данных при ANALYZE. Вариант ANALYZE выполняет запрос, поэтому изменение применяется без отката.

7. Снимайте повторный план после изменения. Смена операции подтверждает, что изменение затронуло узкое место.

Рекомендации:

1. Не читайте план без ANALYZE как факт. Оценка планировщика отличается от фактического времени при устаревшей статистике.
2. Учитывайте объём данных при снятии плана. План на малом объёме может отличаться от плана на рабочем объёме.
3. Сопоставляйте операцию чтения с условием фильтра. Последовательное чтение при наличии подходящего индекса указывает на неверную оценку.
4. Фиксируйте длительность запроса в метриках. Рост длительности указывает на смену плана либо на рост объёма данных.
5. Пересматриваем план при изменении схемы. Новая схема меняет доступные индексы и способ соединения.

Практический сценарий: план выбирал последовательное чтение

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

1. Диагностика. План с ANALYZE показал последовательное чтение таблицы. Оценка числа строк в плане составляла десятки тысяч, фактическое число строк — единицы, поэтому расхождение указывало на устаревшую статистику.
2. Причина. Статистика таблицы не обновлялась после массового удаления строк, поэтому оценка объёма превышала фактический и планировщик выбрал последовательное чтение.
3. Действия. (а) Обновили статистику таблицы, чтобы оценка соответствовала фактическому объёму; (б) сняли повторный план и убедились в выборе чтения по индексу; (в) сравнили фактическое время до и после обновления и зафиксировали сокращение; (г) добавили вывод BUFFERS, чтобы объём чтения был виден при последующей диагностике; (д) настроили метрику длительности запроса, чтобы смена плана обнаруживалась.
4. Результат. Запрос выполняется по индексу, поэтому время ответа сократилось и не растёт с объёмом таблицы.

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

FAQ

Чем EXPLAIN отличается от EXPLAIN ANALYZE? EXPLAIN показывает оценку плана без выполнения, EXPLAIN ANALYZE выполняет запрос и подставляет фактическое время каждой операции.

Что показывает вывод BUFFERS? Число прочитанных блоков из буфера и с диска. Объём чтения указывает на источник времени операции.

О чём говорит расхождение оценки и факта? Об устаревшей статистике таблицы. Оценка объёма не соответствует фактическому, поэтому план выбирается против неверной оценки.

Нужно ли откатывать запрос при ANALYZE? Да, если запрос изменяет данные. Вариант ANALYZE выполняет запрос, поэтому изменение применяется без отката.

Источники: PostgreSQL USING EXPLAIN, EXPLAIN ANALYZE, planner statistics — ссылки в шапке.