[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]