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=VxSlz9RreEpapjGMwaPEK1E1eQvacJiB1+gIkON35DUu2Xik6OAs3g6oTTC7p+bGmD
	UY1xTsU5oo8yswlCilBFcVPmtwxmJX3DRy1bp69IwXKfJI/8Uyz9Nrm3QtnbwgGArxNG
	sMRsPjNNevBbUddA8q5cf88fAPCWv8osF7PXc=
Message-ID: <4ADAA720.3050706@gmail.com>
Date: Sat, 17 Oct 2009 23:26:56 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: matthias schoeneich <matthias.schoeneich@gmx.net>
CC: pgsql-sql@postgresql.org
Subject: Re: Efficiency in Multiple Queries of Same Table in a PL/PgSQL
	Function
References: <302670f20910161927h688e13e7qd096e63e68b7ae6c@mail.gmail.com>
	<4AD93837.7030500@gmail.com> <hbc9qk$vc4$1@news.hub.org>
In-Reply-To: <hbc9qk$vc4$1@news.hub.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

But if I read the OP correctly the sigma are in fact used additively in 
each row in blah.  "sigma_* = sigma_* +"

matthias schoeneich wrote:
> 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! */
>>>
>>>   
>>
>>
>