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