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!
(Here is the doc excerpt, from
http://www.postgresql.org/docs/8.2/interactive/locale.html :
"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.")