Почему мой индекс не используется

Перевод Why is my index not being used с Postgres OnLine Journal

"Почему мой индекс не используется?" - очень давний и, возможно, наиболее часто задаваемый даже опытными пользователями вопрос.

В этой статье мы кратко опишем основные причины и попробуем упорядочить их по статистической значимости.

Если кто не в курсе, то узнать о том, что ваш индекс используется или не используется можно с помощью инструкций EXPLAIN, EXPLAIN ANALYZE, или, например, продвинутого инструмента графического объяснения планов в PgAdmin.

Ок, допустим запрос не использует индекс. Почему и что можно с этим сделать?

Проблема: Устаревшая статистика.
Сейчас это менее вероятно в связи с тем, что по умолчанию auto-vacuum включён. Но если в таблице только что было обновлено/добавлено/удалено много записей, или был добавлен новый индекс, то есть вероятность, что во время выполнения запроса новая статистика ещё не соберётся.

Решение: vacuum analyze verbose ;
Я добавил verbose для того, что бы видеть что делает сборка мусора/статистики, и, если вы настолько же нетерпеливы как и я и ваша таблица довольно большая, то здорово видеть что что-то в самом деле происходит. Так же можно выполнить vacuum analyze verbose без указания таблицы, если необходимо провести профилактику всей БД.

Проблема: Планировщик решил, что последовательное сканирование быстрее чем индексное.
Это может случиться если а) ваша таблица относительно мала, или поле, по которому вы индексируете, содержит много дубликатов.

Решение: В случае использования, например boolean, когда 50% данных содержат одно значение, а вторая половина другое, индексирование будет не очень полезным. Однако, это неплохая причина для использования частичных индексов, например для индексирования только активных данных.

Проблема: Вы создали индекс, который не совместим с тем, как вы фильтруете данные. Есть ряд таких случаев.

1. LIKE '%me' никогда не будет использовать индексы, но можно использовать LIKE 'me%'.

2. Ловушка с upper/lower - если определить индекс как:

CREATE INDEX idx_faults_name ON faults USING btree(fault_name);

и выполнить такой запрос

SELECT * FROM faults where UPPER(fault_name) LIKE 'CAR%'

то индекс не будет использоваться. Тут необходимо создать индекс следующим образом:

CREATE INDEX idx_faults_name ON faults USING btree(upper(fault_name));

3. Этого не избежал даже я, всегда полезно почитать о проблемах других людей в новостных группах, т.к. вы узнаёте о вещах, о которых даже не подозревали. Если ваш сервер инициализирован с C-локалью, выше сказанное опять же не будет работать. Подобный вопрос недавно было в группе pgsql-novice, где Tom Lane дал подробный ответ, и это, по моему, затрагивает многих. Ситуацию возможно исправит:

CREATE INDEX idx_faults_uname_varchar_pattern ON faults USING btree(upper(fault_name) varchar_pattern_ops);

Однако, даже в этом случае может потребоваться учитывать описанное ниже:

Не совсем понятно является ли это проблемой кодировки БД, самих данных или разницы версий 8.3 b 8.2. Похоже, что в 8.3 в конкретных случаях с данными в кодировке UTF-8 требуется точное сравнение, однако в подобных ситуациях с 8.2 в SQL-ASCII достаточно varchar_pattern_ops как для точного сравнения так и для LIKE.

CREATE INDEX idx_faults_uname ON faults USING btree(upper(fault_name)); 

SELECT fault_name from  faults
WHERE upper(fault_name) IN('CASCADIA ABDUCTION', 'CABIN FEVER');

4. Ошибки новичков, когда делается что-нибудь типа создания индекса по дате и сравнение текста с этой датой приведённой к тексту.

Проблема: Не все индексы могу быть использованы.
Не смотря на то, что с версии 8.1+ поддерживаются Bitmap Index Scan, позволяющие множеству индексов быть использованными в запросе путём создания в памяти bitmap индексов, если индексов много, не ждите, что будут использоваться все ожидаемые. Иногда сканирование таблицы бывает эффективнее.

Проблема: Планировщик не идеален.
Решение: Рыдать и молиться о более светлых временах. На самом деле я был очень потрясён возможностями планировщика Postgresql в сравнении с другими СУБД. Некоторые говорят, если бы только тут были "хинты", я бы сделал это работающим быстрее. Я же думаю, что хинты это плохая идея и лучшим решением было бы сделать планировщик лучше. Проблема хинтов в том, что они отодвигают на второй план одну хорошую вещь, идею того, что СУБД знает состояние данных лучше чем человек и постоянно обновляет это знание. Хинты могут быстро стать не актуальными, в то время когда хороший планировщик будет постоянно менять стратегию по мере изменений в БД, и это то что делает программирование баз данных уникальным.

Leo Hsu and Regina Obe