NUMERIC type benchmarks

Tom Lane wrote:

> A more significant point is that you have presented no evidence to back
> up your claim that this would be materially faster than the existing
> type. I doubt that the extra pallocs are all that expensive. (I think
> it'd be far more helpful to reimplement numeric using base-10000
> representation --- four decimal digits per int16 --- and then eliminate
> the distinction between storage format and computation format. See past
> discussions in the pghackers archives.)

I did several tests with functions designed to sum the number 12345 a million
times. The results are as follows (Pentium II 450, Redhat 6.2):

Postgres PL/PGSQL original numeric: 14.8 seconds
Postgres PL/PGSQL modified numeric: 11.0 seconds
Postgres PL/PGSQL float8: 10.7 seconds
GNU AWK: 2.5 seconds
Oracle PL/SQL number: 2.0 seconds

The modified Postgres numeric type is the original source code modified to use
a 32 digit NumericVar attribute digit buffer that eliminates palloc()/pfree()
calls when ndigits < 32.

Surely those are performance differences worth considering...

- Mark Butler

Note: The functions are as follows, all called with 12345 as a parameter,
except for the awk program, which has it hard coded:

PostgreSQL
==========

create function test_f1(float8) returns float8 as '
declare
i integer;
val float8;
begin

val := 0;

for i in 1 .. 1000000 loop
val := val + $1;
end loop;

return val;
end;'
language 'plpgsql';

create function test_f2(numeric) returns numeric as '
declare
i integer;
val numeric;
begin

val := 0;

for i in 1 .. 1000000 loop
val := val + $1;
end loop;

return val;
end;'
language 'plpgsql';

Awk
===

BEGIN {
val = 0;
p = 12345;
for(i = 1; i <= 1000000; i++)
{
val = val + p;
}
printf("%20f\n", val);
}

Oracle
======

create or replace function test_f2(p number) return number is
i number;
val number;
begin

val := 0;

for i in 1 .. 1000000 loop
val := val + p;
end loop;

return val;
end;
/