From: Chris Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: LIKE on index not working
Date: Thu, 22 Jul 2004 10:42:44 -0400
Message-ID: <60hds0gl23.fsf@dev6.int.libertyrms.info> (raw)
References: <030e01c46fbe$e8baeb90$5100000a@PHOENIX>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: LIKE on index not working
In-Reply-To: <60hds0gl23.fsf@dev6.int.libertyrms.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox