[rt-devel] Postgres performance.

[Greg Stark]

David Wheeler <david at kineticode.com> writes:

>  > Can you post (or send me) the results of "explain analyze" on this and the
>  > optimized query?
> 
> I can. Attached.

Ok.

So first of all, do you run vacuum and analyze on your database frequently? If
not you should run "vacuum full" and then start running both frequently. How
frequently depends on the amount of updates your database gets, but as often
as once an hour is not unusual.

The big gun to be sure everything is up-to-date for optimizing like this is:
 vacuum full analyze
which cleans up everything and gets accurate statistics.

The only bad guess postgres is making appears to be on a table called
CachedGroupMembers. Is it possible this table has received lots of updates
since the last time the database was analyzed?

That said, I don't think that's the main difference between the original query
and your optimized one. I think a big problem is that postgres isn't using an
index on groups. It seems to be having trouble discerning the the groups_2.Id
= Principals_4.id is on both sides of the OR and can thus be pulled out to do
an index lookup.

I'm confused by why this isn't happening because some testing shows postgres
is indeed capable of doing this type of rewriting. 

Out of curiosity, what happens if you run 

 explain analyze 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 (
		   (
                     Principals_4.id = Groups_2.Id
                     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.Id = Principals_4.id
                     AND ( 
		              Groups_2.Type = ACL_5.PrincipalType
		          AND Principals_4.PrincipalType = 'Group'
                          AND ( (Groups_2.Domain = 'RT::Queue-Role' AND Groups_2.Instance = '14') OR (Groups_2.Domain = 'RT::Ticket-Role' AND Groups_2.Instance = '135'))
		         )
                   )
              )
          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;


--
greg