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

Re: LIKE should use index when condition doesn't include

From: Palle Girgensohn <girgen(at)pingpong(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: LIKE should use index when condition doesn't include
Date: 2004-03-31 00:28:31
Message-ID: 162280000.1080692911@palle.girgensohn.se (view raw or flat)
Thread:
Lists: pgsql-performance

--On tisdag, mars 30, 2004 19.16.44 -0500 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 
wrote:

> Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
>> Shouldn't the optimizer use indices if the like condition does not have
>> any  wildcards?
>
> I can't get excited about this; if you are depending on LIKE to be fast
> then you should have locale-insensitive indexes in place to support it.
> Switching the tests around so that this special case is supported even
> with an index that doesn't otherwise support LIKE would complicate the
> code unduly IMHO, to support a rather pointless corner case...

OK, I agree. Sad, though, that throw away ability to use order by is the 
only way to get index scans using LIKE... :(

But what about ILIKE. It does not take advantage of indices built with 
lower():

girgen=# create index person_foo on person (lower(last_name));
girgen=# vacuum analyze person;
girgen=# explain select * from person where  lower(last_name) = 
'girgensohn';
                                 QUERY PLAN 

---------------------------------------------------------------------------
--
 Index Scan using person_foo on person  (cost=0.00..137.58 rows=78 width=96)
   Index Cond: (lower(last_name) = 'girgensohn'::text)
(2 rows)

girgen=# explain select * from person where  last_name = 'Girgensohn';
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on person  (cost=0.00..441.35 rows=4 width=96)
   Filter: (last_name = 'Girgensohn'::text)
(2 rows)

girgen=# explain select * from person where  lower(last_name) like 
'girgen%';
                                          QUERY PLAN 

---------------------------------------------------------------------------
-------------------
 Index Scan using person_foo on person  (cost=0.00..137.58 rows=78 width=96)
   Index Cond: ((lower(last_name) >= 'girgen'::text) AND (lower(last_name) 
< 'girgeo'::text))
   Filter: (lower(last_name) ~~ 'girgen%'::text)
(3 rows)

girgen=# explain select * from person where  last_name ilike 'girgen%';
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on person  (cost=0.00..441.35 rows=5 width=96)
   Filter: (last_name ~~* 'girgen%'::text)
(2 rows)


postgresql 7.4.2, freebsd 4.9 stable.


/Palle


In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2004-03-31 00:56:09
Subject: Re: LIKE should use index when condition doesn't include
Previous:From: Tom LaneDate: 2004-03-31 00:16:44
Subject: Re: LIKE should use index when condition doesn't include wildcard

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