Re: Strange plpgsql performance -- arithmetic, numeric()

> 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