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 514825E46CC for ; Thu, 19 Aug 2004 10:49:19 -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 35031-07 for ; Thu, 19 Aug 2004 13:49:12 +0000 (GMT) Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id E26CD5E46DF for ; Thu, 19 Aug 2004 10:49:10 -0300 (ADT) Received: (qmail 7814 invoked from network); 19 Aug 2004 13:49:13 -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:49:13 -0000 Subject: Re: help with query From: Dave Cramer Reply-To: pg@fastcrypt.com To: Brad Bulger Cc: perform In-Reply-To: <4124AD68.8000208@madfish.com> References: <1092921433.1622.569.camel@localhost.localdomain> <4124AD68.8000208@madfish.com> Content-Type: text/plain Organization: Cramer Consulting Message-Id: <1092923438.1544.574.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Thu, 19 Aug 2004 09:50:38 -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/289 X-Sequence-Number: 7925 Brad, Thanks, that runs on the same order of magnitude as the subqueries. DAve On Thu, 2004-08-19 at 09:38, Brad Bulger wrote: > You're doing a join except not, is the trouble, looks like. The query is really > "FROM Tickets main, Links", but when Tickets.id is 17417, you've got no join > to the Links table. So you end up getting every row in Links for each row in > Tickets with id = 17417. > > I'd think this wants to be two queries or a union: > > 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 ( (main.id = '17417')) > union > SELECT distinct main.oid,main.* FROM Tickets main, Links > 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 (main.id = Links.LocalTarget) ) > ; > > or else, yah, a subquery: > > [...] > AND ( > main.id = '17417' > or > exists( > select true from Links > where Type = 'MemberOf' and LocalTarget = '17417' > and (LocalBase = main.id or LocalTarget = main.id) > ) > ) > > Those are the only things I can think of to make it work, anyways. > > Dave Cramer wrote: > > > 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