Decimal64 / Decimal128

New in version 16.04.

Deepgreen DB includes a new set of arbitrary precision decimal types that are more efficient than the native postgres Numeric type. These are the Decimal64 and Decimal128 types that have 16-digit and 34-digit precisions respectively.

Installation

After installing the Deepgreen DB bin file, execute the following commands on your Deepgreen DB host to install the decimal64 and decimal128 types and functions into the database.

% source deepgreendb/greenplum_path.sh
% cd $GPHOME/share/postgresql/contrib/
% psql your-database -f pg_decimal.sql

Performance

We can now do some exercise to demonstrate the performance characteristics of these types. The session below shows that the simple query select avg(x), sum(2*x) from table over a million records takes:

% psql test
test=# drop table if exists tt;
DROP TABLE
test=# create table tt(
	ii bigint, 
 	f64 double precision,
	d64 decimal64, 
	d128 decimal128, 
	n numeric(15, 3))
distributed randomly;
CREATE TABLE
test=# insert into tt 
	select i, 
	    i + 0.123, 
	    (i + 0.123)::decimal64, 
	    (i + 0.123)::decimal128, 
	    i + 0.123 
	from generate_series(1, 1000000) i;
INSERT 0 1000000
test=# \timing on
Timing is on.
test=# select count(*) from tt;
  count  
---------
 1000000
(1 row)

Time: 16.929 ms
test=# set vitesse.enable=1;
SET
Time: 1.888 ms
test=# select avg(f64),sum(2*f64) from tt;
       avg       |       sum        
-----------------+------------------
 500000.62300062 | 1000001246001.24
(1 row)

Time: 22.867 ms
test=# select avg(d64),sum(2*d64) from tt;
    avg     |        sum        
------------+-------------------
 500000.623 | 1000001246000.000
(1 row)

Time: 37.752 ms
test=# select avg(d128),sum(2*d128) from tt;
    avg     |        sum        
------------+-------------------
 500000.623 | 1000001246000.000
(1 row)

Time: 60.480 ms
test=# set vitesse.enable=1;
SET
Time: 1.704 ms
test=# select avg(n),sum(2*n) from tt;
         avg         |        sum        
---------------------+-------------------
 500000.623000000000 | 1000001246000.000
(1 row)

Time: 86.086 ms
test=# set vitesse.enable=0;
SET
Time: 1.633 ms
test=# select avg(n),sum(2*n) from tt;
         avg         |        sum        
---------------------+-------------------
 500000.623000000000 | 1000001246000.000
(1 row)

Time: 206.458 ms