Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux