From: Eliot Gable <egable+pgsql-performance@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Replacing Cursors with Temporary Tables
Date: Fri, 23 Apr 2010 20:39:13 -0400
Message-ID: <x2kbf6923ed1004231739qe92d06c2o557d4e31e66ba3d7@mail.gmail.com> (raw)
In-Reply-To: <r2wb42b73151004231401r120510e8ne5ece4cdda768d5a@mail.gmail.com>
References: <n2ybf6923ed1004211316ib844d82bn7a63bd3292ca80a0@mail.gmail.com>
<n2sb42b73151004220711mc2135db6heccf53ec1fb0136e@mail.gmail.com>
<q2yb42b73151004220742gbb7ae4adyc3781cedf98e8559@mail.gmail.com>
<m2gbf6923ed1004221357ua9ab54a3s92dfdbb59cfef832@mail.gmail.com>
<n2qbf6923ed1004231342l61f766fax8fb2a482aa3b0faf@mail.gmail.com>
<r2wb42b73151004231401r120510e8ne5ece4cdda768d5a@mail.gmail.com>
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 <mmoncure@gmail.com> wrote:
> On Fri, Apr 23, 2010 at 4:42 PM, Eliot Gable
> <egable+pgsql-performance@gmail.com <egable%2Bpgsql-performance@gmail.com>>
> 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
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-performance@postgresql.org
Cc: egable+pgsql-performance@gmail.com
Subject: Re: Replacing Cursors with Temporary Tables
In-Reply-To: <x2kbf6923ed1004231739qe92d06c2o557d4e31e66ba3d7@mail.gmail.com>
* 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