From: Chris Browne <cbbrowne@acm.org>
Subject: Re: LIKE on index not working
Date: Thu, 22 Jul 2004 10:42:44 -0400
Organization: cbbrowne Computing Inc
Lines: 55
Message-ID: <60hds0gl23.fsf@dev6.int.libertyrms.info>
References: <030e01c46fbe$e8baeb90$5100000a@PHOENIX>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.110003 (No Gnus v0.3) XEmacs/21.4 (Security Through
	Obscurity, linux)
Cancel-Lock: sha1:ut3mW6q5cEo+YOBPsV/wv5h9ZN8=
To: pgsql-sql@postgresql.org

cjcox@optushome.com.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.