X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id CB9A69DC87F for ; Thu, 9 Feb 2006 19:21:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 57926-05 for ; Thu, 9 Feb 2006 19:22:02 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 78A369DC84A for ; Thu, 9 Feb 2006 19:21:57 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k19NM0Ys005732; Thu, 9 Feb 2006 18:22:00 -0500 (EST) To: Rafael Martinez Guerrero cc: pgsql-performance@postgresql.org Subject: Re: Help with optimizing a sql statement In-reply-to: <1139497827.25608.1014.camel@bbking.uio.no> References: <1139497827.25608.1014.camel@bbking.uio.no> Comments: In-reply-to Rafael Martinez Guerrero message dated "Thu, 09 Feb 2006 16:10:27 +0100" Date: Thu, 09 Feb 2006 18:22:00 -0500 Message-ID: <5731.1139527320@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108] X-Spam-Score: 0.108 X-Spam-Level: X-Archive-Number: 200602/90 X-Sequence-Number: 17076 Rafael Martinez Guerrero 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