作者
digoal
日期
2019-10-17
标签
PostgreSQL , hash , join , agg
背景
hash join, hash agg，适用场景：
分析场景，特点：
1、运算数据量大
2、聚合后结果少
PostgreSQL 有哪些加速特性：hash , jit , parallel , 向量计算 , 列存储 , GPU 加速。
怎么测？hash , jit , parallel , 向量计算 , 列存都是通用加速能力，普通x86就可以获得，GPU加速加nvidia的卡也能获得（通过pg_strom插件）。
测试例子
环境：
32c64ht 512g PostgreSQL 12
测试表和数据
```
create table a(id int, info text, crt_Time timestamp, c1 int);
insert into a select generate_series(1,100000000),'test',now(),random()*100;
analyze a;
postgres=# \dt+ a
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+---------+-------------
public | a | table | postgres | 5746 MB |
(1 row)
无索引，拼数据库的硬核计算能力
```
开启并行和jit进行测试
set jit=on;
set max_parallel_workers_per_gather =32;
alter table a set (parallel_workers =32);
set min_parallel_table_scan_size =0;
set min_parallel_index_scan_size =0;
set parallel_setup_cost =0;
set parallel_tuple_cost =0;
测试结果
1、1亿 join 1亿，全匹配，聚合后101条。12.5秒
```
postgres=# select t1.c1,count(*) from a t1 join a t2 using (id) group by t1.c1;
c1 | count
-----+---------
0 | 500349
1 | 999967
2 | 998609
3 | 999403
4 | 1000197
5 | 999844
6 | 997887
7 | 1001363
8 | 1000188
9 | 1001032
10 | 1001050
11 | 1000779
12 | 999791
13 | 998404
14 | 1000304
15 | 999887
16 | 999517
17 | 999493
18 | 999513
19 | 1000255
20 | 1000588
21 | 999592
22 | 1000649
23 | 1001539
24 | 999437
25 | 999633
26 | 1001389
27 | 999672
28 | 999556
29 | 999234
30 | 1000466
31 | 999030
32 | 999224
33 | 1000345
34 | 1000335
35 | 1001090
36 | 1000207
37 | 999211
38 | 1001367
39 | 998481
40 | 1001687
41 | 1000593
42 | 999477
43 | 998926
44 | 1001033
45 | 1000633
46 | 1000861
47 | 998765
48 | 1000326
49 | 999152
50 | 999082
51 | 1000643
52 | 999918
53 | 1000459
54 | 999024
55 | 1000304
56 | 998536
57 | 1000993
58 | 1000368
59 | 1002081
60 | 1000017
61 | 1000546
62 | 999840
63 | 998869
64 | 1001703
65 | 1001240
66 | 999138
67 | 999310
68 | 998368
69 | 998870
70 | 999856
71 | 1000222
72 | 999178
73 | 999710
74 | 999659
75 | 1000201
76 | 999353
77 | 999517
78 | 1000528
79 | 1000997
80 | 1000022
81 | 998998
82 | 1001967
83 | 999373
84 | 998547
85 | 999962
86 | 1000440
87 | 999956
88 | 1000479
89 | 999868
90 | 1000523
91 | 1000554
92 | 1000717
93 | 999384
94 | 1000795
95 | 1000647
96 | 999412
97 | 1000065
98 | 999731
99 | 999891
100 | 499809
(101 rows)
Time: 12496.291 ms (00:12.496)
```
2、1亿 join 1亿，全匹配，过滤后100万条，聚合。766毫秒
```
postgres=# select t1.c1,count(*) from a t1 join a t2 on (t1.id=t2.id and t1.c1=2 and t2.c1=2) group by t1.c1;
c1 | count
----+--------
2 | 998609
(1 row)
Time: 766.432 ms
```
3、1亿排序limit 10。441毫秒
```
postgres=# select * from a order by c1,id desc limit 10;
id | info | crt_time | c1
----------+------+----------------------------+----
99999990 | test | 2019-10-17 15:46:53.730743 | 0
99999775 | test | 2019-10-17 15:46:53.730743 | 0
99999628 | test | 2019-10-17 15:46:53.730743 | 0
99999295 | test | 2019-10-17 15:46:53.730743 | 0
99999247 | test | 2019-10-17 15:46:53.730743 | 0
99999222 | test | 2019-10-17 15:46:53.730743 | 0
99998761 | test | 2019-10-17 15:46:53.730743 | 0
99998741 | test | 2019-10-17 15:46:53.730743 | 0
99998727 | test | 2019-10-17 15:46:53.730743 | 0
99998059 | test | 2019-10-17 15:46:53.730743 | 0
(10 rows)
Time: 441.794 ms
```
4、1亿分组聚合，聚合后101条。693毫秒
```
postgres=# select c1,count(*) from a group by c1;
c1 | count
-----+---------
0 | 500349
1 | 999967
2 | 998609
3 | 999403
4 | 1000197
5 | 999844
6 | 997887
7 | 1001363
8 | 1000188
9 | 1001032
10 | 1001050
11 | 1000779
12 | 999791
13 | 998404
14 | 1000304
15 | 999887
16 | 999517
17 | 999493
18 | 999513
19 | 1000255
20 | 1000588
21 | 999592
22 | 1000649
23 | 1001539
24 | 999437
25 | 999633
26 | 1001389
27 | 999672
28 | 999556
29 | 999234
30 | 1000466
31 | 999030
32 | 999224
33 | 1000345
34 | 1000335
35 | 1001090
36 | 1000207
37 | 999211
38 | 1001367
39 | 998481
40 | 1001687
41 | 1000593
42 | 999477
43 | 998926
44 | 1001033
45 | 1000633
46 | 1000861
47 | 998765
48 | 1000326
49 | 999152
50 | 999082
51 | 1000643
52 | 999918
53 | 1000459
54 | 999024
55 | 1000304
56 | 998536
57 | 1000993
58 | 1000368
59 | 1002081
60 | 1000017
61 | 1000546
62 | 999840
63 | 998869
64 | 1001703
65 | 1001240
66 | 999138
67 | 999310
68 | 998368
69 | 998870
70 | 999856
71 | 1000222
72 | 999178
73 | 999710
74 | 999659
75 | 1000201
76 | 999353
77 | 999517
78 | 1000528
79 | 1000997
80 | 1000022
81 | 998998
82 | 1001967
83 | 999373
84 | 998547
85 | 999962
86 | 1000440
87 | 999956
88 | 1000479
89 | 999868
90 | 1000523
91 | 1000554
92 | 1000717
93 | 999384
94 | 1000795
95 | 1000647
96 | 999412
97 | 1000065
98 | 999731
99 | 999891
100 | 499809
(101 rows)
Time: 693.793 ms
```
执行计划
1、hash join + parallel + jit
```
postgres=# explain select t1.c1,count(*) from a t1 join a t2 using (id) group by t1.c1;
QUERY PLAN
Finalize GroupAggregate (cost=1657122.68..1657229.70 rows=101 width=12)
Group Key: t1.c1
-> Gather Merge (cost=1657122.68..1657212.53 rows=3232 width=12)
Workers Planned: 32
-> Sort (cost=1657121.85..1657122.10 rows=101 width=12)
Sort Key: t1.c1
-> Partial HashAggregate (cost=1657117.48..1657118.49 rows=101 width=12)
Group Key: t1.c1
-> Parallel Hash Join (cost=817815.59..1641492.46 rows=3125004 width=4)
Hash Cond: (t1.id = t2.id)
-> Parallel Seq Scan on a t1 (cost=0.00..766545.04 rows=3125004 width=8)
-> Parallel Hash (cost=766545.04..766545.04 rows=3125004 width=4)
-> Parallel Seq Scan on a t2 (cost=0.00..766545.04 rows=3125004 width=4)
JIT:
Functions: 17
Options: Inlining true, Optimization true, Expressions true, Deforming true
(16 rows)
postgres=# explain select t1.c1,count(*) from a t1 join a t2 on (t1.id=t2.id and t1.c1=2 and t2.c1=2) group by t1.c1;
QUERY PLAN
Finalize GroupAggregate (cost=774736.46..1549227.42 rows=101 width=12)
Group Key: t1.c1
-> Gather (cost=774736.46..1549210.25 rows=3232 width=12)
Workers Planned: 32
-> Partial GroupAggregate (cost=774736.46..1549210.25 rows=101 width=12)
Group Key: t1.c1
-> Parallel Hash Join (cost=774736.46..1549207.77 rows=294 width=4)
Hash Cond: (t1.id = t2.id)
-> Parallel Seq Scan on a t1 (cost=0.00..774357.55 rows=30313 width=8)
Filter: (c1 = 2)
-> Parallel Hash (cost=774357.55..774357.55 rows=30313 width=4)
-> Parallel Seq Scan on a t2 (cost=0.00..774357.55 rows=30313 width=4)
Filter: (c1 = 2)
JIT:
Functions: 20
Options: Inlining true, Optimization true, Expressions true, Deforming true
(16 rows)
```
2、parallel sort + jit
```
postgres=# explain select * from a order by c1,id desc limit 10;
QUERY PLAN
Limit (cost=834076.08..834076.36 rows=10 width=21)
-> Gather Merge (cost=834076.08..3614089.20 rows=100000128 width=21)
Workers Planned: 32
-> Sort (cost=834075.25..841887.76 rows=3125004 width=21)
Sort Key: c1, id DESC
-> Parallel Seq Scan on a (cost=0.00..766545.04 rows=3125004 width=21)
JIT:
Functions: 1
Options: Inlining true, Optimization true, Expressions true, Deforming true
(9 rows)
```
3、hash agg + parallel + jit
```
postgres=# explain select c1,count(*) from a group by c1;
QUERY PLAN
Finalize GroupAggregate (cost=782175.26..782282.28 rows=101 width=12)
Group Key: c1
-> Gather Merge (cost=782175.26..782265.11 rows=3232 width=12)
Workers Planned: 32
-> Sort (cost=782174.43..782174.68 rows=101 width=12)
Sort Key: c1
-> Partial HashAggregate (cost=782170.06..782171.07 rows=101 width=12)
Group Key: c1
-> Parallel Seq Scan on a (cost=0.00..766545.04 rows=3125004 width=4)
JIT:
Functions: 9
Options: Inlining true, Optimization true, Expressions true, Deforming true
(12 rows)
```
小结
1亿数据，无索引，拼数据库的硬核计算能力。用到了PG原生的hash agg, hash group, parallel, jit技术。
性能到底有多强？(本例还没有使用CPU向量计算功能，如果使用的话10亿记录计算只需要2秒。)
1、1亿 join 1亿，全匹配，聚合后101条。耗时 12.5 秒
2、1亿 join 1亿，全匹配，过滤后100万条，聚合后1条。耗时 766 毫秒
3、1亿排序limit 10。耗时 441 毫秒
4、1亿分组聚合，聚合后101条。耗时 693 毫秒
企业再也不用拖一堆数据库用了，大中型业务的oltp+olap一个实例完全胜任。果然是玉树临风的PG数据库，大量国产数据库基于PG。aws，阿里等企业去O的拳头产品。
除此以外PG 12已经开放了存储引擎接口，可以支持列存储，分析能力将更上一层楼，现在已经这么NB了，再上一层楼岂不是要飞起来。
参考
1、vops
《PostgreSQL VOPS 向量计算 + DBLINK异步并行 - 单实例 10亿 聚合计算跑进2秒》
2、并行计算
3、hash agg , hash group
4、通用 gpu 加速
《PostgreSQL GPU 加速(HeteroDB pg_strom) (GPU计算, GPU-DIO-Nvme SSD, 列存, GPU内存缓存)》
5、jit
《PostgreSQL 11 preview - with_llvm JIT支持部署与试用》
《PostgreSQL 11 preview - JIT接口放开》
7、列存储zedstore
《PostgreSQL 基于access method api的列存zedstore》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
