[rt-devel] postgres query optimising (was: Re: [rt-users] Is this slow? It seems slow.)

[Jamie Wilkinson]

This one time, at band camp, Jesse Vincent wrote:
>RT runs on Mysql 3, whichi doesn't support UNION, which is one reason
>I'm leery of switching to something that requires a UNION in the middle
>of a stable series.

I've got two queries now, derived from the one that is being generated, that
returns what looks to be the same results from the database.  Unfortunately
I'm coming at a few brick walls trying to implement either of them in
WhoHasRight.

So, attached are the two queries.  The first uses a union, the second
removes the joins.

My gut feeling is that the query as being constructed *isn't* totally
correct -- the duplicated aliases and joins seem to be confusing the
problem.  Each half of the WhichGroup OR check_roles block (see the code to
WhoHaveRight for the naming) isnt' actually related to each other, but
because they're both using the same aliases compounded with the 4 way join,
Postgres is treating it like either half depends on the contents of the
other, and so it's slow.  When the OR is broken up explicitly with the
UNION, or by removing the JOINs, Postgres can make some better decisions
about the query.  (disclaimer: I am only guessing here, may be talking out
of my arse -- but the observations seem to hold)

The union select runs much faster than the no-JOIN query, so I'd prefer the
final implementation to use that one.  As for mysql support, the union
select could be implemented in DBIx::Searchbuilder simply by running the two
queries and concatenating the results in perl code.

So, the way forward looks like supporting UNION in SearchBuilder, and then
using that functionality in RT.

-- 
jaq at spacepants.org                           http://spacepants.org/jaq.gpg
-------------- next part --------------
explain analyze
                                                                                
SELECt dISTINCT main.* FROM
 (
  (
   (
    (
     Users main
     JOIN Principals as Principals_1 ON (main.id = Principals_1.id)
    )
    JOIN CachedGroupMembers as CachedGroupMembers_5 ON (Principals_1.id = CachedGroupMembers_5.MemberId)
   )
   JOIN CachedGroupMembers as CachedGroupMembers_6 ON (Principals_1.id = CachedGroupMembers_6.MemberId)
  )
  JOIN Principals as Principals_3  ON (CachedGroupMembers_5.GroupId = Principals_3.id)
 ),
 Groups Groups_2,
 ACL ACL_4
WHERE
 (
                                                                                

  (ACL_4.RightName = 'SuperUser')
  OR
  (ACL_4.RightName = 'OwnTicket')
 )
 AND
 (
  (CachedGroupMembers_6.GroupId = '4')
 )
 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 = 5
  )
 )
 AND
 (
   ACL_4.PrincipalId = Principals_3.id
   AND
   Principals_3.id = Groups_2.id
   AND
   ACL_4.PrincipalType = 'Group'
   AND
   (
    Groups_2.Domain = 'SystemInternal'
    OR
    Groups_2.Domain = 'UserDefined'
    OR
    Groups_2.Domain = 'ACLEquivalence'
   )
  )
  )
 AND
 Principals_1.id = main.id

union



SELECT DISTINCT main.* FROM
 (
  (
   (
    (
     Users main
     JOIN Principals as Principals_1 ON (main.id = Principals_1.id)
    )
    JOIN CachedGroupMembers as CachedGroupMembers_5 ON (Principals_1.id = CachedGroupMembers_5.MemberId)
   )
   JOIN CachedGroupMembers as CachedGroupMembers_6 ON (Principals_1.id = CachedGroupMembers_6.MemberId)
  )
  JOIN Principals as Principals_3  ON (CachedGroupMembers_5.GroupId = Principals_3.id)
 ),
 Groups Groups_2,
 ACL ACL_4
WHERE
 (
  (ACL_4.RightName = 'SuperUser')
  OR
  (ACL_4.RightName = 'OwnTicket')
 )
 AND
 (
  (CachedGroupMembers_6.GroupId = '4')
 )
 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 = 5
  )
 )
 AND
  (
   (
    (
     Groups_2.Domain = 'RT::Queue-Role'
     AND
     Groups_2.Instance = 5
    )
    OR
    (
     Groups_2.Domain = 'RT::Ticket-Role'
     AND
     Groups_2.Instance = 388
    )
   )
   AND
   Groups_2.Type = ACL_4.PrincipalType
   AND
   Groups_2.id = Principals_3.id
   AND
   Principals_3.PrincipalType = 'Group'
  )
 AND
 Principals_1.id = main.id
                                                                                
;
-------------- next part --------------
select distinct users.* from

 users,
 groups,
 principals,
 acl,
 cachedgroupmembers

where

 cachedgroupmembers.groupid = 4
 and
 cachedgroupmembers.memberid = principals.id
 and                                                                            
 acl.rightname in ('SuperUser', 'OwnTicket')
 and
 principals.disabled = 0
 and
 principals.principaltype = 'User'
 and
 (
  acl.objecttype = 'RT::System'
  or
  (acl.objecttype = 'RT::Queue'
   and
   acl.objectid = 4
  )
 )
 and
 principals.id = users.id
 and
 (
  (
   (groups.domain = 'RT::Ticket-Role' and groups.instance = 388)
   or
   (groups.domain = 'RT::Queue-Role' and groups.instance = 5)
   and
   groups.type = acl.principaltype
   and
   groups.id = principals.id
   and
   principals.principaltype = 'Group'
  )
  or
  (
   acl.principalid = principals.id
   and
   principals.id = groups.id
   and
   acl.principaltype = 'Group'
   and
   groups.domain in ('SystemInternal', 'UserDefined', 'ACLEquivalence')
  )
 )
;