Re: [SQL] LIKE on index not working
Am Donnerstag, 22. Juli 2004 09:38 schrieb Chris Cox: > For some reason I just can't get this to use the index for the following > query. I'm using PostgreSQL 7.3.4. In 7.3, LIKE cannot use an index unless you set the locale to C. In 7.4, LIKE can use an index, but it has to be a different kind of index, as explained here: http://www.postgresql.org/docs/7.4/static/indexes-opclass.html -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] LIKE on index not working
[EMAIL PROTECTED] ("Chris Cox") writes: > Hi all, > > For some reason I just can't get this to use the index for the following > query. I'm using PostgreSQL 7.3.4. > > Here's the details (let me know if you need anymore information to provide > any assistance): > > Indexes: person_pkey primary key btree (personid), > ix_person_active btree (bactive), > ix_person_fullname btree (tsurname, tfirstname), > ix_person_member btree (bmember), > ix_person_supporter btree (bsupporter), > ix_person_surname btree (lower(tsurname)) > > smartteamscouts=# explain analyze select * from person where bmember = 1 AND > lower(tsurname) like lower('weaver'); > QUERY PLAN > > --- > Seq Scan on person (cost=0.00..12946.58 rows=310 width=416) (actual > time=873.94..1899.09 rows=6 loops=1) >Filter: ((bmember = 1) AND (lower((tsurname)::text) ~~ 'weaver'::text)) > Total runtime: 1899.64 msec > (3 rows) > > smartteamscouts=# explain analyze select * from person where bmember = 1 AND > lower(tsurname) = lower('weaver'); > QUERY PLAN > > --- > Index Scan using ix_person_surname on person (cost=0.00..1265.78 rows=310 > width=416) (actual time=0.91..2.03 rows=6 loops=1) >Index Cond: (lower((tsurname)::text) = 'weaver'::text) >Filter: (bmember = 1) > Total runtime: 2.36 msec > (4 rows) > > As you can see, using the '=' operator it works just fine, but as soon as > the 'like' operator comes into it, no good. > > Is this a bug in 7.3.4? Or is it something else I need to adjust? A problem with this is that it needs to evaluate lower(tsurname) for each row, which makes the index pretty much useless. If you had a functional index on lower(tsurname), that might turn out better... create index ix_lower_surname on person(lower(tsurname)); -- "cbbrowne","@","acm.org" http://www3.sympatico.ca/cbbrowne/oses.html Make sure your code does nothing gracefully. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LIKE on index not working
"Chris Cox" <[EMAIL PROTECTED]> writes: > For some reason I just can't get this to use the index for the following > query. I'm using PostgreSQL 7.3.4. It works for me in 7.3.6 (see below). I'd guess that you are using a non-LIKE-safe locale setting --- can you get LIKE to use indexes at all? regression=# create table fooey(f1 varchar); CREATE TABLE regression=# create index fooeyi on fooey(lower(f1)); CREATE INDEX regression=# explain select * from fooey where lower(f1) = lower('z'); QUERY PLAN -- Index Scan using fooeyi on fooey (cost=0.00..17.08 rows=5 width=32) Index Cond: (lower((f1)::text) = 'z'::text) (2 rows) regression=# explain select * from fooey where lower(f1) like lower('z'); QUERY PLAN -- Index Scan using fooeyi on fooey (cost=0.00..17.08 rows=5 width=32) Index Cond: (lower((f1)::text) = 'z'::text) Filter: (lower((f1)::text) ~~ 'z'::text) (3 rows) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] LIKE on index not working
Hi Peter, Thanks for the tip. The locale we're using is en_US.UTF-8. From my limited knowledge of locales, that's a non-C one isn't it? Am I right in saying that to fix it I need to initdb again with a C locale? How do I go about doing that on an environment with some 132 databases? What a pain! Chris - Original Message - From: "Peter Eisentraut" <[EMAIL PROTECTED]> To: "Chris Cox" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, July 22, 2004 10:41 PM Subject: Re: [SQL] LIKE on index not working > Am Donnerstag, 22. Juli 2004 09:38 schrieb Chris Cox: > > For some reason I just can't get this to use the index for the following > > query. I'm using PostgreSQL 7.3.4. > > In 7.3, LIKE cannot use an index unless you set the locale to C. In 7.4, LIKE > can use an index, but it has to be a different kind of index, as explained > here: > > http://www.postgresql.org/docs/7.4/static/indexes-opclass.html > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match