Почему селекты могут стать медленнее при большом количестве индексов
Что проверяют
Понимаете ли вы, что индекс это не бесплатное ускорение, а структура со своей ценой и на записи, и на чтении. Вопрос с подвохом: почти все знают про замедление вставок, а спрашивают именно про селекты, и ждут механизмов, а не общих слов про накладные расходы.
Как отвечать
Назовите конкретные причины.
Планирование. Оптимизатор перебирает способы доступа, и чем больше индексов на таблице, тем больше вариантов он рассматривает. У коротких запросов время планирования сравнимо с временем выполнения, и лишние индексы съедают его на каждом вызове.
Память. Кэш буферов один на всех. Индексы, которыми никто не пользуется, всё равно читаются и правятся при изменениях данных и вытесняют из памяти горячие страницы таблицы и нужных индексов. Запрос, который раньше попадал в кэш, начинает ходить на диск.
Ошибочный выбор плана. Несколько похожих индексов и устаревшая статистика приводят к тому, что планировщик берёт менее избирательный. Выборка заметной доли строк через индекс это случайные обращения к таблице, и она проигрывает последовательному чтению.
Раздувание таблицы. В PostgreSQL обновление строки, задевающее проиндексированную колонку, лишает оптимизации HOT: править приходится все индексы, растёт число мёртвых версий, автоочистка не успевает, и тот же селект читает больше страниц.
Дальше скажите, чем это лечат: искать неиспользуемые индексы в pg_stat_user_indexes, убирать одиночные, которые уже покрыты префиксом составного, и смотреть EXPLAIN (ANALYZE, BUFFERS) вместо догадок.
Где спотыкаются
Отвечают только про замедление вставок и обновлений. Это верно, но мимо вопроса: спрашивали про чтение, и без планирования, вытеснения кэша и раздувания ответ неполный.
Считают, что индекс всегда быстрее полного сканирования. При выборке большой доли таблицы последовательное чтение дешевле, и планировщик выбирает его намеренно, а не по ошибке.
Заводят индекс на каждую колонку из условий. Составной индекс закрывает запросы по своему префиксу, а десяток одиночных обычно означает, что план возьмёт один из них, а платить таблица будет за все.