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

[Jesse Vincent]

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


> - 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
-- 
http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.