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

[Palle Girgensohn]

--On tisdag, mars 30, 2004 14.38.31 -0500 Jesse Vincent 
<jesse at bestpractical.com> wrote:

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

Ooops...

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

First, I just set up a postgresql cluster initdb:ed with locale=C. The 
query is down to 14 secs, so it is not that big a difference. I does not 
use indices differently, so I guess I was wrong, or they changed something.

In this specific example, it seems like all that is compared are ID:s 
(except the email). Are you really sure they need to be compared 
case-insensitive?

I remade the query with = except for Users_3.EmailAddress ILIKE 
'girgen at p...', and it is really fast, 0,1 s, and it returns all the same 
rows. I also remade the query using lower() = lower(), and it is OK, 0,5 s. 
This is really the postgresql planner making non-optimal assumptions, I 
guess. ILIKE between two db fields is really not a LIKE, it is a lower() = 
lower()... Postgresql should ideally understand this.

I'll ask the postgresql developers about this. We'll see what they say. I 
also found the lines in SearchBuilder that handle this. Changed between 
0.98_1 and 0.99... So, I can downgrade for now, I guess... Still, do you 
really need to match ID:s case-insensitive?

/Palle


SELECT DISTINCT main.* FROM Tickets main , Groups Groups_1,
CachedGroupMembers CachedGroupMembers_2, Users Users_3 WHERE
((CachedGroupMembers_2.MemberId = Users_3.id)) AND ((Groups_1.id
= CachedGroupMembers_2.GroupId)) AND ((main.EffectiveId =
main.id)) AND ((main.Status != 'deleted')) AND ((main.Type =
'ticket')) AND ((main.id = Groups_1.Instance)) AND ( ( (
(Users_3.EmailAddress ILIKE 'girgen at pingpong.net')AND(Groups_1.Domain
= 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor') ) ) AND (
(main.Status = 'new')OR(main.Status = 'open') ) ) ORDER BY
main.Priority DESC LIMIT 10