<posted & mailed>
Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> 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
