text_pattern_ops index not being used for prefix query

Поиск
Список
Период
Сортировка
От Alexander Staubo
Тема text_pattern_ops index not being used for prefix query
Дата
Msg-id 8C473FB8-9487-496D-9747-B4964CB3BD4C@purefiction.net
обсуждение исходный текст
Ответы Re: text_pattern_ops index not being used for prefix query  (Maxim Boguk <maxim.boguk@gmail.com>)
Список pgsql-general
Indexing on a text column:

    create index index_documents_on_id_with_pattern_ops (id text_pattern_ops);

This works fine:

    > explain select id from documents where id like 'dingbat%';
    Index Only Scan using index_documents_on_id_with_pattern_ops on documents  (cost=0.56..8.58 rows=736 width=19)
      Index Cond: ((id >= 'dingbat'::text) AND (id < 'dingbau'::text))
      Filter: (id ~~ 'dingbat%'::text)

But for some reason, if an underscore character appears in my search string, it falls back to a disasterously slow
seqscan:

    > explain select id from documents where id like '_dingbat%';
    Seq Scan on documents  (cost=0.00..779238.28 rows=736 width=19)
      Filter: (id ~~ '_dingbat%'::text)

Is this because of PostgreSQL’s collation system? Using “C” doesn’t work either.



В списке pgsql-general по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: Showing matrix with single query
Следующее
От: Maxim Boguk
Дата:
Сообщение: Re: text_pattern_ops index not being used for prefix query