Partition-Wise Join in Non-SMP Scenarios

Usage Specifications

Partition-wise join usage specifications in non-SMP scenarios are as follows:

• Only level-1 range partitions are supported.
• Hash Join, Nestloop Join, and Merge Join are supported.
• Only Inner Join is supported.
• query_dop must be set to 1.
• In non-SMP scenarios, partition-wise join selects paths based on a rule. Therefore, using the partition-wise join plan may cause performance deterioration. You can determine whether to enable it based on the actual situation.
• Only FQS plans are supported.

Examples

-- Create a range partitioned table.
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(# );

-- Use an FQS plan.
gaussdb=# SET enable_fast_query_shipping= ON;
SET

-- Set query_dop to 1 to disable SMP.
gaussdb=# SET query_dop = 1;
SET

-- Disable partition-wise join in a non-SMP scenario.
gaussdb=# SET enable_partitionwise = off;
SET

-- View the non-partition-wise join execution plan.
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)

-- Enable partition-wise join in a non-SMP scenario.
gaussdb=# SET enable_partitionwise = on;
SET

-- View the partition-wise join plan in a non-SMP scenario. According to the execution plan, the Partition Iterator operator is pulled up to the upper layer of the Hash Join operator. In this way, each pair of partitions is scanned and joined immediately. (Previously, partition join starts after all partitions are scanned.)
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)

-- Drop the partitioned table.
gaussdb=# DROP TABLE range_part;