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 7: don't forget to increase your free space map settings
Re: [SQL] LIKE on index not working
[EMAIL PROTECTED] ("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.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LIKE on index not working
"Chris Cox" <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] LIKE on index not working
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
