Re: pl-pgsql "return set of..." "return next..." performance question

On 16/11/2007, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com> wrote:
> I don't think so. Here's why....
>
> As an experiment, I created another temp table with records identical to
> what will be returned in the set. Then I loaded that temp table with
> all the results to be returned. And finally, I returned * from that
> table. I inserted "raise notice" statements to monitor progress.
>
> The query runs just as fast inside pl-pgsql as it did in psql (very very
> fast). But returning * from that table takes a good 10 seconds. (There
> are
> only 145 records in the table).
>

Then some is broken :(.

loop over return next has come cost, but not too much.

postgres=# create table foot(a integer);
CREATE TABLE
postgres=# insert into foot select i from generate_series(1,100000) g(i);
INSERT 0 100000
postgres=# create or replace function rs() returns setof integer as
$$declare i integer; begin for i in select a from foot loop return
next i; end loop; return; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# select * from rs() limit 10;
rs
----
1
2
3
4
5
6
7
8
9
10
(10 rows)

postgres=# \timing
Timing is on.
postgres=# select count(*) from (select * from rs) a;
ERROR: relation "rs" does not exist
postgres=# select count(*) from (select * from rs()) a;
count
--------
100000
(1 row)

Time: 327,740 ms
postgres=#

Pavel

> :-(
>
>
> -----Original Message-----
> From: Pavel Stehule [mailto:pavel(dot)stehule(at)gmail(dot)com]
> Sent: Friday, November 16, 2007 1:16 PM
> To: Gauthier, Dave
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] pl-pgsql "return set of..." "return next..."
> performance question
>
> Hello
>
> >
> > I noticed that it takes a long time to return the set of records. But
> if I
> > run the same query at the psql cli, it runs blindingly fast. So it
> appears
> > that the process of returning the records via "return next" is the
> > performance culprit.
> >
> > Any ideas?
>
> Try use holdable cursors
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html#PLPG
> SQL-CURSOR-USING
>
> But problem can be in
> http://groups.google.com/group/pgsql.general/browse_thread/thread/38aa20
> 64fcce53ed/69b7362839c3ab4c
>
> Regards
> Pavel Stehule
>