X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 189385E46F4 for ; Thu, 19 Aug 2004 10:15:54 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 21927-10 for ; Thu, 19 Aug 2004 13:15:46 +0000 (GMT) Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id 12C445E46CA for ; Thu, 19 Aug 2004 10:15:43 -0300 (ADT) Received: (qmail 1636 invoked from network); 19 Aug 2004 13:15:47 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.36?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 19 Aug 2004 13:15:47 -0000 Subject: help with query From: Dave Cramer Reply-To: pg@fastcrypt.com To: perform Content-Type: text/plain Organization: Cramer Consulting Message-Id: <1092921433.1622.569.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Thu, 19 Aug 2004 09:17:13 -0400 Content-Transfer-Encoding: 7bit X-AntiVirus: scanned for viruses by AMaViS 0.2.1 (http://amavis.org/) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/287 X-Sequence-Number: 7923 RT uses a query like: SELECT distinct main.oid,main.* FROM Tickets main WHERE (main.EffectiveId = main.id) AND (main.Status != 'deleted') AND ( (main.Type = 'ticket') OR (main.Type = 'subticket') ) AND ( (main.Queue = '9') ) AND (( ( (Links.Type = 'MemberOf') AND (Links.LocalTarget = '17417') AND (main.id = Links.LocalBase) ) OR ( (Links.Type = 'MemberOf') AND (Links.LocalBase = '17417') AND (main.id = Links.LocalTarget) ) or (main.id = '17417') ) ); which produces a query plan: Nested Loop (cost=0.00..813.88 rows=1 width=169) Join Filter: (((("inner"."type")::text = 'MemberOf'::text) OR ("outer".id = 17417)) AND (("inner".localbase = 17417) OR (("inner"."type")::text = 'MemberOf'::text) OR ("outer".id = 17417)) AND (("outer".id = "inner".localtarget) OR (("inner"."type")::text = 'MemberOf'::text) OR ("outer".id = 17417)) AND ((("inner"."type")::text = 'MemberOf'::text) OR ("inner" .localtarget = 17417) OR ("outer".id = 17417)) AND (("inner".localbase = 17417) OR ("inner".localtarget = 17417) OR ("outer".id = 17417)) AND (("outer".id = "inner".localtarget) OR ( "inner".localtarget = 17417) OR ("outer".id = 17417)) AND ((("inner"."type")::text = 'MemberOf'::text) OR ("outer".id = "inner".localbase) OR ("outer".id = 17417)) AND (("inner".loca lbase = 17417) OR ("outer".id = "inner".localbase) OR ("outer".id = 17417)) AND (("outer".id = "inner".localtarget) OR ("outer".id = "inner".localbase) OR ("outer".id = 17417))) -> Index Scan using tickets1 on tickets main (cost=0.00..657.61 rows=1 width=169) Index Cond: (queue = 9) Filter: ((effectiveid = id) AND ((status)::text <> 'deleted'::text) AND ((("type")::text = 'ticket'::text) OR (("type")::text = 'subticket'::text))) -> Seq Scan on links (cost=0.00..46.62 rows=1462 width=20) If I rewrite the query as: SELECT main.* FROM Tickets main WHERE (main.EffectiveId = main.id) AND (main.Status != 'deleted') AND ( (main.Type = 'ticket') OR (main.Type = 'subticket') ) AND ( (main.Queue = '9') ) AND ( 17417 in (select links.localtarget from links where links.type='MemberOf' and main.id=links.localbase) or 17417 in ( select links.localbase from links where links.type='MemberOf' and main.id=links.localtarget) or main.id = '17417' ) ; The time for the query goes from 1500ms to 15ms. The two OR clauses ( (Links.Type = 'MemberOf') AND (Links.LocalTarget = '17417') AND (main.id = Links.LocalBase) ) OR ( (Links.Type = 'MemberOf') AND (Links.LocalBase = '17417') AND (main.id = Links.LocalTarget) ) don't contribute to the result set in this particular dataset, which is why the speed increases so dramatically. Is there a way to rewrite the top query to get the same results? I have already talked to Best Practical, and subqueries are not easily embraced. Dave -- Dave Cramer 519 939 0336 ICQ # 14675561