"Chris Cox" <cjcox@optushome.com.au> 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.08rows=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.08rows=5 width=32) Index Cond: (lower((f1)::text) = 'z'::text) Filter: (lower((f1)::text) ~~
'z'::text)
(3 rows)
regards, tom lane