[rt-users] Poor performance in upgrade from RT2 to RT3

[Ruslan U. Zakirov]

Jesse Vincent wrote:

>
>On Wed, Sep 17, 2003 at 10:56:42AM -0600, Carl Gibbons wrote:
>  
>
>>Thanks for the replies. I have since upgraded to RT 3.0.5, and I have 
>>tuned a few more things in postgresql.conf.  Still very slow.  I set the 
>>following in postgresql.conf:
>>
>>log_statement = true
>>log_duration = true
>>log_timestamp = true
>>
>>to reveal the inefficient RT3 query, copied below. I suspect that some 
>>"create index..." hacking might improve this horrible query, but I'm not 
>>a DBA and I don't know how to analyze this one. Would somebody on this 
>>list please help me figure out what to do about this?
>>    
>>
>
>Ok. It appears that the fix for infinite looping in postgres with
>DBIx::SearchBuilder 0.92 broke some of the optimization we do there.
>:/
>
>	Jesse
>  
>
This request is one of major perfomance issues for me now.
Search by requestor email is slow under mySQL too, but if I add another 
email then mySQL hangs for ages.
It's very offten when our workers do misstake in email and then without 
deleting condition add another one and mySQL try to do its job. This 
search locks user's session for long time(~15 hours, one of this 
requests has finished :-) ) Same things happen with other search 
conditions when you add another one of same type and they joins on OR 
clause.
                Best regards. Ruslan.

>
>  
>
>>- Carl
>>
>>2003-09-17 16:19:16 LOG:  query: SELECT DISTINCT main.* FROM Tickets 
>>main , Groups Groups_1, Principals Principals_2, CachedGroupMembers 
>>CachedGroupMembers_3, Users Users_4  WHERE ((main.EffectiveId = 
>>main.id)) AND ((main.Type = 'ticket')) AND ( (  ( 
>>(lower(Users_4.EmailAddress) = 'cgibbons at du.edu')AND(Groups_1.Domain = 
>>'RT::Ticket-Role')AND(Groups_1.Type = 
>>'Requestor')AND(Principals_2.PrincipalType = 'Group') )  ) AND ( 
>>(main.Status = 'new')OR(main.Status = 'open') ) )  AND main.id = 
>>Groups_1.Instance  AND Groups_1.id = Principals_2.ObjectId  AND 
>>Principals_2.id = CachedGroupMembers_3.GroupId  AND 
>>CachedGroupMembers_3.MemberId = Users_4.id  ORDER BY main.Priority DESC 
>>LIMIT 10
>>2003-09-17 16:20:27 LOG:  duration: 71.308111 sec
>>    
>>