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

标签

PostgreSQL , 并行join , 分区智能映射并行JOIN , hash 并行 , MPP

背景

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

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

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

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

涉及开关如下

postgres=# show enable_partition_wise_join ;
enable_partition_wise_join
----------------------------
off
(1 row)

解释

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、建立两个结构一样的分区表

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);

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

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)

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)

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、创建两个结构一样的测试分区表

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');

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)

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)

HASH分区智能JOIN例子

1、创建HASH分区表

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);

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)

性能对比

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

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

1、单表

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、分区表

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、性能对比

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)

分区表

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开关

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)

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

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

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