EXPLAIN ANALYZE: читаем план запроса

Запрос, который работал секунду на тестовых 10 000 строк, тормозит 45 секунд на продакшн-базе с 50 миллионами. Проблема не в коде - в том, что планировщик выбрал неверный план из-за устаревшей статистики. Один `EXPLAIN ANALYZE` - и причина видна за 30 секунд.

• **Shopify** использует автоматический анализ планов для каждого slow query - EXPLAIN ANALYZE запускается автоматически для запросов дольше 100 мс и результат сохраняется в систему мониторинга.
• **GitHub** обнаружил, что их крупнейший JOIN деградировал после миграции данных: статистика устарела, планировщик выбрал Nested Loop вместо Hash Join. Один ANALYZE восстановил производительность.
• **Postgres-расширение pg_stat_statements** - стандарт в любом серьезном продакшне: показывает топ-N запросов по total_time и позволяет приоритизировать оптимизацию.

Анатомия EXPLAIN ANALYZE

PostgreSQL не скрывает, как думает: команда `EXPLAIN ANALYZE` возвращает полное дерево плана выполнения - с оценочными и фактическими стоимостями, числом строк, временем каждого узла. Это рентген запроса, видимый любому, кто умеет его читать.

Разница между `EXPLAIN` и `EXPLAIN ANALYZE`: первый только строит план без выполнения (cost - оценка планировщика), второй реально выполняет запрос и показывает actual rows/time. Для SELECT это безопасно, для INSERT/UPDATE/DELETE - используй `BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;`.

Seq Scan, Index Scan, Bitmap Scan

Три типа сканирования - это три разные стратегии доступа к данным. PostgreSQL выбирает между ними на основе selectivity: какую долю таблицы затронет фильтр. Удивительный факт: иногда Seq Scan быстрее Index Scan - особенно если нужно 30-40% строк таблицы, потому что последовательное чтение диска быстрее хаотичных random reads.

**Seq Scan** - читает таблицу страница за страницей, подходит для больших выборок (>5-10% строк). **Index Scan** - идет по B-tree, потом для каждой записи делает heap fetch (random I/O), идеален для <1-2% строк. **Bitmap Index Scan** - собирает битовую маску совпадений в памяти, затем читает heap пачками - компромисс для 1-10% строк или нескольких индексов через AND/OR.

Nested Loop, Hash Join, Merge Join

JOIN - самая дорогая операция в реляционных запросах. PostgreSQL реализует три алгоритма, и выбор между ними зависит от размера таблиц, наличия индексов и `work_mem`. Неверный выбор алгоритма - частая причина медленных JOIN в продакшне.

**Nested Loop** - O(N*M), подходит если inner relation маленькая или есть индекс. **Hash Join** - O(N+M), строит хеш-таблицу из меньшей relation в `work_mem`, затем проходит по большей. При нехватке памяти - spillover на диск (batches>1 в плане). **Merge Join** - O(N log N + M log M), требует отсортированных входов, хорош для больших уже отсортированных наборов.

Cost Model: как планировщик считает стоимость

Планировщик PostgreSQL не угадывает - он считает. Каждый узел плана получает `cost` по формуле с весами seq_page_cost, random_page_cost, cpu_tuple_cost. На SSD разница между sequential и random I/O минимальна, но PostgreSQL по умолчанию настроен на HDD. Это первое, что меняют при переезде на SSD.

Ключевые GUC-параметры cost model: `seq_page_cost=1.0` (baseline), `random_page_cost=4.0` (для SSD - ставят 1.1-1.5), `cpu_tuple_cost=0.01`, `effective_cache_size` - оценка OS page cache (влияет на Index Scan cost). Статистика таблиц: `pg_statistic`, обновляется через `ANALYZE`, управляется `default_statistics_target` (по умолчанию 100, для skewed данных - до 500).

Практические техники: buffers, loops, параллелизм

Умение читать `EXPLAIN ANALYZE` - это умение задавать правильные вопросы к плану: где самые дорогие узлы, где actual vs estimated расходятся, есть ли Seq Scan на больших таблицах, много ли loops в Nested Loop. Ответы на эти вопросы указывают на конкретное действие.

**Чеклист анализа плана:** (1) Найти самый дорогой узел по actual time. (2) Проверить `rows estimated` vs `rows actual` - расхождение >10x требует ANALYZE. (3) Найти Seq Scan на таблицах >100k строк - возможно нужен индекс. (4) Найти Nested Loop с большим loops - возможно нужен Hash Join (проверить work_mem). (5) Проверить `Buffers: read` vs `hit` - много read = данные не в кеше. (6) Параллельный план (`Workers Planned`) - включен ли `max_parallel_workers_per_gather`.

Ключевые идеи

• **EXPLAIN ANALYZE** показывает фактические строки и время - расхождение с оценкой >10x - сигнал устаревшей статистики (`ANALYZE table`).
• **Тип сканирования** зависит от selectivity: Index Scan для <2% строк, Bitmap Scan для 2-10%, Seq Scan для >10% - и от `random_page_cost` (снижать до 1.1-1.5 на SSD).
• **Hash Join Batches>1** - хеш не влез в `work_mem`, спиллится на диск; увеличение `work_mem` решает проблему. **Nested Loop с высоким loops** - кандидат на замену Hash Join.

Связанные темы

EXPLAIN ANALYZE работает поверх механизмов, изученных ранее:

• Индексы: B-tree и структуры — Index Scan в плане - это навигация по B-tree из предыдущего урока
• Расширенные индексы — Partial и composite индексы меняют доступные планировщику пути

Вопросы для размышления

• Если `actual rows` в 100 раз меньше `estimated rows` - какой следующий шаг?
• Когда выгодно принудительно отключить seq_scan через `SET enable_seqscan=off` для диагностики?
• Что изменится в планах запросов после переезда с HDD на NVMe SSD, если не менять конфигурацию PostgreSQL?

Связанные уроки

• alg-21-dp