Re: Help with optimizing a sql statement

Rafael Martinez Guerrero <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no> writes:
> WHERE ((ACL_2.RightName = 'OwnTicket'))
> AND ((CachedGroupMembers_4.MemberId = Principals_1.id))
> AND ((Groups_3.id = CachedGroupMembers_4.GroupId))
> AND ((Principals_1.Disabled = '0') or (Principals_1.Disabled = '0'))
> AND ((Principals_1.id != '1'))
> AND ((main.id = Principals_1.id))
> AND ( ( ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType =
> 'Group' AND ( Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain =
> 'UserDefined' OR Groups_3.Domain = 'ACLEquivalence')) OR ( (
> (Groups_3.Domain = 'RT::Queue-Role' ) ) AND Groups_3.Type
> =ACL_2.PrincipalType) )
> AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue')
> )

Are you sure this WHERE clause really expresses your intent? It seems
awfully oddly constructed. Removing the redundant parens and clarifying
the layout, I get

WHERE ACL_2.RightName = 'OwnTicket'
AND CachedGroupMembers_4.MemberId = Principals_1.id
AND Groups_3.id = CachedGroupMembers_4.GroupId
AND (Principals_1.Disabled = '0' or Principals_1.Disabled = '0')
AND Principals_1.id != '1'
AND main.id = Principals_1.id
AND ( ( ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = 'Group' AND
(Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain = 'UserDefined' OR Groups_3.Domain = 'ACLEquivalence') )
OR
( Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Type = ACL_2.PrincipalType )
)
AND (ACL_2.ObjectType = 'RT::System' OR ACL_2.ObjectType = 'RT::Queue')

That next-to-last major AND clause seems a rather unholy mix of join and
restriction clauses; I wonder if it's not buggy in itself. If it is
correct, I think most of the performance problem comes from the fact
that the planner can't break it down into independent clauses. You
might try getting rid of the central OR in favor of doing a UNION of
two queries that comprise all the other terms. More repetitious, but
would likely perform better.

BTW, what PG version is this? It looks to me like it's doing some
manipulations of the WHERE clause that we got rid of a couple years ago.
If this is 7.4 or older then you really ought to be thinking about an
update.

regards, tom lane