November 5, 2008

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

Перевод 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

2 comments:

Rick Kir said...

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

Это высказывание, Сергей, не совсем верное, поскольку таблица может быть велика ( у меня 100 млн) и поиск по ней осуществляется через inner join с другой таблицей ( 200 тысяч записей). Несмотря на то, что индексы есть на обеих, все равно оптимизатор отказывается использовать индекс. Поэтому здесь выход вижу только в партиционировании 100 миллионной таблицы.

Sergey Konoplev said...

Вариантов почему у вас не используются индексы может быть много. Если покажете запрос и схему, можем попробовать разобраться.

Post a Comment