digoal
2023-01-11
PostgreSQL , PolarDB , fixeddecimal , decimal128 , decimal64
PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的价值产出, 将数据变成生产力.
本文将介绍PolarDB 开源版 numeric 性能增强插件 : fixeddecimal , pgdecimal (decimal128 , decimal64)
测试环境为macOS+docker, PolarDB部署请参考下文:
PolarDB底层PostgreSQL原生numeric为了支持科学计算, 采用了较为复杂的实现, 采用可变长度存储, 支持超长精确数值, 但是导致了性能下降.
参考 src/backend/utils/adt/numeric.c
整型、浮点、整型序列、"无限"精度数值
| Name | Storage Size | Description | Range |
|---|---|---|---|
| smallint | 2 bytes | small-range integer | -32768 to +32767 |
| integer | 4 bytes | typical choice for integer | -2147483648 to +2147483647 |
| bigint | 8 bytes | large-range integer | -9223372036854775808 to +9223372036854775807 |
| decimal | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
| numeric | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
| real | 4 bytes | variable-precision, inexact | 6 decimal digits precision |
| double precision | 8 bytes | variable-precision, inexact | 15 decimal digits precision |
| smallserial | 2 bytes | small autoincrementing integer | 1 to 32767 |
| serial | 4 bytes | autoincrementing integer | 1 to 2147483647 |
| bigserial | 8 bytes | large autoincrementing integer | 1 to 9223372036854775807 |
PostgreSQL内置的numeric/decimal类型属于"无限"精度数值类型. 其他数值类型(float,real,int2,int4,int8)都是定长存储，使用时不需要调用palloc，效率较高。
如果你要使用超过双精能表示的有效范围的数值，目前只能选择decimal\numeric类型，而这个类型前面说了，由于是变长设计，需要调用palloc，效率一般。
https://github.com/2ndQuadrant/fixeddecimal
git clone --depth 1 https://github.com/2ndQuadrant/fixeddecimal
cd fixeddecimal/
USE_PGXS=1 make
USE_PGXS=1 make install
psql
postgres=# create extension fixeddecimal ;
CREATE EXTENSION
postgres=# \dT
List of data types
Schema | Name | Description
--------+--------------+-------------
public | fixeddecimal |
(1 row)
postgres=# \do
List of operators
Schema | Name | Left arg type | Right arg type | Result type | Description
--------+------+---------------+----------------+------------------+-------------
public | * | fixeddecimal | fixeddecimal | fixeddecimal |
public | * | fixeddecimal | integer | fixeddecimal |
public | * | fixeddecimal | smallint | fixeddecimal |
public | * | integer | fixeddecimal | fixeddecimal |
public | * | smallint | fixeddecimal | fixeddecimal |
public | + | fixeddecimal | fixeddecimal | fixeddecimal |
public | + | fixeddecimal | integer | fixeddecimal |
public | + | fixeddecimal | smallint | fixeddecimal |
public | + | integer | fixeddecimal | fixeddecimal |
public | + | smallint | fixeddecimal | fixeddecimal |
public | - | fixeddecimal | fixeddecimal | fixeddecimal |
public | - | fixeddecimal | integer | fixeddecimal |
public | - | fixeddecimal | smallint | fixeddecimal |
public | - | integer | fixeddecimal | fixeddecimal |
public | - | smallint | fixeddecimal | fixeddecimal |
public | - | | fixeddecimal | fixeddecimal |
public | / | fixeddecimal | fixeddecimal | fixeddecimal |
public | / | fixeddecimal | integer | fixeddecimal |
public | / | fixeddecimal | smallint | fixeddecimal |
public | / | integer | fixeddecimal | double precision |
public | / | smallint | fixeddecimal | double precision |
public | < | fixeddecimal | fixeddecimal | boolean |
public | < | fixeddecimal | integer | boolean |
public | < | fixeddecimal | numeric | boolean |
public | < | fixeddecimal | smallint | boolean |
public | < | integer | fixeddecimal | boolean |
public | < | numeric | fixeddecimal | boolean |
public | < | smallint | fixeddecimal | boolean |
public | <= | fixeddecimal | fixeddecimal | boolean |
public | <= | fixeddecimal | integer | boolean |
public | <= | fixeddecimal | numeric | boolean |
public | <= | fixeddecimal | smallint | boolean |
public | <= | integer | fixeddecimal | boolean |
public | <= | numeric | fixeddecimal | boolean |
public | <= | smallint | fixeddecimal | boolean |
public | <> | fixeddecimal | fixeddecimal | boolean |
public | <> | fixeddecimal | integer | boolean |
public | <> | fixeddecimal | numeric | boolean |
public | <> | fixeddecimal | smallint | boolean |
public | <> | integer | fixeddecimal | boolean |
public | <> | numeric | fixeddecimal | boolean |
public | <> | smallint | fixeddecimal | boolean |
public | = | fixeddecimal | fixeddecimal | boolean |
public | = | fixeddecimal | integer | boolean |
public | = | fixeddecimal | numeric | boolean |
public | = | fixeddecimal | smallint | boolean |
public | = | integer | fixeddecimal | boolean |
public | = | numeric | fixeddecimal | boolean |
public | = | smallint | fixeddecimal | boolean |
public | > | fixeddecimal | fixeddecimal | boolean |
public | > | fixeddecimal | integer | boolean |
public | > | fixeddecimal | numeric | boolean |
public | > | fixeddecimal | smallint | boolean |
public | > | integer | fixeddecimal | boolean |
public | > | numeric | fixeddecimal | boolean |
public | > | smallint | fixeddecimal | boolean |
public | >= | fixeddecimal | fixeddecimal | boolean |
public | >= | fixeddecimal | integer | boolean |
public | >= | fixeddecimal | numeric | boolean |
public | >= | fixeddecimal | smallint | boolean |
public | >= | integer | fixeddecimal | boolean |
public | >= | numeric | fixeddecimal | boolean |
public | >= | smallint | fixeddecimal | boolean |
(63 rows)
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+---------------------------------+------------------+-----------------------------+------
public | abs | fixeddecimal | fixeddecimal | func
public | avg | fixeddecimal | fixeddecimal | agg
public | dtofixeddecimal | fixeddecimal | double precision | func
public | fixeddecimal | fixeddecimal | fixeddecimal, integer | func
public | fixeddecimal_avg | fixeddecimal | internal | func
public | fixeddecimal_avg_accum | internal | internal, fixeddecimal | func
public | fixeddecimal_cmp | integer | fixeddecimal, fixeddecimal | func
public | fixeddecimal_hash | integer | fixeddecimal | func
public | fixeddecimal_int2_cmp | integer | fixeddecimal, smallint | func
public | fixeddecimal_int2_eq | boolean | fixeddecimal, smallint | func
public | fixeddecimal_int2_ge | boolean | fixeddecimal, smallint | func
public | fixeddecimal_int2_gt | boolean | fixeddecimal, smallint | func
public | fixeddecimal_int2_le | boolean | fixeddecimal, smallint | func
public | fixeddecimal_int2_lt | boolean | fixeddecimal, smallint | func
public | fixeddecimal_int2_ne | boolean | fixeddecimal, smallint | func
public | fixeddecimal_int4_cmp | integer | fixeddecimal, integer | func
public | fixeddecimal_int4_eq | boolean | fixeddecimal, integer | func
public | fixeddecimal_int4_ge | boolean | fixeddecimal, integer | func
public | fixeddecimal_int4_gt | boolean | fixeddecimal, integer | func
public | fixeddecimal_int4_le | boolean | fixeddecimal, integer | func
public | fixeddecimal_int4_lt | boolean | fixeddecimal, integer | func
public | fixeddecimal_int4_ne | boolean | fixeddecimal, integer | func
public | fixeddecimal_numeric | numeric | fixeddecimal | func
public | fixeddecimal_numeric_cmp | integer | fixeddecimal, numeric | func
public | fixeddecimal_numeric_eq | boolean | fixeddecimal, numeric | func
public | fixeddecimal_numeric_ge | boolean | fixeddecimal, numeric | func
public | fixeddecimal_numeric_gt | boolean | fixeddecimal, numeric | func
public | fixeddecimal_numeric_le | boolean | fixeddecimal, numeric | func
public | fixeddecimal_numeric_lt | boolean | fixeddecimal, numeric | func
public | fixeddecimal_numeric_ne | boolean | fixeddecimal, numeric | func
public | fixeddecimal_sum | fixeddecimal | internal | func
public | fixeddecimalaggstatecombine | internal | internal, internal | func
public | fixeddecimalaggstatedeserialize | internal | bytea, internal | func
public | fixeddecimalaggstateserialize | bytea | internal | func
public | fixeddecimaldiv | fixeddecimal | fixeddecimal, fixeddecimal | func
public | fixeddecimaleq | boolean | fixeddecimal, fixeddecimal | func
public | fixeddecimalge | boolean | fixeddecimal, fixeddecimal | func
public | fixeddecimalgt | boolean | fixeddecimal, fixeddecimal | func
public | fixeddecimalin | fixeddecimal | cstring, oid, integer | func
public | fixeddecimalint2 | smallint | fixeddecimal | func
public | fixeddecimalint2div | fixeddecimal | fixeddecimal, smallint | func
public | fixeddecimalint2mi | fixeddecimal | fixeddecimal, smallint | func
public | fixeddecimalint2mul | fixeddecimal | fixeddecimal, smallint | func
public | fixeddecimalint2pl | fixeddecimal | fixeddecimal, smallint | func
public | fixeddecimalint4 | integer | fixeddecimal | func
public | fixeddecimalint4div | fixeddecimal | fixeddecimal, integer | func
public | fixeddecimalint4mi | fixeddecimal | fixeddecimal, integer | func
public | fixeddecimalint4mul | fixeddecimal | fixeddecimal, integer | func
public | fixeddecimalint4pl | fixeddecimal | fixeddecimal, integer | func
public | fixeddecimallarger | fixeddecimal | fixeddecimal, fixeddecimal | func
public | fixeddecimalle | boolean | fixeddecimal, fixeddecimal | func
public | fixeddecimallt | boolean | fixeddecimal, fixeddecimal | func
public | fixeddecimalmi | fixeddecimal | fixeddecimal, fixeddecimal | func
public | fixeddecimalmul | fixeddecimal | fixeddecimal, fixeddecimal | func
public | fixeddecimalne | boolean | fixeddecimal, fixeddecimal | func
public | fixeddecimalout | cstring | fixeddecimal | func
public | fixeddecimalpl | fixeddecimal | fixeddecimal, fixeddecimal | func
public | fixeddecimalrecv | fixeddecimal | internal | func
public | fixeddecimalsend | bytea | fixeddecimal | func
public | fixeddecimalsmaller | fixeddecimal | fixeddecimal, fixeddecimal | func
public | fixeddecimaltod | double precision | fixeddecimal | func
public | fixeddecimaltof | real | fixeddecimal | func
public | fixeddecimaltypmodin | integer | cstring[] | func
public | fixeddecimaltypmodout | cstring | integer | func
public | fixeddecimalum | fixeddecimal | fixeddecimal | func
public | ftofixeddecimal | fixeddecimal | real | func
public | gen_arr | integer[] | normal integer, hot integer | func
public | int2_fixeddecimal_cmp | integer | smallint, fixeddecimal | func
public | int2_fixeddecimal_eq | boolean | smallint, fixeddecimal | func
public | int2_fixeddecimal_ge | boolean | smallint, fixeddecimal | func
public | int2_fixeddecimal_gt | boolean | smallint, fixeddecimal | func
public | int2_fixeddecimal_le | boolean | smallint, fixeddecimal | func
public | int2_fixeddecimal_lt | boolean | smallint, fixeddecimal | func
public | int2_fixeddecimal_ne | boolean | smallint, fixeddecimal | func
public | int2fixeddecimal | fixeddecimal | smallint | func
public | int2fixeddecimaldiv | double precision | smallint, fixeddecimal | func
public | int2fixeddecimalmi | fixeddecimal | smallint, fixeddecimal | func
public | int2fixeddecimalmul | fixeddecimal | smallint, fixeddecimal | func
public | int2fixeddecimalpl | fixeddecimal | smallint, fixeddecimal | func
public | int4_fixeddecimal_cmp | integer | integer, fixeddecimal | func
public | int4_fixeddecimal_eq | boolean | integer, fixeddecimal | func
public | int4_fixeddecimal_ge | boolean | integer, fixeddecimal | func
public | int4_fixeddecimal_gt | boolean | integer, fixeddecimal | func
public | int4_fixeddecimal_le | boolean | integer, fixeddecimal | func
public | int4_fixeddecimal_lt | boolean | integer, fixeddecimal | func
public | int4_fixeddecimal_ne | boolean | integer, fixeddecimal | func
public | int4fixeddecimal | fixeddecimal | integer | func
public | int4fixeddecimaldiv | double precision | integer, fixeddecimal | func
public | int4fixeddecimalmi | fixeddecimal | integer, fixeddecimal | func
public | int4fixeddecimalmul | fixeddecimal | integer, fixeddecimal | func
public | int4fixeddecimalpl | fixeddecimal | integer, fixeddecimal | func
public | max | fixeddecimal | fixeddecimal | agg
public | min | fixeddecimal | fixeddecimal | agg
public | numeric_fixeddecimal | fixeddecimal | numeric | func
public | numeric_fixeddecimal_cmp | integer | numeric, fixeddecimal | func
public | numeric_fixeddecimal_eq | boolean | numeric, fixeddecimal | func
public | numeric_fixeddecimal_ge | boolean | numeric, fixeddecimal | func
public | numeric_fixeddecimal_gt | boolean | numeric, fixeddecimal | func
public | numeric_fixeddecimal_le | boolean | numeric, fixeddecimal | func
public | numeric_fixeddecimal_lt | boolean | numeric, fixeddecimal | func
public | numeric_fixeddecimal_ne | boolean | numeric, fixeddecimal | func
public | sum | fixeddecimal | fixeddecimal | agg
(102 rows)
对比fixeddecimal numeric性能
postgres=# create temp table t1 (id fixeddecimal);
CREATE TABLE
postgres=# create temp table t2 (id numeric);
CREATE TABLE
postgres=# insert into t1 select generate_series(1,10000000);
INSERT 0 10000000
postgres=# insert into t2 select generate_series(1,10000000);
INSERT 0 10000000
postgres=# select pg_column_size(id) from t1 limit 1;
pg_column_size
----------------
8
(1 row)
postgres=# select pg_column_size(id) from t2 limit 1;
pg_column_size
----------------
5
(1 row)
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
------------+----------------------+-------+----------+------------+-------------
pg_temp_13 | t1 | table | postgres | 346 MB |
pg_temp_13 | t2 | table | postgres | 346 MB |
-- fixeddecimal:
postgres=# explain analyze select sum(id),avg(id),min(id),max(id) from t1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=244248.96..244248.97 rows=1 width=32) (actual time=3348.701..3348.702 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..144248.48 rows=10000048 width=8) (actual time=0.134..1925.172 rows=10000000 loops=1)
Planning Time: 0.152 ms
Execution Time: 3348.950 ms
(4 rows)
postgres=# explain analyze select sum(id),avg(id),min(id),max(id) from t1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=244248.96..244248.97 rows=1 width=32) (actual time=2943.527..2943.528 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..144248.48 rows=10000048 width=8) (actual time=0.147..1543.519 rows=10000000 loops=1)
Planning Time: 0.065 ms
Execution Time: 2943.618 ms
(4 rows)
-- numeric:
postgres=# explain analyze select sum(id),avg(id),min(id),max(id) from t2;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=164602.57..164602.58 rows=1 width=128) (actual time=5507.606..5507.608 rows=1 loops=1)
-> Seq Scan on t2 (cost=0.00..104425.28 rows=6017728 width=32) (actual time=0.061..2850.369 rows=10000000 loops=1)
Planning Time: 0.091 ms
Execution Time: 5507.643 ms
(4 rows)
postgres=# explain analyze select sum(id),avg(id),min(id),max(id) from t2;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=164602.57..164602.58 rows=1 width=128) (actual time=3890.299..3890.300 rows=1 loops=1)
-> Seq Scan on t2 (cost=0.00..104425.28 rows=6017728 width=32) (actual time=0.124..1583.379 rows=10000000 loops=1)
Planning Time: 0.060 ms
Execution Time: 3890.340 ms
(4 rows)
fixeddecimal比numeric性能更好.
《PostgreSQL decimal64 decimal128 高效率数值 类型扩展》
https://github.com/vitesse-ftian/pgdecimal
有两个常见的decimal库，decNumber以及Intel提供的Intel ADX库。
pgdecimal插件选择了decNumber库.
https://github.com/gcc-mirror/gcc/tree/master/libdecnumber
decimal库的性能对比
http://speleotrove.com/decimal/dpintro.html
decNumber与Inter ADX性能接近，但是Inter ADX提供了decimal64/128, int32/64, float/double类型的相互转换，这个很给力。（也许将来vitesse会支持intel adx库吧）
pgdecimal 依赖的decNumber，因此我们必须先安装decNumber
1. 下载 decNumber package
http://speleotrove.com/decimal/
cd ~
wget http://speleotrove.com/decimal/decNumber-icu-368.zip
unzip decNumber-icu-368.zip
2. 安装decNumber到PolarDB for postgresql软件目录中(假设PolarDB for postgresql安装在/home/postgres/tmp_basedir_polardb_pg_1100_bld)
首先要在PolarDB for postgresql软件的include目录中，创建一个空目录，
mkdir -p /home/postgres/tmp_basedir_polardb_pg_1100_bld/include/decnumber
在decNumber src目录中创建Makefile，install -D 修改为对应要安装的目录。
cd ~/decNumber
## 如果makefile有问题, 缩进改成TAB键即可
vi Makefile
OBJS = decSingle.o decDouble.o decQuad.o decNumber.o decContext.o
CFLAGS = -Wall -g -O2 -fPIC
libdecnumber.a: $(OBJS)
ar -rcs libdecnumber.a $(OBJS)
clean:
rm -f libdecnumber.a $(OBJS)
install:
install -D *.h /home/postgres/tmp_basedir_polardb_pg_1100_bld/include/decnumber
install -D libdecnumber.a /home/postgres/tmp_basedir_polardb_pg_1100_bld/lib
3. 编译安装decNumber
cd ~/decNumber
make
make install
cd ~
git clone --depth 1 https://github.com/vitesse-ftian/pgdecimal
cd pgdecimal
有一个小BUG，.control的版本号没有与sql文件的版本号对齐
mv decimal--2.0.sql decimal--1.0.sql
另外，需要修改一下Makefile，指定版本，以及decnumber的include和lib目录
vi Makefile
PG_CPPFLAGS = -I/home/postgres/tmp_basedir_polardb_pg_1100_bld/include/decnumber
SHLIB_LINK = -L/home/postgres/tmp_basedir_polardb_pg_1100_bld/lib -ldecnumber
DATA = decimal--1.0.sql
由于PolarDB基于PG 11, 还需要修改一下GET_8_BYTES和SET_8_BYTES
vi decimal.c
- return SET_8_BYTES(myunion.retval);
+ return (Datum) (myunion.retval);
- myunion.value = GET_8_BYTES(X);
+ myunion.value = (Datum) (X);
相关代码:
src/include/postgres.h
380 #define GET_1_BYTE(datum) (((Datum) (datum)) & 0x000000ff)
381 #define GET_2_BYTES(datum) (((Datum) (datum)) & 0x0000ffff)
382 #define GET_4_BYTES(datum) (((Datum) (datum)) & 0xffffffff)
383 #if SIZEOF_DATUM == 8
384 #define GET_8_BYTES(datum) ((Datum) (datum))
385 #endif
386 #define SET_1_BYTE(value) (((Datum) (value)) & 0x000000ff)
387 #define SET_2_BYTES(value) (((Datum) (value)) & 0x0000ffff)
388 #define SET_4_BYTES(value) (((Datum) (value)) & 0xffffffff)
389 #if SIZEOF_DATUM == 8
390 #define SET_8_BYTES(value) ((Datum) (value))
391 #endif
安装pgdecimal
USE_PGXS=1 make clean
USE_PGXS=1 make
USE_PGXS=1 make install
使用 pgdecimal
psql
postgres=# create extension decimal;
CREATE EXTENSION
使用int8, float8, decimal64, decimal128, numeric(15,3) 几种类型，分别比较这几种类型的性能。
create table tt(ii bigint, d double precision, d64 decimal64, d128 decimal128, n numeric(15, 3));
postgres=# \timing
Timing is on.
生成测试数据
postgres=# 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
postgres=# select * from tt limit 2;
ii | d | d64 | d128 | n
----+-------+-------+-------+-------
1 | 1.123 | 1.123 | 1.123 | 1.123
2 | 2.123 | 2.123 | 2.123 | 2.123
(2 rows)
postgres=# \d tt
Table "public.tt"
Column | Type | Collation | Nullable | Default
--------+------------------+-----------+----------+---------
ii | bigint | | |
d | double precision | | |
d64 | decimal64 | | |
d128 | decimal128 | | |
n | numeric(15,3) | | |
postgres=# select pg_column_size(ii), pg_column_size(d),pg_column_size(d64),pg_column_size(d128),pg_column_size(n) from tt limit 1;
pg_column_size | pg_column_size | pg_column_size | pg_column_size | pg_column_size
----------------+----------------+----------------+----------------+----------------
8 | 8 | 8 | 16 | 7
(1 row)
普通查询性能对比
postgres=# select count(*) from tt where (d + d*d + d*d*d + d*d*d*d) > 10000000;
count
--------
999945
(1 row)
Time: 184.109 ms
postgres=# select count(*) from tt where (n + n*n + n*n*n + n*n*n*n) > 10000000;
count
--------
999945
(1 row)
Time: 572.717 ms
postgres=# select count(*) from tt where (d64 + d64*d64 + d64*d64*d64 + d64*d64*d64*d64) > 10000000;
count
--------
999945
(1 row)
Time: 910.323 ms
postgres=# select count(*) from tt where (d128 + d128*d128 + d128*d128*d128 + d128*d128*d128*d128) > 10000000;
count
--------
999945
(1 row)
Time: 1205.731 ms (00:01.206)
排序性能对比
postgres=# select * from tt order by d limit 2 offset 999000;
ii | d | d64 | d128 | n
--------+------------+------------+------------+------------
999001 | 999001.123 | 999001.123 | 999001.123 | 999001.123
999002 | 999002.123 | 999002.123 | 999002.123 | 999002.123
(2 rows)
Time: 426.318 ms
postgres=# select * from tt order by n limit 2 offset 999000;
ii | d | d64 | d128 | n
--------+------------+------------+------------+------------
999001 | 999001.123 | 999001.123 | 999001.123 | 999001.123
999002 | 999002.123 | 999002.123 | 999002.123 | 999002.123
(2 rows)
Time: 550.317 ms
postgres=# select * from tt order by d64 limit 2 offset 999000;
ii | d | d64 | d128 | n
--------+------------+------------+------------+------------
999001 | 999001.123 | 999001.123 | 999001.123 | 999001.123
999002 | 999002.123 | 999002.123 | 999002.123 | 999002.123
(2 rows)
Time: 454.495 ms
postgres=# select * from tt order by d128 limit 2 offset 999000;
ii | d | d64 | d128 | n
--------+------------+------------+------------+------------
999001 | 999001.123 | 999001.123 | 999001.123 | 999001.123
999002 | 999002.123 | 999002.123 | 999002.123 | 999002.123
(2 rows)
Time: 521.732 ms
哈希JOIN性能对比
postgres=# explain select count(*) from tt t1 join tt t2 on t1.d64 * t1.d64 + t1.d64 = t2.d64 + t2.d64 * t2.d64;
QUERY PLAN
----------------------------------------------------------------------------------
Aggregate (cost=6444771894.00..6444771894.01 rows=1 width=8)
-> Hash Join (cost=35867.50..5273007844.00 rows=468705620000 width=0)
Hash Cond: (((t1.d64 * t1.d64) + t1.d64) = (t2.d64 + (t2.d64 * t2.d64)))
-> Seq Scan on tt t1 (cost=0.00..19982.00 rows=968200 width=8)
-> Hash (cost=19982.00..19982.00 rows=968200 width=8)
-> Seq Scan on tt t2 (cost=0.00..19982.00 rows=968200 width=8)
(6 rows)
Time: 1.487 ms
postgres=# select count(*) from tt t1 join tt t2 on t1.d64 * t1.d64 + t1.d64 = t2.d64 + t2.d64 * t2.d64;
count
---------
1000000
(1 row)
Time: 1785.457 ms (00:01.785)
postgres=# select count(*) from tt t1 join tt t2 on t1.n * t1.n + t1.n = t2.n + t2.n * t2.n;
count
---------
1000000
(1 row)
Time: 1751.460 ms (00:01.751)
嵌套循环性能对比
postgres=# explain select count(*) from tt t1, tt t2 where t1.ii < 10000 and t2.ii < 10000 and t1.d * t1.d + t1.d > t2.d + t2.d * t2.d;
QUERY PLAN
--------------------------------------------------------------------------------
Aggregate (cost=3097723814.80..3097723814.81 rows=1 width=8)
-> Nested Loop (cost=0.00..3010926657.06 rows=34718863096 width=0)
Join Filter: (((t1.d * t1.d) + t1.d) > (t2.d + (t2.d * t2.d)))
-> Seq Scan on tt t1 (cost=0.00..22402.50 rows=322733 width=8)
Filter: (ii < 10000)
-> Materialize (cost=0.00..25277.17 rows=322733 width=8)
-> Seq Scan on tt t2 (cost=0.00..22402.50 rows=322733 width=8)
Filter: (ii < 10000)
(8 rows)
Time: 1.408 ms
postgres=# select count(*) from tt t1, tt t2 where t1.ii < 10000 and t2.ii < 10000 and t1.d * t1.d + t1.d > t2.d + t2.d * t2.d;
count
----------
49985001
(1 row)
Time: 24398.160 ms (00:24.398)
postgres=# select count(*) from tt t1, tt t2 where t1.ii < 10000 and t2.ii < 10000 and t1.n * t1.n + t1.n > t2.n + t2.n * t2.n;
count
----------
49985001
(1 row)
Time: 60969.515 ms (01:00.970)
postgres=# select count(*) from tt t1, tt t2 where t1.ii < 10000 and t2.ii < 10000 and t1.d64 * t1.d64 + t1.d64 > t2.d64 + t2.d64 * t2.d64;
count
----------
49985001
(1 row)
Time: 39702.844 ms (00:39.703)
postgres=# select count(*) from tt t1, tt t2 where t1.ii < 10000 and t2.ii < 10000 and t1.d128 * t1.d128 + t1.d128 > t2.d128 + t2.d128 * t2.d128;
count
----------
49985001
(1 row)
Time: 54284.330 ms (00:54.284)
性能最好的是float8，其次是decimal64, decimal64不需要使用palloc，性能比numeric好1.5倍左右，而decimal128也比numeric性能好不少。
