X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 367BCD1B49E for ; Thu, 29 Jan 2004 02:05:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 01653-01 for ; Wed, 28 Jan 2004 22:05:25 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 222D3D1CA6B for ; Wed, 28 Jan 2004 22:05:21 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i0T24oI1010116; Wed, 28 Jan 2004 21:04:50 -0500 (EST) To: Jack Coates Cc: pgsql-performance Subject: Re: query optimization question In-reply-to: <1075338353.7494.71.camel@cletus.lyris.com> References: <1075338353.7494.71.camel@cletus.lyris.com> Comments: In-reply-to Jack Coates message dated "Wed, 28 Jan 2004 17:05:54 -0800" Date: Wed, 28 Jan 2004 21:04:49 -0500 Message-ID: <10115.1075341889@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/300 X-Sequence-Number: 5474 Jack Coates writes: > I've got a query that needs some help, please. Is there a way to avoid > all the looping? I've got freedom to work with the double-indented > sections below ) AND (, but the initial select distinct wrapper is much > more difficult to change. This is auto-generated code. Well, you're not going to get any serious improvement without a wholesale rewrite of the query --- I'd think that something driven by a GROUP BY memberid_ HAVING count(*) = whatever at the outer level would be a better way to approach it. As you have it, the system has no choice but to fully evaluate two very expensive subselects, from scratch, for each outer row. However... > ( select count(*) from lyrActiveRecips, members_ a, outmail_ > where lyrActiveRecips.UserName = a.UserNameLC_ > and lyrActiveRecips.Domain = a.Domain_ > and a.MemberID_ = members_.MemberID_ > and outmail_.MessageID_ = lyrActiveRecips.MailingID Is memberid_ a unique identifier for members_, as one would think from the name? If so, can't you drop the join of members_ a in this subselect, and just use the corresponding fields from the outer table? > ( select count(*) from lyrCompletedRecips, members_ a, outmail_ > where a.MemberID_ = lyrCompletedRecips.MemberID > and a.UserNameLC_ = members_.UserNameLC_ > and a.Domain_ = members_.Domain_ > and outmail_.MessageID_ = lyrCompletedRecips.MailingID Why are the join conditions different here from the other subselect? Can't you rephrase them the same as above, and then again remove the inner appearance of members_ ? regards, tom lane