PostgreSQL
EXPLAIN ANALYZE: читаем план запроса
Запрос работает 8 секунд. Добавили индекс - стал работать 12 секунд. Почему? Ответ скрыт в трёх строках EXPLAIN ANALYZE - но только если знать, что именно там искать.
- **Notion** при миграции на PG14 обнаружил через EXPLAIN ANALYZE, что 3 критических запроса дашборда выбирали Hash Join вместо Nested Loop из-за устаревшей статистики - после ANALYZE и пересмотра random_page_cost суммарное время дашборда упало с 4.2s до 0.8s.
- **Shopify** встроил автоматический парсер EXPLAIN ANALYZE в CI/CD: любой PR, который меняет query plan критических запросов (переход от Index Scan к Seq Scan), блокируется до ревью DBA - это предотвращает деградации в продакшене.
- **GitLab** публично документирует в migration guidelines требование прикладывать EXPLAIN ANALYZE output для любой миграции, затрагивающей таблицы > 1M строк - это стало стандартом после нескольких инцидентов с плохими планами.
- **Heroku** в 2019 году добавил в Dataclips автоматический анализ запросов: если EXPLAIN показывает Seq Scan на таблице > 100k строк без условия LIMIT, пользователю показывается предупреждение с рекомендацией добавить индекс.
Структура вывода EXPLAIN
`EXPLAIN` показывает план запроса - дерево операций, которое планировщик выбрал для выполнения. `EXPLAIN ANALYZE` дополнительно выполняет запрос и показывает реальные метрики. Понимание этого вывода - базовый навык любого backend-разработчика, работающего с PostgreSQL.
Каждый узел плана показывает: `cost=startup..total` (оценочная стоимость), `rows` (ожидаемое количество строк), `width` (средний размер строки в байтах). После `ANALYZE` добавляется `actual time=first..total` (реальное время в мс) и `loops` (сколько раз узел выполнялся).
EXPLAIN без ANALYZE не выполняет запрос - безопасно для SELECT, но также и для DML. EXPLAIN ANALYZE реально модифицирует данные - оборачивайте в транзакцию с ROLLBACK при проверке UPDATE/DELETE.
Что означает `loops=5` у узла Index Scan в выводе EXPLAIN ANALYZE?
Типы сканирований
PostgreSQL выбирает между тремя основными методами доступа к данным. Выбор зависит от **selectivity** (какую долю таблицы нужно вернуть) и наличия индексов.
| Тип | Когда выгоден | Характеристика |
|---|---|---|
| Seq Scan | Выборка > 5-15% строк | Читает страницы последовательно, I/O эффективен |
| Index Scan | Высокоселективный фильтр (< 1-5%) | Случайный доступ к heap, дорог при больших выборках |
| Index Only Scan | Все нужные колонки есть в индексе | Не читает heap вообще, самый быстрый |
Seq Scan не всегда плохо - на маленьких таблицах (< 1000 строк) PostgreSQL почти всегда выбирает Seq Scan даже при наличии индекса: накладные расходы на random I/O индекса превышают пользу.
Запрос `SELECT * FROM users WHERE country = 'US'` использует Seq Scan, хотя индекс на country существует. Таблица 10M строк, 40% - из US. Это баг планировщика?
Bitmap Scan
**Bitmap Heap Scan** - гибридный метод: сначала строится битовая карта страниц (Bitmap Index Scan), затем heap читается последовательно по этой карте. Это компромисс между Index Scan (случайный I/O, 1 строка за раз) и Seq Scan (читает всё).
Ключевое преимущество Bitmap Scan: возможность объединить несколько индексов через `BitmapAnd`/`BitmapOr`. Без этого PostgreSQL мог бы использовать только один индекс для фильтрации. При большом количестве совпадений карта переходит в «lossy» режим: `Heap Blocks: lossy=320` означает, что требуется `Recheck Cond` на уровне строк.
Если в плане есть `Recheck Cond` с `Heap Blocks: lossy=N` - это сигнал, что `work_mem` не хватило для точной битовой карты. Увеличение work_mem может переключить в exact mode и убрать лишние перечитывания.
В плане видно `Heap Blocks: lossy=540`. Что это означает и как исправить?
Cost Model
PostgreSQL оценивает стоимость каждого плана в абстрактных единицах - **cost units**. Базовая единица: чтение одной страницы с диска (seq_page_cost = 1.0). Все остальные операции нормированы относительно неё.
| Параметр | Default | Смысл |
|---|---|---|
| seq_page_cost | 1.0 | Чтение страницы при последовательном доступе |
| random_page_cost | 4.0 | Случайное чтение страницы (меняй на 1.1-2.0 для SSD) |
| cpu_tuple_cost | 0.01 | Обработка одного тьюпла CPU |
| cpu_index_tuple_cost | 0.005 | Обработка одной записи индекса |
| cpu_operator_cost | 0.0025 | Выполнение одного оператора/функции |
На AWS Aurora и RDS с io1/gp3 хранилищем рекомендуется `random_page_cost = 1.1-2.0`. При дефолтном значении 4.0 планировщик избыточно предпочитает Seq Scan - это частая причина «почему индекс не используется» на облачных инсталляциях.
На SSD-кластере планировщик продолжает выбирать Seq Scan вместо Index Scan. Первое что нужно проверить?
Actual vs Estimated Rows
Самый важный сигнал в EXPLAIN ANALYZE - расхождение между `rows=N` (estimate) и `actual rows=M`. Если estimate ошибается на порядок, планировщик может выбрать принципиально неверный план: Hash Join вместо Nested Loop или наоборот.
Инструмент `explain.depesz.com` или `explain.tensor.ru` позволяет вставить вывод EXPLAIN ANALYZE и получить визуализацию с подсветкой узлов, где estimate сильно расходится с actual - это экономит время при диагностике.
Высокий cost в EXPLAIN означает, что запрос медленный
Cost - это оценочная единица планировщика, не миллисекунды. Только actual time в EXPLAIN ANALYZE показывает реальное время
Cost используется только для сравнения альтернативных планов между собой. Запрос с cost=100000 может выполниться за 10ms, а запрос с cost=500 может занять 5 секунд - если estimates были неточными или cost model не откалибрована под конкретное железо.
EXPLAIN ANALYZE показывает `rows=5` (estimate) и `actual rows=50000` для Index Scan. Каковы вероятные последствия?
Ключевые идеи
- **EXPLAIN ANALYZE** выполняет запрос и показывает реальное время (actual time) vs оценочную стоимость (cost) - расхождение между ними указывает на проблему со статистикой.
- **Seq Scan** выгоден при большой выборке (>5-15%), **Index Scan** при высокой селективности (<1-5%), **Bitmap Scan** объединяет несколько индексов через BitmapAnd/BitmapOr.
- **random_page_cost** надо снижать до 1.1-2.0 на SSD, иначе планировщик избыточно предпочитает Seq Scan.
- Главный сигнал проблемы: `rows=5` vs `actual rows=50000` - такое расхождение означает, что планировщик выбрал план на основе ошибочных данных.
Связанные темы
EXPLAIN ANALYZE - точка пересечения нескольких ключевых тем PostgreSQL:
- Планировщик и статистика — Расхождение actual vs estimated rows объясняется тем, как планировщик строит оценки через pg_statistic
- Алгоритмы JOIN — Выбор Hash Join vs Nested Loop vs Merge Join виден в EXPLAIN и зависит от тех же cost estimates
- Обслуживание индексов — EXPLAIN ANALYZE - единственный надёжный способ проверить, используется ли конкретный индекс в нужных запросах
Вопросы для размышления
- Когда последний раз запускался EXPLAIN ANALYZE на самых частых запросах вашего приложения, и соответствуют ли реальные планы ожидаемым?
- Как random_page_cost настроен на вашем кластере, и соответствует ли это типу хранилища (HDD/SSD/NVMe)?
- Есть ли в вашем проекте процесс проверки query plans при деплое изменений схемы БД?