PostgreSQL VOPS 向量计算 + DBLINK异步并行 - 单实例 10亿 聚合计算跑进2秒
背景
目前PostgreSQL已内置了并行计算的能力（透明和非透明）。
结合列存+向量计算，PostgreSQL 统计分析能力到底能有多强？
本文以vops（向量计算） + 并行计算为例，演示了单实例PostgreSQL 10亿数据量的count,avg,min,max,sum查询，耗时不到2秒。
我们可以大胆的预见PostgreSQL支持列存和向量计算后，OLAP能力会有多强。
相关文档：
《PostgreSQL 11 preview - parallel hash (含hash JOIN , hash agg等) 性能极大提升》
《PostgreSQL 向量化执行插件(瓦片式实现-vops) 10x提速OLAP》
DEMO
1、创建生成dblink连接的函数，重复创建不报错。
create or replace function conn(
name, -- dblink名字
text -- 连接串,URL
) returns void as $$
declare
begin
perform dblink_connect($1, $2);
return;
exception when others then
return;
end;
$$ language plpgsql strict;
2、生成10亿条数据，并按哈希分成56份（如果是多张表的话，请都按被JOIN的字段进行HASH分区）。
为了测试方便，这里将数据按同样的RANGE分成了56份，实际上使用HASH效果一样。
create unlogged table a(id int, c1 int);
do language plpgsql $$
declare
begin
for i in 0..55 loop
execute format('create unlogged table a%s (like a) inherits (a)', i);
end loop;
end;
$$;
生成10亿测试数据
do language plpgsql $$
declare
begin
for i in 0..55 loop
perform conn('link'||i, 'hostaddr=127.0.0.1 port=1921 user=postgres dbname=postgres');
perform dblink_send_query('link'||i, format('insert into a%s select generate_series(%s, %s), random()*99', i, i*17857142+1, (i+1)*17857142));
end loop;
end;
$$;
1、普通分片表10亿
1、空间占用，34GB
postgres=# \dt+ a*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+---------+-------------
public | a | table | postgres | 0 bytes |
public | a0 | table | postgres | 617 MB |
public | a1 | table | postgres | 617 MB |
public | a10 | table | postgres | 617 MB |
public | a11 | table | postgres | 617 MB |
public | a12 | table | postgres | 617 MB |
public | a13 | table | postgres | 617 MB |
public | a14 | table | postgres | 617 MB |
public | a15 | table | postgres | 617 MB |
public | a16 | table | postgres | 617 MB |
.............
2、已使用DBLINK异步调用，防止再度并行，设置参数
alter role postgres set max_parallel_workers_per_gather=0;
alter role postgres set work_mem='2GB';
重新进入，参数生效
3、分片并行group by聚合函数
create or replace function get_res() returns setof record as $$
declare
begin
for i in 0..55 loop
perform conn('link'||i, 'hostaddr=127.0.0.1 port=1921 user=postgres dbname=postgres');
perform 1 from dblink_get_result('link'||i) as t(c1 int, cnt int8, min int, max int, sum int8);
perform dblink_send_query('link'||i, format('select c1,count(*),min(id),max(id),sum(id) from a%s group by c1', i));
end loop;
for i in 0..55 loop
return query select * from dblink_get_result('link'||i) as t(c1 int, cnt int8, min int, max int, sum int8);
end loop;
end;
$$ language plpgsql strict;
4、查询耗时，9.6秒。
select c1, sum(cnt), sum(sum)/sum(cnt)::float8, min(min), max(max), sum(sum) from get_res() as t(c1 int, cnt int8, min int, max int, sum int8) group by c1;
postgres=# select c1, sum(cnt), sum(sum)/sum(cnt)::float8, min(min), max(max), sum(sum) from get_res() as t(c1 int, cnt int8, min int, max int, sum int8) group by c1;
c1 | sum | ?column? | min | max | sum
----+----------+------------------+-----+-----------+------------------
7 | 10104223 | 500196670.66755 | 13 | 999999922 | 5054098704282486
41 | 10098079 | 500105983.312797 | 75 | 999999899 | 5050109727865311
72 | 10099035 | 499987853.746656 | 3 | 999999917 | 5049394834562362
2 | 10098707 | 500027259.278005 | 119 | 999999881 | 5049628783461600
29 | 10101931 | 500172863.741008 | 16 | 999999911 | 5052711757584067
71 | 10099125 | 500129933.363706 | 51 | 999999877 | 5050874713281733
6 | 10104713 | 500020892.341227 | 20 | 999999943 | 5052567611111995
74 | 10098824 | 499966081.890066 | 170 | 999999890 | 5049069466977368
38 | 10103601 | 499961610.602928 | 89 | 999999949 | 5051412628849349
88 | 10103668 | 499850251.056789 | 64 | 999999780 | 5050320986394444
84 | 10105345 | 500042003.07277 | 271 | 999999895 | 5053096955541396
68 | 10098999 | 499977042.1773 | 59 | 999999857 | 5049267648971511
55 | 10103707 | 499960231.761415 | 45 | 999999850 | 5051451693369436
64 | 10102942 | 500113184.404115 | 47 | 999999693 | 5052614495470081
24 | 10099982 | 499936812.84724 | 190 | 999999901 | 5049352810894492
9 | 10104341 | 499996733.660471 | 23 | 999999915 | 5052137495791580
63 | 10102023 | 499895229.112864 | 353 | 999999910 | 5049953102088419
98 | 10097327 | 499944814.066904 | 22 | 999999919 | 5048106269587731
59 | 10099747 | 499892322.509487 | 2 | 999999808 | 5048785984588223
78 | 10107866 | 500012506.299485 | 69 | 999999740 | 5054059411999348
18 | 10101977 | 499926956.667668 | 4 | 999999897 | 5050250617936782
90 | 10096744 | 500199293.810021 | 43 | 999999875 | 5050384218580567
42 | 10101533 | 500126553.670129 | 12 | 999999865 | 5052044886075076
10 | 10095854 | 500035390.09691 | 17 | 999999719 | 5048284293251445
79 | 10098900 | 499957103.416255 | 114 | 999999846 | 5049016791690421
12 | 10105025 | 499832509.132034 | 109 | 999999799 | 5050820000591935
32 | 10099491 | 500129108.718979 | 84 | 999999914 | 5051049432345352
81 | 10106622 | 499963749.199852 | 229 | 999999944 | 5052944626865710
34 | 10099359 | 500111032.921868 | 52 | 999999836 | 5050800861338764
85 | 10103461 | 499947692.923157 | 18 | 999999904 | 5051202017489091
37 | 10099584 | 500057275.745026 | 104 | 999999849 | 5050370461198054
69 | 10097014 | 500143184.435491 | 91 | 999999720 | 5049952735249734
52 | 10101573 | 500040171.388144 | 355 | 999999932 | 5051192294209851
76 | 10101689 | 499896977.810699 | 438 | 999999843 | 5049803801883577
35 | 10099622 | 500114054.898034 | 44 | 999999747 | 5050962911357394
65 | 10103111 | 499851261.81228 | 48 | 999999726 | 5050052781579531
31 | 10099277 | 499845188.908577 | 112 | 999999838 | 5048075019905046
51 | 10099397 | 499984737.203741 | 65 | 999999933 | 5049544354961246
57 | 10103823 | 500008680.942373 | 100 | 999999925 | 5051999210705205
19 | 10102592 | 500036435.174349 | 81 | 999999939 | 5051664089700899
33 | 10096464 | 500058135.753455 | 66 | 999999773 | 5048818965541870
0 | 5048622 | 500110433.47565 | 263 | 999999912 | 2524868536874701
50 | 10099019 | 500071716.158314 | 177 | 999999894 | 5050233762845416
89 | 10102886 | 499906895.352583 | 21 | 999999941 | 5050502374361080
66 | 10100455 | 500019718.104865 | 11 | 999999889 | 5050426661830877
17 | 10099749 | 500020536.906571 | 125 | 999999900 | 5050081917601604
14 | 10097995 | 499872474.436678 | 50 | 999999887 | 5047709747499202
87 | 10100597 | 499967794.215203 | 14 | 999999907 | 5049973202346696
67 | 10105426 | 500125756.387578 | 245 | 999999866 | 5053983821868692
96 | 10099064 | 499905578.187197 | 243 | 999999892 | 5048578428069509
61 | 10100503 | 500007920.183475 | 28 | 999999903 | 5050331497836948
3 | 10101198 | 499940146.381583 | 30 | 999999883 | 5049994406749350
39 | 10098880 | 500243255.820608 | 32 | 999999920 | 5051896611341620
11 | 10101799 | 500096301.902747 | 129 | 999999916 | 5051872322464871
43 | 10107301 | 500040498.08918 | 163 | 999999842 | 5054059826377266
5 | 10100558 | 499863342.325278 | 31 | 999999940 | 5048898681230323
75 | 10101250 | 500037496.222365 | 63 | 999999928 | 5051003758716163
97 | 10103503 | 500029746.921376 | 123 | 999999921 | 5052052048109365
21 | 10102929 | 500098408.485223 | 446 | 999999926 | 5052458713939206
93 | 10105004 | 499972842.513604 | 25 | 999999852 | 5052227573491343
60 | 10105650 | 499950744.869079 | 209 | 999999873 | 5052327244886207
27 | 10102588 | 499797776.872304 | 41 | 999999858 | 5049251023056813
45 | 10100466 | 499847713.021728 | 38 | 999999841 | 5048694830553717
70 | 10103640 | 500126978.162685 | 238 | 999999801 | 5053102941643626
91 | 10102311 | 499764530.032 | 1 | 999999950 | 5048776709152101
22 | 10099804 | 500243130.452219 | 250 | 999999867 | 5052357569913843
49 | 10099635 | 500022230.537738 | 19 | 999999896 | 5050042020317004
13 | 10100454 | 499986238.143486 | 53 | 999999646 | 5050087999001322
86 | 10099751 | 499991374.31582 | 7 | 999999782 | 5049788382737577
26 | 10105606 | 500021405.572664 | 6 | 999999938 | 5053019316283545
1 | 10095409 | 500001476.917716 | 206 | 999999776 | 5047719410088402
58 | 10099972 | 499913934.276812 | 35 | 999999792 | 5049116738605642
25 | 10104666 | 500031883.894369 | 137 | 999999510 | 5052655176103381
82 | 10101286 | 499966805.257835 | 10 | 999999534 | 5050307690415695
44 | 10104380 | 499990027.500454 | 9 | 999999872 | 5052089234075034
20 | 10100166 | 500033955.063915 | 130 | 999999908 | 5050425951782085
23 | 10099118 | 500033099.525857 | 58 | 999999918 | 5049893276017372
92 | 10097628 | 499985682.503551 | 79 | 999999951 | 5048669427246965
62 | 10098974 | 500002801.70268 | 33 | 999999765 | 5049515294322520
53 | 10104969 | 500071097.824301 | 5 | 999999948 | 5053202941310532
56 | 10099521 | 499829373.88272 | 157 | 999999902 | 5048037257945384
40 | 10098880 | 500118160.98974 | 202 | 999999731 | 5050633293656069
73 | 10102325 | 499981690.451386 | 62 | 999999809 | 5050977530989301
95 | 10102589 | 499885307.442653 | 74 | 999999770 | 5050135808231760
4 | 10094812 | 499884810.734528 | 37 | 999999815 | 5046243186020638
30 | 10097653 | 499985894.883754 | 127 | 999999861 | 5048684071430626
77 | 10097737 | 500019227.640071 | 34 | 999999751 | 5049062655652572
15 | 10100451 | 499990119.574357 | 86 | 999999946 | 5050125703244929
36 | 10097433 | 499910715.309699 | 60 | 999999952 | 5047814953821765
94 | 10097423 | 500030504.883845 | 102 | 999999930 | 5049019520715746
83 | 10097518 | 499986390.218504 | 8 | 999999777 | 5048621574986367
28 | 10102778 | 499892202.902798 | 147 | 999999929 | 5050299949857921
48 | 10095113 | 500116265.596128 | 171 | 999999942 | 5048730214330929
46 | 10101708 | 499972043.149036 | 24 | 999999878 | 5050571588054961
99 | 5051395 | 499818438.433132 | 39 | 999999936 | 2524780360808931
47 | 10106118 | 500030970.343216 | 29 | 999999844 | 5053371989943041
54 | 10096312 | 500084305.08323 | 77 | 999999934 | 5049007170423474
16 | 10104458 | 500003081.506687 | 36 | 999999800 | 5052260136954892
80 | 10102160 | 499846266.993651 | 54 | 999999945 | 5049526964572583
8 | 10103060 | 500007130.726843 | 136 | 999999891 | 5051602042161142
(100 rows)
Time: 9608.447 ms (00:09.608)
5、CPU资源
top - 16:25:09 up 30 days, 21:12, 2 users, load average: 15.10, 6.80, 6.63
Tasks: 522 total, 57 running, 465 sleeping, 0 stopped, 0 zombie
%Cpu(s): 99.8 us, 0.2 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 23094336+total, 9608932 free, 5427976 used, 21590646+buff/cache
KiB Swap: 0 total, 0 free, 0 used. 10311448+avail Mem
2、VOPS瓦片式存储表10亿
1、创建VOPS表
create unlogged table b(id vops_int4, c1 vops_int4);
do language plpgsql $$
declare
begin
for i in 0..55 loop
execute format('create unlogged table b%s (like b) inherits (b)', i);
end loop;
end;
$$;
2、将10亿原始数据转换为vops
do language plpgsql $$
declare
begin
for i in 0..55 loop
perform conn('link'||i, 'hostaddr=127.0.0.1 port=1921 user=postgres dbname=postgres');
perform dblink_send_query('link'||i, format('select populate(''b%s''::regclass, ''a%s''::regclass)', i, i));
end loop;
end;
$$;
3、数据样例
postgres=# select * from b0 limit 2;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64}
c1 | {91,59,72,18,53,26,86,83,44,82,66,42,7,87,66,29,10,85,49,6,89,98,9,46,93,7,42,61,47,3,5,39,62,77,58,16,4,45,99,49,27,66,90,35,55,58,64,65,44,14,71,34,13,80,80,7,87,23,68,36,26,73,75,88}
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | {65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128}
c1 | {51,33,6,55,78,5,5,7,72,95,41,27,54,6,92,98,19,64,34,32,45,15,39,33,38,9,69,64,82,45,53,34,78,59,90,57,64,94,64,37,91,6,64,46,12,58,45,31,23,79,64,68,94,4,2,33,13,71,97,95,17,51,30,95}
postgres=# select * from a0 limit 2;
-[ RECORD 1 ]
id | 1
c1 | 91
-[ RECORD 2 ]
id | 2
c1 | 59
4、空间占用，8.7GB
postgres=# \dt+ b*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+---------+-------------
public | b | table | postgres | 0 bytes |
public | b0 | table | postgres | 156 MB |
public | b1 | table | postgres | 156 MB |
public | b10 | table | postgres | 156 MB |
public | b11 | table | postgres | 156 MB |
public | b12 | table | postgres | 156 MB |
public | b13 | table | postgres | 156 MB |
public | b14 | table | postgres | 156 MB |
public | b15 | table | postgres | 156 MB |
public | b16 | table | postgres | 156 MB |
public | b17 | table | postgres | 156 MB |
public | b18 | table | postgres | 156 MB |
public | b19 | table | postgres | 156 MB |
public | b2 | table | postgres | 156 MB |
public | b20 | table | postgres | 156 MB |
5、vops（瓦片式存储）分片表并行聚合
create or replace function get_res_vops() returns setof record as $$
declare
begin
for i in 0..55 loop
perform conn('link'||i, 'hostaddr=127.0.0.1 port=1921 user=postgres dbname=postgres');
perform 1 from dblink_get_result('link'||i) as t(c1 int, cnt int8, aggs int8[]);
perform dblink_send_query('link'||i, format('select (reduce(map(c1, ''min,max,sum'', id,id,id))).* from b%s', i));
end loop;
for i in 0..55 loop
return query select * from dblink_get_result('link'||i) as t(c1 int, cnt int8, aggs int8[]);
end loop;
end;
$$ language plpgsql strict;
6、查询耗时，1.97秒
select c1, sum(cnt), sum(aggs[3])/sum(cnt)::float8, min(aggs[1]), max(aggs[2]), sum(aggs[3]) from ( select * from get_res_vops() as t(c1 int, cnt int8, aggs int8[])) t group by c1;
postgres=# select c1, sum(cnt), sum(aggs[3])/sum(cnt)::float8, min(aggs[1]), max(aggs[2]), sum(aggs[3]) from ( select * from get_res_vops() as t(c1 int, cnt int8, aggs int8[])) t group by c1;
c1 | sum | ?column? | min | max | sum
----+----------+------------------+-----+-----------+------------------
7 | 10104223 | 500196670.66755 | 13 | 999999922 | 5054098704282486
41 | 10098079 | 500105983.312797 | 75 | 999999899 | 5050109727865311
72 | 10099035 | 499987853.746656 | 3 | 999999917 | 5049394834562362
2 | 10098707 | 500027259.278005 | 119 | 999999881 | 5049628783461600
29 | 10101931 | 500172863.741008 | 16 | 999999911 | 5052711757584067
71 | 10099125 | 500129933.363706 | 51 | 999999877 | 5050874713281733
6 | 10104713 | 500020892.341227 | 20 | 999999943 | 5052567611111995
74 | 10098824 | 499966081.890066 | 170 | 999999890 | 5049069466977368
38 | 10103601 | 499961610.602928 | 89 | 999999949 | 5051412628849349
88 | 10103668 | 499850251.056789 | 64 | 999999780 | 5050320986394444
84 | 10105345 | 500042003.07277 | 271 | 999999895 | 5053096955541396
68 | 10098999 | 499977042.1773 | 59 | 999999857 | 5049267648971511
55 | 10103707 | 499960231.761415 | 45 | 999999850 | 5051451693369436
64 | 10102942 | 500113184.404115 | 47 | 999999693 | 5052614495470081
24 | 10099982 | 499936812.84724 | 190 | 999999901 | 5049352810894492
9 | 10104341 | 499996733.660471 | 23 | 999999915 | 5052137495791580
63 | 10102023 | 499895229.112864 | 353 | 999999910 | 5049953102088419
98 | 10097327 | 499944814.066904 | 22 | 999999919 | 5048106269587731
78 | 10107866 | 500012506.299485 | 69 | 999999740 | 5054059411999348
59 | 10099747 | 499892322.509487 | 2 | 999999808 | 5048785984588223
18 | 10101977 | 499926956.667668 | 4 | 999999897 | 5050250617936782
90 | 10096744 | 500199293.810021 | 43 | 999999875 | 5050384218580567
42 | 10101533 | 500126553.670129 | 12 | 999999865 | 5052044886075076
79 | 10098900 | 499957103.416255 | 114 | 999999846 | 5049016791690421
10 | 10095854 | 500035390.09691 | 17 | 999999719 | 5048284293251445
12 | 10105025 | 499832509.132034 | 109 | 999999799 | 5050820000591935
32 | 10099491 | 500129108.718979 | 84 | 999999914 | 5051049432345352
81 | 10106622 | 499963749.199852 | 229 | 999999944 | 5052944626865710
34 | 10099359 | 500111032.921868 | 52 | 999999836 | 5050800861338764
85 | 10103461 | 499947692.923157 | 18 | 999999904 | 5051202017489091
37 | 10099584 | 500057275.745026 | 104 | 999999849 | 5050370461198054
69 | 10097014 | 500143184.435491 | 91 | 999999720 | 5049952735249734
52 | 10101573 | 500040171.388144 | 355 | 999999932 | 5051192294209851
76 | 10101689 | 499896977.810699 | 438 | 999999843 | 5049803801883577
35 | 10099622 | 500114054.898034 | 44 | 999999747 | 5050962911357394
65 | 10103111 | 499851261.81228 | 48 | 999999726 | 5050052781579531
31 | 10099277 | 499845188.908577 | 112 | 999999838 | 5048075019905046
51 | 10099397 | 499984737.203741 | 65 | 999999933 | 5049544354961246
57 | 10103823 | 500008680.942373 | 100 | 999999925 | 5051999210705205
19 | 10102592 | 500036435.174349 | 81 | 999999939 | 5051664089700899
33 | 10096464 | 500058135.753455 | 66 | 999999773 | 5048818965541870
0 | 5048622 | 500110433.47565 | 263 | 999999912 | 2524868536874701
50 | 10099019 | 500071716.158314 | 177 | 999999894 | 5050233762845416
89 | 10102886 | 499906895.352583 | 21 | 999999941 | 5050502374361080
66 | 10100455 | 500019718.104865 | 11 | 999999889 | 5050426661830877
17 | 10099749 | 500020536.906571 | 125 | 999999900 | 5050081917601604
14 | 10097995 | 499872474.436678 | 50 | 999999887 | 5047709747499202
67 | 10105426 | 500125756.387578 | 245 | 999999866 | 5053983821868692
87 | 10100597 | 499967794.215203 | 14 | 999999907 | 5049973202346696
96 | 10099064 | 499905578.187197 | 243 | 999999892 | 5048578428069509
61 | 10100503 | 500007920.183475 | 28 | 999999903 | 5050331497836948
3 | 10101198 | 499940146.381583 | 30 | 999999883 | 5049994406749350
39 | 10098880 | 500243255.820608 | 32 | 999999920 | 5051896611341620
11 | 10101799 | 500096301.902747 | 129 | 999999916 | 5051872322464871
43 | 10107301 | 500040498.08918 | 163 | 999999842 | 5054059826377266
5 | 10100558 | 499863342.325278 | 31 | 999999940 | 5048898681230323
75 | 10101250 | 500037496.222365 | 63 | 999999928 | 5051003758716163
97 | 10103503 | 500029746.921376 | 123 | 999999921 | 5052052048109365
21 | 10102929 | 500098408.485223 | 446 | 999999926 | 5052458713939206
93 | 10105004 | 499972842.513604 | 25 | 999999852 | 5052227573491343
60 | 10105650 | 499950744.869079 | 209 | 999999873 | 5052327244886207
27 | 10102588 | 499797776.872304 | 41 | 999999858 | 5049251023056813
45 | 10100466 | 499847713.021728 | 38 | 999999841 | 5048694830553717
70 | 10103640 | 500126978.162685 | 238 | 999999801 | 5053102941643626
91 | 10102311 | 499764530.032 | 1 | 999999950 | 5048776709152101
22 | 10099804 | 500243130.452219 | 250 | 999999867 | 5052357569913843
49 | 10099635 | 500022230.537738 | 19 | 999999896 | 5050042020317004
13 | 10100454 | 499986238.143486 | 53 | 999999646 | 5050087999001322
86 | 10099751 | 499991374.31582 | 7 | 999999782 | 5049788382737577
26 | 10105606 | 500021405.572664 | 6 | 999999938 | 5053019316283545
1 | 10095409 | 500001476.917716 | 206 | 999999776 | 5047719410088402
58 | 10099972 | 499913934.276812 | 35 | 999999792 | 5049116738605642
25 | 10104666 | 500031883.894369 | 137 | 999999510 | 5052655176103381
82 | 10101286 | 499966805.257835 | 10 | 999999534 | 5050307690415695
44 | 10104380 | 499990027.500454 | 9 | 999999872 | 5052089234075034
20 | 10100166 | 500033955.063915 | 130 | 999999908 | 5050425951782085
23 | 10099118 | 500033099.525857 | 58 | 999999918 | 5049893276017372
92 | 10097628 | 499985682.503551 | 79 | 999999951 | 5048669427246965
62 | 10098974 | 500002801.70268 | 33 | 999999765 | 5049515294322520
53 | 10104969 | 500071097.824301 | 5 | 999999948 | 5053202941310532
56 | 10099521 | 499829373.88272 | 157 | 999999902 | 5048037257945384
40 | 10098880 | 500118160.98974 | 202 | 999999731 | 5050633293656069
73 | 10102325 | 499981690.451386 | 62 | 999999809 | 5050977530989301
95 | 10102589 | 499885307.442653 | 74 | 999999770 | 5050135808231760
30 | 10097653 | 499985894.883754 | 127 | 999999861 | 5048684071430626
4 | 10094812 | 499884810.734528 | 37 | 999999815 | 5046243186020638
77 | 10097737 | 500019227.640071 | 34 | 999999751 | 5049062655652572
15 | 10100451 | 499990119.574357 | 86 | 999999946 | 5050125703244929
94 | 10097423 | 500030504.883845 | 102 | 999999930 | 5049019520715746
36 | 10097433 | 499910715.309699 | 60 | 999999952 | 5047814953821765
83 | 10097518 | 499986390.218504 | 8 | 999999777 | 5048621574986367
28 | 10102778 | 499892202.902798 | 147 | 999999929 | 5050299949857921
48 | 10095113 | 500116265.596128 | 171 | 999999942 | 5048730214330929
99 | 5051395 | 499818438.433132 | 39 | 999999936 | 2524780360808931
46 | 10101708 | 499972043.149036 | 24 | 999999878 | 5050571588054961
47 | 10106118 | 500030970.343216 | 29 | 999999844 | 5053371989943041
54 | 10096312 | 500084305.08323 | 77 | 999999934 | 5049007170423474
16 | 10104458 | 500003081.506687 | 36 | 999999800 | 5052260136954892
80 | 10102160 | 499846266.993651 | 54 | 999999945 | 5049526964572583
8 | 10103060 | 500007130.726843 | 136 | 999999891 | 5051602042161142
(100 rows)
Time: 1972.137 ms (00:01.972)
7、CPU资源使用
top - 16:27:44 up 30 days, 21:15, 2 users, load average: 7.49, 6.21, 6.41
Tasks: 522 total, 58 running, 464 sleeping, 0 stopped, 0 zombie
%Cpu(s): 99.9 us, 0.1 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 23094336+total, 7251336 free, 7786940 used, 21590508+buff/cache
KiB Swap: 0 total, 0 free, 0 used. 10075729+avail Mem
普通单表10亿，HASH并行聚合
1、普通表并行聚合。
create unlogged table c(id int, c1 int);
insert into c select * from a;
2、空间占用，34GB
postgres=# \dt+ c
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+-------+-------------
public | c | table | postgres | 34 GB |
(1 row)
3、并行度设置
set max_parallel_workers_per_gather =56;
set min_parallel_table_scan_size =0;
set min_parallel_index_scan_size =0;
set parallel_tuple_cost =0;
set parallel_setup_cost =0;
alter table c set (parallel_workers =56);
explain select c1, count(*), avg(id), min(id), max(id), sum(id) from c group by c1;
postgres=# explain select c1, count(*), avg(id), min(id), max(id), sum(id) from c group by c1;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=4871557.23..4871656.48 rows=100 width=60)
Group Key: c1
-> Sort (cost=4871557.23..4871571.23 rows=5600 width=60)
Sort Key: c1
-> Gather (cost=4871207.60..4871208.60 rows=5600 width=60)
Workers Planned: 56
-> Partial HashAggregate (cost=4871207.60..4871208.60 rows=100 width=60)
Group Key: c1
-> Parallel Seq Scan on c (cost=0.00..4603350.44 rows=17857144 width=8)
(9 rows)
4、聚合耗时，11.6秒
select c1, count(*), avg(id), min(id), max(id), sum(id) from c group by c1;
postgres=# select c1, count(*), avg(id), min(id), max(id), sum(id) from c group by c1;
c1 | count | avg | min | max | sum
----+----------+--------------------+-----+-----------+------------------
0 | 5048622 | 500110433.47564959 | 263 | 999999912 | 2524868536874701
1 | 10095409 | 500001476.91771596 | 206 | 999999776 | 5047719410088402
2 | 10098707 | 500027259.27800460 | 119 | 999999881 | 5049628783461600
3 | 10101198 | 499940146.38158266 | 30 | 999999883 | 5049994406749350
4 | 10094812 | 499884810.73452760 | 37 | 999999815 | 5046243186020638
5 | 10100558 | 499863342.32527777 | 31 | 999999940 | 5048898681230323
6 | 10104713 | 500020892.34122681 | 20 | 999999943 | 5052567611111995
7 | 10104223 | 500196670.66755019 | 13 | 999999922 | 5054098704282486
8 | 10103060 | 500007130.72684335 | 136 | 999999891 | 5051602042161142
9 | 10104341 | 499996733.66047128 | 23 | 999999915 | 5052137495791580
10 | 10095854 | 500035390.09690958 | 17 | 999999719 | 5048284293251445
11 | 10101799 | 500096301.90274732 | 129 | 999999916 | 5051872322464871
12 | 10105025 | 499832509.13203431 | 109 | 999999799 | 5050820000591935
13 | 10100454 | 499986238.14348563 | 53 | 999999646 | 5050087999001322
14 | 10097995 | 499872474.43667797 | 50 | 999999887 | 5047709747499202
15 | 10100451 | 499990119.57435653 | 86 | 999999946 | 5050125703244929
16 | 10104458 | 500003081.50668665 | 36 | 999999800 | 5052260136954892
17 | 10099749 | 500020536.90657104 | 125 | 999999900 | 5050081917601604
18 | 10101977 | 499926956.66766832 | 4 | 999999897 | 5050250617936782
19 | 10102592 | 500036435.17434922 | 81 | 999999939 | 5051664089700899
20 | 10100166 | 500033955.06391529 | 130 | 999999908 | 5050425951782085
21 | 10102929 | 500098408.48522305 | 446 | 999999926 | 5052458713939206
22 | 10099804 | 500243130.45221897 | 250 | 999999867 | 5052357569913843
23 | 10099118 | 500033099.52585681 | 58 | 999999918 | 5049893276017372
24 | 10099982 | 499936812.84723993 | 190 | 999999901 | 5049352810894492
25 | 10104666 | 500031883.89436929 | 137 | 999999510 | 5052655176103381
26 | 10105606 | 500021405.57266383 | 6 | 999999938 | 5053019316283545
27 | 10102588 | 499797776.87230371 | 41 | 999999858 | 5049251023056813
28 | 10102778 | 499892202.90279773 | 147 | 999999929 | 5050299949857921
29 | 10101931 | 500172863.74100823 | 16 | 999999911 | 5052711757584067
30 | 10097653 | 499985894.88375427 | 127 | 999999861 | 5048684071430626
31 | 10099277 | 499845188.90857692 | 112 | 999999838 | 5048075019905046
32 | 10099491 | 500129108.71897920 | 84 | 999999914 | 5051049432345352
33 | 10096464 | 500058135.75345487 | 66 | 999999773 | 5048818965541870
34 | 10099359 | 500111032.92186801 | 52 | 999999836 | 5050800861338764
35 | 10099622 | 500114054.89803420 | 44 | 999999747 | 5050962911357394
36 | 10097433 | 499910715.30969950 | 60 | 999999952 | 5047814953821765
37 | 10099584 | 500057275.74502613 | 104 | 999999849 | 5050370461198054
38 | 10103601 | 499961610.60292751 | 89 | 999999949 | 5051412628849349
39 | 10098880 | 500243255.82060783 | 32 | 999999920 | 5051896611341620
40 | 10098880 | 500118160.98974035 | 202 | 999999731 | 5050633293656069
41 | 10098079 | 500105983.31279751 | 75 | 999999899 | 5050109727865311
42 | 10101533 | 500126553.67012868 | 12 | 999999865 | 5052044886075076
43 | 10107301 | 500040498.08917989 | 163 | 999999842 | 5054059826377266
44 | 10104380 | 499990027.50045366 | 9 | 999999872 | 5052089234075034
45 | 10100466 | 499847713.02172761 | 38 | 999999841 | 5048694830553717
46 | 10101708 | 499972043.14903589 | 24 | 999999878 | 5050571588054961
47 | 10106118 | 500030970.34321596 | 29 | 999999844 | 5053371989943041
48 | 10095113 | 500116265.59612844 | 171 | 999999942 | 5048730214330929
49 | 10099635 | 500022230.53773765 | 19 | 999999896 | 5050042020317004
50 | 10099019 | 500071716.15831359 | 177 | 999999894 | 5050233762845416
51 | 10099397 | 499984737.20374058 | 65 | 999999933 | 5049544354961246
52 | 10101573 | 500040171.38814430 | 355 | 999999932 | 5051192294209851
53 | 10104969 | 500071097.82430129 | 5 | 999999948 | 5053202941310532
54 | 10096312 | 500084305.08322980 | 77 | 999999934 | 5049007170423474
55 | 10103707 | 499960231.76141549 | 45 | 999999850 | 5051451693369436
56 | 10099521 | 499829373.88272018 | 157 | 999999902 | 5048037257945384
57 | 10103823 | 500008680.94237251 | 100 | 999999925 | 5051999210705205
58 | 10099972 | 499913934.27681205 | 35 | 999999792 | 5049116738605642
59 | 10099747 | 499892322.50948692 | 2 | 999999808 | 5048785984588223
60 | 10105650 | 499950744.86907888 | 209 | 999999873 | 5052327244886207
61 | 10100503 | 500007920.18347482 | 28 | 999999903 | 5050331497836948
62 | 10098974 | 500002801.70267990 | 33 | 999999765 | 5049515294322520
63 | 10102023 | 499895229.11286373 | 353 | 999999910 | 5049953102088419
64 | 10102942 | 500113184.40411526 | 47 | 999999693 | 5052614495470081
65 | 10103111 | 499851261.81228049 | 48 | 999999726 | 5050052781579531
66 | 10100455 | 500019718.10486528 | 11 | 999999889 | 5050426661830877
67 | 10105426 | 500125756.38757753 | 245 | 999999866 | 5053983821868692
68 | 10098999 | 499977042.17730005 | 59 | 999999857 | 5049267648971511
69 | 10097014 | 500143184.43549093 | 91 | 999999720 | 5049952735249734
70 | 10103640 | 500126978.16268454 | 238 | 999999801 | 5053102941643626
71 | 10099125 | 500129933.36370557 | 51 | 999999877 | 5050874713281733
72 | 10099035 | 499987853.74665619 | 3 | 999999917 | 5049394834562362
73 | 10102325 | 499981690.45138629 | 62 | 999999809 | 5050977530989301
74 | 10098824 | 499966081.89006641 | 170 | 999999890 | 5049069466977368
75 | 10101250 | 500037496.22236486 | 63 | 999999928 | 5051003758716163
76 | 10101689 | 499896977.81069849 | 438 | 999999843 | 5049803801883577
77 | 10097737 | 500019227.64007143 | 34 | 999999751 | 5049062655652572
78 | 10107866 | 500012506.29948478 | 69 | 999999740 | 5054059411999348
79 | 10098900 | 499957103.41625533 | 114 | 999999846 | 5049016791690421
80 | 10102160 | 499846266.99365116 | 54 | 999999945 | 5049526964572583
81 | 10106622 | 499963749.19985233 | 229 | 999999944 | 5052944626865710
82 | 10101286 | 499966805.25783499 | 10 | 999999534 | 5050307690415695
83 | 10097518 | 499986390.21850389 | 8 | 999999777 | 5048621574986367
84 | 10105345 | 500042003.07276951 | 271 | 999999895 | 5053096955541396
85 | 10103461 | 499947692.92315683 | 18 | 999999904 | 5051202017489091
86 | 10099751 | 499991374.31581996 | 7 | 999999782 | 5049788382737577
87 | 10100597 | 499967794.21520292 | 14 | 999999907 | 5049973202346696
88 | 10103668 | 499850251.05678888 | 64 | 999999780 | 5050320986394444
89 | 10102886 | 499906895.35258341 | 21 | 999999941 | 5050502374361080
90 | 10096744 | 500199293.81002103 | 43 | 999999875 | 5050384218580567
91 | 10102311 | 499764530.03199971 | 1 | 999999950 | 5048776709152101
92 | 10097628 | 499985682.50355083 | 79 | 999999951 | 5048669427246965
93 | 10105004 | 499972842.51360445 | 25 | 999999852 | 5052227573491343
94 | 10097423 | 500030504.88384472 | 102 | 999999930 | 5049019520715746
95 | 10102589 | 499885307.44265257 | 74 | 999999770 | 5050135808231760
96 | 10099064 | 499905578.18719725 | 243 | 999999892 | 5048578428069509
97 | 10103503 | 500029746.92137618 | 123 | 999999921 | 5052052048109365
98 | 10097327 | 499944814.06690414 | 22 | 999999919 | 5048106269587731
99 | 5051395 | 499818438.43313204 | 39 | 999999936 | 2524780360808931
(100 rows)
Time: 11585.315 ms (00:11.585)
5、CPU消耗
top - 17:25:46 up 30 days, 22:13, 2 users, load average: 4.78, 1.92, 2.38
Tasks: 522 total, 58 running, 464 sleeping, 0 stopped, 0 zombie
%Cpu(s): 96.1 us, 3.6 sy, 0.0 ni, 0.0 id, 0.3 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 23094336+total, 79267856 free, 2570612 used, 14910489+buff/cache
KiB Swap: 0 total, 0 free, 0 used. 15125537+avail Mem
小结
目前PostgreSQL已内置了并行计算的能力（透明和非透明）。结合列存+向量计算，PostgreSQL 统计分析能力到底能有多强？
本文以vops（向量计算） + 并行计算为例，演示了单实例PostgreSQL 10亿数据量的count,avg,min,max,sum查询，耗时不到2秒。
我们可以大胆的预见PostgreSQL结合列存、向量计算，OLAP能力会有多强。
| 数据量 | 硬件 | CASE | 优化方法 | 空间占用 | 耗时 | CPU 利用率 |
|---|---|---|---|---|---|---|
| 10亿 | 56核虚拟机 | 10亿分组聚合求count,avg,min,max,sum | 普通单表HASH并行聚合 | 34 GB | 11.6 秒 | 100 % |
| 10亿 | 56核虚拟机 | 10亿分组聚合求count,avg,min,max,sum | 普通多表dblink异步调用并行聚合 | 34 GB | 9.6 秒 | 100 % |
| 10亿 | 56核虚拟机 | 10亿分组聚合求count,avg,min,max,sum | vops瓦片式多表dblink异步调用并行聚合 | 8.7 GB | 1.97 秒 | 100 % |
参考
《PostgreSQL 11 preview - parallel hash (含hash JOIN , hash agg等) 性能极大提升》
《PostgreSQL 向量化执行插件(瓦片式实现) 10x提速OLAP》
https://postgrespro.com/docs/enterprise/10/vops