> Incrementing the loop counter by a factor of 10, from 1000 to 10000
> makes the process take more than 100 times longer. (I only saw
> this happen when I happened upon using a numeric() datatype
> and then dividing i/100 to avoid overflow. It does not happen
> without the array and working with other, much larger, arrays
> of other data types you see no slowdown.)
>
It's not bug, it's feature ;-). plpgsql isn't good language for
inicialisation big arrays. If it's possible use plperl for example.
CREATE OR REPLACE FUNCTION speed1(integer) RETURNS numeric(7,2)[] AS $$
$i = 0.00;
@myarray = ();
while ($i<$_[0]) {
push @myarray, $i;
$i = $i + 1;
}
return '{'.join(',',@myarray).'}';
$$ LANGUAGE plperlu;
select speed(100);
CREATE OR REPLACE FUNCTION speed2(integer) RETURNS numeric(7,2)[] AS $$
DECLARE a numeric(7,2)[] = '{}';
BEGIN
FOR _i IN 1..$1 LOOP
a[_i] := _i;
END LOOP;
RETURN a;
END;
$$ LANGUAGE plpgsql;
tarif=# select speed(10000);
Time: 28,269 ms
tarif=# select speed2(10000);
Time: 91186,199 ms
Regards
Pavel Stehule
