float和numeric性能对比

背景

由于PostgreSQL自己实现了numeric这个数据类型，所以我们对比一下它和double的性能差别在哪里？

创建测试表：

postgres=# create table tt(c1 numeric,c2 numeric);  
CREATE TABLE  
postgres=# create table tf(c1 float,c2 float);  
CREATE TABLE  
postgres=# alter table tt alter column c1 set storage plain;  
ALTER TABLE  
postgres=# alter table tt alter column c2 set storage plain;  
ALTER TABLE  
postgres=# insert into tt values (1.1111,1.1111);  
INSERT 0 1  
postgres=# insert into tf values (1.1111,1.1111);  
INSERT 0 1  

算术运算测试

numeric类型

postgres@digoal-> vi test.sql  
\setrandom id 1 100000  
select c1+c2,c1*c2,c1/c2,c1-c2 from tt;  
  
postgres@digoal-> pgbench -M simple -n -r -P 1 -f ./test.sql -c 8 -j 8 -T 10  
progress: 1.0 s, 34614.9 tps, lat 0.222 ms stddev 0.575  
progress: 2.0 s, 35814.5 tps, lat 0.222 ms stddev 0.336  
progress: 3.0 s, 35636.9 tps, lat 0.223 ms stddev 0.145  
progress: 4.0 s, 35028.1 tps, lat 0.227 ms stddev 0.114  
progress: 5.0 s, 35029.7 tps, lat 0.227 ms stddev 0.118  
progress: 6.0 s, 34949.7 tps, lat 0.228 ms stddev 0.100  

浮点类型

postgres@digoal-> vi test.sql  
select c1+c2,c1*c2,c1/c2,c1-c2 from tf;  
  
postgres@digoal-> pgbench -M simple -n -r -P 1 -f ./test.sql -c 8 -j 8 -T 10  
progress: 1.0 s, 33783.2 tps, lat 0.224 ms stddev 0.569  
progress: 2.0 s, 34801.2 tps, lat 0.228 ms stddev 0.091  
progress: 3.0 s, 34580.0 tps, lat 0.230 ms stddev 0.104  
progress: 4.0 s, 34729.3 tps, lat 0.229 ms stddev 0.080  
progress: 5.0 s, 34698.9 tps, lat 0.229 ms stddev 0.091  
progress: 6.0 s, 34148.4 tps, lat 0.233 ms stddev 0.436  

浮点数计算测试，求平方根和立方根

numeric类型

postgres@digoal-> vi test.sql  
select |/c1, ||/c1, c1+c2,c1*c2,c1/c2,c1-c2 from tt;  
  
postgres@digoal-> pgbench -M simple -n -r -P 1 -f ./test.sql -c 8 -j 8 -T 6  
progress: 1.0 s, 26325.5 tps, lat 0.280 ms stddev 1.503  
progress: 2.0 s, 30850.7 tps, lat 0.258 ms stddev 0.118  
progress: 3.0 s, 30162.7 tps, lat 0.264 ms stddev 0.469  
progress: 4.0 s, 29400.8 tps, lat 0.271 ms stddev 0.761  
progress: 5.0 s, 29819.5 tps, lat 0.267 ms stddev 0.068  
progress: 6.0 s, 29666.2 tps, lat 0.268 ms stddev 0.092  

浮点类型

postgres@digoal-> vi test.sql  
select |/c1, ||/c1, c1+c2,c1*c2,c1/c2,c1-c2 from tf;  
  
postgres@digoal-> pgbench -M simple -n -r -P 1 -f ./test.sql -c 8 -j 8 -T 6  
progress: 1.0 s, 29492.5 tps, lat 0.257 ms stddev 0.661  
progress: 2.0 s, 31574.9 tps, lat 0.252 ms stddev 0.110  
progress: 3.0 s, 30789.9 tps, lat 0.258 ms stddev 0.713  
progress: 4.0 s, 31586.5 tps, lat 0.252 ms stddev 0.122  
progress: 5.0 s, 31376.2 tps, lat 0.254 ms stddev 0.113  
progress: 6.0 s, 30528.7 tps, lat 0.261 ms stddev 0.804  

浮点计算能力还可以用计算pi来测试。

postgres=# with recursive pi(lv,c) as(select 1::numeric lv, 1::numeric c  
union all  
select lv+1 lv, sqrt((c/2)*(c/2)+(1-sqrt(1-(c/2)*(c/2)))*(1-sqrt(1-(c/2)*(c/2))))c  
from pi where lv<70  
)  
select 3*power(2,lv)*c/2 p from pi where lv=70;  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 3.141592653589793238462643383279502884197173210676508828591799173627672296937730683572727070051247010385408657297906552561934357541748941419646573653781176751502075275364894766592919774594486776874289831682259435431753374790218811531309  
680127251335811196919015814326437780537106443311977254069719721230342031049561475087911964609567004428452253759748140674953204696747100314818765448066726464044548271610562612419951498432236962881311986436720693282807069197840168203412882  
330865420521682237840223652454295014406725123279478939773441267345198930346982182598393806453993932030504130776155364994041414832433907918479228544876228103179194096407101131681883595844997406888708840813129610672352522895911735472301296  
264619790416226452429886388202065527847451560867262943447896420329981704754176383705004206817426442340965787246827730834011310772289022931085988576767674833771312642269035423307305893857506497484077402126223946040174768095391028677170542  
3017050589465937844090932027387228042122862798288453632  
(1 row)  
Time: 513.449 ms  
  
postgres=# with recursive pi(lv,c) as(select 1::float lv, 1::float c      
union all  
select lv+1 lv, sqrt((c/2)*(c/2)+(1-sqrt(1-(c/2)*(c/2)))*(1-sqrt(1-(c/2)*(c/2))))c  
from pi where lv<70  
)  
select 3*power(2,lv)*c/2 p from pi where lv=70;  
        p           
------------------  
 3.14159265358979  
(1 row)  
Time: 1.431 ms  

算术运算，numeric性能比double略好。

浮点数计算方面，numeric性能比double差很多，但是numeric可以计算更多的位数。

实际上固定长度的值(float, int)还有一个好处，支持CPU向量指令计算。而numeric不行，在海量数据分析场景非常有利。

digoal’s 大量PostgreSQL文章入口