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 5E68C5E46CC for ; Thu, 19 Aug 2004 10:36:01 -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 32625-02 for ; Thu, 19 Aug 2004 13:35:53 +0000 (GMT) Received: from bugge.potatoe.com (bugge.potatoe.com [216.240.48.15]) by svr1.postgresql.org (Postfix) with ESMTP id DD37F5E37D0 for ; Thu, 19 Aug 2004 10:35:50 -0300 (ADT) Received: from [127.0.0.1] (localhost [127.0.0.1]) by bugge.potatoe.com (Postfix) with ESMTP id 1DCC2728418; Thu, 19 Aug 2004 06:38:49 -0700 (PDT) Message-ID: <4124AD68.8000208@madfish.com> Date: Thu, 19 Aug 2004 06:38:48 -0700 From: Brad Bulger Organization: The Madfish Group User-Agent: Mozilla Thunderbird 0.7+ (Macintosh/20040715) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pg@fastcrypt.com Cc: pgsql-performance@postgresql.org Subject: Re: help with query References: <1092921433.1622.569.camel@localhost.localdomain> In-Reply-To: <1092921433.1622.569.camel@localhost.localdomain> Content-Type: text/plain; charset=ISO-8859-1; 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/288 X-Sequence-Number: 7924 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