Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E850D47645B for ; Thu, 24 Oct 2002 16:32:28 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 4AFF747643B for ; Thu, 24 Oct 2002 16:32:28 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g9OKWIhR014323; Thu, 24 Oct 2002 16:32:19 -0400 (EDT) To: Stephan Szabo Cc: Tomasz Myrta , pgsql-sql@postgresql.org Subject: Re: sub-select with aggregate In-reply-to: <20021024093022.A21018-100000@megazone23.bigpanda.com> References: <20021024093022.A21018-100000@megazone23.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Thu, 24 Oct 2002 09:39:14 -0700" Date: Thu, 24 Oct 2002 16:32:18 -0400 Message-ID: <14322.1035491538@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/343 X-Sequence-Number: 10060 Stephan Szabo writes: > Basically, as I understand it, > select * from a,b where a.a=b.a and a.a=3; > isn't going to realize that b.a=3 and act as if > you typed that. We have talked about adding code to make that deduction. The issue is how to do so without expending a lot of cycles (that will be wasted in every query where no improvement results, which is most of them). There are also some ticklish issues about side-effects of adding such clauses. in the above example, if we did filter b with b.a=3, then the join clause a.a=b.a becomes a no-op and does not reduce the number of rows; if we fail to account for that fact we will underestimate the number of rows out of the join, possibly pessimizing higher levels of plan. Also, we might as well implement the join as a nestloop; no percentage in fooling with hash or merge overhead. I have some thoughts about this, but it's not happening for 7.3 ... regards, tom lane