[SQL] LIKE on index not working

[Chris Cox]

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?

Thanks for your help!

Chris



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] LIKE on index not working

[Chris Cox]

Hi Peter,

Thanks for the tip.  The locale we're using is en_US.UTF-8.  From my limited
knowledge of locales, that's a non-C one isn't it?

Am I right in saying that to fix it I need to initdb again with a C locale?
How do I go about doing that on an environment with some 132 databases?

What a pain!

Chris

- Original Message - 
From: "Peter Eisentraut" <[EMAIL PROTECTED]>
To: "Chris Cox" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, July 22, 2004 10:41 PM
Subject: Re: [SQL] LIKE on index not working


> Am Donnerstag, 22. Juli 2004 09:38 schrieb Chris Cox:
> > For some reason I just can't get this to use the index for the following
> > query.  I'm using PostgreSQL 7.3.4.
>
> In 7.3, LIKE cannot use an index unless you set the locale to C.  In 7.4,
LIKE
> can use an index, but it has to be a different kind of index, as explained
> here:
>
> http://www.postgresql.org/docs/7.4/static/indexes-opclass.html
>
> -- 
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match