Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A4B62475F5E for ; Mon, 14 Oct 2002 13:17:43 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 1B586476022 for ; Mon, 14 Oct 2002 13:17:43 -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 g9EHHXhR017132; Mon, 14 Oct 2002 13:17:33 -0400 (EDT) To: Richard Huxton Cc: Heiko Stoermer , pgsql-sql@postgresql.org Subject: Re: Stored procedure returning row or resultset In-reply-to: <200210141605.04208.dev@archonet.com> References: <200210141622.54638.heiko.stoermer@login-solutions.de> <200210141605.04208.dev@archonet.com> Comments: In-reply-to Richard Huxton message dated "Mon, 14 Oct 2002 16:05:03 +0100" Date: Mon, 14 Oct 2002 13:17:32 -0400 Message-ID: <17131.1034615852@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/149 X-Sequence-Number: 9866 Richard Huxton writes: > Have you looked at marking f1() etc cachable? This means Postgresql > will only call the function once for each parameter-set. Unfortunately that's not true at all, or at least not helpful for this problem. The cachable attribute was poorly named, because it leads people to think that PG *will* cache function results, as opposed to *could* cache function results. A possible workaround is along the lines of SELECT f1, f1 + f2, f1 + f2 + f3 FROM (SELECT f1() as f1, f2() as f2, f3() as f3 LIMIT 1) tmp; Note the LIMIT 1 ... without that, the planner may flatten the two levels of SELECT together, eliminating the savings you're trying for. (I don't recall offhand all the conditions that govern flattening of a sub-select, but I'm pretty sure a sub-LIMIT will prevent it.) regards, tom lane