В каких ситуациях индекс может замедлить запрос на чтение

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

Знаете ли вы, что индекс помогает не всегда, и понимаете ли, как планировщик выбирает между индексом и чтением всей таблицы. Следом просят показать, как вы это обнаружите: EXPLAIN (ANALYZE, BUFFERS) и сравнение планов.

Как отвечать

Сначала отделите запись от чтения: обслуживание индексов при INSERT и UPDATE замедляет запись, а не выборку. Чтению индекс вредит в других случаях:

• низкая селективность: запрос забирает заметную долю таблицы, а обход индекса превращается в случайное чтение страниц. Последовательный проход дешевле, и планировщик это знает, пока статистика верна;
• устаревшая статистика: после массовой загрузки оценка строк расходится с фактом, и индексный план выбирается под сотню строк, а получает миллион. Лечится ANALYZE;
• индекс под ORDER BY ... LIMIT: для WHERE status = 'rare' ORDER BY created_at LIMIT 10 планировщик может пойти по индексу на created_at, надеясь быстро набрать десять строк, и перебрать миллионы, если редких строк мало;
• походы в таблицу: индексный поиск по многим строкам требует чтения heap за каждой. Index-only scan выручает, только если VACUUM успевает обновлять карту видимости;
• общий план подготовленного запроса: psycopg 3 и asyncpg используют prepared statements, и после нескольких выполнений PostgreSQL может перейти на общий план, плохой для редких значений параметра;
• раздутый индекс: он вытесняет из кеша страницы, нужные другим запросам.

В Django план смотрят через QuerySet.explain(), в SQLAlchemy выполняют EXPLAIN над скомпилированным запросом.

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

Говорят, что много индексов замедляют SELECT, потому что «их все нужно обновлять». Обновление касается записи.

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

Лечат плохой план удалением индекса, не проверив статистику и сам запрос.