В каких случаях индексы работают неэффективно?

В каких случаях индексы работают неэффективно?

• на больших таблицах с частыми операциями insert/update/delete, поскольку обновление индексов создаёт дополнительные накладные расходы
• в запросах, фильтрующих по низкораспределённым или часто повторяющимся значениям, то есть имеющим низкую селективность
• чрезмерное количество индексов снижает производительность записи и требует больше места для хранения
• для запросов, затрагивающих значительную часть таблицы: с учётом затрат на чтение через индекс full table scan может оказаться быстрее
• индексы по столбцам, содержащим много NULL или повторяющихся значений, обычно дают небольшой эффект
• в сложных запросах с многочисленными соединениями и агрегациями планировщик может предпочесть полное сканирование таблицы
• для небольших таблиц: их полное чтение выполняется быстро, поэтому индекс не приносит ускорения

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

Подробный ответ

Основной ответ

Индексы перестают быть эффективными, если расходы на их поддержку и использование превышают ускорение, которое получают запросы. Они могут увеличивать время операций записи (INSERT, UPDATE, DELETE), а в небольших таблицах и при некоторых сценариях выполнения запросов их применение не всегда оправданно.

Ключевые моменты

• Небольшой объём данных: если строк в таблице мало, полное сканирование (full table scan) нередко оказывается быстрее индекса. Обращение к индексу добавляет overhead, связанный с последующим доступом к страницам данных.
• Высокая селективность: индекс приносит наибольшую пользу, когда условие отбирает небольшую долю записей. Если запрос возвращает существенную часть таблицы, например >30-40%, оптимизатор часто выбирает full scan как менее затратный по ресурсам вариант.
• Частые операции записи: после каждой вставки, модификации или удаления индекс требуется поддерживать в актуальном состоянии. Это увеличивает задержки и нагрузку, особенно при интенсивном потоке изменений.
• Индексы на низкокардинальных столбцах, например на булевых полях или полях с небольшим числом возможных значений, как правило, малоэффективны: они не сокращают объём сканирования в достаточной степени.
• Неадекватный выбор типа индекса — например, применение B-tree к неупорядоченным данным или частые запросы с LIKE и wildcard у начало строки вместо полнотекстового поиска вместо этого — снижает практическую пользу индекса.

Практический контекст

В PostgreSQL 14+ при разборе запросов рекомендуется изучать планы (EXPLAIN ANALYZE) и статистику селективности. В хранилищах с высокой нагрузкой на запись иногда применяют частичные индексы или сжатие, а также удаляют индексы с низкой эффективностью, чтобы поддерживать 99.9% uptime и сокращать задержки. Для OLAP-сценариев full scan часто предпочтительнее индексов, тогда как в OLTP обычно действует обратная ситуация.