Hello, On Dec 25, 2012, at 1:37 PM, Haifeng Liu <liuhaif...@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 but like 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 additional to 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. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin