pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLIKE on index not working
5+ messages / 4 participants
[nested] [flat]
* LIKE on index not working
@ 2004-07-22 07:38 Chris Cox <cjcox@optushome.com.au>
2004-07-22 12:41 ` Re: LIKE on index not working Peter Eisentraut <peter_e@gmx.net>
2004-07-22 14:42 ` Re: LIKE on index not working Chris Browne <cbbrowne@acm.org>
2004-07-22 17:10 ` Re: LIKE on index not working Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 5+ messages in thread
From: Chris Cox @ 2004-07-22 07:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LIKE on index not working
2004-07-22 07:38 LIKE on index not working Chris Cox <cjcox@optushome.com.au>
@ 2004-07-22 12:41 ` Peter Eisentraut <peter_e@gmx.net>
2004-07-22 23:46 ` Re: LIKE on index not working Chris Cox <cjcox@optushome.com.au>
2 siblings, 1 reply; 5+ messages in thread
From: Peter Eisentraut @ 2004-07-22 12:41 UTC (permalink / raw)
To: Chris Cox <cjcox@optushome.com.au>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LIKE on index not working
2004-07-22 07:38 LIKE on index not working Chris Cox <cjcox@optushome.com.au>
2004-07-22 12:41 ` Re: LIKE on index not working Peter Eisentraut <peter_e@gmx.net>
@ 2004-07-22 23:46 ` Chris Cox <cjcox@optushome.com.au>
0 siblings, 0 replies; 5+ messages in thread
From: Chris Cox @ 2004-07-22 23:46 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
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/
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LIKE on index not working
2004-07-22 07:38 LIKE on index not working Chris Cox <cjcox@optushome.com.au>
@ 2004-07-22 14:42 ` Chris Browne <cbbrowne@acm.org>
2 siblings, 0 replies; 5+ messages in thread
From: Chris Browne @ 2004-07-22 14:42 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LIKE on index not working
2004-07-22 07:38 LIKE on index not working Chris Cox <cjcox@optushome.com.au>
@ 2004-07-22 17:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2004-07-22 17:10 UTC (permalink / raw)
To: Chris Cox <cjcox@optushome.com.au>; +Cc: pgsql-sql
"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.08 rows=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.08 rows=5 width=32)
Index Cond: (lower((f1)::text) = 'z'::text)
Filter: (lower((f1)::text) ~~ 'z'::text)
(3 rows)
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-07-22 23:46 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-22 07:38 LIKE on index not working Chris Cox <cjcox@optushome.com.au>
2004-07-22 12:41 ` Peter Eisentraut <peter_e@gmx.net>
2004-07-22 23:46 ` Chris Cox <cjcox@optushome.com.au>
2004-07-22 14:42 ` Chris Browne <cbbrowne@acm.org>
2004-07-22 17:10 ` Tom Lane <tgl@sss.pgh.pa.us>
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