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

[Jesse Vincent]

On Mar 30, 2004, at 2:29 PM, Palle Girgensohn wrote:

> Hi,
>
> With RT & the latest (0.99) DBIx-SearchBuilder, clicking "Home" takes 
> approximately 20 second or more. Most of the time is spent running 
> this query:
>
> ~21 seconds... this is because of the ILIKE:s. I have postgresql 
> intialized with LC_ALL=sv_SE.ISO8859-1. Indices are not used in 
> postgresql for "LIKE" when the database collation order is not of 
> locale 'C'. I usually want this, since I need sorting to be localized.
>
> Looking at the query above, I can't see that ILIKE cannot be replaced 
> with `=' in all cases. I'd say the way it is done now is a bug. I 
> cannot find my way around the RT code, so I'd appreciate some help 
> doing this.
>
> Here's the same query without the ILIKE:s:
>
> Postgresql-7.4.2
> FreeBSD 4.9 stable
> DBIx-SearchBuilder 0.99
> Perl 5.8.3
>
> 0,1 s, pretty darn much better... ;-)
>
> So, in short, I'd like to exterminate unnecessesary ILIKEs. In this 
> example, all are.
>

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAGH!

So, we need to do case-insensitive comparisons.  Every time a postgres 
hacker looks at this issue, they tell me something different.

What is the right way to do fast case-insensitive comparisons in 
postgres that take database indexes into account?



> Any ideas how to pursue this?
>
> /Palle
>
> _______________________________________________
> rt-devel mailing list
> rt-devel at lists.bestpractical.com
> http://lists.bestpractical.com/mailman/listinfo/rt-devel
>