Прямой ответ
Тормозящий запрос в Postgres разбирается одной командой: EXPLAIN (ANALYZE, BUFFERS) SELECT ... — планировщик показывает что он сделал (дерево операций), сколько строк прошло через каждый узел и где ушло время; читать его — навык на час практики. Три вещи, которые ищет взгляд: Seq Scan на большой таблице (полное сканирование там, где ожидается индекс — про индексы и запросы: SQLAlchemy-практика); расхождение оценки и факта (rows=10 в оценке против actual rows=500000 — статистика устарела, ANALYZE таблицы или запрос хитрее, чем думает планировщик — отсюда неверный выбор join'ов); дорогие узлы по actual time (время в миллисекундах на узел: самый долгий — кандидат на оптимизацию, часто это не там, где кажется — «тормозит JOIN», а тормозит сортировка миллиона строк под ним). Различие EXPLAIN и EXPLAIN ANALYZE принципиально: первый — только план (быстро, безопасно), второй — исполняет запрос (на UPDATE/DELETE — реально изменит данные: оборачивать в транзакцию с ROLLBACK). И контекст для Python-разработчика: план показывает и следствия ORM-паттернов — сотня мелких запросов N+1 не видна в одном плане (каждый быстр) — ловится логом запросов/метрикой их числа (N+1).
Основы чтения: узлы, строки, время
Дерево плана читается изнутри-вверх (самые вложенные узлы исполняются первыми): Seq Scan on orders (cost=0.00..18334.00 rows=1000000) — сканирование таблицы (cost — условная стоимость в страницах/строках для сравнения планов, не время); Index Scan using orders_user_idx on orders — по индексу; actual time=0.02..15.3 rows=42 loops=1 (в ANALYZE) — реальное время запуска/завершения, фактические строки, число итераций (loops=50000 у вложенного узла — красный флаг N+1-подобного поведения внутри запроса). Ключевые операции: Sort (external merge Disk: 120MB — сортировка не влезла в work_mem и ушла на диск — классический тормоз: поднять work_mem сессии или убрать сортировку из запроса); Hash Join (построение хеш-таблицы по меньшей выборке — эффективен на больших; Batches: 64 + disk — не хватило памяти); Nested Loop (внешняя строка → поиск во внутренней — быстр при малой внешней выборке и индексе внутри, катастрофа при миллионах с обеих сторон); Bitmap Heap Scan (индекс отобрал строки, битмап отсортил чтения — компромисс для средних выборок). Buffers: shared hit=10 read=50000 (с BUFFERS) — попадания в кеш против чтений с диска: запрос, читающий 50k блоков с диска при каждом вызове, — кандидат на индекс/кеш независимо от «нормального» времени на тёплой машине.
Оценка против факта: корень неверных планов
Планировщик выбирает план по оценкам (статистика pg_statistic: распределения значений, корреляции); оценка неверна — план неверен. Диагностика: в каждом узле сравнить rows= (оценка) и actual rows= — расхождение в разы/порядки — проблема статистики. Частые причины: устаревшая статистика после массовой загрузки (миллион строк добавили — autovacuum/ANALYZE не успел — ANALYZE таблица вручную; для регулярных больших загрузок — ALTER TABLE ... SET (autovacuum_analyze_scale_factor=0.01) — анализ чаще дефолтных 10%); неоднородные данные (в колонке 99% одного значения — оценка «по среднему» для редкого значения ошибается на порядки — расширенная статистика CREATE STATISTICS); сложные предикаты (комбинации условий считаются независимыми — WHERE a=1 AND b=2 при коррелированных a/b — недооценка в разы — тоже расширенная статистика или переписывание); параметризованные планы в prepared statements (generic plan «на среднего» параметр против custom — на skewed-данных prepared-запросы иногда деградируют — plan_cache_mode=force_custom_plan как эксперимент). Правило практики: прежде чем добавлять индекс «потому что медленно» — посмотрите, не врёт ли оценка (индекс при верной статистике может оказаться не нужен — планировщик сам выберет верное).
Типовые тормоза и их лечение по плану
Каталог находок: (1) Seq Scan + фильтр с низкой селективностью — индекс не создан или не подходит (функция над колонкой WHERE lower(email)= — нужен функциональный индекс; LIKE '%suffix' — b-tree бессилен, нужен trigram/pg_trgm); (2) Index Scan с rows=500000 — индекс есть, но фильтр слабый («индекс по status при 90% одинаковых» — частичный индекс WHERE status='new' или составной с селективной колонкой); (3) Nested Loop loops=100000 — декартова структура: join-условие потеряно/неиндексированная FK-колонка (индексы на FK — не автоматическая вещь в Postgres — создавать явно); (4) Sort external merge — work_mem мал для объёма (сессия: SET work_mem='64MB' для отчёта; глобально осторожно — память × подключения × узлы сортировки); (5) Append из десятков партиций без pruning — запрос без условия по ключу партиционирования сканирует всё (добавить фильтр — pruning включится); (6) SubPlan на строку (коррелированный подзапрос исполняется loops=N) — переписать на JOIN/LATERAL. Инструменты поверх EXPLAIN: auto_explain (логирует планы медленных запросов прода — ловит то, что не воспроизводится вручную), pg_stat_statements (топ запросов по суммарному времени — начинать оптимизацию с него, а не с «мне кажется, этот запрос медленный» — подключение), визуализаторы планов (explain.depesz.com / explain.dalibo.com — подсветкой показывают узлы-виновники).
Практический сценарий: отчёт, который вешал базу
Симптом: ежедневный отчёт «продажи по менеджерам за период» на растущей базе стал занимать 4 минуты и грузить CPU. Разбор: (1) EXPLAIN (ANALYZE, BUFFERS) — дерево: Hash Join (orders × users) с Seq Scan по orders — actual rows=4.2M при оценке 800k (расхождение 5x — статистика после роста устарела), затем Sort external merge 340MB (сортировка по manager на диске); (2) ANALYZE orders — оценка выправилась, план сменился на Index Scan по новому составному условию — но время 3:20 (сортировка осталась); (3) смотрим запрос: сортировка нужна только для группировки по менеджеру — переписали на GROUP BY manager_id с агрегацией в БД (сортировка презентационная ушла в Python по 40 строкам результата — разделение ответственности: БД агрегирует, код форматирует); (4) частичный индекс ON orders (created_at) WHERE status='paid' — отчёт читает 5% таблицы; (5) итог — 9 секунд; фиксация: pg_stat_statements показывает отчёт в топе — до/после записаны, auto_explain с порогом 2s включён на проде для будущих сюрпризов. Сценарий показывает порядок: статистика → структура запроса → индекс — именно в таком порядке дешевле (индекс до ANALYZE мог не понадобиться вовсе).
FAQ
Почему EXPLAIN без ANALYZE врёт о скорости? Он показывает оценку стоимости, а не время: план «выглядит индексным», но фактические строки/время видны только в ANALYZE; и наоборот — ANALYZE исполняет запрос (кеш прогревается, данные меняются) — для диагностики записи использовать транзакцию с откатом.
Seq Scan — всегда плохо? Нет: при выборке значимой доли таблицы (>5–10%) полное сканирование быстрее индексного (случайные чтения по индексу дороже последовательных) — планировщик прав; плохо — seq scan на маленькой выборке (нет/не годится индекс) и на таблицах, которые читаются постоянно по узкому фильтру.
Стоит ли читать cost-числа буквально? Cost — единицы условной стоимости (страницы/строки с настройками seq_page_cost и др.) для сравнения планов между собой, не миллисекунды и не «нагрузка»; абсолютное значение бессмысленно, относительное — объясняет выбор планировщика («почему он выбрал это» — потому что cost ниже по его модели, а модель может врать — см. оценки строк).
Как поймать медленный запрос, который не воспроизводится?
pg_stat_statements (суммарная статистика по нормализованным запросам — топ по total_exec_time) + auto_explain (план в лог при превышении порога) + log_min_duration_statement=1000 (тексты запросов медленнее секунды — логи их сохранят) — связка ловит «ночные» и редкие запросы без ручного караула.
Источники: документация PostgreSQL (Using EXPLAIN, query config), Use The Index Luke — ссылки в шапке.