Какие минусы могут быть от использования индексов

Что проверяют

Понимаете ли вы, что индекс это сделка: чтение оплачивается записью, диском и обслуживанием. Следом почти всегда идёт продолжение: как найти лишние индексы и почему планировщик иногда игнорирует подходящий.

Как отвечать

Перечислите цену по пунктам:

• запись дорожает: INSERT и DELETE правят все индексы таблицы, UPDATE те, чьи колонки задеты. В PostgreSQL это ещё и лишний WAL, и потеря HOT-обновления: пока изменяемая колонка не входит ни в один индекс, новая версия строки остаётся на той же странице, а с индексом на этой колонке оптимизация выключается;
• место: индекс на большой таблице легко весит десятки процентов от неё, а пять индексов вместе занимают больше самих данных. Это и вытеснение полезного из кэша, и объём бэкапа;
• обслуживание: btree распухает при частых обновлениях и требует REINDEX CONCURRENTLY, автовакууму прибавляется работы;
• планирование: чем больше индексов, тем дольше перебор планов. На выборке, задевающей заметную долю таблицы, последовательное чтение дешевле случайных обращений, и отказ планировщика от индекса это правильное решение, а не баг;
• бесполезные индексы: на маленькой таблице, на колонке с низкой селективностью вроде is_active, дубли по префиксу (индекс по (a) не нужен, если есть по (a, b)), индекс, мимо которого запрос идёт из-за функции или приведения типа.

Подкрепите практикой: лишние видно по idx_scan в pg_stat_user_indexes, размер по pg_relation_size, факт применения по EXPLAIN (ANALYZE, BUFFERS). Частичный индекс с WHERE и покрывающий с INCLUDE часто дают ту же выгоду дешевле.

Где спотыкаются

Создают индексы «на всякий случай» и не проверяют статистику. Через год в таблице десяток индексов, половина не бралась ни разу, а запись просела вдвое.

Путаются в порядке колонок составного индекса. Индекс по (a, b) почти не помогает запросу, который фильтрует только по b, и такой промах в собеседовании ловят одним уточняющим вопросом.

Строят индекс на нагруженной таблице без CONCURRENTLY и блокируют запись на всё время сборки. А если прогон с CONCURRENTLY упал, забывают про оставшийся INVALID-индекс: место он занимает, запросам не помогает.