Re: LIKE on index not working

cjcox(at)optushome(dot)com(dot)au ("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.