Thread: Does index based on text with 'en_US.utf8' collation support like query?

Hi,

I found that queries like "select * from tbl where col1 like 'abc%'" will use index only when the col1(and the index)
use'C' collation. If I use 'default' which is 'en_US.utf8' in my database, the equation condition will use the index
butlike query will not. 

I also tried to specify the collation explicitly: like ('abc%' collate 'en_US.utf8'), not work too.

Is that only 'C' collation support 'like' query or I missed something in my sql? Or do I need to install something
additionalto support index on other collation? 

Thanks in advance.

Hello,

On Dec 25, 2012, at 1:37 PM, Haifeng Liu <liuhaifeng@live.com> wrote:

>
> Hi,
>
> I found that queries like "select * from tbl where col1 like 'abc%'" will use index only when the col1(and the index)
use'C' collation. If I use 'default' which is 'en_US.utf8' in my database, the equation condition will use the index
butlike query will not. 

This is a documented behavior, LIKE with a default operator class only works with C locale.

>
> I also tried to specify the collation explicitly: like ('abc%' collate 'en_US.utf8'), not work too.
>
> Is that only 'C' collation support 'like' query or I missed something in my sql? Or do I need to install something
additionalto support index on other collation? 

You need to add a new index with a special operator class called 'text_pattern_ops' (or 'varchar_pattern_ops), i.e:
CREATE INDEX col1_pattern_idx ON tbl(col1 text_pattern_ops);

This index will be used in LIKE queries under non-C locale. You also need to preserve your existing default (aka
text_ops)indexes if you want comparison or inequality operations on the col1 column to be indexed.  

Refer to http://www.postgresql.org/docs/current/static/indexes-opclass.html for more details.

--
Alexey Klyukin        http://www.commandprompt.com
The PostgreSQL Company – Command Prompt, Inc.