X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id F1F859FA5BE for ; Tue, 13 Jun 2006 11:37:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 84860-06 for ; Tue, 13 Jun 2006 11:37:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 424809FA12F for ; Tue, 13 Jun 2006 11:37:02 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k5DEax3o022610; Tue, 13 Jun 2006 10:36:59 -0400 (EDT) To: "Craig A. James" cc: pgsql-performance@postgresql.org Subject: Re: function not called if part of aggregate In-reply-to: <448E5A92.9050206@modgraph-usa.com> References: <448C505C.2080504@modgraph-usa.com> <87ac8jfu7t.fsf@stark.xeocode.com> <448E5A92.9050206@modgraph-usa.com> Comments: In-reply-to "Craig A. James" message dated "Mon, 12 Jun 2006 23:26:26 -0700" Date: Tue, 13 Jun 2006 10:36:59 -0400 Message-ID: <22609.1150209419@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/186 X-Sequence-Number: 19543 "Craig A. James" writes: > But the weird thing is that, in spite of flattening, which would appear to make the queries equivalent, the function gets called in one case, and not in the other. No, nothing particularly weird about it. ORDER BY in a subselect acts as an "optimization fence" that prevents flattening. An un-flattened subquery will evaluate all its output columns whether the parent query reads them or not. (This is not set in stone mind you, but in the current planner implementation it's hard to avoid, because such a sub-query gets planned before we've figured out which columns the parent wants to reference.) The cases in which you had the function in a subquery without ORDER BY were flattenable, and in that case the planner threw the function expression away as being unreferenced. regards, tom lane