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;
/