[rt-users] crazy sql during "reply" action

[Jamie Wilkinson]

Hi all,

I'm running RT 3.0.0 with Postgres 7.3.2.  It's running at an average speed,
but when I click on the Reply link to eply to a ticket, the database jumps
up to 100% CPU usage for about 15 minutes, before it finally returns and I
am presented wi th the Update ticket page for sending correspondance.

A bit of investigation showed that the database is choking on this SQL
query:

SELECT DISTINCT main.* 
FROM Users main, Principals Principals_1, Groups Groups_2,
     Principals Principals_3, Principals Principals_4, ACL ACL_5,
     CachedGroupMembers CachedGroupMembers_6,
     CachedGroupMembers CachedGroupMembers_7
WHERE ((ACL_5.RightName = 'SuperUser')OR(ACL_5.RightName = 'OwnTicket')) AND
      ((CachedGroupMembers_7.GroupId = '4')) AND
      ((Principals_1.Disabled = '0')) AND
      ((Principals_1.PrincipalType = 'User')) AND
      ((Principals_3.PrincipalType = 'User')) AND
      (ACL_5.ObjectType = 'RT::System'  OR 
          (ACL_5.ObjectType = 'RT::Queue'  AND ACL_5.ObjectId = '4')) AND
      ((ACL_5.PrincipalId = Principals_4.Id AND
        Principals_4.id = Groups_2.Id AND
        ACL_5.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 = '4') OR
         (Groups_2.Domain = 'RT::Ticket-Role' AND
          Groups_2.Instance = '376')) AND
        Groups_2.Type = ACL_5.PrincipalType AND
        Groups_2.Id = Principals_4.id AND
        Principals_4.PrincipalType = 'Group')) AND
      main.id = Principals_1.id AND
      Principals_1.id = CachedGroupMembers_7.MemberId AND
      main.id = Principals_3.id AND
      CachedGroupMembers_6.MemberId = Principals_3.Id AND
      CachedGroupMembers_6.GroupId = Principals_4.Id
ORDER BY main.Name ASC

Now, my first question is: What is this query trying to do?  I ran it
manually, it looks like it's trying to fill out the Owner dropdown box, i.e.
finding all the users who could possibly own this ticket.

My second question is: did you write this query by hand? :-)  It's quite
insane.

Finally, what can I do to speed this up?  15 minutes is incredibly
undesirable to wait to reply to a ticket, we may as well not bother using
RT.

I've already checked postgres's configuration for tuning, and checked the
indexes relating to this query and they seem okay.  I'm not sure that this
is a problem with the database, though, only this query.

-- 
jaq at spacepants.org                           http://spacepants.org/jaq.gpg