Skip site navigation (1) Skip section navigation (2)

Re: LIKE on index not working

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Chris Cox" <cjcox(at)optushome(dot)com(dot)au>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: LIKE on index not working
Date: 2004-07-22 17:10:38
Message-ID: 10467.1090516238@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
"Chris Cox" <cjcox(at)optushome(dot)com(dot)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

In response to

pgsql-sql by date

Next:From: Stephan SzaboDate: 2004-07-22 17:27:00
Subject: Re: Problem with transaction in functions and tempory tables
Previous:From: Josh BerkusDate: 2004-07-22 17:05:59
Subject: Re: surrogate key or not?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group