--On tisdag, mars 30, 2004 19.16.44 -0500 Tom Lane <[EMAIL PROTECTED]> wrote:

Palle Girgensohn <[EMAIL PROTECTED]> 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



---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to