Can LIKE under utf8 use INDEXes?

Поиск
Список
Период
Сортировка
От Robert James
Тема Can LIKE under utf8 use INDEXes?
Дата
Msg-id e09785e00907220957r487000b2ubc8c244299e47b41@mail.gmail.com
обсуждение исходный текст
Ответы Re: Can LIKE under utf8 use INDEXes?
Re: Can LIKE under utf8 use INDEXes?
Re: Can LIKE under utf8 use INDEXes?
Список pgsql-general
Hi.  I'm confused about the behavior of LIKE under utf8 locale.
Accoding to the docs ( http://www.postgresql.org/docs/8.2/interactive/locale.html - excerpted below), it seems that LIKE ignores locale and hence can't use indexes.  Yet, EXPLAIN clearly shows it using indexes.
The docs suggest a workaround, to allow LIKE to use indexes - but I couldn't figure it out.  Although I'm stuck with locale utf8, all my data is 7-bit ascii.  I'm doing a tremendous amount of WHERE x LIKE 'abc%' - what's the best way to set up a good index?
(I can change the settings for this database - but the cluster must remain utf8).
Thanks!
"The drawback of using locales other than C or POSIX in PostgreSQL is its performance impact. It slows character handling and prevents ordinary indexes from being used by LIKE. For this reason use locales only if you actually need them. As a workaround to allow PostgreSQL to use indexes with LIKE clauses under a non-C locale, several custom operator classes exist. These allow the creation of an index that performs a strict character-by-character comparison, ignoring locale comparison rules. Refer to Section 11.8 for more information.")

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

Предыдущее
От: Justin Pasher
Дата:
Сообщение: Re: Best practices for moving UTF8 databases
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: psql \du [PATCH] extended \du with [+] - was missing