DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=RciF1/UQaUr2f4ne8N0myzmAdoomptN2LHVH6HPhYYQNl+x7uMCOCD8gioFt9kqwb1
	OO66h81/Kn0rwRq7HInKyNgvYi3d0QCkiLzyvVUgwfAql3rtHHe+C2nqLj/uSypDvkld
	AJ3xZeGduOCTRKRMu0Ts2a1d6lF0/ml+zGatY=
Message-ID: <4AD93837.7030500@gmail.com>
Date: Fri, 16 Oct 2009 21:21:27 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Gary Chambers <gwchamb@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Efficiency in Multiple Queries of Same Table in a PL/PgSQL
	Function
References: <302670f20910161927h688e13e7qd096e63e68b7ae6c@mail.gmail.com>
In-Reply-To: <302670f20910161927h688e13e7qd096e63e68b7ae6c@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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! */
>
>