From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Huxton <dev@archonet.com>
Cc: Heiko Stoermer <heiko.stoermer@login-solutions.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Stored procedure returning row or resultset
Date: Mon, 14 Oct 2002 13:17:32 -0400
Message-ID: <17131.1034615852@sss.pgh.pa.us> (raw)
In-Reply-To: <200210141605.04208.dev@archonet.com>
References: <200210141622.54638.heiko.stoermer@login-solutions.de>
<200210141605.04208.dev@archonet.com>
Richard Huxton <dev@archonet.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, dev@archonet.com, heiko.stoermer@login-solutions.de
Subject: Re: Stored procedure returning row or resultset
In-Reply-To: <17131.1034615852@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox