Ahhh - I See. Thanks, Craig.
Although, once you've built the index with varchar_pattern_ops index,
the following two (essentially equivalent) queries will run at vastly
different speeds:
select * from A where A.value like 'Nacho';
select * from A where A.value = 'Nacho';
Seems that the optimizer should catch this...
C
-----Original Message-----
From: Craig Ringer [mailto:craig@postnewspapers.com.au]
Sent: Thursday, April 24, 2008 01:20
To: Christopher Condit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] initdb in 8.3
Christopher Condit wrote:
> I have a question related to this issue:
> Now that the locale has changed, it seems that the planner no longer
> wants to use the indexes for running LIKE queries on varchar columns
> unless I specify varchar_pattern_ops when creating the index. And if I
> create the index with varchar_pattern_ops, then the planner won't use
> it for = queries.
>
> What's the correct solution to this problem (when using UTF-8 and
> lc_collate and lc_ctype are both 1252)? Do I need to create two
indexes?
http://www.postgresql.org/docs/faqs.FAQ.html#item4.6
http://www.postgresql.org/docs/8.3/static/indexes-opclass.html
It should really also be mentioned in the section on the LIKE operator.
By the way, when I tried to leave a comment on the pattern matching
operator doc page I found that after logging in I was presented with a
blank comments form again. When I re-entered my comment and submitted I
got an error indicating that '' is not a valid NUMERIC. Going back to
the original comments form on the pattern matching operator doc page
(having logged in) and submitting there works fine.
--
Craig Ringer
