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.

Examples

-- Create a range partitioned table.
gaussdb=# CREATE TABLE range_part (
    a integer,
    b integer,
    c integer
) PARTITION BY RANGE (a)
(
    PARTITION range_part_p1 VALUES LESS THAN (10),
    PARTITION range_part_p2 VALUES LESS THAN (20),
    PARTITION range_part_p3 VALUES LESS THAN (30),
    PARTITION range_part_p4 VALUES LESS THAN (40)
);
CREATE TABLE

-- 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 plan in a non-SMP scenario.
gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM range_part t1 INNER JOIN range_part t2 ON (t1.a = t2.a);
                       QUERY PLAN                        
---------------------------------------------------------
 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
(11 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                           
---------------------------------------------------------------
 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
(10 rows)

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