PostgreSQL 11 preview - 分区智能并行JOIN (类似MPP架构，性能暴增)

103 阅读 0 评论 68 点赞

我是靠谱客的博主火星上发卡，这篇文章主要介绍PostgreSQL 11 preview - 分区智能并行JOIN (类似MPP架构，性能暴增)，现在分享给大家，希望可以做个参考。

背景

PostgreSQL 10开始引入了内置分区表功能，当分区表与分区表发生JOIN时，大家想象一下，分区和分区能直接一对一JOIN，而跳过与其他分区JOIN吗？

实际上我们在单表上，通过HASH可以达到类似的目的，即分片与分片JOIN，这样可以把JOIN的数据集合变小，同时能使用并行：

《PostgreSQL dblink异步调用实现并行hash分片JOIN - 含数据交、并、差提速案例》

PostgreSQL 11对分区表进行了增强，分区表的JOIN在满足一定条件时，已类似MPP架构的JOIN，本文测试CASE，性能暴增2.4倍。

涉及开关如下

复制代码

1
2
3
4
5
6
postgres=# show enable_partition_wise_join ;
enable_partition_wise_join
----------------------------
off
(1 row)

解释

复制代码

1
2
3
4
5
6
7
8
enable_partition_wise_join (boolean)
Enables or disables the query planner's use of partition-wise join,
which allows a join between partitioned tables to be performed by joining the matching partitions.
Partition-wise join currently applies only when the join conditions include all the partition keys,
which must be of the same data type and have exactly matching sets of child partitions.
Because partition-wise join planning can use significantly more CPU time and memory during planning,
the default is off.

RANGE分区智能JOIN例子

1、建立两个结构一样的分区表

复制代码

1
2
3
4
5
6
create table a(id int, info text, crt_time timestamp) partition by range(id);
create table a0 partition of a for values from (0) to (10000);
create table a1 partition of a for values from (10000) to (20000);
create table a2 partition of a for values from (20000) to (30000);
create table a3 partition of a for values from (30000) to (40000);

复制代码

1
2
3
4
5
6
create table b(bid int , info text, crt_time timestamp, c1 int, c2 int) partition by range(bid);
create table b0 partition of b for values from (0) to (10000);
create table b1 partition of b for values from (10000) to (20000);
create table b2 partition of b for values from (20000) to (30000);
create table b3 partition of b for values from (30000) to (40000);

2、当开关关闭时，不会使用并行JOIN

复制代码

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
postgres=# set enable_partition_wise_join =off;
SET
postgres=# explain select a.* from a join b on (a.id=b.bid);
QUERY PLAN
-------------------------------------------------------------------------
Merge Join
(cost=685.10..2088.62 rows=92208 width=44)
Merge Cond: (b0.bid = a0.id)
->
Sort
(cost=325.48..335.68 rows=4080 width=4)
Sort Key: b0.bid
->
Append
(cost=0.00..80.80 rows=4080 width=4)
->
Seq Scan on b0
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b1
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b2
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b3
(cost=0.00..20.20 rows=1020 width=4)
->
Sort
(cost=359.61..370.91 rows=4520 width=44)
Sort Key: a0.id
->
Append
(cost=0.00..85.20 rows=4520 width=44)
->
Seq Scan on a0
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a1
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a2
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a3
(cost=0.00..21.30 rows=1130 width=44)
(16 rows)

3、打开开关，使用并行分区JOIN

复制代码

postgres=# set enable_partition_wise_join =on;
SET
postgres=# explain select a.* from a join b on (a.id=b.bid);
QUERY PLAN
-------------------------------------------------------------------------
Append
(cost=149.77..965.28 rows=23052 width=44)
->
Merge Join
(cost=149.77..241.32 rows=5763 width=44)
Merge Cond: (b0.bid = a0.id)
->
Sort
(cost=71.17..73.72 rows=1020 width=4)
Sort Key: b0.bid
->
Seq Scan on b0
(cost=0.00..20.20 rows=1020 width=4)
->
Sort
(cost=78.60..81.43 rows=1130 width=44)
Sort Key: a0.id
->
Seq Scan on a0
(cost=0.00..21.30 rows=1130 width=44)
->
Merge Join
(cost=149.77..241.32 rows=5763 width=44)
Merge Cond: (b1.bid = a1.id)
->
Sort
(cost=71.17..73.72 rows=1020 width=4)
Sort Key: b1.bid
->
Seq Scan on b1
(cost=0.00..20.20 rows=1020 width=4)
->
Sort
(cost=78.60..81.43 rows=1130 width=44)
Sort Key: a1.id
->
Seq Scan on a1
(cost=0.00..21.30 rows=1130 width=44)
->
Merge Join
(cost=149.77..241.32 rows=5763 width=44)
Merge Cond: (b2.bid = a2.id)
->
Sort
(cost=71.17..73.72 rows=1020 width=4)
Sort Key: b2.bid
->
Seq Scan on b2
(cost=0.00..20.20 rows=1020 width=4)
->
Sort
(cost=78.60..81.43 rows=1130 width=44)
Sort Key: a2.id
->
Seq Scan on a2
(cost=0.00..21.30 rows=1130 width=44)
->
Merge Join
(cost=149.77..241.32 rows=5763 width=44)
Merge Cond: (b3.bid = a3.id)
->
Sort
(cost=71.17..73.72 rows=1020 width=4)
Sort Key: b3.bid
->
Seq Scan on b3
(cost=0.00..20.20 rows=1020 width=4)
->
Sort
(cost=78.60..81.43 rows=1130 width=44)
Sort Key: a3.id
->
Seq Scan on a3
(cost=0.00..21.30 rows=1130 width=44)
(33 rows)

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
postgres=# set enable_partition_wise_join =on;
SET
postgres=# explain select a.* from a join b on (a.id=b.bid);
QUERY PLAN
-------------------------------------------------------------------------
Append
(cost=149.77..965.28 rows=23052 width=44)
->
Merge Join
(cost=149.77..241.32 rows=5763 width=44)
Merge Cond: (b0.bid = a0.id)
->
Sort
(cost=71.17..73.72 rows=1020 width=4)
Sort Key: b0.bid
->
Seq Scan on b0
(cost=0.00..20.20 rows=1020 width=4)
->
Sort
(cost=78.60..81.43 rows=1130 width=44)
Sort Key: a0.id
->
Seq Scan on a0
(cost=0.00..21.30 rows=1130 width=44)
->
Merge Join
(cost=149.77..241.32 rows=5763 width=44)
Merge Cond: (b1.bid = a1.id)
->
Sort
(cost=71.17..73.72 rows=1020 width=4)
Sort Key: b1.bid
->
Seq Scan on b1
(cost=0.00..20.20 rows=1020 width=4)
->
Sort
(cost=78.60..81.43 rows=1130 width=44)
Sort Key: a1.id
->
Seq Scan on a1
(cost=0.00..21.30 rows=1130 width=44)
->
Merge Join
(cost=149.77..241.32 rows=5763 width=44)
Merge Cond: (b2.bid = a2.id)
->
Sort
(cost=71.17..73.72 rows=1020 width=4)
Sort Key: b2.bid
->
Seq Scan on b2
(cost=0.00..20.20 rows=1020 width=4)
->
Sort
(cost=78.60..81.43 rows=1130 width=44)
Sort Key: a2.id
->
Seq Scan on a2
(cost=0.00..21.30 rows=1130 width=44)
->
Merge Join
(cost=149.77..241.32 rows=5763 width=44)
Merge Cond: (b3.bid = a3.id)
->
Sort
(cost=71.17..73.72 rows=1020 width=4)
Sort Key: b3.bid
->
Seq Scan on b3
(cost=0.00..20.20 rows=1020 width=4)
->
Sort
(cost=78.60..81.43 rows=1130 width=44)
Sort Key: a3.id
->
Seq Scan on a3
(cost=0.00..21.30 rows=1130 width=44)
(33 rows)

4、当分区结构不一样时，不会用到分区并行JOIN

复制代码

postgres=# create table b4 partition of b for values from (40000) to (50000);
CREATE TABLE
postgres=# explain select a.* from a join b on (a.id=b.bid);
QUERY PLAN
-------------------------------------------------------------------------
Merge Join
(cost=774.68..2526.18 rows=115260 width=44)
Merge Cond: (a0.id = b0.bid)
->
Sort
(cost=359.61..370.91 rows=4520 width=44)
Sort Key: a0.id
->
Append
(cost=0.00..85.20 rows=4520 width=44)
->
Seq Scan on a0
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a1
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a2
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a3
(cost=0.00..21.30 rows=1130 width=44)
->
Sort
(cost=415.07..427.82 rows=5100 width=4)
Sort Key: b0.bid
->
Append
(cost=0.00..101.00 rows=5100 width=4)
->
Seq Scan on b0
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b1
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b2
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b3
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b4
(cost=0.00..20.20 rows=1020 width=4)
(17 rows)

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
postgres=# create table b4 partition of b for values from (40000) to (50000);
CREATE TABLE
postgres=# explain select a.* from a join b on (a.id=b.bid);
QUERY PLAN
-------------------------------------------------------------------------
Merge Join
(cost=774.68..2526.18 rows=115260 width=44)
Merge Cond: (a0.id = b0.bid)
->
Sort
(cost=359.61..370.91 rows=4520 width=44)
Sort Key: a0.id
->
Append
(cost=0.00..85.20 rows=4520 width=44)
->
Seq Scan on a0
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a1
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a2
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a3
(cost=0.00..21.30 rows=1130 width=44)
->
Sort
(cost=415.07..427.82 rows=5100 width=4)
Sort Key: b0.bid
->
Append
(cost=0.00..101.00 rows=5100 width=4)
->
Seq Scan on b0
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b1
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b2
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b3
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b4
(cost=0.00..20.20 rows=1020 width=4)
(17 rows)

复制代码

drop table a2;
drop table a3;
create table a2 partition of a for values from (20000) to (40000);
create table a3 partition of a for values from (40000) to (41000);
create table a4 partition of a for values from (41000) to (60000);
postgres=# explain select a.* from a join b on (a.id=b.bid);
QUERY PLAN
-------------------------------------------------------------------------
Merge Join
(cost=873.67..3060.30 rows=144075 width=44)
Merge Cond: (b0.bid = a0.id)
->
Sort
(cost=415.07..427.82 rows=5100 width=4)
Sort Key: b0.bid
->
Append
(cost=0.00..101.00 rows=5100 width=4)
->
Seq Scan on b0
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b1
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b2
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b3
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b4
(cost=0.00..20.20 rows=1020 width=4)
->
Sort
(cost=458.61..472.73 rows=5650 width=44)
Sort Key: a0.id
->
Append
(cost=0.00..106.50 rows=5650 width=44)
->
Seq Scan on a0
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a1
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a2
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a3
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a4
(cost=0.00..21.30 rows=1130 width=44)
(18 rows)

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
drop table a2;
drop table a3;
create table a2 partition of a for values from (20000) to (40000);
create table a3 partition of a for values from (40000) to (41000);
create table a4 partition of a for values from (41000) to (60000);
postgres=# explain select a.* from a join b on (a.id=b.bid);
QUERY PLAN
-------------------------------------------------------------------------
Merge Join
(cost=873.67..3060.30 rows=144075 width=44)
Merge Cond: (b0.bid = a0.id)
->
Sort
(cost=415.07..427.82 rows=5100 width=4)
Sort Key: b0.bid
->
Append
(cost=0.00..101.00 rows=5100 width=4)
->
Seq Scan on b0
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b1
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b2
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b3
(cost=0.00..20.20 rows=1020 width=4)
->
Seq Scan on b4
(cost=0.00..20.20 rows=1020 width=4)
->
Sort
(cost=458.61..472.73 rows=5650 width=44)
Sort Key: a0.id
->
Append
(cost=0.00..106.50 rows=5650 width=44)
->
Seq Scan on a0
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a1
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a2
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a3
(cost=0.00..21.30 rows=1130 width=44)
->
Seq Scan on a4
(cost=0.00..21.30 rows=1130 width=44)
(18 rows)

LIST分区智能JOIN例子

1、创建两个结构一样的测试分区表

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE aa (
city_id
bigserial not null,
name
text not null,
population
bigint
) PARTITION BY LIST (left(lower(name), 1));
CREATE TABLE aa0
PARTITION OF aa (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('a', 'b');
CREATE TABLE aa1
PARTITION OF aa (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('c', 'd');

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE bb (
city_id
bigserial not null,
name
text not null,
population
bigint
) PARTITION BY LIST (left(lower(name), 1));
CREATE TABLE bb0
PARTITION OF bb (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('a', 'b');
CREATE TABLE bb1
PARTITION OF bb (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('c', 'd');

2、使用了并行分区JOIN

复制代码

postgres=# explain select aa.* from aa join bb on (left(lower(aa.name), 1)=left(lower(bb.name), 1));
QUERY PLAN
-----------------------------------------------------------------------------------
Append
(cost=149.08..616.51 rows=11448 width=48)
->
Merge Join
(cost=149.08..308.25 rows=5724 width=48)
Merge Cond: (("left"(lower(aa0.name), 1)) = ("left"(lower(bb0.name), 1)))
->
Sort
(cost=74.54..77.21 rows=1070 width=48)
Sort Key: ("left"(lower(aa0.name), 1))
->
Seq Scan on aa0
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=74.54..77.21 rows=1070 width=32)
Sort Key: ("left"(lower(bb0.name), 1))
->
Seq Scan on bb0
(cost=0.00..20.70 rows=1070 width=32)
->
Merge Join
(cost=149.08..308.25 rows=5724 width=48)
Merge Cond: (("left"(lower(aa1.name), 1)) = ("left"(lower(bb1.name), 1)))
->
Sort
(cost=74.54..77.21 rows=1070 width=48)
Sort Key: ("left"(lower(aa1.name), 1))
->
Seq Scan on aa1
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=74.54..77.21 rows=1070 width=32)
Sort Key: ("left"(lower(bb1.name), 1))
->
Seq Scan on bb1
(cost=0.00..20.70 rows=1070 width=32)
(17 rows)

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
postgres=# explain select aa.* from aa join bb on (left(lower(aa.name), 1)=left(lower(bb.name), 1));
QUERY PLAN
-----------------------------------------------------------------------------------
Append
(cost=149.08..616.51 rows=11448 width=48)
->
Merge Join
(cost=149.08..308.25 rows=5724 width=48)
Merge Cond: (("left"(lower(aa0.name), 1)) = ("left"(lower(bb0.name), 1)))
->
Sort
(cost=74.54..77.21 rows=1070 width=48)
Sort Key: ("left"(lower(aa0.name), 1))
->
Seq Scan on aa0
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=74.54..77.21 rows=1070 width=32)
Sort Key: ("left"(lower(bb0.name), 1))
->
Seq Scan on bb0
(cost=0.00..20.70 rows=1070 width=32)
->
Merge Join
(cost=149.08..308.25 rows=5724 width=48)
Merge Cond: (("left"(lower(aa1.name), 1)) = ("left"(lower(bb1.name), 1)))
->
Sort
(cost=74.54..77.21 rows=1070 width=48)
Sort Key: ("left"(lower(aa1.name), 1))
->
Seq Scan on aa1
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=74.54..77.21 rows=1070 width=32)
Sort Key: ("left"(lower(bb1.name), 1))
->
Seq Scan on bb1
(cost=0.00..20.70 rows=1070 width=32)
(17 rows)

3、当结构不一致时，不会使用并行分区JOIN

复制代码

CREATE TABLE aa2
PARTITION OF aa (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('e', 'f', 'g');
CREATE TABLE bb2
PARTITION OF bb (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('e', 'f');
postgres=# explain select aa.* from aa join bb on (left(lower(aa.name), 1)=left(lower(bb.name), 1));
QUERY PLAN
--------------------------------------------------------------------------------
Merge Join
(cost=498.11..1834.26 rows=51520 width=48)
Merge Cond: (("left"(lower(aa0.name), 1)) = ("left"(lower(bb0.name), 1)))
->
Sort
(cost=249.06..257.08 rows=3210 width=48)
Sort Key: ("left"(lower(aa0.name), 1))
->
Result
(cost=0.00..62.10 rows=3210 width=48)
->
Append
(cost=0.00..62.10 rows=3210 width=48)
->
Seq Scan on aa0
(cost=0.00..20.70 rows=1070 width=48)
->
Seq Scan on aa1
(cost=0.00..20.70 rows=1070 width=48)
->
Seq Scan on aa2
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=249.06..257.08 rows=3210 width=32)
Sort Key: ("left"(lower(bb0.name), 1))
->
Result
(cost=0.00..62.10 rows=3210 width=32)
->
Append
(cost=0.00..62.10 rows=3210 width=32)
->
Seq Scan on bb0
(cost=0.00..20.70 rows=1070 width=32)
->
Seq Scan on bb1
(cost=0.00..20.70 rows=1070 width=32)
->
Seq Scan on bb2
(cost=0.00..20.70 rows=1070 width=32)
(16 rows)

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
CREATE TABLE aa2
PARTITION OF aa (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('e', 'f', 'g');
CREATE TABLE bb2
PARTITION OF bb (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('e', 'f');
postgres=# explain select aa.* from aa join bb on (left(lower(aa.name), 1)=left(lower(bb.name), 1));
QUERY PLAN
--------------------------------------------------------------------------------
Merge Join
(cost=498.11..1834.26 rows=51520 width=48)
Merge Cond: (("left"(lower(aa0.name), 1)) = ("left"(lower(bb0.name), 1)))
->
Sort
(cost=249.06..257.08 rows=3210 width=48)
Sort Key: ("left"(lower(aa0.name), 1))
->
Result
(cost=0.00..62.10 rows=3210 width=48)
->
Append
(cost=0.00..62.10 rows=3210 width=48)
->
Seq Scan on aa0
(cost=0.00..20.70 rows=1070 width=48)
->
Seq Scan on aa1
(cost=0.00..20.70 rows=1070 width=48)
->
Seq Scan on aa2
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=249.06..257.08 rows=3210 width=32)
Sort Key: ("left"(lower(bb0.name), 1))
->
Result
(cost=0.00..62.10 rows=3210 width=32)
->
Append
(cost=0.00..62.10 rows=3210 width=32)
->
Seq Scan on bb0
(cost=0.00..20.70 rows=1070 width=32)
->
Seq Scan on bb1
(cost=0.00..20.70 rows=1070 width=32)
->
Seq Scan on bb2
(cost=0.00..20.70 rows=1070 width=32)
(16 rows)

HASH分区智能JOIN例子

1、创建HASH分区表

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE aaa (
order_id
bigint not null,
cust_id
bigint not null,
status
text
) PARTITION BY HASH (order_id);
CREATE TABLE aaa0 PARTITION OF aaa
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE aaa1 PARTITION OF aaa
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE aaa2 PARTITION OF aaa
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE aaa3 PARTITION OF aaa
FOR VALUES WITH (MODULUS 4, REMAINDER 3);

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE bbb (
order_id
bigint not null,
cust_id
bigint not null,
status
text
) PARTITION BY HASH (order_id);
CREATE TABLE bbb0 PARTITION OF bbb
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE bbb1 PARTITION OF bbb
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE bbb2 PARTITION OF bbb
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE bbb3 PARTITION OF bbb
FOR VALUES WITH (MODULUS 4, REMAINDER 3);

2、当结构一样时，会使用分区并行JOIN

复制代码

postgres=#
explain select aaa.* from aaa join bbb on (aaa.order_id = bbb.order_id);
QUERY PLAN
---------------------------------------------------------------------------
Append
(cost=149.08..961.21 rows=22896 width=48)
->
Merge Join
(cost=149.08..240.30 rows=5724 width=48)
Merge Cond: (aaa0.order_id = bbb0.order_id)
->
Sort
(cost=74.54..77.21 rows=1070 width=48)
Sort Key: aaa0.order_id
->
Seq Scan on aaa0
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=74.54..77.21 rows=1070 width=8)
Sort Key: bbb0.order_id
->
Seq Scan on bbb0
(cost=0.00..20.70 rows=1070 width=8)
->
Merge Join
(cost=149.08..240.30 rows=5724 width=48)
Merge Cond: (aaa1.order_id = bbb1.order_id)
->
Sort
(cost=74.54..77.21 rows=1070 width=48)
Sort Key: aaa1.order_id
->
Seq Scan on aaa1
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=74.54..77.21 rows=1070 width=8)
Sort Key: bbb1.order_id
->
Seq Scan on bbb1
(cost=0.00..20.70 rows=1070 width=8)
->
Merge Join
(cost=149.08..240.30 rows=5724 width=48)
Merge Cond: (aaa2.order_id = bbb2.order_id)
->
Sort
(cost=74.54..77.21 rows=1070 width=48)
Sort Key: aaa2.order_id
->
Seq Scan on aaa2
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=74.54..77.21 rows=1070 width=8)
Sort Key: bbb2.order_id
->
Seq Scan on bbb2
(cost=0.00..20.70 rows=1070 width=8)
->
Merge Join
(cost=149.08..240.30 rows=5724 width=48)
Merge Cond: (aaa3.order_id = bbb3.order_id)
->
Sort
(cost=74.54..77.21 rows=1070 width=48)
Sort Key: aaa3.order_id
->
Seq Scan on aaa3
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=74.54..77.21 rows=1070 width=8)
Sort Key: bbb3.order_id
->
Seq Scan on bbb3
(cost=0.00..20.70 rows=1070 width=8)
(33 rows)

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
postgres=#
explain select aaa.* from aaa join bbb on (aaa.order_id = bbb.order_id);
QUERY PLAN
---------------------------------------------------------------------------
Append
(cost=149.08..961.21 rows=22896 width=48)
->
Merge Join
(cost=149.08..240.30 rows=5724 width=48)
Merge Cond: (aaa0.order_id = bbb0.order_id)
->
Sort
(cost=74.54..77.21 rows=1070 width=48)
Sort Key: aaa0.order_id
->
Seq Scan on aaa0
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=74.54..77.21 rows=1070 width=8)
Sort Key: bbb0.order_id
->
Seq Scan on bbb0
(cost=0.00..20.70 rows=1070 width=8)
->
Merge Join
(cost=149.08..240.30 rows=5724 width=48)
Merge Cond: (aaa1.order_id = bbb1.order_id)
->
Sort
(cost=74.54..77.21 rows=1070 width=48)
Sort Key: aaa1.order_id
->
Seq Scan on aaa1
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=74.54..77.21 rows=1070 width=8)
Sort Key: bbb1.order_id
->
Seq Scan on bbb1
(cost=0.00..20.70 rows=1070 width=8)
->
Merge Join
(cost=149.08..240.30 rows=5724 width=48)
Merge Cond: (aaa2.order_id = bbb2.order_id)
->
Sort
(cost=74.54..77.21 rows=1070 width=48)
Sort Key: aaa2.order_id
->
Seq Scan on aaa2
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=74.54..77.21 rows=1070 width=8)
Sort Key: bbb2.order_id
->
Seq Scan on bbb2
(cost=0.00..20.70 rows=1070 width=8)
->
Merge Join
(cost=149.08..240.30 rows=5724 width=48)
Merge Cond: (aaa3.order_id = bbb3.order_id)
->
Sort
(cost=74.54..77.21 rows=1070 width=48)
Sort Key: aaa3.order_id
->
Seq Scan on aaa3
(cost=0.00..20.70 rows=1070 width=48)
->
Sort
(cost=74.54..77.21 rows=1070 width=8)
Sort Key: bbb3.order_id
->
Seq Scan on bbb3
(cost=0.00..20.70 rows=1070 width=8)
(33 rows)

性能对比

对比单表、分区表（智能JOIN）的性能。

数据量1亿，HASH分区，32个分区。大家都使用并行，并行度一样。

1、单表

复制代码

1
2
3
4
create table a(id int, info text);
alter table a set (parallel_workers =32);
insert into a select generate_series(1,100000000), md5(random()::text);

2、分区表

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE b (id int, info text) PARTITION BY HASH (id);
do language plpgsql $$
declare
begin
for i in 0..31 loop
execute format('CREATE TABLE b%s PARTITION OF b FOR VALUES WITH (MODULUS 32, REMAINDER %s)', i, i);
execute format('alter table b%s set (parallel_workers=32)', i);
end loop;
end;
$$;
insert into b select generate_series(1,100000000), md5(random()::text);

3、性能对比

复制代码

1
2
3
4
5
6
set parallel_setup_cost =0;
set parallel_tuple_cost =0;
set min_parallel_table_scan_size =0;
set min_parallel_index_scan_size =0;
set max_parallel_workers_per_gather =32;

单表

复制代码

explain analyze select count(*) from a t1 join a t2 using (id) group by mod(hashtext(t1.info), 32);
postgres=# explain analyze select count(*) from a t1 join a t2 using (id) group by mod(hashtext(t1.info), 32);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate
(cost=2263558.48..6833369.72 rows=81819969 width=12) (actual time=13693.081..14677.473 rows=63 loops=1)
Group Key: (mod(hashtext(t1.info), 32))
->
Gather Merge
(cost=2263558.48..5106069.86 rows=100000064 width=12) (actual time=13679.757..14676.917 rows=2079 loops=1)
Workers Planned: 32
Workers Launched: 32
->
Partial GroupAggregate
(cost=2263557.65..2333870.19 rows=3125002 width=12) (actual time=13537.336..14481.211 rows=63 loops=33)
Group Key: (mod(hashtext(t1.info), 32))
->
Sort
(cost=2263557.65..2271370.15 rows=3125002 width=4) (actual time=13523.386..14056.543 rows=3030303 loops=33)
Sort Key: (mod(hashtext(t1.info), 32))
Sort Method: external merge
Disk: 33920kB
Worker 0:
Sort Method: external merge
Disk: 41224kB
Worker 1:
Sort Method: external merge
Disk: 44792kB
Worker 2:
Sort Method: external merge
Disk: 38296kB
Worker 3:
Sort Method: external merge
Disk: 35640kB
Worker 4:
Sort Method: external merge
Disk: 44672kB
Worker 5:
Sort Method: external merge
Disk: 42608kB
Worker 6:
Sort Method: external merge
Disk: 47680kB
Worker 7:
Sort Method: external merge
Disk: 47040kB
Worker 8:
Sort Method: external merge
Disk: 36960kB
Worker 9:
Sort Method: external merge
Disk: 47288kB
Worker 10:
Sort Method: external merge
Disk: 38896kB
Worker 11:
Sort Method: external merge
Disk: 43032kB
Worker 12:
Sort Method: external merge
Disk: 41184kB
Worker 13:
Sort Method: external merge
Disk: 37392kB
Worker 14:
Sort Method: external merge
Disk: 41448kB
Worker 15:
Sort Method: external merge
Disk: 48344kB
Worker 16:
Sort Method: external merge
Disk: 38712kB
Worker 17:
Sort Method: external merge
Disk: 45608kB
Worker 18:
Sort Method: external merge
Disk: 38904kB
Worker 19:
Sort Method: external merge
Disk: 37448kB
Worker 20:
Sort Method: external merge
Disk: 44784kB
Worker 21:
Sort Method: external merge
Disk: 40408kB
Worker 22:
Sort Method: external merge
Disk: 39904kB
Worker 23:
Sort Method: external merge
Disk: 43144kB
Worker 24:
Sort Method: external merge
Disk: 35720kB
Worker 25:
Sort Method: external merge
Disk: 48360kB
Worker 26:
Sort Method: external merge
Disk: 41960kB
Worker 27:
Sort Method: external merge
Disk: 42416kB
Worker 28:
Sort Method: external merge
Disk: 37712kB
Worker 29:
Sort Method: external merge
Disk: 45280kB
Worker 30:
Sort Method: external merge
Disk: 45752kB
Worker 31:
Sort Method: external merge
Disk: 36072kB
->
Parallel Hash Join
(cost=915854.55..1877609.41 rows=3125002 width=4) (actual time=9832.233..12171.841 rows=3030303 loops=33)
Hash Cond: (t1.id = t2.id)
->
Parallel Seq Scan on a t1
(cost=0.00..864584.03 rows=3125002 width=37) (actual time=0.022..772.247 rows=3030303 loops=33)
->
Parallel Hash
(cost=864584.03..864584.03 rows=3125002 width=4) (actual time=3674.043..3674.043 rows=3030303 loops=33)
Buckets: 131072
Batches: 2048
Memory Usage: 3232kB
->
Parallel Seq Scan on a t2
(cost=0.00..864584.03 rows=3125002 width=4) (actual time=0.027..723.891 rows=3030303 loops=33)
Planning time: 0.130 ms
Execution time: 21241.336 ms
(50 rows)

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
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
129
130
131
132
133
134
135
136
137
138
explain analyze select count(*) from a t1 join a t2 using (id) group by mod(hashtext(t1.info), 32);
postgres=# explain analyze select count(*) from a t1 join a t2 using (id) group by mod(hashtext(t1.info), 32);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate
(cost=2263558.48..6833369.72 rows=81819969 width=12) (actual time=13693.081..14677.473 rows=63 loops=1)
Group Key: (mod(hashtext(t1.info), 32))
->
Gather Merge
(cost=2263558.48..5106069.86 rows=100000064 width=12) (actual time=13679.757..14676.917 rows=2079 loops=1)
Workers Planned: 32
Workers Launched: 32
->
Partial GroupAggregate
(cost=2263557.65..2333870.19 rows=3125002 width=12) (actual time=13537.336..14481.211 rows=63 loops=33)
Group Key: (mod(hashtext(t1.info), 32))
->
Sort
(cost=2263557.65..2271370.15 rows=3125002 width=4) (actual time=13523.386..14056.543 rows=3030303 loops=33)
Sort Key: (mod(hashtext(t1.info), 32))
Sort Method: external merge
Disk: 33920kB
Worker 0:
Sort Method: external merge
Disk: 41224kB
Worker 1:
Sort Method: external merge
Disk: 44792kB
Worker 2:
Sort Method: external merge
Disk: 38296kB
Worker 3:
Sort Method: external merge
Disk: 35640kB
Worker 4:
Sort Method: external merge
Disk: 44672kB
Worker 5:
Sort Method: external merge
Disk: 42608kB
Worker 6:
Sort Method: external merge
Disk: 47680kB
Worker 7:
Sort Method: external merge
Disk: 47040kB
Worker 8:
Sort Method: external merge
Disk: 36960kB
Worker 9:
Sort Method: external merge
Disk: 47288kB
Worker 10:
Sort Method: external merge
Disk: 38896kB
Worker 11:
Sort Method: external merge
Disk: 43032kB
Worker 12:
Sort Method: external merge
Disk: 41184kB
Worker 13:
Sort Method: external merge
Disk: 37392kB
Worker 14:
Sort Method: external merge
Disk: 41448kB
Worker 15:
Sort Method: external merge
Disk: 48344kB
Worker 16:
Sort Method: external merge
Disk: 38712kB
Worker 17:
Sort Method: external merge
Disk: 45608kB
Worker 18:
Sort Method: external merge
Disk: 38904kB
Worker 19:
Sort Method: external merge
Disk: 37448kB
Worker 20:
Sort Method: external merge
Disk: 44784kB
Worker 21:
Sort Method: external merge
Disk: 40408kB
Worker 22:
Sort Method: external merge
Disk: 39904kB
Worker 23:
Sort Method: external merge
Disk: 43144kB
Worker 24:
Sort Method: external merge
Disk: 35720kB
Worker 25:
Sort Method: external merge
Disk: 48360kB
Worker 26:
Sort Method: external merge
Disk: 41960kB
Worker 27:
Sort Method: external merge
Disk: 42416kB
Worker 28:
Sort Method: external merge
Disk: 37712kB
Worker 29:
Sort Method: external merge
Disk: 45280kB
Worker 30:
Sort Method: external merge
Disk: 45752kB
Worker 31:
Sort Method: external merge
Disk: 36072kB
->
Parallel Hash Join
(cost=915854.55..1877609.41 rows=3125002 width=4) (actual time=9832.233..12171.841 rows=3030303 loops=33)
Hash Cond: (t1.id = t2.id)
->
Parallel Seq Scan on a t1
(cost=0.00..864584.03 rows=3125002 width=37) (actual time=0.022..772.247 rows=3030303 loops=33)
->
Parallel Hash
(cost=864584.03..864584.03 rows=3125002 width=4) (actual time=3674.043..3674.043 rows=3030303 loops=33)
Buckets: 131072
Batches: 2048
Memory Usage: 3232kB
->
Parallel Seq Scan on a t2
(cost=0.00..864584.03 rows=3125002 width=4) (actual time=0.027..723.891 rows=3030303 loops=33)
Planning time: 0.130 ms
Execution time: 21241.336 ms
(50 rows)

分区表

复制代码

set enable_partition_wise_join =on;
explain analyze select count(*) from b t1 join b t2 using (id) group by mod(hashtext(t1.info), 32);
postgres=# explain analyze select count(*) from b t1 join b t2 using (id) group by mod(hashtext(t1.info), 32);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate
(cost=614852193.02..614852244.02 rows=200 width=12) (actual time=7204.477..7204.930 rows=63 loops=1)
Group Key: (mod(hashtext(t1_19.info), 32))
->
Sort
(cost=614852193.02..614852209.02 rows=6400 width=12) (actual time=7204.463..7204.642 rows=2079 loops=1)
Sort Key: (mod(hashtext(t1_19.info), 32))
Sort Method: quicksort
Memory: 194kB
->
Gather
(cost=614851785.41..614851788.41 rows=6400 width=12) (actual time=7203.693..7204.108 rows=2079 loops=1)
Workers Planned: 32
Workers Launched: 32
->
Partial HashAggregate
(cost=614851785.41..614851788.41 rows=200 width=12) (actual time=7015.166..7015.178 rows=63 loops=33)
Group Key: mod(hashtext(t1_19.info), 32)
->
Result
(cost=28593.10..469548988.58 rows=29060559366 width=4) (actual time=3619.535..6398.725 rows=3030303 loops=33)
->
Parallel Append
(cost=28593.10..33640598.09 rows=29060559366 width=32) (actual time=3619.533..5727.722 rows=3030303 loops=33)
->
Parallel Hash Join
(cost=28802.97..1931500.70 rows=1712811342 width=32) (actual time=1866.954..2706.440 rows=1564195 loops=2)
Hash Cond: (t1_19.id = t2_19.id)
->
Parallel Seq Scan on b19 t1_19
(cost=0.00..27104.65 rows=103465 width=36) (actual time=0.020..369.823 rows=1564195 loops=2)
->
Parallel Hash
(cost=27104.65..27104.65 rows=103465 width=4) (actual time=847.192..847.192 rows=1564195 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b19 t2_19
(cost=0.00..27104.65 rows=103465 width=4) (actual time=0.023..783.139 rows=3128390 loops=1)
->
Parallel Hash Join
(cost=28797.52..1930765.83 rows=1712154401 width=32) (actual time=1836.637..2732.267 rows=1563871 loops=2)
Hash Cond: (t1_23.id = t2_23.id)
->
Parallel Seq Scan on b23 t1_23
(cost=0.00..27099.45 rows=103445 width=36) (actual time=0.015..745.650 rows=3127742 loops=1)
->
Parallel Hash
(cost=27099.45..27099.45 rows=103445 width=4) (actual time=840.882..840.882 rows=1563871 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b23 t2_23
(cost=0.00..27099.45 rows=103445 width=4) (actual time=0.025..762.816 rows=3127742 loops=1)
->
Parallel Hash Join
(cost=28792.08..1930183.67 rows=1711497585 width=32) (actual time=1808.072..2566.087 rows=1563583 loops=2)
Hash Cond: (t1_21.id = t2_21.id)
->
Parallel Seq Scan on b21 t1_21
(cost=0.00..27094.26 rows=103426 width=36) (actual time=0.011..727.971 rows=3127166 loops=1)
->
Parallel Hash
(cost=27094.26..27094.26 rows=103426 width=4) (actual time=826.727..826.727 rows=1563583 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b21 t2_21
(cost=0.00..27094.26 rows=103426 width=4) (actual time=0.022..767.670 rows=3127166 loops=1)
->
Parallel Hash Join
(cost=28784.54..1929220.60 rows=1710709573 width=32) (actual time=1916.147..2766.580 rows=1563226 loops=2)
Hash Cond: (t1_27.id = t2_27.id)
->
Parallel Seq Scan on b27 t1_27
(cost=0.00..27088.02 rows=103402 width=36) (actual time=0.012..744.061 rows=3126453 loops=1)
->
Parallel Hash
(cost=27088.02..27088.02 rows=103402 width=4) (actual time=892.154..892.154 rows=1563226 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b27 t2_27
(cost=0.00..27088.02 rows=103402 width=4) (actual time=0.024..819.622 rows=3126453 loops=1)
->
Parallel Hash Join
(cost=28781.27..1928805.77 rows=1710315634 width=32) (actual time=1855.321..2695.239 rows=1563010 loops=2)
Hash Cond: (t1_29.id = t2_29.id)
->
Parallel Seq Scan on b29 t1_29
(cost=0.00..27084.90 rows=103390 width=36) (actual time=0.016..758.210 rows=3126019 loops=1)
->
Parallel Hash
(cost=27084.90..27084.90 rows=103390 width=4) (actual time=815.498..815.498 rows=1563010 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b29 t2_29
(cost=0.00..27084.90 rows=103390 width=4) (actual time=0.044..755.479 rows=3126019 loops=1)
->
Parallel Hash Join
(cost=28779.09..1928486.16 rows=1710053034 width=32) (actual time=1999.648..2810.363 rows=1562892 loops=2)
Hash Cond: (t1_17.id = t2_17.id)
->
Parallel Seq Scan on b17 t1_17
(cost=0.00..27082.82 rows=103382 width=36) (actual time=0.010..841.027 rows=3125783 loops=1)
->
Parallel Hash
(cost=27082.82..27082.82 rows=103382 width=4) (actual time=841.996..841.996 rows=1562892 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b17 t2_17
(cost=0.00..27082.82 rows=103382 width=4) (actual time=0.023..775.531 rows=3125783 loops=1)
->
Parallel Hash Join
(cost=28779.09..1928486.16 rows=1710053034 width=32) (actual time=1885.162..2741.378 rows=1562898 loops=2)
Hash Cond: (t1_30.id = t2_30.id)
->
Parallel Seq Scan on b30 t1_30
(cost=0.00..27082.82 rows=103382 width=36) (actual time=0.014..836.915 rows=3125795 loops=1)
->
Parallel Hash
(cost=27082.82..27082.82 rows=103382 width=4) (actual time=796.030..796.030 rows=1562898 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b30 t2_30
(cost=0.00..27082.82 rows=103382 width=4) (actual time=0.022..727.776 rows=3125795 loops=1)
->
Parallel Hash Join
(cost=28778.00..1928390.98 rows=1709921741 width=32) (actual time=1961.159..2715.142 rows=1562835 loops=2)
Hash Cond: (t1_15.id = t2_15.id)
->
Parallel Seq Scan on b15 t1_15
(cost=0.00..27081.78 rows=103378 width=36) (actual time=0.012..771.256 rows=3125670 loops=1)
->
Parallel Hash
(cost=27081.78..27081.78 rows=103378 width=4) (actual time=916.632..916.632 rows=1562835 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b15 t2_15
(cost=0.00..27081.78 rows=103378 width=4) (actual time=0.022..844.903 rows=3125670 loops=1)
->
Parallel Hash Join
(cost=28775.83..1928071.40 rows=1709659171 width=32) (actual time=1891.613..2721.231 rows=1562730 loops=2)
Hash Cond: (t1_28.id = t2_28.id)
->
Parallel Seq Scan on b28 t1_28
(cost=0.00..27079.70 rows=103370 width=36) (actual time=0.014..775.292 rows=3125461 loops=1)
->
Parallel Hash
(cost=27079.70..27079.70 rows=103370 width=4) (actual time=861.424..861.424 rows=1562730 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b28 t2_28
(cost=0.00..27079.70 rows=103370 width=4) (actual time=0.027..791.442 rows=3125461 loops=1)
->
Parallel Hash Join
(cost=28773.65..1927751.86 rows=1709396622 width=32) (actual time=1951.851..2723.702 rows=1562602 loops=2)
Hash Cond: (t1_20.id = t2_20.id)
->
Parallel Seq Scan on b20 t1_20
(cost=0.00..27077.62 rows=103362 width=36) (actual time=0.012..829.750 rows=3125204 loops=1)
->
Parallel Hash
(cost=27077.62..27077.62 rows=103362 width=4) (actual time=822.328..822.328 rows=1562602 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b20 t2_20
(cost=0.00..27077.62 rows=103362 width=4) (actual time=0.021..753.268 rows=3125204 loops=1)
->
Parallel Hash Join
(cost=28763.84..1926507.98 rows=1708215397 width=32) (actual time=1350.844..1894.188 rows=1041391 loops=3)
Hash Cond: (t1_24.id = t2_24.id)
->
Parallel Seq Scan on b24 t1_24
(cost=0.00..27068.26 rows=103326 width=36) (actual time=0.012..800.586 rows=3124172 loops=1)
->
Parallel Hash
(cost=27068.26..27068.26 rows=103326 width=4) (actual time=599.137..599.137 rows=1041391 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b24 t2_24
(cost=0.00..27068.26 rows=103326 width=4) (actual time=0.031..811.976 rows=3124172 loops=1)
->
Parallel Hash Join
(cost=28762.75..1926283.69 rows=1708084175 width=32) (actual time=1880.594..2656.604 rows=1562024 loops=2)
Hash Cond: (t1_22.id = t2_22.id)
->
Parallel Seq Scan on b22 t1_22
(cost=0.00..27067.22 rows=103322 width=36) (actual time=0.018..755.265 rows=3124048 loops=1)
->
Parallel Hash
(cost=27067.22..27067.22 rows=103322 width=4) (actual time=823.162..823.162 rows=1562024 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b22 t2_22
(cost=0.00..27067.22 rows=103322 width=4) (actual time=0.031..762.119 rows=3124048 loops=1)
->
Parallel Hash Join
(cost=28762.75..1926283.69 rows=1708084175 width=32) (actual time=1223.705..1780.166 rows=1041335 loops=3)
Hash Cond: (t1_25.id = t2_25.id)
->
Parallel Seq Scan on b25 t1_25
(cost=0.00..27067.22 rows=103322 width=36) (actual time=0.011..710.056 rows=3124005 loops=1)
->
Parallel Hash
(cost=27067.22..27067.22 rows=103322 width=4) (actual time=571.546..571.546 rows=1041335 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b25 t2_25
(cost=0.00..27067.22 rows=103322 width=4) (actual time=0.031..785.465 rows=3124005 loops=1)
->
Parallel Hash Join
(cost=28761.66..1926188.57 rows=1707952958 width=32) (actual time=1199.223..1769.093 rows=1041316 loops=3)
Hash Cond: (t1_18.id = t2_18.id)
->
Parallel Seq Scan on b18 t1_18
(cost=0.00..27066.18 rows=103318 width=36) (actual time=0.010..740.937 rows=3123949 loops=1)
->
Parallel Hash
(cost=27066.18..27066.18 rows=103318 width=4) (actual time=540.893..540.893 rows=1041316 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b18 t2_18
(cost=0.00..27066.18 rows=103318 width=4) (actual time=0.027..745.852 rows=3123949 loops=1)
->
Parallel Hash Join
(cost=28758.40..1925797.32 rows=1707559337 width=32) (actual time=1736.535..2528.234 rows=1561768 loops=2)
Hash Cond: (t1_16.id = t2_16.id)
->
Parallel Seq Scan on b16 t1_16
(cost=0.00..27063.07 rows=103307 width=36) (actual time=0.015..691.218 rows=3123536 loops=1)
->
Parallel Hash
(cost=27063.07..27063.07 rows=103307 width=4) (actual time=815.419..815.419 rows=1561768 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b16 t2_16
(cost=0.00..27063.07 rows=103307 width=4) (actual time=0.022..754.738 rows=3123536 loops=1)
->
Parallel Hash Join
(cost=28752.95..1925061.54 rows=1706903403 width=32) (actual time=1830.592..2591.947 rows=1561455 loops=2)
Hash Cond: (t1_31.id = t2_31.id)
->
Parallel Seq Scan on b31 t1_31
(cost=0.00..27057.87 rows=103287 width=36) (actual time=0.015..708.338 rows=3122910 loops=1)
->
Parallel Hash
(cost=27057.87..27057.87 rows=103287 width=4) (actual time=890.061..890.061 rows=1561455 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b31 t2_31
(cost=0.00..27057.87 rows=103287 width=4) (actual time=0.050..807.619 rows=3122910 loops=1)
->
Parallel Hash Join
(cost=28743.15..1923689.45 rows=1705723040 width=32) (actual time=1320.140..1887.795 rows=1040626 loops=3)
Hash Cond: (t1_26.id = t2_26.id)
->
Parallel Seq Scan on b26 t1_26
(cost=0.00..27048.51 rows=103251 width=36) (actual time=0.013..775.102 rows=3121877 loops=1)
->
Parallel Hash
(cost=27048.51..27048.51 rows=103251 width=4) (actual time=604.374..604.374 rows=1040626 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b26 t2_26
(cost=0.00..27048.51 rows=103251 width=4) (actual time=0.022..822.645 rows=3121877 loops=1)
->
Parallel Hash Join
(cost=28653.78..58010.65 rows=97768 width=33) (actual time=1766.702..2603.998 rows=1564294 loops=2)
Hash Cond: (t1_13.id = t2_13.id)
->
Parallel Seq Scan on b13 t1_13
(cost=0.00..27049.68 rows=97768 width=37) (actual time=0.011..758.550 rows=3128587 loops=1)
->
Parallel Hash
(cost=27049.68..27049.68 rows=97768 width=4) (actual time=757.912..757.912 rows=1564294 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b13 t2_13
(cost=0.00..27049.68 rows=97768 width=4) (actual time=0.022..708.487 rows=3128587 loops=1)
->
Parallel Hash Join
(cost=28639.70..57982.90 rows=97720 width=33) (actual time=1787.518..2610.471 rows=1563526 loops=2)
Hash Cond: (t1_4.id = t2_4.id)
->
Parallel Seq Scan on b4 t1_4
(cost=0.00..27036.20 rows=97720 width=37) (actual time=0.011..721.138 rows=3127053 loops=1)
->
Parallel Hash
(cost=27036.20..27036.20 rows=97720 width=4) (actual time=823.404..823.404 rows=1563526 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b4 t2_4
(cost=0.00..27036.20 rows=97720 width=4) (actual time=0.022..757.877 rows=3127053 loops=1)
->
Parallel Hash Join
(cost=28636.41..57976.41 rows=97707 width=33) (actual time=1841.218..2659.950 rows=1563313 loops=2)
Hash Cond: (t1_6.id = t2_6.id)
->
Parallel Seq Scan on b6 t1_6
(cost=0.00..27033.07 rows=97707 width=37) (actual time=0.012..725.716 rows=3126626 loops=1)
->
Parallel Hash
(cost=27033.07..27033.07 rows=97707 width=4) (actual time=838.918..838.918 rows=1563313 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b6 t2_6
(cost=0.00..27033.07 rows=97707 width=4) (actual time=0.022..761.078 rows=3126626 loops=1)
->
Parallel Hash Join
(cost=28627.80..57959.43 rows=97680 width=33) (actual time=1904.717..2738.413 rows=1562877 loops=2)
Hash Cond: (t1_8.id = t2_8.id)
->
Parallel Seq Scan on b8 t1_8
(cost=0.00..27024.80 rows=97680 width=37) (actual time=0.012..794.271 rows=3125754 loops=1)
->
Parallel Hash
(cost=27024.80..27024.80 rows=97680 width=4) (actual time=816.413..816.413 rows=1562877 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b8 t2_8
(cost=0.00..27024.80 rows=97680 width=4) (actual time=0.024..752.536 rows=3125754 loops=1)
->
Parallel Hash Join
(cost=28624.49..57952.92 rows=97666 width=33) (actual time=1291.905..1899.778 rows=1041776 loops=3)
Hash Cond: (t1_7.id = t2_7.id)
->
Parallel Seq Scan on b7 t1_7
(cost=0.00..27021.66 rows=97666 width=37) (actual time=0.020..839.558 rows=3125327 loops=1)
->
Parallel Hash
(cost=27021.66..27021.66 rows=97666 width=4) (actual time=538.444..538.444 rows=1041776 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b7 t2_7
(cost=0.00..27021.66 rows=97666 width=4) (actual time=0.025..737.354 rows=3125327 loops=1)
->
Parallel Hash Join
(cost=28619.12..57940.33 rows=97650 width=33) (actual time=1336.851..1898.101 rows=1041600 loops=3)
Hash Cond: (t1_14.id = t2_14.id)
->
Parallel Seq Scan on b14 t1_14
(cost=0.00..27016.50 rows=97650 width=37) (actual time=0.014..879.777 rows=3124800 loops=1)
->
Parallel Hash
(cost=27016.50..27016.50 rows=97650 width=4) (actual time=557.214..557.214 rows=1041600 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b14 t2_14
(cost=0.00..27016.50 rows=97650 width=4) (actual time=0.024..771.426 rows=3124800 loops=1)
->
Parallel Hash Join
(cost=28616.90..57935.96 rows=97640 width=33) (actual time=1763.645..2637.993 rows=1562242 loops=2)
Hash Cond: (t1_2.id = t2_2.id)
->
Parallel Seq Scan on b2 t1_2
(cost=0.00..27014.40 rows=97640 width=37) (actual time=0.012..779.228 rows=3124485 loops=1)
->
Parallel Hash
(cost=27014.40..27014.40 rows=97640 width=4) (actual time=713.576..713.576 rows=1562242 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b2 t2_2
(cost=0.00..27014.40 rows=97640 width=4) (actual time=0.021..692.229 rows=3124485 loops=1)
->
Parallel Hash Join
(cost=28616.88..57935.93 rows=97639 width=33) (actual time=1913.659..2728.474 rows=1562226 loops=2)
Hash Cond: (t1_3.id = t2_3.id)
->
Parallel Seq Scan on b3 t1_3
(cost=0.00..27014.39 rows=97639 width=37) (actual time=0.010..810.788 rows=3124453 loops=1)
->
Parallel Hash
(cost=27014.39..27014.39 rows=97639 width=4) (actual time=812.084..812.084 rows=1562226 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b3 t2_3
(cost=0.00..27014.39 rows=97639 width=4) (actual time=0.021..744.328 rows=3124453 loops=1)
->
Parallel Hash Join
(cost=28614.72..57931.67 rows=97632 width=33) (actual time=1871.882..2659.464 rows=1562112 loops=2)
Hash Cond: (t1.id = t2.id)
->
Parallel Seq Scan on b0 t1
(cost=0.00..27012.32 rows=97632 width=37) (actual time=0.012..757.401 rows=3124223 loops=1)
->
Parallel Hash
(cost=27012.32..27012.32 rows=97632 width=4) (actual time=820.929..820.929 rows=1562112 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b0 t2
(cost=0.00..27012.32 rows=97632 width=4) (actual time=0.020..745.483 rows=3124223 loops=1)
->
Parallel Hash Join
(cost=28613.68..57929.60 rows=97630 width=33) (actual time=1324.474..1901.956 rows=1041391 loops=3)
Hash Cond: (t1_12.id = t2_12.id)
->
Parallel Seq Scan on b12 t1_12
(cost=0.00..27011.30 rows=97630 width=37) (actual time=0.018..812.690 rows=3124172 loops=1)
->
Parallel Hash
(cost=27011.30..27011.30 rows=97630 width=4) (actual time=585.582..585.582 rows=1041391 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b12 t2_12
(cost=0.00..27011.30 rows=97630 width=4) (actual time=0.027..795.302 rows=3124172 loops=1)
->
Parallel Hash Join
(cost=28612.60..57927.48 rows=97627 width=33) (actual time=1756.082..2559.447 rows=1562026 loops=2)
Hash Cond: (t1_5.id = t2_5.id)
->
Parallel Seq Scan on b5 t1_5
(cost=0.00..27010.27 rows=97627 width=37) (actual time=0.015..703.728 rows=3124053 loops=1)
->
Parallel Hash
(cost=27010.27..27010.27 rows=97627 width=4) (actual time=815.540..815.540 rows=1562026 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b5 t2_5
(cost=0.00..27010.27 rows=97627 width=4) (actual time=0.021..735.105 rows=3124053 loops=1)
->
Parallel Hash Join
(cost=28610.45..57923.24 rows=97620 width=33) (actual time=1231.338..1903.723 rows=1041280 loops=3)
Hash Cond: (t1_10.id = t2_10.id)
->
Parallel Seq Scan on b10 t1_10
(cost=0.00..27008.20 rows=97620 width=37) (actual time=0.017..737.708 rows=3123840 loops=1)
->
Parallel Hash
(cost=27008.20..27008.20 rows=97620 width=4) (actual time=552.640..552.640 rows=1041280 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b10 t2_10
(cost=0.00..27008.20 rows=97620 width=4) (actual time=0.028..759.707 rows=3123840 loops=1)
->
Parallel Hash Join
(cost=28606.11..57914.68 rows=97605 width=33) (actual time=1787.829..2661.597 rows=1561676 loops=2)
Hash Cond: (t1_9.id = t2_9.id)
->
Parallel Seq Scan on b9 t1_9
(cost=0.00..27004.05 rows=97605 width=37) (actual time=0.011..737.515 rows=3123353 loops=1)
->
Parallel Hash
(cost=27004.05..27004.05 rows=97605 width=4) (actual time=807.676..807.676 rows=1561676 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b9 t2_9
(cost=0.00..27004.05 rows=97605 width=4) (actual time=0.021..741.285 rows=3123353 loops=1)
->
Parallel Hash Join
(cost=28605.00..57912.49 rows=97600 width=33) (actual time=1959.902..2790.390 rows=1561594 loops=2)
Hash Cond: (t1_1.id = t2_1.id)
->
Parallel Seq Scan on b1 t1_1
(cost=0.00..27003.00 rows=97600 width=37) (actual time=0.013..411.643 rows=1561594 loops=2)
->
Parallel Hash
(cost=27003.00..27003.00 rows=97600 width=4) (actual time=827.679..827.679 rows=1561594 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b1 t2_1
(cost=0.00..27003.00 rows=97600 width=4) (actual time=0.029..731.938 rows=3123189 loops=1)
->
Parallel Hash Join
(cost=28593.10..57889.03 rows=97560 width=33) (actual time=1779.294..2644.233 rows=1560952 loops=2)
Hash Cond: (t1_11.id = t2_11.id)
->
Parallel Seq Scan on b11 t1_11
(cost=0.00..26991.60 rows=97560 width=37) (actual time=0.017..407.205 rows=1560952 loops=2)
->
Parallel Hash
(cost=26991.60..26991.60 rows=97560 width=4) (actual time=734.729..734.729 rows=1560952 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b11 t2_11
(cost=0.00..26991.60 rows=97560 width=4) (actual time=0.023..353.979 rows=1560952 loops=2)
Planning time: 1.792 ms
Execution time: 8767.304 ms
(206 rows)

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
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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
set enable_partition_wise_join =on;
explain analyze select count(*) from b t1 join b t2 using (id) group by mod(hashtext(t1.info), 32);
postgres=# explain analyze select count(*) from b t1 join b t2 using (id) group by mod(hashtext(t1.info), 32);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate
(cost=614852193.02..614852244.02 rows=200 width=12) (actual time=7204.477..7204.930 rows=63 loops=1)
Group Key: (mod(hashtext(t1_19.info), 32))
->
Sort
(cost=614852193.02..614852209.02 rows=6400 width=12) (actual time=7204.463..7204.642 rows=2079 loops=1)
Sort Key: (mod(hashtext(t1_19.info), 32))
Sort Method: quicksort
Memory: 194kB
->
Gather
(cost=614851785.41..614851788.41 rows=6400 width=12) (actual time=7203.693..7204.108 rows=2079 loops=1)
Workers Planned: 32
Workers Launched: 32
->
Partial HashAggregate
(cost=614851785.41..614851788.41 rows=200 width=12) (actual time=7015.166..7015.178 rows=63 loops=33)
Group Key: mod(hashtext(t1_19.info), 32)
->
Result
(cost=28593.10..469548988.58 rows=29060559366 width=4) (actual time=3619.535..6398.725 rows=3030303 loops=33)
->
Parallel Append
(cost=28593.10..33640598.09 rows=29060559366 width=32) (actual time=3619.533..5727.722 rows=3030303 loops=33)
->
Parallel Hash Join
(cost=28802.97..1931500.70 rows=1712811342 width=32) (actual time=1866.954..2706.440 rows=1564195 loops=2)
Hash Cond: (t1_19.id = t2_19.id)
->
Parallel Seq Scan on b19 t1_19
(cost=0.00..27104.65 rows=103465 width=36) (actual time=0.020..369.823 rows=1564195 loops=2)
->
Parallel Hash
(cost=27104.65..27104.65 rows=103465 width=4) (actual time=847.192..847.192 rows=1564195 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b19 t2_19
(cost=0.00..27104.65 rows=103465 width=4) (actual time=0.023..783.139 rows=3128390 loops=1)
->
Parallel Hash Join
(cost=28797.52..1930765.83 rows=1712154401 width=32) (actual time=1836.637..2732.267 rows=1563871 loops=2)
Hash Cond: (t1_23.id = t2_23.id)
->
Parallel Seq Scan on b23 t1_23
(cost=0.00..27099.45 rows=103445 width=36) (actual time=0.015..745.650 rows=3127742 loops=1)
->
Parallel Hash
(cost=27099.45..27099.45 rows=103445 width=4) (actual time=840.882..840.882 rows=1563871 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b23 t2_23
(cost=0.00..27099.45 rows=103445 width=4) (actual time=0.025..762.816 rows=3127742 loops=1)
->
Parallel Hash Join
(cost=28792.08..1930183.67 rows=1711497585 width=32) (actual time=1808.072..2566.087 rows=1563583 loops=2)
Hash Cond: (t1_21.id = t2_21.id)
->
Parallel Seq Scan on b21 t1_21
(cost=0.00..27094.26 rows=103426 width=36) (actual time=0.011..727.971 rows=3127166 loops=1)
->
Parallel Hash
(cost=27094.26..27094.26 rows=103426 width=4) (actual time=826.727..826.727 rows=1563583 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b21 t2_21
(cost=0.00..27094.26 rows=103426 width=4) (actual time=0.022..767.670 rows=3127166 loops=1)
->
Parallel Hash Join
(cost=28784.54..1929220.60 rows=1710709573 width=32) (actual time=1916.147..2766.580 rows=1563226 loops=2)
Hash Cond: (t1_27.id = t2_27.id)
->
Parallel Seq Scan on b27 t1_27
(cost=0.00..27088.02 rows=103402 width=36) (actual time=0.012..744.061 rows=3126453 loops=1)
->
Parallel Hash
(cost=27088.02..27088.02 rows=103402 width=4) (actual time=892.154..892.154 rows=1563226 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b27 t2_27
(cost=0.00..27088.02 rows=103402 width=4) (actual time=0.024..819.622 rows=3126453 loops=1)
->
Parallel Hash Join
(cost=28781.27..1928805.77 rows=1710315634 width=32) (actual time=1855.321..2695.239 rows=1563010 loops=2)
Hash Cond: (t1_29.id = t2_29.id)
->
Parallel Seq Scan on b29 t1_29
(cost=0.00..27084.90 rows=103390 width=36) (actual time=0.016..758.210 rows=3126019 loops=1)
->
Parallel Hash
(cost=27084.90..27084.90 rows=103390 width=4) (actual time=815.498..815.498 rows=1563010 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b29 t2_29
(cost=0.00..27084.90 rows=103390 width=4) (actual time=0.044..755.479 rows=3126019 loops=1)
->
Parallel Hash Join
(cost=28779.09..1928486.16 rows=1710053034 width=32) (actual time=1999.648..2810.363 rows=1562892 loops=2)
Hash Cond: (t1_17.id = t2_17.id)
->
Parallel Seq Scan on b17 t1_17
(cost=0.00..27082.82 rows=103382 width=36) (actual time=0.010..841.027 rows=3125783 loops=1)
->
Parallel Hash
(cost=27082.82..27082.82 rows=103382 width=4) (actual time=841.996..841.996 rows=1562892 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b17 t2_17
(cost=0.00..27082.82 rows=103382 width=4) (actual time=0.023..775.531 rows=3125783 loops=1)
->
Parallel Hash Join
(cost=28779.09..1928486.16 rows=1710053034 width=32) (actual time=1885.162..2741.378 rows=1562898 loops=2)
Hash Cond: (t1_30.id = t2_30.id)
->
Parallel Seq Scan on b30 t1_30
(cost=0.00..27082.82 rows=103382 width=36) (actual time=0.014..836.915 rows=3125795 loops=1)
->
Parallel Hash
(cost=27082.82..27082.82 rows=103382 width=4) (actual time=796.030..796.030 rows=1562898 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b30 t2_30
(cost=0.00..27082.82 rows=103382 width=4) (actual time=0.022..727.776 rows=3125795 loops=1)
->
Parallel Hash Join
(cost=28778.00..1928390.98 rows=1709921741 width=32) (actual time=1961.159..2715.142 rows=1562835 loops=2)
Hash Cond: (t1_15.id = t2_15.id)
->
Parallel Seq Scan on b15 t1_15
(cost=0.00..27081.78 rows=103378 width=36) (actual time=0.012..771.256 rows=3125670 loops=1)
->
Parallel Hash
(cost=27081.78..27081.78 rows=103378 width=4) (actual time=916.632..916.632 rows=1562835 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b15 t2_15
(cost=0.00..27081.78 rows=103378 width=4) (actual time=0.022..844.903 rows=3125670 loops=1)
->
Parallel Hash Join
(cost=28775.83..1928071.40 rows=1709659171 width=32) (actual time=1891.613..2721.231 rows=1562730 loops=2)
Hash Cond: (t1_28.id = t2_28.id)
->
Parallel Seq Scan on b28 t1_28
(cost=0.00..27079.70 rows=103370 width=36) (actual time=0.014..775.292 rows=3125461 loops=1)
->
Parallel Hash
(cost=27079.70..27079.70 rows=103370 width=4) (actual time=861.424..861.424 rows=1562730 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b28 t2_28
(cost=0.00..27079.70 rows=103370 width=4) (actual time=0.027..791.442 rows=3125461 loops=1)
->
Parallel Hash Join
(cost=28773.65..1927751.86 rows=1709396622 width=32) (actual time=1951.851..2723.702 rows=1562602 loops=2)
Hash Cond: (t1_20.id = t2_20.id)
->
Parallel Seq Scan on b20 t1_20
(cost=0.00..27077.62 rows=103362 width=36) (actual time=0.012..829.750 rows=3125204 loops=1)
->
Parallel Hash
(cost=27077.62..27077.62 rows=103362 width=4) (actual time=822.328..822.328 rows=1562602 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b20 t2_20
(cost=0.00..27077.62 rows=103362 width=4) (actual time=0.021..753.268 rows=3125204 loops=1)
->
Parallel Hash Join
(cost=28763.84..1926507.98 rows=1708215397 width=32) (actual time=1350.844..1894.188 rows=1041391 loops=3)
Hash Cond: (t1_24.id = t2_24.id)
->
Parallel Seq Scan on b24 t1_24
(cost=0.00..27068.26 rows=103326 width=36) (actual time=0.012..800.586 rows=3124172 loops=1)
->
Parallel Hash
(cost=27068.26..27068.26 rows=103326 width=4) (actual time=599.137..599.137 rows=1041391 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b24 t2_24
(cost=0.00..27068.26 rows=103326 width=4) (actual time=0.031..811.976 rows=3124172 loops=1)
->
Parallel Hash Join
(cost=28762.75..1926283.69 rows=1708084175 width=32) (actual time=1880.594..2656.604 rows=1562024 loops=2)
Hash Cond: (t1_22.id = t2_22.id)
->
Parallel Seq Scan on b22 t1_22
(cost=0.00..27067.22 rows=103322 width=36) (actual time=0.018..755.265 rows=3124048 loops=1)
->
Parallel Hash
(cost=27067.22..27067.22 rows=103322 width=4) (actual time=823.162..823.162 rows=1562024 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b22 t2_22
(cost=0.00..27067.22 rows=103322 width=4) (actual time=0.031..762.119 rows=3124048 loops=1)
->
Parallel Hash Join
(cost=28762.75..1926283.69 rows=1708084175 width=32) (actual time=1223.705..1780.166 rows=1041335 loops=3)
Hash Cond: (t1_25.id = t2_25.id)
->
Parallel Seq Scan on b25 t1_25
(cost=0.00..27067.22 rows=103322 width=36) (actual time=0.011..710.056 rows=3124005 loops=1)
->
Parallel Hash
(cost=27067.22..27067.22 rows=103322 width=4) (actual time=571.546..571.546 rows=1041335 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b25 t2_25
(cost=0.00..27067.22 rows=103322 width=4) (actual time=0.031..785.465 rows=3124005 loops=1)
->
Parallel Hash Join
(cost=28761.66..1926188.57 rows=1707952958 width=32) (actual time=1199.223..1769.093 rows=1041316 loops=3)
Hash Cond: (t1_18.id = t2_18.id)
->
Parallel Seq Scan on b18 t1_18
(cost=0.00..27066.18 rows=103318 width=36) (actual time=0.010..740.937 rows=3123949 loops=1)
->
Parallel Hash
(cost=27066.18..27066.18 rows=103318 width=4) (actual time=540.893..540.893 rows=1041316 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b18 t2_18
(cost=0.00..27066.18 rows=103318 width=4) (actual time=0.027..745.852 rows=3123949 loops=1)
->
Parallel Hash Join
(cost=28758.40..1925797.32 rows=1707559337 width=32) (actual time=1736.535..2528.234 rows=1561768 loops=2)
Hash Cond: (t1_16.id = t2_16.id)
->
Parallel Seq Scan on b16 t1_16
(cost=0.00..27063.07 rows=103307 width=36) (actual time=0.015..691.218 rows=3123536 loops=1)
->
Parallel Hash
(cost=27063.07..27063.07 rows=103307 width=4) (actual time=815.419..815.419 rows=1561768 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b16 t2_16
(cost=0.00..27063.07 rows=103307 width=4) (actual time=0.022..754.738 rows=3123536 loops=1)
->
Parallel Hash Join
(cost=28752.95..1925061.54 rows=1706903403 width=32) (actual time=1830.592..2591.947 rows=1561455 loops=2)
Hash Cond: (t1_31.id = t2_31.id)
->
Parallel Seq Scan on b31 t1_31
(cost=0.00..27057.87 rows=103287 width=36) (actual time=0.015..708.338 rows=3122910 loops=1)
->
Parallel Hash
(cost=27057.87..27057.87 rows=103287 width=4) (actual time=890.061..890.061 rows=1561455 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b31 t2_31
(cost=0.00..27057.87 rows=103287 width=4) (actual time=0.050..807.619 rows=3122910 loops=1)
->
Parallel Hash Join
(cost=28743.15..1923689.45 rows=1705723040 width=32) (actual time=1320.140..1887.795 rows=1040626 loops=3)
Hash Cond: (t1_26.id = t2_26.id)
->
Parallel Seq Scan on b26 t1_26
(cost=0.00..27048.51 rows=103251 width=36) (actual time=0.013..775.102 rows=3121877 loops=1)
->
Parallel Hash
(cost=27048.51..27048.51 rows=103251 width=4) (actual time=604.374..604.374 rows=1040626 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b26 t2_26
(cost=0.00..27048.51 rows=103251 width=4) (actual time=0.022..822.645 rows=3121877 loops=1)
->
Parallel Hash Join
(cost=28653.78..58010.65 rows=97768 width=33) (actual time=1766.702..2603.998 rows=1564294 loops=2)
Hash Cond: (t1_13.id = t2_13.id)
->
Parallel Seq Scan on b13 t1_13
(cost=0.00..27049.68 rows=97768 width=37) (actual time=0.011..758.550 rows=3128587 loops=1)
->
Parallel Hash
(cost=27049.68..27049.68 rows=97768 width=4) (actual time=757.912..757.912 rows=1564294 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b13 t2_13
(cost=0.00..27049.68 rows=97768 width=4) (actual time=0.022..708.487 rows=3128587 loops=1)
->
Parallel Hash Join
(cost=28639.70..57982.90 rows=97720 width=33) (actual time=1787.518..2610.471 rows=1563526 loops=2)
Hash Cond: (t1_4.id = t2_4.id)
->
Parallel Seq Scan on b4 t1_4
(cost=0.00..27036.20 rows=97720 width=37) (actual time=0.011..721.138 rows=3127053 loops=1)
->
Parallel Hash
(cost=27036.20..27036.20 rows=97720 width=4) (actual time=823.404..823.404 rows=1563526 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b4 t2_4
(cost=0.00..27036.20 rows=97720 width=4) (actual time=0.022..757.877 rows=3127053 loops=1)
->
Parallel Hash Join
(cost=28636.41..57976.41 rows=97707 width=33) (actual time=1841.218..2659.950 rows=1563313 loops=2)
Hash Cond: (t1_6.id = t2_6.id)
->
Parallel Seq Scan on b6 t1_6
(cost=0.00..27033.07 rows=97707 width=37) (actual time=0.012..725.716 rows=3126626 loops=1)
->
Parallel Hash
(cost=27033.07..27033.07 rows=97707 width=4) (actual time=838.918..838.918 rows=1563313 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b6 t2_6
(cost=0.00..27033.07 rows=97707 width=4) (actual time=0.022..761.078 rows=3126626 loops=1)
->
Parallel Hash Join
(cost=28627.80..57959.43 rows=97680 width=33) (actual time=1904.717..2738.413 rows=1562877 loops=2)
Hash Cond: (t1_8.id = t2_8.id)
->
Parallel Seq Scan on b8 t1_8
(cost=0.00..27024.80 rows=97680 width=37) (actual time=0.012..794.271 rows=3125754 loops=1)
->
Parallel Hash
(cost=27024.80..27024.80 rows=97680 width=4) (actual time=816.413..816.413 rows=1562877 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b8 t2_8
(cost=0.00..27024.80 rows=97680 width=4) (actual time=0.024..752.536 rows=3125754 loops=1)
->
Parallel Hash Join
(cost=28624.49..57952.92 rows=97666 width=33) (actual time=1291.905..1899.778 rows=1041776 loops=3)
Hash Cond: (t1_7.id = t2_7.id)
->
Parallel Seq Scan on b7 t1_7
(cost=0.00..27021.66 rows=97666 width=37) (actual time=0.020..839.558 rows=3125327 loops=1)
->
Parallel Hash
(cost=27021.66..27021.66 rows=97666 width=4) (actual time=538.444..538.444 rows=1041776 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b7 t2_7
(cost=0.00..27021.66 rows=97666 width=4) (actual time=0.025..737.354 rows=3125327 loops=1)
->
Parallel Hash Join
(cost=28619.12..57940.33 rows=97650 width=33) (actual time=1336.851..1898.101 rows=1041600 loops=3)
Hash Cond: (t1_14.id = t2_14.id)
->
Parallel Seq Scan on b14 t1_14
(cost=0.00..27016.50 rows=97650 width=37) (actual time=0.014..879.777 rows=3124800 loops=1)
->
Parallel Hash
(cost=27016.50..27016.50 rows=97650 width=4) (actual time=557.214..557.214 rows=1041600 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b14 t2_14
(cost=0.00..27016.50 rows=97650 width=4) (actual time=0.024..771.426 rows=3124800 loops=1)
->
Parallel Hash Join
(cost=28616.90..57935.96 rows=97640 width=33) (actual time=1763.645..2637.993 rows=1562242 loops=2)
Hash Cond: (t1_2.id = t2_2.id)
->
Parallel Seq Scan on b2 t1_2
(cost=0.00..27014.40 rows=97640 width=37) (actual time=0.012..779.228 rows=3124485 loops=1)
->
Parallel Hash
(cost=27014.40..27014.40 rows=97640 width=4) (actual time=713.576..713.576 rows=1562242 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b2 t2_2
(cost=0.00..27014.40 rows=97640 width=4) (actual time=0.021..692.229 rows=3124485 loops=1)
->
Parallel Hash Join
(cost=28616.88..57935.93 rows=97639 width=33) (actual time=1913.659..2728.474 rows=1562226 loops=2)
Hash Cond: (t1_3.id = t2_3.id)
->
Parallel Seq Scan on b3 t1_3
(cost=0.00..27014.39 rows=97639 width=37) (actual time=0.010..810.788 rows=3124453 loops=1)
->
Parallel Hash
(cost=27014.39..27014.39 rows=97639 width=4) (actual time=812.084..812.084 rows=1562226 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b3 t2_3
(cost=0.00..27014.39 rows=97639 width=4) (actual time=0.021..744.328 rows=3124453 loops=1)
->
Parallel Hash Join
(cost=28614.72..57931.67 rows=97632 width=33) (actual time=1871.882..2659.464 rows=1562112 loops=2)
Hash Cond: (t1.id = t2.id)
->
Parallel Seq Scan on b0 t1
(cost=0.00..27012.32 rows=97632 width=37) (actual time=0.012..757.401 rows=3124223 loops=1)
->
Parallel Hash
(cost=27012.32..27012.32 rows=97632 width=4) (actual time=820.929..820.929 rows=1562112 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b0 t2
(cost=0.00..27012.32 rows=97632 width=4) (actual time=0.020..745.483 rows=3124223 loops=1)
->
Parallel Hash Join
(cost=28613.68..57929.60 rows=97630 width=33) (actual time=1324.474..1901.956 rows=1041391 loops=3)
Hash Cond: (t1_12.id = t2_12.id)
->
Parallel Seq Scan on b12 t1_12
(cost=0.00..27011.30 rows=97630 width=37) (actual time=0.018..812.690 rows=3124172 loops=1)
->
Parallel Hash
(cost=27011.30..27011.30 rows=97630 width=4) (actual time=585.582..585.582 rows=1041391 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b12 t2_12
(cost=0.00..27011.30 rows=97630 width=4) (actual time=0.027..795.302 rows=3124172 loops=1)
->
Parallel Hash Join
(cost=28612.60..57927.48 rows=97627 width=33) (actual time=1756.082..2559.447 rows=1562026 loops=2)
Hash Cond: (t1_5.id = t2_5.id)
->
Parallel Seq Scan on b5 t1_5
(cost=0.00..27010.27 rows=97627 width=37) (actual time=0.015..703.728 rows=3124053 loops=1)
->
Parallel Hash
(cost=27010.27..27010.27 rows=97627 width=4) (actual time=815.540..815.540 rows=1562026 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b5 t2_5
(cost=0.00..27010.27 rows=97627 width=4) (actual time=0.021..735.105 rows=3124053 loops=1)
->
Parallel Hash Join
(cost=28610.45..57923.24 rows=97620 width=33) (actual time=1231.338..1903.723 rows=1041280 loops=3)
Hash Cond: (t1_10.id = t2_10.id)
->
Parallel Seq Scan on b10 t1_10
(cost=0.00..27008.20 rows=97620 width=37) (actual time=0.017..737.708 rows=3123840 loops=1)
->
Parallel Hash
(cost=27008.20..27008.20 rows=97620 width=4) (actual time=552.640..552.640 rows=1041280 loops=3)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b10 t2_10
(cost=0.00..27008.20 rows=97620 width=4) (actual time=0.028..759.707 rows=3123840 loops=1)
->
Parallel Hash Join
(cost=28606.11..57914.68 rows=97605 width=33) (actual time=1787.829..2661.597 rows=1561676 loops=2)
Hash Cond: (t1_9.id = t2_9.id)
->
Parallel Seq Scan on b9 t1_9
(cost=0.00..27004.05 rows=97605 width=37) (actual time=0.011..737.515 rows=3123353 loops=1)
->
Parallel Hash
(cost=27004.05..27004.05 rows=97605 width=4) (actual time=807.676..807.676 rows=1561676 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b9 t2_9
(cost=0.00..27004.05 rows=97605 width=4) (actual time=0.021..741.285 rows=3123353 loops=1)
->
Parallel Hash Join
(cost=28605.00..57912.49 rows=97600 width=33) (actual time=1959.902..2790.390 rows=1561594 loops=2)
Hash Cond: (t1_1.id = t2_1.id)
->
Parallel Seq Scan on b1 t1_1
(cost=0.00..27003.00 rows=97600 width=37) (actual time=0.013..411.643 rows=1561594 loops=2)
->
Parallel Hash
(cost=27003.00..27003.00 rows=97600 width=4) (actual time=827.679..827.679 rows=1561594 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b1 t2_1
(cost=0.00..27003.00 rows=97600 width=4) (actual time=0.029..731.938 rows=3123189 loops=1)
->
Parallel Hash Join
(cost=28593.10..57889.03 rows=97560 width=33) (actual time=1779.294..2644.233 rows=1560952 loops=2)
Hash Cond: (t1_11.id = t2_11.id)
->
Parallel Seq Scan on b11 t1_11
(cost=0.00..26991.60 rows=97560 width=37) (actual time=0.017..407.205 rows=1560952 loops=2)
->
Parallel Hash
(cost=26991.60..26991.60 rows=97560 width=4) (actual time=734.729..734.729 rows=1560952 loops=2)
Buckets: 131072
Batches: 64
Memory Usage: 2976kB
->
Parallel Seq Scan on b11 t2_11
(cost=0.00..26991.60 rows=97560 width=4) (actual time=0.023..353.979 rows=1560952 loops=2)
Planning time: 1.792 ms
Execution time: 8767.304 ms
(206 rows)

小结

PostgreSQL 11对分区表的一个重大功能点改进，分区智能JOIN，对于分区结构一致，并且JOIN字段包含了双方的分区键时，会选择分区与分区JOIN，然后APPEND的执行计划。大幅提高性能。

必须满足以下条件，优化器才会使用分区JOIN分区。

1、打开enable_partition_wise_join开关

复制代码

1
2
3
4
5
6
postgres=# show enable_partition_wise_join ;
enable_partition_wise_join
----------------------------
on
(1 row)

2、分区表的模式一致（range, list, hash）

3、分区表的分区数目

4、分区表每个分区的定义一致。

5、分区字段必须参与JOIN（但是可以含其他JOIN字段）。

6、分区字段的类型必须一致

7、如果是表达式分区键，那么表达式必须一致

注意，由于判断是否使用智能分区并行JOIN需要耗费一定的优化器判断逻辑，会带来执行计划成本的提升，所以默认这个开关是关闭的。

智能分区JOIN的内部原理与并行HASH JOIN有一些类似，和MPP架构也有一些类似。

《PostgreSQL dblink异步调用实现并行hash分片JOIN - 含数据交、并、差提速案例》

《PostgreSQL 11 preview - parallel hash join(并行哈希JOIN) 性能极大提升》

结合PostgreSQL 11的append并行和hash join并行，使得PG在分区表的JOIN上，性能暴增。

复制代码

postgres=#
explain select aaa.* from aaa join bbb on (aaa.order_id = bbb.order_id);
QUERY PLAN
---------------------------------------------------------------------------------------
Gather
(cost=10.75..85.35 rows=91592 width=48)
Workers Planned: 32
->
Parallel Append
(cost=10.75..85.35 rows=716 width=48)
->
Parallel Hash Join
(cost=10.75..21.34 rows=179 width=48)
Hash Cond: (aaa0.order_id = bbb0.order_id)
->
Parallel Seq Scan on aaa0
(cost=0.00..10.33 rows=33 width=48)
->
Parallel Hash
(cost=10.33..10.33 rows=33 width=8)
->
Parallel Seq Scan on bbb0
(cost=0.00..10.33 rows=33 width=8)
->
Parallel Hash Join
(cost=10.75..21.34 rows=179 width=48)
Hash Cond: (aaa1.order_id = bbb1.order_id)
->
Parallel Seq Scan on aaa1
(cost=0.00..10.33 rows=33 width=48)
->
Parallel Hash
(cost=10.33..10.33 rows=33 width=8)
->
Parallel Seq Scan on bbb1
(cost=0.00..10.33 rows=33 width=8)
->
Parallel Hash Join
(cost=10.75..21.34 rows=179 width=48)
Hash Cond: (aaa2.order_id = bbb2.order_id)
->
Parallel Seq Scan on aaa2
(cost=0.00..10.33 rows=33 width=48)
->
Parallel Hash
(cost=10.33..10.33 rows=33 width=8)
->
Parallel Seq Scan on bbb2
(cost=0.00..10.33 rows=33 width=8)
->
Parallel Hash Join
(cost=10.75..21.34 rows=179 width=48)
Hash Cond: (aaa3.order_id = bbb3.order_id)
->
Parallel Seq Scan on aaa3
(cost=0.00..10.33 rows=33 width=48)
->
Parallel Hash
(cost=10.33..10.33 rows=33 width=8)
->
Parallel Seq Scan on bbb3
(cost=0.00..10.33 rows=33 width=8)
(23 rows)

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
postgres=#
explain select aaa.* from aaa join bbb on (aaa.order_id = bbb.order_id);
QUERY PLAN
---------------------------------------------------------------------------------------
Gather
(cost=10.75..85.35 rows=91592 width=48)
Workers Planned: 32
->
Parallel Append
(cost=10.75..85.35 rows=716 width=48)
->
Parallel Hash Join
(cost=10.75..21.34 rows=179 width=48)
Hash Cond: (aaa0.order_id = bbb0.order_id)
->
Parallel Seq Scan on aaa0
(cost=0.00..10.33 rows=33 width=48)
->
Parallel Hash
(cost=10.33..10.33 rows=33 width=8)
->
Parallel Seq Scan on bbb0
(cost=0.00..10.33 rows=33 width=8)
->
Parallel Hash Join
(cost=10.75..21.34 rows=179 width=48)
Hash Cond: (aaa1.order_id = bbb1.order_id)
->
Parallel Seq Scan on aaa1
(cost=0.00..10.33 rows=33 width=48)
->
Parallel Hash
(cost=10.33..10.33 rows=33 width=8)
->
Parallel Seq Scan on bbb1
(cost=0.00..10.33 rows=33 width=8)
->
Parallel Hash Join
(cost=10.75..21.34 rows=179 width=48)
Hash Cond: (aaa2.order_id = bbb2.order_id)
->
Parallel Seq Scan on aaa2
(cost=0.00..10.33 rows=33 width=48)
->
Parallel Hash
(cost=10.33..10.33 rows=33 width=8)
->
Parallel Seq Scan on bbb2
(cost=0.00..10.33 rows=33 width=8)
->
Parallel Hash Join
(cost=10.75..21.34 rows=179 width=48)
Hash Cond: (aaa3.order_id = bbb3.order_id)
->
Parallel Seq Scan on aaa3
(cost=0.00..10.33 rows=33 width=48)
->
Parallel Hash
(cost=10.33..10.33 rows=33 width=8)
->
Parallel Seq Scan on bbb3
(cost=0.00..10.33 rows=33 width=8)
(23 rows)

相比非智能分区JOIN，本例测试性能提升2.4倍。

如果单表JOIN能加上JOIN字段的rehash的手段，就能在PG内部实现类似MPP的任意表的并行分片JOIN了。类似如下方法中提到的人为干预的分片HASH并行：

《PostgreSQL dblink异步调用实现并行hash分片JOIN - 含数据交、并、差提速案例》

最后

以上就是火星上发卡最近收集整理的关于PostgreSQL 11 preview - 分区智能并行JOIN (类似MPP架构，性能暴增)的全部内容，更多相关PostgreSQL内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：数据库
浏览次数：103 次浏览
发布日期：2024-01-06 14:31:10
本文链接：https://www.kaopuke.com/article/k-p-k_13_u_23_ogfw_14__23_g3.html

PostgreSQL 11 preview - 分区智能并行JOIN (类似MPP架构，性能暴增)

标签

背景

RANGE分区智能JOIN例子

LIST分区智能JOIN例子

HASH分区智能JOIN例子

性能对比

小结

最后

评论列表共有 0 条评论

发表评论取消回复

PostgreSQL 11 preview - 分区智能并行JOIN (类似MPP架构，性能暴增)

标签

背景

RANGE分区智能JOIN例子

LIST分区智能JOIN例子

HASH分区智能JOIN例子

性能对比

小结

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

微信扫一扫：分享

发表评论取消回复