From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Cox <cjcox@optushome.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: LIKE on index not working
Date: Thu, 22 Jul 2004 13:10:38 -0400
Message-ID: <10467.1090516238@sss.pgh.pa.us> (raw)
In-Reply-To: <030e01c46fbe$e8baeb90$5100000a@PHOENIX>
References: <030e01c46fbe$e8baeb90$5100000a@PHOENIX>
"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
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: tgl@sss.pgh.pa.us, cjcox@optushome.com.au
Subject: Re: LIKE on index not working
In-Reply-To: <10467.1090516238@sss.pgh.pa.us>
* 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