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

--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