Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id ABE54632D50 for ; Fri, 23 Apr 2010 21:39:25 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 34644-03 for ; Sat, 24 Apr 2010 00:39:07 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f196.google.com (mail-qy0-f196.google.com [209.85.221.196]) by mail.postgresql.org (Postfix) with ESMTP id CFCD363286E for ; Fri, 23 Apr 2010 21:39:14 -0300 (ADT) Received: by qyk34 with SMTP id 34so6104835qyk.10 for ; Fri, 23 Apr 2010 17:39:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:sender:received :in-reply-to:references:date:x-google-sender-auth:message-id:subject :from:to:content-type; bh=mDBqeklhSsh5sn92x5mYJDEM3iOSElvBdwT8K6Owi9s=; b=QbEoWNRtABBjVpvzL/Y1l9Su+vMDr0bhmtVpadrqf1rAJgi90eY9898OEHPM6M9Z7p LuGzG5UDTiY/DCBVMyqtXXVDzImd5z2bN5RZWkOWm3TjI1BBplM9GWt7mw2jKpopFHQD wGRS+y4QDsSQxj8W2kgOwOZU30wsEkWYhpjZE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:content-type; b=XTUDXQoi0f7jEH5PAhK1sSEBt36oD7v0WbUo89ZWrD2hs+519boEwodthLjJ6S5VPN WZo/pwl4lFCxD3RN+BD84hF31+nOwx3DbxvkyYlv2rungYI6jrq3pNlDAGiqai/mB698 tUbdWFYCNEMjp7NIjm/BayaQhSKCdoXHeQCJE= MIME-Version: 1.0 Received: by 10.229.215.11 with SMTP id hc11mr1025686qcb.45.1272069553602; Fri, 23 Apr 2010 17:39:13 -0700 (PDT) Received: by 10.229.37.65 with HTTP; Fri, 23 Apr 2010 17:39:13 -0700 (PDT) In-Reply-To: References: Date: Fri, 23 Apr 2010 20:39:13 -0400 X-Google-Sender-Auth: 7965835a29781301 Message-ID: Subject: Re: Replacing Cursors with Temporary Tables From: Eliot Gable To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=00163630fd47df42800484f0c541 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.4 tagged_above=-10 required=5 tests=AWL=0.400, BAYES_20=-0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/308 X-Sequence-Number: 38558 --00163630fd47df42800484f0c541 Content-Type: text/plain; charset=ISO-8859-1 That's a good point. However, even after changing it, it is still 12ms with the function call verses 6ms without the extra function call. Though, it is worth noting that if you can make the function call be guaranteed to return the same results when used with the same input parameters, it ends up being faster (roughly 3ms in my test case) due to caching -- at least when executing it multiple times in a row like this. Unfortunately, I cannot take advantage of that, because in my particular use case, the chances of it being called again with the same input values within the cache lifetime of the results is close to zero. Add to that the fact that the function queries tables that could change between transactions (meaning the function is volatile) and it's a moot point. However, it is worth noting that for those people using a non-volatile function call multiple times in the same transaction with the same input values, there is no need to inline the function call. On Fri, Apr 23, 2010 at 5:01 PM, Merlin Moncure wrote: > On Fri, Apr 23, 2010 at 4:42 PM, Eliot Gable > > > wrote: > > To answer the question of whether calling a stored procedure adds any > > significant overhead, I built a test case and the short answer is that it > > seems that it does: > > > > CREATE OR REPLACE FUNCTION Test1() RETURNS INTEGER AS > > $BODY$ > > DECLARE > > temp INTEGER; > > BEGIN > > FOR i IN 1..1000 LOOP > > SELECT 1 AS id INTO temp; > > END LOOP; > > RETURN 1; > > END; > > $BODY$ > > LANGUAGE plpgsql; > > > > CREATE OR REPLACE FUNCTION Test2A() RETURNS SETOF INTEGER AS > > $BODY$ > > DECLARE > > BEGIN > > RETURN QUERY SELECT 1 AS id; > > END; > > $BODY$ > > LANGUAGE plpgsql; > > > > CREATE OR REPLACE FUNCTION Test2B() RETURNS INTEGER AS > > $BODY$ > > DECLARE > > temp INTEGER; > > BEGIN > > FOR i IN 1..1000 LOOP > > temp := Test2A(); > > END LOOP; > > RETURN 1; > > END; > > $BODY$ > > LANGUAGE plpgsql; > > > > > > EXPLAIN ANALYZE SELECT * FROM Test1(); > > "Function Scan on test1 (cost=0.00..0.26 rows=1 width=4) (actual > > time=6.568..6.569 rows=1 loops=1)" > > "Total runtime: 6.585 ms" > > > > > > EXPLAIN ANALYZE SELECT * FROM Test2B(); > > "Function Scan on test2b (cost=0.00..0.26 rows=1 width=4) (actual > > time=29.006..29.007 rows=1 loops=1)" > > "Total runtime: 29.020 ms" > > That's not a fair test. test2a() is a SRF which has higher overhead > than regular function. Try it this way and the timings will level > out: > > CREATE OR REPLACE FUNCTION Test2A() RETURNS INTEGER AS > $BODY$ > DECLARE > BEGIN > RETURN 1 ; > END; > $BODY$ > LANGUAGE plpgsql ; > > merlin > -- Eliot Gable "We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower "I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower "Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero --00163630fd47df42800484f0c541 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
That's a good point. However, even after changing it, it is still = 12ms with the function call verses 6ms without the extra function call. Tho= ugh, it is worth noting that if you can make the function call be guarantee= d to return the same results when used with the same input parameters, it e= nds up being faster (roughly 3ms in my test case) due to caching -- at leas= t when executing it multiple times in a row like this. Unfortunately, I can= not take advantage of that, because in my particular use case, the chances = of it being called again with the same input values within the cache lifeti= me of the results is close to zero. Add to that the fact that the function = queries tables that could change between transactions (meaning the function= is volatile) and it's a moot point. However, it is worth noting that f= or those people using a non-volatile function call multiple times in the sa= me transaction with the same input values, there is no need to inline the f= unction call.


On Fri, Apr 23, 2010 at= 5:01 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
On Fri, Apr 23, 2010 at 4:42 PM, Eliot Gable
> To answer the question of whether calling= a stored procedure adds any
> significant overhead, I built a test case and the short answer is that= it
> seems that it does:
>
> CREATE OR REPLACE FUNCTION Test1() RETURNS INTEGER AS
> $BODY$
> DECLARE
> =A0=A0=A0 temp INTEGER;
> BEGIN
> =A0=A0=A0 FOR i IN 1..1000 LOOP
> =A0=A0=A0 =A0=A0=A0 SELECT 1 AS id INTO temp;
> =A0=A0=A0 END LOOP;
> =A0=A0=A0 RETURN 1;
> END;
> $BODY$
> LANGUAGE plpgsql;
>
> CREATE OR REPLACE FUNCTION Test2A() RETURNS SETOF INTEGER AS
> $BODY$
> DECLARE
> BEGIN
> =A0=A0=A0 RETURN QUERY SELECT 1 AS id;
> END;
> $BODY$
> LANGUAGE plpgsql;
>
> CREATE OR REPLACE FUNCTION Test2B() RETURNS INTEGER AS
> $BODY$
> DECLARE
> =A0=A0=A0 temp INTEGER;
> BEGIN
> =A0=A0=A0 FOR i IN 1..1000 LOOP
> =A0=A0=A0 =A0=A0=A0 temp :=3D Test2A();
> =A0=A0=A0 END LOOP;
> =A0=A0=A0 RETURN 1;
> END;
> $BODY$
> LANGUAGE plpgsql;
>
>
> EXPLAIN ANALYZE SELECT * FROM Test1();
> "Function Scan on test1=A0 (cost=3D0.00..0.26 rows=3D1 width=3D4)= (actual
> time=3D6.568..6.569 rows=3D1 loops=3D1)"
> "Total runtime: 6.585 ms"
>
>
> EXPLAIN ANALYZE SELECT * FROM Test2B();
> "Function Scan on test2b=A0 (cost=3D0.00..0.26 rows=3D1 width=3D4= ) (actual
> time=3D29.006..29.007 rows=3D1 loops=3D1)"
> "Total runtime: 29.020 ms"

That's not a fair test. =A0test2a() is a SRF which has high= er overhead
than regular function. =A0Try it this way and the timings will level
out:

CREATE OR REPLACE FUNCTION Test2A() RETURNS =A0INTEGER AS
$BODY$
DECLARE
BEGIN
=A0 =A0RETURN =A01 ;
END;
$BODY$
LANGUAGE plpgsql ;

merlin



--
Eliot Gable
<= br>"We do not inherit the Earth from our ancestors: we borrow it from = our children." ~David Brower

"I decided the words were to= o conservative for me. We're not borrowing from our children, we're= stealing from them--and it's not even considered to be a crime." = ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst e= at to live; not live to eat.) ~Marcus Tullius Cicero
--00163630fd47df42800484f0c541--