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

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).

:-(


-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Friday, November 16, 2007 1:16 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.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