<posted & mailed> Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes: >> On Sat, Jul 10, 2004 at 09:18:28PM -0700, elein wrote: >>> The new plperl returns sets by having >>> the function return an array. > >> I think RETURN NEXT does the same thing anyway ... they just store >> tuples in a Tuplestore and then the whole thing is returned. So the >> function actually doesn't return until the whole function is done. > > However, it's likely that the tuplestore infrastructure can deal > comfortably with sets far larger than a Perl array can. (For one thing, > it will swap tuples out to a temp file on disk once the set size exceeds > work_mem.) I think elein's concern is justified, unless someone can > produce a test case showing that plperl actually performs OK with a > large result set. > > As a simple test for plpgsql's speed with such things, I tried > > create function seq(int) returns setof int as ' > begin > for i in 1..$1 loop > return next i; > end loop; > return; > end' language plpgsql; > > regression=# \timing > Timing is on. > regression=# select count(*) from seq(100000); > count > -------- > 100000 > (1 row) > > Time: 396.524 ms > regression=# select count(*) from seq(1000000); > count > --------- > 1000000 > (1 row) > > Time: 3615.115 ms > regression=# select count(*) from seq(10000000); > count > ---------- > 10000000 > (1 row) > > Time: 40356.972 ms > > My Perl is too rusty to immediately whip out the equivalent incantation > in plperl; would someone like to compare the timings on their own machine? > I don't have access to a machine with plperl installed, but it would be very close to this: create function seq(int) returns setof int as $$ my $count = shift; my $ret = []; for my $i ( 1 .. $count ) { push @$ret, $i; } return $ret; $$ language 'plperl'; ... hmmm... the "push" line may need to be: push @$ret, { val => $i }; Hope it helps! > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- --miker ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend