[rt-devel] Postgres performance.

[Aidan Mountford]

Yep - Can do....

The original query looks like this....

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 = '14'))
         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 = '14')
                        OR (Groups_2.Domain = 'RT::Ticket-Role'
                            AND Groups_2.Instance = '135'))
                        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

The above took 59 seconds on our production DB (and returned 10 rows)

Without much effort I did this up with..

SELECT DISTINCT main.*
   FROM  (((((Users main join Principals Principals_1 on main.id =
Principals_1.id)
             join CachedGroupMembers CachedGroupMembers_7 on Principals_1.id
= CachedGroupMembers_7.MemberId)
            join Principals Principals_3 on Principals_1.id =
Principals_3.id)
           join CachedGroupMembers CachedGroupMembers_6 on
CachedGroupMembers_6.MemberId = Principals_3.Id)
          join Principals Principals_4 on CachedGroupMembers_6.GroupId =
Principals_4.Id),
         Groups Groups_2,
         ACL ACL_5
   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 =
'14'))
          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 = '14')
                        OR (Groups_2.Domain = 'RT::Ticket-Role'
                            AND Groups_2.Instance = '135'))
                        AND Groups_2.Type = ACL_5.PrincipalType
                        AND Groups_2.Id = Principals_4.id
                        AND Principals_4.PrincipalType = 'Group'))
   ORDER BY main.Name ASC


This ran in about 6 seconds. (and returned the same 10 rows)

And then I tried a little harder...

SELECT DISTINCT main.*
   FROM  ((((((Users main join Principals Principals_1 on main.id =
Principals_1.id)
               join CachedGroupMembers CachedGroupMembers_7 on
Principals_1.id = CachedGroupMembers_7.MemberId)
              join Principals Principals_3 on Principals_1.id =
Principals_3.id)
             join CachedGroupMembers CachedGroupMembers_6 on
CachedGroupMembers_6.MemberId = Principals_3.Id)
            join Principals Principals_4 on CachedGroupMembers_6.GroupId =
Principals_4.Id)
           join Groups Groups_2 on Principals_4.id = Groups_2.Id),
          ACL ACL_5
   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 =
'14'))

          AND (   (ACL_5.PrincipalId = Principals_4.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 = '14')
                        OR (Groups_2.Domain = 'RT::Ticket-Role'
                            AND Groups_2.Instance = '135'))
                        AND Groups_2.Type = ACL_5.PrincipalType
                        AND Principals_4.PrincipalType = 'Group'))
   ORDER BY main.Name ASC

And this gave the same result in less than one second...

There is more than can be done with that ACL table - but I have had little
time to look at it

I hope this helps you guys...

A

-----Original Message-----
From: rt-devel-admin at lists.fsck.com
[mailto:rt-devel-admin at lists.fsck.com]On Behalf Of Jesse Vincent
Sent: Thursday, 3 April 2003 12:44 AM
To: Aidan Mountford
Cc: rt-devel at lists.fsck.com
Subject: Re: [rt-devel] Postgres performance.





On Wed, Apr 02, 2003 at 07:51:39PM +0930, Aidan Mountford wrote:
> Hello all,
>
> I am using RT with a pg backend.
>
> I am having some fairly serious issues relating to the performance of some
> of the queries built by the query builder.
>
> The worst issues seem to appear from an ACL lookup that occurs when
> responding/commenting.
>
> I have captured this query and note that it is not using joins.
>
> To prove the point, i manually optimised this query, and reduced the
> execution time from almost a  minute down to about 4 seconds.

Can you show us the unoptimized and optimized queries?


--
http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.
_______________________________________________
rt-devel mailing list
rt-devel at lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel