From: | "Christopher Condit" <condit(at)sdsc(dot)edu> |
---|---|
To: | "Craig Ringer" <craig(at)postnewspapers(dot)com(dot)au> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: initdb in 8.3 |
Date: | 2008-04-24 13:51:30 |
Message-ID: | 845AADAC1106E44996327D62097E4C6B01025FE0@et.ad.sdsc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)postnewspapers(dot)com(dot)au]
Sent: Thursday, April 24, 2008 01:20
To: Christopher Condit
Cc: pgsql-general(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-04-24 14:02:16 | Re: initdb in 8.3 |
Previous Message | Martin Gainty | 2008-04-24 13:27:02 | Re: Stored procedures in C |