EXPLAIN ANALYZE в PostgreSQL: как читать план и какие индексы реально нужны
С чего начинать: не с плана, а со списка запросов
Индексы добавляют под конкретные запросы, а не «на все колонки из WHERE». Сначала найдите, какие запросы действительно дорогие. Расширение pg_stat_statements собирает статистику по нормализованным запросам:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- требует shared_preload_libraries
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms,
left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;Сортировка по total_exec_time показывает, где база тратит время суммарно. Запрос на 3 мс, вызываемый миллион раз, часто важнее отчёта на 2 секунды раз в день.
Как снять план
EXPLAIN (ANALYZE, BUFFERS) SELECT id, total, created_at FROM orders WHERE user_id = 4815 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;
ANALYZE реально выполняет запрос. Для UPDATE и DELETE оборачивайте его в транзакцию с откатом:
BEGIN; EXPLAIN (ANALYZE, BUFFERS) DELETE FROM sessions WHERE expires_at < now(); ROLLBACK;
BUFFERS показывает, сколько страниц прочитано из кэша (shared hit) и с диска (read). В PostgreSQL 18 эта информация выводится вместе с ANALYZE автоматически, в более ранних версиях параметр нужно указывать явно.
Как читать узлы
План — дерево. Читается изнутри наружу: самые вложенные узлы выполняются первыми. У каждого узла две группы цифр:
Limit (cost=0.00..1843.20 rows=20 width=24) (actual time=0.041..96.512 rows=20 loops=1)
-> Sort (cost=... rows=1180 ...) (actual time=96.501..96.506 rows=20 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 27kB
-> Seq Scan on orders (cost=0.00..41250.00 rows=1180 width=24)
(actual time=0.030..95.880 rows=1342 loops=1)
Filter: ((user_id = 4815) AND (status = 'paid'::text))
Rows Removed by Filter: 1998658Это иллюстративный план, но паттерн в нём типичный:
- `cost` — оценка планировщика в условных единицах. Сравнивать её между разными запросами бессмысленно.
- `actual time=A..B` — миллисекунды до первой строки и до последней. Время родителя включает время детей.
- `rows` в оценке и в факте. Расхождение на порядки — главный признак проблемы: планировщик выбрал план под неверное количество строк.
- `loops` — сколько раз узел выполнялся. Реальное время и строки нужно умножать на
loops. Внутренний узел Nested Loop сloops=50000и временем 0,02 мс — это секунда. - `Rows Removed by Filter` — строки, прочитанные зря. Здесь два миллиона строк просмотрено ради 1342 нужных.
Какой индекс нужен этому запросу
Запрос фильтрует по равенству user_id и status и сортирует по created_at. Порядок колонок в составном индексе: сначала колонки с равенством, затем колонка сортировки.
CREATE INDEX CONCURRENTLY orders_user_status_created_idx ON orders (user_id, status, created_at DESC);
С таким индексом план превращается в Index Scan с Limit: база читает ровно 20 записей по индексу в нужном порядке и не сортирует ничего. CONCURRENTLY не блокирует запись в таблицу на время построения, но не работает внутри транзакции и при сбое оставляет невалидный индекс, который нужно удалить и создать заново.
Если почти все запросы смотрят на одно значение статуса, частичный индекс меньше и дешевле в обслуживании:
CREATE INDEX CONCURRENTLY orders_pending_idx ON orders (created_at) WHERE status = 'pending';
Он будет использован, только если условие запроса включает status = 'pending' буквально.
Узлы, на которые стоит смотреть
- `Sort Method: external merge Disk: ...` — сортировка не поместилась в
work_memи ушла на диск. Лечится индексом с нужным порядком или увеличениемwork_memдля конкретной сессии, а не глобально. - `Bitmap Heap Scan` с `Recheck Cond` и `Heap Blocks: lossy` — битовая карта не поместилась в память и стала постраничной.
- `Index Only Scan` с большим `Heap Fetches` — индекс покрывает запрос, но карта видимости устарела, и база всё равно ходит в таблицу. Помогает
VACUUM(или настройка автовакуума для этой таблицы). - `Hash Join` с `Batches: 8` — хеш-таблица не уместилась в память.
- Оценка `rows=1`, а факт — тысячи. Обновите статистику
ANALYZE orders;. Если колонки коррелируют (город и страна), помогает расширенная статистика:CREATE STATISTICS ... (dependencies) ON city, country FROM addresses;.
Почему индекс есть, а не используется
- 1.Функция или приведение типа на колонке:
WHERE lower(email) = $1не использует индекс поemail. Нужен индекс по выражению(lower(email)). - 2.
LIKE '%abc'с ведущим процентом. B-tree тут не поможет, нуженpg_trgmс GIN. - 3.Условие выбирает большую долю таблицы. Последовательное чтение тогда действительно дешевле, и планировщик прав.
- 4.Разные типы: колонка
bigint, параметр передан какnumeric. - 5.Маленькая таблица. Для пары тысяч строк
Seq Scanнормален.
Лишние индексы тоже стоят денег
Каждый индекс замедляет вставку и обновление и занимает место. Кандидаты на удаление:
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;
Статистика локальна для сервера: индекс, который не нужен на primary, может обслуживать отчёты на реплике. И не удаляйте индексы, обеспечивающие UNIQUE или первичный ключ.
Когда EXPLAIN не покажет проблему
Если запрос быстрый в ручном прогоне, но медленный в продакшене, дело может быть в блокировках, конкуренции за соединения или в другом плане для параметризованного запроса. Включите auto_explain с порогом auto_explain.log_min_duration, чтобы получить план именно медленного выполнения из логов, и смотрите pg_stat_activity на ожидания Lock.