From: matthias schoeneich <matthias.schoeneich@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: Efficiency in Multiple Queries of Same Table in a PL/PgSQL Function
Date: Sat, 17 Oct 2009 13:29:32 +0200
Message-ID: <hbc9qk$vc4$1@news.hub.org> (raw)
In-Reply-To: <4AD93837.7030500@gmail.com>
References: <302670f20910161927h688e13e7qd096e63e68b7ae6c@mail.gmail.com>
<4AD93837.7030500@gmail.com>
Hi,
as you don't seem to need the sigma_*'s, you could calc the whole result
with one query using:
CREATE OR REPLACE FUNCTION poly_example2() RETURNS SETOF FLOAT8 AS
$poly_example$
DECLARE
f_result FLOAT8 := 0.0;
i_rowcount INT := 0 ;
BEGIN
SELECT sum((RANDOM() * 100 ) * (term.i * term.i) + RANDOM() *
(term.j * term.j) + term.k) +
sum((RANDOM() * 53 ) * (term.i * term.i) +(RANDOM()* 5) *
(term.j * term.j) + term.k) +
sum( 96.232234 * (term.i * term.i) + 0.32322325 *
(term.j * term.j) + term.k) ,
count(*)
INTO f_result ,
i_rowcount
FROM blah AS term;
IF i_rowcount > 0 THEN
RETURN NEXT f_result;
ELSE
RETURN NEXT 0;
END IF;
END;
$poly_example$ LANGUAGE plpgsql STRICT IMMUTABLE;
I've just put it in your plpgsql body to handle the case where table
blah contains no rows.
Matthias
Rob Sargent schrieb:
> I don't see anything in the assignment statements (sigma_* :=) which
> would prevent one from doing all three of them within a single for
> loop. In fact, written as is there's some chance the values of the
> sigma_*s might change between repeated calls to the function since there
> is no explicit ordering of the rows returned from table blah. Putting
> all the assignments into a single select from blah would at least say
> that the sigma values are from the same dataset per run.
>
>
> As to efficiency in general, I would expect the entire table (~50 rows)
> would be entirely in memory after the first select, but you plan triples
> the time in the loop. This expense would likely only be noticeable if
> the function itself is called /lots/.
>
> Gary Chambers wrote:
>> All...
>>
>> In the poly_example function below, I am emulating an actual
>> requirement by querying the same table three (3) times in order to
>> derive a solution to a problem. Is this the best or most efficient
>> and effective way to implement this? The table (which consists of
>> only five (5) FLOAT8 columns) I'm querying contains less than 50 rows.
>> Thanks in advance for any insight or criticisms you offer.
>>
>> CREATE OR REPLACE FUNCTION poly_example() RETURNS SETOF FLOAT8 AS
>> $poly_example$
>> DECLARE
>> term blah%ROWTYPE;
>> sigma_l FLOAT8 := 0.0;
>> sigma_b FLOAT8 := 0.0;
>> sigma_r FLOAT8 := 0.0;
>>
>> BEGIN
>> FOR term in SELECT * FROM blah LOOP
>> sigma_l := sigma_l + (RANDOM() * 100) * (term.i * term.i) +
>> RANDOM() * (term.j * term.j) + term.k;
>> END LOOP;
>>
>> FOR term in SELECT * FROM blah LOOP
>> sigma_b := sigma_b + (RANDOM() * 53) * (term.i * term.i) +
>> (RANDOM() * 5) * (term.j * term.j) + term.k;
>> END LOOP;
>>
>> FOR term in SELECT * FROM blah LOOP
>> sigma_r := sigma_r + 96.232234 * (term.i * term.i) +
>> 0.32322325 * (term.j * term.j) + term.k;
>> END LOOP;
>>
>> RETURN NEXT sigma_l + sigma_b + sigma_r;
>> END;
>> $poly_example$ LANGUAGE plpgsql STRICT IMMUTABLE;
>>
>> -- Gary Chambers
>>
>> /* Nothing fancy and nothing Microsoft! */
>>
>>
>
>
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: matthias.schoeneich@gmx.net
Subject: Re: Efficiency in Multiple Queries of Same Table in a PL/PgSQL Function
In-Reply-To: <hbc9qk$vc4$1@news.hub.org>
* 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