Thread: LIKE on index not working
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
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/
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.
"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
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" <peter_e@gmx.net> To: "Chris Cox" <cjcox@optushome.com.au> Cc: <pgsql-sql@postgresql.org> 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/ >