Re: Help with optimizing a sql statement

On Thu, 2006-02-09 at 18:22 -0500, Tom Lane wrote:
> 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
>
[............]

This is an application that we have not programmed, so I am not sure
what they are trying to do here. I will contact the developers. Tomorrow
I will try to test some of your suggestions.

> 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.
>

We are running 7.4.8 in this server and will upgrade to 8.0.6 in a few
weeks.

Thanks.
--
Rafael Martinez, <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/