PostgreSQL: индексы и EXPLAIN ANALYZE — как ускорить запросы

Индекс ускоряет чтение, но замедляет запись и занимает место. Поэтому правильный порядок работы такой: найти конкретный медленный запрос, посмотреть его план, понять, чего не хватает, и только потом создавать индекс. EXPLAIN без ANALYZE показывает предположения планировщика, а EXPLAIN ANALYZE — реальные цифры. Для диагностики почти всегда нужен второй вариант с ключом BUFFERS.

Снимаем план правильно

Запрос при ANALYZE выполняется по-настоящему, поэтому на продакшене будьте осторожны с UPDATE, DELETE и тяжёлыми SELECT. Для изменяющих запросов используйте BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;. Ключ BUFFERS показывает работу с кэшем страниц: shared hit означает, что страница уже в памяти, read — что её пришлось читать с диска. Именно read и temp read/written объясняют ситуацию «медленно только иногда».

psql -d app -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id, total FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;"

В выводе смотрите на actual time, actual rows и loops. Время узла указано на один цикл, поэтому при loops = 1000 общее время умножается. Сравните planned rows и actual rows: расхождение в разы означает, что планировщик ошибся в оценке селективности, и индекс может выбираться неверно.

Где снимать план

Пустая таблица обманывает: Seq Scan на десяти строках ничего не говорит о поведении на миллионе. Тестируйте на копии с реалистичным объёмом и распределением данных. Если копии нет, хотя бы оцените селективность запросом count(*) с теми же условиями и сравните результат с pg_class.reltuples. План зависит и от кэша: первый запуск читает с диска, второй попадает в shared buffers. Снимайте несколько прогонов и смотрите на устойчивый результат, а не на лучший.

Для тяжёлых запросов удобно сохранять план в JSON и разбирать визуализатором, но не доверяйте только картинке: цифры actual rows и Buffers важнее цвета узла. EXPLAIN на реплике может отличаться, если там другая статистика или есть отставание репликации.

Читаем узлы плана

Seq Scan — последовательное чтение всей таблицы; он нормален для маленьких таблиц и для выборки большой доли строк. Index Scan читает индекс и ходит в таблицу за каждой строкой, это случайные обращения. Bitmap Heap Scan сначала собирает страницы, а затем читает их пачками — компромисс для средней селективности. Index Only Scan не обращается к таблице, но только если все нужные колонки есть в индексе и карта видимости актуальна; параметр Heap Fetches показывает, сколько раз всё-таки пришлось сходить в таблицу.

Nested Loop хорош для нескольких строк с внешней стороны, Hash Join — для больших наборов без индекса, Merge Join — для уже отсортированных данных. Если в плане появился Sort с внешней сортировкой на диск, проверьте work_mem и сам запрос: иногда индекс с правильным порядком колонок убирает сортировку полностью.

SELECT query, calls, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

Какой индекс выбрать

B-tree — универсальный вариант для равенства, диапазонов, ORDER BY и префиксного LIKE ‘abc%’. GIN нужен для jsonb, массивов и полнотекстового поиска, GiST — для геометрии и диапазонов, BRIN — для очень больших таблиц, которые растут по времени и хранятся в физическом порядке, Hash — только для равенства и редко даёт выигрыш.

• Составной индекс: колонки с равенством ставьте раньше, с диапазоном — позже.
• Частичный индекс: WHERE status = ‘new’ резко уменьшает размер, если запросы всегда фильтруют по этому признаку.
• Покрывающий индекс: INCLUDE (status) позволяет получить Index Only Scan.
• Индекс по выражению: lower(email) для регистронезависимого поиска.

CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at DESC) INCLUDE (status);

CONCURRENTLY не блокирует запись, но работает дольше, не выполняется внутри транзакции и при сбое оставляет невалидный индекс. Проверьте его через pg_index.indisvalid и при необходимости удалите и создайте заново. Для больших таблиц сначала оцените размер: индекс на 50 миллионов строк может занять десятки гигабайт.

Почему индекс не используется

Самые частые причины: функция над колонкой (WHERE lower(email) = …), несовпадение типов и неявное приведение, LIKE с ведущим процентом, низкая селективность, устаревшая статистика и «слишком умный» OR. Для jsonb обычный B-tree не поможет — нужен GIN. Иногда мешает большое расхождение между оценкой и фактом: помогает ANALYZE, увеличение statistics target для колонки или расширенная статистика CREATE STATISTICS для связанных колонок.

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 20;

Ноль в idx_scan за месяц — повод проверить, нужен ли индекс вообще. Но не удаляйте сразу: счётчик может не покрывать редкие отчёты. Сначала соберите статистику за характерный период, затем удаляйте CONCURRENTLY и следите за планами ключевых запросов.

Отдельно проверьте autovacuum и долгие транзакции: они удерживают старые версии строк, из-за чего таблица и индексы раздуваются, а планировщик получает искажённые оценки. Для контроля размера используйте pg_relation_size и pg_indexes_size, а не только вывод du по каталогу данных.

Обслуживание и цена

Индексы требуют VACUUM: мёртвые версии строк и устаревшая карта видимости ухудшают Index Only Scan. Обычный VACUUM обновляет карту видимости, ANALYZE собирает статистику. Раздутые индексы перестраиваются REINDEX CONCURRENTLY. На интенсивно обновляемых таблицах помогает fillfactor ниже 100, чтобы обновления оставались в той же странице и работал HOT-механизм.

VACUUM (ANALYZE, VERBOSE) orders;

После создания индекса обязательно повторите EXPLAIN ANALYZE тем же запросом и сравните время и Buffers. Ускорение в два раза на одном запросе может обернуться замедлением вставок на 10–15 процентов. Измеряйте не только SELECT, но и общую нагрузку на запись.

Чек-лист

• Нашли конкретный запрос через pg_stat_statements (если расширение включено), а не «на глаз».
• Сняли EXPLAIN (ANALYZE, BUFFERS) и сравнили оценки с фактом.
• Проверили, не мешают ли функция, тип или сортировка.
• Выбрали тип индекса и порядок колонок под условия запроса.
• Создали CONCURRENTLY и проверили indisvalid.
• Измерили эффект и накладные расходы на запись.

Итог

Индекс — это гипотеза о том, как будут искать данные, и EXPLAIN ANALYZE её проверяет. Начинайте с плана и реальных Buffers, а не с модных типов индексов; сравнивайте оценку строк с фактом; создавайте индексы CONCURRENTLY; удаляйте неиспользуемые после наблюдения. Такой цикл занимает меньше времени, чем перебор индексов наугад, и не превращает базу в набор структур, которые тормозят каждую вставку.