[rt-users] Upgrade from 3.0.11 to 3.4.4, owner query time out

Andre Sachs asachs at clue.co.za
Mon Jan 23 08:00:20 EST 2006

Hi all,

I have a largish RT installation (+- 200K tickets) that I am in the  
process of upgrading (3.0.11 -> 3.4.4).
Having had a look at pervious posts to the list I see that other have  
run into the same issue.

The query that is causing fastcgi to timeout :

SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL  
ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4   
WHERE ((ACL_2.RightName = 'OwnTicket')) AND  
((CachedGroupMembers_4.MemberId = Principals_1.id)) AND ((Groups_3.id  
= CachedGroupMembers_4.GroupId)) AND ((Principals_1.Disabled = '0')or 
(Principals_1.Disabled = '0')) AND ((Principals_1.id != '1')) AND  
((main.id = Principals_1.id)) AND  ( (    ACL_2.PrincipalId =  
Groups_3.id AND ACL_2.PrincipalType = 'Group'   AND  
(   Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain =  
'UserDefined' OR Groups_3.Domain = 'ACLEquivalence'))  OR  
( ( (Groups_3.Domain = 'RT::Queue-Role' )  )  AND Groups_3.Type =  
ACL_2.PrincipalType) )  AND (ACL_2.ObjectType = 'RT::System' OR   
(ACL_2.ObjectType = 'RT::Queue') ) ORDER BY main.Name ASC;

The cost of this query :

  Unique  (cost=499689.57..499690.27 rows=8 width=2692)

My mistake was setting the initial instance up with rights for  
Everyone to Create Ticket and Reply To Ticket. Is it possible to  
reverse this from a database perspective as I need to only see the  
local support agents in the owner drop down (all the agents have  
email address in the same domain). Any ideas appreciated.

I understand that the common wisdom is to upgrade to 3.4.5, I am  
however not sure that it will deliver the result I require.

Thanks and regards,

Andre Sachs
IT Architect, Clue Technologies PTY (LTD)
email: andre at clue.co.za

More information about the rt-users mailing list