Skip site navigation (1) Skip section navigation (2)

Re: Replacing Cursors with Temporary Tables

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Eliot Gable <egable+pgsql-performance(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Replacing Cursors with Temporary Tables
Date: 2010-04-23 21:01:21
Message-ID: r2wb42b73151004231401r120510e8ne5ece4cdda768d5a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, Apr 23, 2010 at 4:42 PM, Eliot Gable
<egable+pgsql-performance(at)gmail(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2010-04-23 22:06:26
Subject: Re: Optimization idea
Previous:From: Eliot GableDate: 2010-04-23 20:42:57
Subject: Re: Replacing Cursors with Temporary Tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group