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 5F05B5E46FA for ; Thu, 19 Aug 2004 11:21:32 -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 48893-03 for ; Thu, 19 Aug 2004 14:21:25 +0000 (GMT) Received: from simmts12-srv.bellnexxia.net (simmts12.bellnexxia.net [206.47.199.141]) by svr1.postgresql.org (Postfix) with ESMTP id 005E65E46F4 for ; Thu, 19 Aug 2004 11:21:21 -0300 (ADT) Received: from localhost ([67.71.24.121]) by simmts12-srv.bellnexxia.net (InterMail vM.5.01.06.10 201-253-122-130-110-20040306) with ESMTP id <20040819142039.ONSS1580.simmts12-srv.bellnexxia.net@localhost>; Thu, 19 Aug 2004 10:20:39 -0400 Received: from [192.168.250.6] ([192.168.250.6]) by (8.11.6/8.11.6) with ESMTP id i7JELPg05469; Thu, 19 Aug 2004 10:21:26 -0400 Message-ID: <4124B7B5.6050409@sympatico.ca> Date: Thu, 19 Aug 2004 10:22:45 -0400 From: Jean-Luc Lachance User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en, en-us, en-gb, fr, fr-ca MIME-Version: 1.0 To: pg@fastcrypt.com Cc: Brad Bulger , perform Subject: Re: help with query References: <1092921433.1622.569.camel@localhost.localdomain> <4124AD68.8000208@madfish.com> <1092923438.1544.574.camel@localhost.localdomain> In-Reply-To: <1092923438.1544.574.camel@localhost.localdomain> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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/291 X-Sequence-Number: 7927 how about: 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' OR main.id IN ( SELECT DISTINCT LocalTarget from Links where Type = 'MemberOf' and LocalTarget = '17417') OR main.id IN ( SELECT DISTINCT LocalBase from Links where Type = 'MemberOf' and LocalTarget = '17417')) Dave Cramer wrote: > 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 >>