Почему PostgreSQL не использует мой индекс для поиска текстового префикса при определенных сопоставлениях?

Рассмотреть возможность:

create table tab (foo text not null);
create index tab_ix_foo on tab(foo);

select * from tab where foo like 'pre%';

Postgres не использует индекс для этого поиска. При использовании сопоставления "POSIX" Postgres использует индекс: http://sqlfiddle.com/#!12/ed1cc/1

При использовании сопоставления "en_US" Postgres использует последовательное сканирование: http://sqlfiddle.com/#!12/efb18/1

В чем разница?


postgresql indexing prefix postgresql-9.2 postgresql-performance
person jameshfisher    schedule 24.03.2013    source источник
comment
Учитывая, что это пустая таблица, не могли бы вы предоставить что-нибудь, что предполагает, что такой индекс используется или не используется с таблицей, когда в ней много записей? Этот пример совершенно бесполезен в отношении проблем с индексами, потому что нет плана, превосходящего последовательное сканирование одной страницы.   -  person Chris Travers    schedule 24.03.2013
comment
@ChrisTravers Хороший вопрос. eegg, опубликуйте, пожалуйста, EXPLAIN ANALYZE из настоящих проблемных запросов.   -  person Craig Ringer    schedule 24.03.2013
comment
Скрипты обновлены для вставки данных перед запуском EXPLAIN.   -  person jameshfisher    schedule 24.03.2013

Ответы (1)


arrow_upward
4
arrow_downward

При использовании локалей, отличных от C (т. е. POSIX), вам необходимо создать свои индексы для поиска текста с префиксами LIKE и ~ с использованием класса операции text_pattern_ops. См. документацию по классам операторов и типы индексов. Я уверен, что есть лучшая ссылка, чем эта страница документации, но я не могу найти ее в данный момент.

Если я изменю ваш SQLFiddle, чтобы использовать text_pattern_ops для индекса en_US , вы увидите, что он может использовать индекс :

create index tab_ix_foo on tab using btree (foo collate "en_US" text_pattern_ops);
--                                                              ^^^^^^^^^^^^^^^^

Вполне вероятно, что вам потребуется создать разные индексы для разных сопоставлений, если вы используете параметр COLLATE в версии 9.2+, поскольку по определению разные сопоставления подразумевают разный порядок строк и, следовательно, разную организацию b-дерева. Похоже, вы уже делаете это в своих тестах.

Также возможно, что ваши данные слишком малы для использования индекса, чтобы быть особенно полезным. Попробуйте протестировать более полезный объем данных.

Этот пост может быть полезен , а также документы по поддержке сортировки.

Почему вы не можете просто использовать один и тот же индекс b-дерева для разных сопоставлений, учтите, что b-деревья требуют стабильного и согласованного порядка, но:

regress=> SELECT ' Bill''s' > ('bills' COLLATE "POSIX");
 ?column? 
----------
 f
(1 row)

regress=> SELECT ' Bill''s' > ('bills' COLLATE "en_US");
 ?column? 
----------
 t
(1 row)

Как видите, сопоставление меняет порядок сортировки. Это то, что делает сопоставление, в значительной степени по определению. Попытка использовать один и тот же индекс для разных сопоставлений подобна попытке использовать один и тот же функциональный индекс для разных функций; это не имеет абсолютно никакого смысла.

person Craig Ringer    schedule 24.03.2013
comment
В этом сообщении предполагается, что индекс не может быть по всему столбцу: stackoverflow.com/questions/9213816/ - person Tim; 24.03.2013
comment
@Tim Нет необходимости ограничивать длину или индекс выражения, но есть обстоятельства, в которых это может быть полезно — это помогает ограничить размер индекса и иногда позволяет выполнять более быстрые запросы в обмен на необходимость более сложные запросы, так как ваш запрос должен использовать точно то же выражение, что и индекс. - person Craig Ringer; 24.03.2013
comment
Я не чувствую, что мне нужно что-то, кроме индекса btree, для поиска по префиксу. Я не думаю, что сопоставление должно иметь отношение к поиску префиксов - если предположить, что все сопоставления определяют отношение упорядочения, которое группирует префиксы вместе (возможно, это предположение является моим неверным). - person jameshfisher; 24.03.2013
comment
B-деревья @eegg полагаются на стабильный порядок; использование b-дерева, созданного для сопоставления C в сопоставлении en_US, нарушило бы этот порядок, поскольку некоторые последовательности, которые раньше были раньше других, вместо этого будут позже. - person Craig Ringer; 24.03.2013