[rt-devel] RT 3.0rc1 - "Ticket Create" slow load issue RESOLVED

[Matthew J. Draper]

> Drop both 2 and 3. Tell me how that does. (That change made 
> it into the repo post cutting rc2.
Okay, I've done this, but it's still taking just as long. Adding
separate indices on groups.domain and groups.instance seem to provide
some improvement to the cost (down to low 400s), according to EXPLAIN,
but only reduced the query runtime by about 3 seconds (the output below
is from prior to adding those indices).

DEBUG:  StartTransactionCommand
LOG:  query: SELECT DISTINCT main.* FROM Users main, Principals
Principals_1, Groups Groups_2, Principals Principals_3, ACL ACL_4,
CachedGroupMembers CachedGroupMembers_5   WHERE ((ACL_4.RightName =
'SuperUser')OR(ACL_4.RightName = 'OwnTicket')) AND
((Principals_1.Disabled = '0')) AND ((Principals_1.PrincipalType =
'User')) AND (ACL_4.ObjectType = 'RT::System'  OR (ACL_4.ObjectType =
'RT::Queue'  AND ACL_4.ObjectId = '19') ) AND ( Principals_3.id =
Groups_2.id  AND (ACL_4.PrincipalId = Principals_3.Id  AND
ACL_4.PrincipalType = 'Group' AND (Groups_2.Domain = 'SystemInternal' OR
Groups_2.Domain = 'UserDefined' OR Groups_2.Domain = 'ACLEquivalence'))
OR ( ( (Groups_2.Domain = 'RT::Queue-Role' AND Groups_2.Instance = '19')
OR ( Groups_2.Domain = 'RT::Ticket-Role' AND Groups_2.Instance = '682')
)  AND Groups_2.Type = ACL_4.PrincipalType AND
Principals_3.PrincipalType = 'Group') )  AND main.id = Principals_1.id
AND CachedGroupMembers_5.MemberId = Principals_1.Id  AND
CachedGroupMembers_5.GroupId = Principals_3.Id  ORDER BY main.Name ASC
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
LOG:  QUERY STATISTICS
! system usage stats:
!       102.463478 elapsed 95.370000 user 6.890000 system sec
!       [102.140000 user 7.750000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       623/455 [824/2297] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [0/0] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:        190 read,          1 written, buffer hit
rate = 98.98%
!       Local  blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Direct blocks:          0 read,          0 written

Matthew