On 16/11/2007, Gauthier, Dave <dave.gauthier@xxxxxxxxx> 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.stehule@xxxxxxxxx] > Sent: Friday, November 16, 2007 1:16 PM > To: Gauthier, Dave > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: 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 > ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
