Thread: pl-pgsql "return set of..." "return next..." performance question
Hi:
I have PL-PgSQL function that returns a set of records. It builds up 2 temp tables and then queries them to generate the set of records to be returned a-la....
for rrec in
select t1.x, t2.x from t1, t2 where....
loop
return next rrec;
end loop;
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?
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#PLPGSQL-CURSOR-USING But problem can be in http://groups.google.com/group/pgsql.general/browse_thread/thread/38aa2064fcce53ed/69b7362839c3ab4c Regards Pavel Stehule
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
On 16/11/2007, Gauthier, Dave <dave.gauthier@intel.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.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 >