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@xxxxxxxxxxxxxxxxxxxxx] Sent: Thursday, April 24, 2008 01:20 To: Christopher Condit Cc: pgsql-general@xxxxxxxxxxxxxx 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
