Когда индекс начинает мешать

«Просто добавь индекс» — фраза, которую я слышу примерно так же часто, как «а можно просто перезапустить сервер». Иногда это действительно решение. Но у индексов есть цена, и она не сводится к месту на диске. Каждый лишний индекс — это постоянные, невидимые невооружённым глазом расходы, которые накапливаются месяцами, пока однажды кто-то не спросит, почему вставки стали медленнее в три раза.

Индекс — это не бесплатное ускорение

Каждый индекс нужно обновлять при любом INSERT, UPDATE и DELETE над соответствующими колонками. Пять индексов на таблице — это пять дополнительных структур, которые PostgreSQL поддерживает на каждую запись. На таблице с высокой частотой записи это ощутимо: растущий bloat в самих индексах, дополнительная нагрузка на WAL, более медленные вставки и, как следствие, более длинные транзакции, которые сами по себе мешают VACUUM.

Почему planner иногда игнорирует индекс

Один из самых частых вопросов: «у меня есть индекс, но PostgreSQL всё равно делает Seq Scan, он сломался?» Почти всегда — нет. Планировщик выбирает не «индекс или не индекс», а путь с наименьшей оценочной стоимостью, взвешивая случайный доступ к диску против последовательного чтения. И у sequential scan есть сценарии, где он объективно дешевле:

• Выборка большой доли таблицы. Если запрос вернёт 40% строк, читать их через индекс — это множество случайных обращений к диску вместо одного последовательного прохода.
• Маленькая таблица. Если таблица помещается в несколько страниц, индекс просто не успевает окупить накладные расходы на переход по нему.
• Устаревшая статистика. Планировщик принимает решения на основе статистики из pg_stats. Если давно не было ANALYZE, оценки могут быть далеки от реальности.
• Низкая селективность значения. Индекс по колонке status, где 90% строк имеют одно и то же значение, почти бесполезен именно для этого значения — но может быть полезен для редких.

Пример

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE status = 'shipped';

Если status = 'shipped' покрывает половину таблицы orders, вы, скорее всего, увидите план вроде:

Seq Scan on orders  (cost=0.00..48291.00 rows=612000 width=94)
                    (actual time=0.021..312.442 rows=598120 loops=1)
  Filter: (status = 'shipped'::text)
  Rows Removed by Filter: 401880
  Buffers: shared hit=41200
Planning Time: 0.112 ms
Execution Time: 334.887 ms

И это правильное решение планировщика, а не баг. Индекс по status здесь почти наверняка не поможет — потенциальный выигрыш от узкой выборки исчезает, когда выбирается больше трети таблицы. В таком случае куда полезнее будет частичный индекс на противоположное, редкое значение — например, на заказы, которые ещё не отгружены.

Где индекс действительно нужен

Частичные, составные и покрывающие индексы

Вместо того чтобы вешать отдельный индекс на каждую колонку, часто выгоднее сделать один составной индекс под конкретный частый запрос, либо частичный индекс, если фильтр всегда затрагивает небольшое подмножество строк:

-- частичный индекс: только незакрытые заказы
CREATE INDEX idx_orders_open
  ON orders (customer_id)
  WHERE status != 'shipped';

Такой индекс в разы меньше полного и обновляется только тогда, когда меняются действительно интересующие вас строки. Похожая идея — покрывающий индекс с INCLUDE: он позволяет получить дополнительные колонки прямо из индекса, без обращения к самой таблице (heap), превращая обычный Index Scan в Index Only Scan:

CREATE INDEX idx_orders_customer_covering
  ON orders (customer_id)
  INCLUDE (order_date, amount);

Если типичный запрос выбирает только customer_id, order_date и amount, планировщику больше не нужно ходить в heap за остальными колонками строки — при условии, что страница уже отмечена как полностью видимая в visibility map.

Индексы тоже страдают от bloat

Как и таблицы, B-tree индексы накапливают «мёртвые» записи при частых обновлениях и удалениях. В отличие от таблиц, у индексов нет отдельного autovacuum-процесса с тем же уровнем контроля — обычный VACUUM чистит и индексы тоже, но при экстремально высокой частоте обновлений индекс может раздуваться быстрее, чем успевает очищаться. Симптом — размер индекса заметно больше, чем можно было бы ожидать по количеству уникальных значений:

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

Лечится это через REINDEX CONCURRENTLY — команда пересоздаёт индекс заново без длительной блокировки таблицы, в отличие от обычного REINDEX.

Как проверить, используется ли индекс вообще

SELECT relname AS table_name,
       indexrelname AS index_name,
       idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

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

Коротко

• Индекс ускоряет чтение, но замедляет запись — это всегда компромисс.
• Seq Scan вместо индекса — часто правильное решение планировщика, а не проблема.
• Составные, частичные и покрывающие индексы почти всегда эффективнее набора индексов «на каждый случай».
• Индексы тоже раздуваются — периодически проверяйте их размер и используйте REINDEX CONCURRENTLY.
• Периодически проверяйте idx_scan и убирайте то, что не используется.