From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help with optimizing a sql statement
Date: Thu, 09 Feb 2006 18:22:00 -0500
Message-ID: <5731.1139527320@sss.pgh.pa.us> (raw)
In-Reply-To: <1139497827.25608.1014.camel@bbking.uio.no>
References: <1139497827.25608.1014.camel@bbking.uio.no>
Rafael Martinez Guerrero <r.m.guerrero@usit.uio.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, r.m.guerrero@usit.uio.no
Subject: Re: Help with optimizing a sql statement
In-Reply-To: <5731.1139527320@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox