[rt-devel] extremely slow query in rt 3.0.10 due to ILIKE instead of =

[Palle Girgensohn]

--On tisdag, mars 30, 2004 14.46.11 -0500 Vivek Khera <vivek at khera.org> 
wrote:

>
> On Mar 30, 2004, at 2:38 PM, Jesse Vincent wrote:
>
>> What is the right way to do fast case-insensitive comparisons in
>> postgres that take database indexes into account?
>
> make your index functional on lower(column) and then compare
> lower(column) = lower('string').  don't just index on the column directly.

True. Note you should preferably compare with '=', unless you *really* need 
LIKE. ILIKE will still not use indices. lower(field)='value' will. 
lower(field) like 'value' will use index *only* if the database uses C 
locale for sorting (i.e. locale was set to `C' or unset when initdb was 
run). For RT, I guess this would be recommended, although for me it is a 
pain, since my other databases on this server need proper localized 
sorting. I guess I need to set up a separate database service for RT?

It is very strange that ILIKE does not use an index when lower() LIKE 
does...

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