非SMP场景下的Partition-wise Join

使用规格

非SMP场景下的Partition-wise Join的使用规格：

• 只支持一级RANGE分区。
• 支持Hash Join、Nestloop Join、Merge Join。
• 只支持Inner Join。
• 需要设置query_dop的值为1。
• 由于非SMP场景下的Partition-wise Join为规则选择，所以Partition-wise Join计划可能造成性能下降，需要用户自行决定是否启用。
• 仅支持FQS计划。

示例

--创建Range分区表。
gaussdb=# CREATE TABLE range_part (
gaussdb(#     a INTEGER,
gaussdb(#     b INTEGER,
gaussdb(#     c INTEGER
gaussdb(# ) PARTITION BY RANGE (a)
gaussdb-# (
gaussdb(# PARTITION range_part_p1 VALUES LESS THAN (10),
gaussdb(# PARTITION range_part_p2 VALUES LESS THAN (20),
gaussdb(# PARTITION range_part_p3 VALUES LESS THAN (30),
gaussdb(# PARTITION range_part_p4 VALUES LESS THAN (40)
gaussdb(# );

--使用FQS计划。
gaussdb=# SET enable_fast_query_shipping= ON;
SET

--设置query_dop为1，关闭SMP。
gaussdb=# SET query_dop = 1;
SET

--关闭非SMP场景下的Partition-wise Join开关。
gaussdb=# SET enable_partitionwise = off;
SET

--查看非Partition-wise Join执行计划。
gaussdb=# SET max_datanode_for_plan = 1;
SET
gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM range_part t1 INNER JOIN range_part t2 ON (t1.a = t2.a);
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Data Node Scan
   Node/s: All datanodes

 Remote SQL: SELECT t1.a, t1.b, t1.c, t2.a, t2.b, t2.c FROM public.range_part t1 JOIN public.range_part t2 ON t1.a = t2.a
 Datanode Name: datanode1
   Hash Join
     Hash Cond: (t1.a = t2.a)
     ->  Partition Iterator
           Iterations: 4
           ->  Partitioned Seq Scan on range_part t1
                 Selected Partitions:  1..4
     ->  Hash
           ->  Partition Iterator
                 Iterations: 4
                 ->  Partitioned Seq Scan on range_part t2
                       Selected Partitions:  1..4

(17 rows)

--打开非SMP场景下的Partition-wise Join开关。
gaussdb=# SET enable_partitionwise = on;
SET

--查看非SMP场景下的Partition-wise Join计划。从执行计划中可以看到，Partition Iterator算子被提到了Hash Join算子的上层。计算方式由原来的依次扫描完所有分区的数据之后再进行Join，改为了每次扫描一对分区，进行Join，再依次遍历下一个分区。
gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM range_part t1 INNER JOIN range_part t2 ON (t1.a = t2.a);
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Data Node Scan
   Node/s: All datanodes

 Remote SQL: SELECT t1.a, t1.b, t1.c, t2.a, t2.b, t2.c FROM public.range_part t1 JOIN public.range_part t2 ON t1.a = t2.a
 Datanode Name: datanode1
   Result
     ->  Partition Iterator
           Iterations: 4
           ->  Hash Join
                 Hash Cond: (t1.a = t2.a)
                 ->  Partitioned Seq Scan on range_part t1
                       Selected Partitions:  1..4
                 ->  Hash
                       ->  Partitioned Seq Scan on range_part t2
                             Selected Partitions:  1..4

(16 rows)

-- 删除分区表
gaussdb=# DROP TABLE range_part;