Что такое селективность индекса и почему не всегда индекс используется?
Что проверяют
Понимаете ли вы, как планировщик выбирает между индексом и полным чтением таблицы, или считаете, что индекс включается сам, раз он есть. Следом почти всегда просят показать план запроса и объяснить, почему в нём Seq Scan, хотя индекс создан.
Как отвечать
Селективность это доля строк, которую отбирает условие. Условие id = 42 отбирает одну строку из миллиона, оно высокоселективное; status = 'active' при 90% активных записей почти ничего не отсекает. Оговорите терминологию: иногда высокой селективностью называют большое число уникальных значений.
Дальше про механику на примере PostgreSQL:
- стоимость: планировщик оценивает варианты по статистике из
pg_stats(n_distinct, частые значения, гистограмма). Чтение по индексу даёт случайные обращения к страницам, и при большой доле строк последовательное чтение выходит дешевле; - промежуточный вариант:
Bitmap Index Scanсобирает адреса строк по индексу и читает страницы по порядку; - устаревшая статистика: после массовой загрузки без
ANALYZEоценки врут, и план выбирается неверный; - условие не совпадает с индексом: функция над колонкой,
LIKE '%текст', неявное приведение типов, запрос без ведущей колонки составного индекса; - маленькая таблица: пара страниц читается целиком быстрее любого индекса.
Для Go добавьте практику: pgx по умолчанию кэширует подготовленные выражения, и после нескольких выполнений PostgreSQL может перейти на общий план, который не знает значений параметров. На перекошенных данных такой план берёт индекс там, где он вреден, или наоборот. Лечится настройкой plan_cache_mode или выбором QueryExecMode в pgx. Проверяется всё через EXPLAIN (ANALYZE, BUFFERS).
Где спотыкаются
Путают селективность с кардинальностью и не могут объяснить, почему индекс по колонке «пол» бесполезен, а по колонке с условием на редкое значение работает.
Называют причиной частые изменения таблицы. Запись из-за индекса замедляется, но планировщик от индекса по этой причине не отказывается.
Лечат план подсказками и отключением enable_seqscan вместо того, чтобы обновить статистику и сравнить оценку и реальное число строк в EXPLAIN ANALYZE.