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

--On tisdag, mars 30, 2004 19.16.44 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

> Palle Girgensohn <girgen@pingpong.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