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

[Andre Sachs]

>>
>> The upgrade to 3.4.5 improved the situation, the owner query now
>> returns results on the Query Builder page.
>> It is however still taking some time to complete the query - 108
>> seconds with a cost that looks like :
>>
>> Unique  (cost=26792.76..26794.16 rows=16 width=2692)
>>
>> To the meat of the question - at install time I setup RT to allow
>> Everyone "Create Ticket" and "Reply To Ticket". Needless to say this
>> was a mistake :)
>> The result is that RT has created lots of possible ticket owners.
>
> Neither of those implies "Own Ticket"  Perhaps you've granted  
> "Everyone"
> the right to Own tickets? Also, are you vacuuming your postgres  
> database
> regularly?

Everyone has rights to "CreateTicket" and "ReplyToTicket". The queues
themselves don't specify any additional rights for the Everyone  
group. The
database was freshly vacuumed before the above result was posted.

I not sure how the "OwnTicket" right has been set for all the  
requesters unless its something that happened historically in 3.0.11.

Here are the table row counts for the tables involved in the slow query:
acl:                                              833
cachedgroupmembers: 1829946
principals:                           861954
users:                                     52835

And the query that is take the time is :
SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL  
ACL_2, CachedGroupMembers CachedGroupMembers_3  WHERE  
((ACL_2.PrincipalId = CachedGroupMembers_3.GroupId)) AND  
((ACL_2.PrincipalType = 'Group')) AND ((ACL_2.RightName =  
'OwnTicket')) AND ((CachedGroupMembers_3.MemberId = Principals_1.id))  
AND ((Principals_1.Disabled = '0')) AND ((Principals_1.PrincipalType  
= 'User')) AND ((Principals_1.id != '1')) AND ((main.id =  
Principals_1.id)) AND ((ACL_2.ObjectType = 'RT::Queue') OR  
(ACL_2.ObjectType = 'RT::System'))    ORDER BY main.Name ASC;

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