在postgresql9.5的时候做过一个测试就是sum()的效率最终的测试结果是sum(int)>sum(numeric)>sum(bigint)当时比较诧异为啥sum(bigint)效率比sum(numeric)还低。sum(numeric)的效率比sum(bigint)快了10%。

在pg10版本的时候对sum()的性能做了优化，pg10.4

最终的测试结果为pg10的效率大幅提升，sum(int)>sum(bigint)>sum(numeric)，当一个表中有bigint，int时，谁放在第一列效率要高点。但是差别不是很大，效率都比numeric高。

bigint for smallint or int arguments, numeric for bigint arguments, otherwise the same as the argument data type

这次主要做abase5.0测试，以及pg11 jit测试。

插入1kw数据测试。

这里只是输出类型的转换，并不会太影响效率。
numeric的算术运算比整数类型要慢很多。
通过求助，最终了解到可能和pg的元组变形(tuple deform)有关,
这次创建三张表分别对应三种数据类型。
create table t_int(n_int int);
create table t_bigint(n_bigint bigint);
create table t_numeric(n_numeric numeric);
insert into t_int select generate_series(1,10000000);
insert into t_bigint select generate_series(1,10000000);
insert into t_numeric select generate_series(1,10000000);
 
https://blog.csdn.net/luojinbai/article/details/42914299
 
numeric :https://explain.depesz.com/s/Lk6P
int:https://explain.depesz.com/s/4Xo4
bigint:https://explain.depesz.com/s/niQI
 
pg:bigint,numeric,int效率测试：
drop table t_int;
drop table t_bigint;
drop table t_numeric;
show shared_buffers;
 
drop table t_bigint
create table t_int(n_int int);
create table t_bigint(n_bigint bigint);
create table t_numeric(n_numeric numeric);
insert into t_int select generate_series(1,10000000);
insert into t_bigint select generate_series(1,10000000);
insert into t_numeric select generate_series(1,10000000);
numeric
https://explain.depesz.com/s/Ivks
select version();
explain analyze
select count(*) from t_num_type
 
SET max_parallel_workers_per_gather = 2;
show max_parallel_workers_per_gather ;
 
select version();
1.单表测试
explain (analyze,buffers,format text) select sum(n_int) from t_int;–560
explain (analyze,buffers,format text) select sum(n_bigint) from t_bigint;–575
explain (analyze,buffers,format text) select sum(n_numeric) from t_numeric;–868
sum(int)>sum(bigint)>sum(numeric)
2.一个表测试
drop table t_num_type
create table t_num_type(n_bigint bigint,n_numeric numeric,n_int int);
insert into t_num_type select n,n,n from generate_series(1,10000000) as t(n)
explain (analyze,buffers,format text) select sum(n_int) from t_num_type ;–661
explain (analyze,buffers,format text) select sum(n_bigint) from t_num_type;–625
explain (analyze,buffers,format text) select sum(n_numeric) from t_num_type;–946
 
sum(bigint)>sum(int)>sum(numeric)
但是整体比单表慢。
select * from t_num_type_3 limit 10
 
drop table t_num_type_2
create table t_num_type_2(n_int int,n_numeric numeric,n_bigint bigint);
insert into t_num_type_2 select n,n,n from generate_series(1,10000000) as t(n)
explain (analyze,buffers,format text) select sum(n_int) from t_num_type_2;–603
explain (analyze,buffers,format text) select sum(n_bigint) from t_num_type_2;–668
explain (analyze,buffers,format text) select sum(n_numeric) from t_num_type_2;–947
sum(int)>sum(bigint)>sum(numeric)
–show jit_above_cost
int放前面int快，bigint又慢了。
 
3.
create table t_num_type_3(n_bigint bigint,n_int int,n_numeric numeric);
insert into t_num_type_3 select n,n,n from generate_series(1,10000000) as t(n)
explain (analyze,buffers,format text) select sum(n_int) from t_num_type_3;–623
explain (analyze,buffers,format text) select sum(n_bigint) from t_num_type_3;–616
explain (analyze,buffers,format text) select sum(n_numeric) from t_num_type_3;–973
 
目前来bigint放到第一列总是快的。当int放到第一列的时候又比bigint快。
 
 
create table t_num_type_4(n_int int,n_bigint bigint,n_numeric numeric);
insert into t_num_type_4 select n,n,n from generate_series(1,10000000) as t(n)
explain (analyze,buffers,format text) select sum(n_int) from t_num_type_4;–617
explain (analyze,buffers,format text) select sum(n_bigint) from t_num_type_4;–643
explain (analyze,buffers,format text) select sum(n_numeric) from t_num_type_4;–973