EXPLAIN/EXPLAIN ANALYZE: чтение плана выполнения запроса

Введение

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

Концепция

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

Пример кода: EXPLAIN/EXPLAIN ANALYZE

-- Простой EXPLAIN — только план, без реального выполнения запроса
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

-- Типичный вывод (синтаксис/детали варьируются между СУБД, пример для PostgreSQL):
--   Index Scan using idx_orders_customer on orders (cost=0.29..8.31 rows=1 width=48)
--     Index Cond: (customer_id = 5)
-- ^ "Index Scan" означает, что индекс реально используется — это хороший знак

-- EXPLAIN ANALYZE — реальное выполнение с измеренным временем на каждом шаге
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;

-- Типичный вывод:
--   Index Scan using idx_orders_customer on orders (cost=0.29..8.31 rows=1 width=48)
--     (actual time=0.025..0.027 rows=3 loops=1)
--     Index Cond: (customer_id = 5)
--   Planning Time: 0.150 ms
--   Execution Time: 0.045 ms

-- Проблемный случай — Seq Scan (полный просмотр таблицы) вместо ожидаемого использования индекса
EXPLAIN SELECT * FROM orders WHERE order_date::text LIKE '2026%'; -- неявное преобразование типа!

-- Типичный вывод для такого проблемного запроса:
--   Seq Scan on orders (cost=0.00..1834.00 rows=500 width=48)
--     Filter: ((order_date)::text ~~ '2026%'::text)
-- ^ "Seq Scan" означает полный просмотр таблицы — индекс на order_date здесь НЕ используется,
--   поскольку преобразование order_date::text "ломает" возможность использовать индекс

Пояснения к коду

Index Scan using idx_orders_customer в выводе первого примера — явное подтверждение, что СУБД действительно использует созданный ранее индекс (статья 413) для этого конкретного запроса, что было бы невозможно узнать достоверно без обращения к EXPLAIN, опираясь лишь на предположение о том, что индекс «должен» использоваться. actual time/Execution Time в выводе EXPLAIN ANALYZE — реально измеренные значения, в отличие от cost (оценка планировщика в условных единицах, не привязанных напрямую к реальным миллисекундам) — для диагностики реальной, наблюдаемой проблемы производительности именно EXPLAIN ANALYZE даёт фактические цифры, тогда как простой EXPLAIN показывает лишь то, что планировщик ожидал заранее, до фактического выполнения. Третий пример показывает классическую, частую проблему — Seq Scan вместо ожидаемого использования индекса, вызванную тем, что неявное преобразование типа (order_date::text) делает значение результата выражения непригодным для прямого использования существующего индекса на исходном, непреобразованном столбце.

Подводные камни

• Использование EXPLAIN ANALYZE непосредственно на production-БД для запроса с реальным побочным эффектом (INSERT/UPDATE/DELETE) без понимания, что EXPLAIN ANALYZE РЕАЛЬНО выполняет запрос (в отличие от обычного EXPLAIN, только планирующего) — для модифицирующих запросов это означает, что данные действительно изменятся, и EXPLAIN ANALYZE для таких запросов стоит выполнять либо в рамках транзакции с последующим ROLLBACK (статья 408), либо исключительно на тестовой, не production-копии данных, если реальное изменение данных недопустимо в момент диагностики.
• Интерпретация плана выполнения без учёта реального объёма и распределения данных в production, если EXPLAIN/EXPLAIN ANALYZE выполнялся на значительно меньшей по объёму тестовой/разработческой БД — план выполнения и реальная эффективность конкретной стратегии (использовать ли индекс или полный просмотр) могут существенно отличаться между небольшой тестовой БД и значительно большей production-БД с другим, реальным распределением и объёмом данных, и окончательные выводы о производительности стоит делать на основе данных, максимально приближенных к реальным production-условиям.
• Игнорирование Seq Scan как автоматически «плохого» признака без анализа конкретной ситуации — полный просмотр таблицы не всегда означает проблему: для небольших таблиц, или запросов, отбирающих значительную долю всех строк таблицы, полный просмотр иногда действительно эффективнее, чем построчный доступ через индекс (статья 413), и оптимизатор СУБД, выбравший Seq Scan вместо доступного индекса, может делать это вполне обоснованно — Seq Scan стоит рассматривать как сигнал для дальнейшего анализа, а не автоматический показатель неправильной конфигурации.
• Однократная проверка плана выполнения без повторной проверки после значимых изменений (рост объёма данных, изменение статистики, статья 417, обновление версии СУБД) — план выполнения, эффективный сегодня для текущего объёма и распределения данных, может со временем стать неоптимальным по мере роста таблицы или изменения паттернов использования, и для критичных по производительности запросов стоит периодически перепроверять актуальность плана выполнения, а не полагаться на единократную, давнюю проверку.