Full Partition-Wise Join in the SMP Scenario

Usage Specifications

Partition-wise join usage specifications in the SMP scenario are as follows:

• Only level-1 hash partitioned tables and level-1 range partitioned tables are supported.
• For hash partitioned tables, the same partitioning policy means that the partition key types and the number of partitions are the same.
• For range partitioned tables, the same partitioning policy means that the partition key types, number of partitions, number of partition keys, and boundary values of each partition are the same.
• Hash Join and Merge Join are supported.
• Seqscan, Indexscan, Indexonlyscan and Imcvscan are supported. For Indexscan and Indexonlyscan, only partitioned local indexes are supported, and the index type is B-tree or UB-tree.
• Related specifications are inherited from SMP specifications. IUD operations are not supported in the SMP scenario.
• The SMP function must be enabled and the value of query_dop must be greater than 1.

Examples

-- Create a hash partitioned table.
gaussdb=# CREATE TABLE hash_part
(
    a INTEGER,
    b INTEGER,
    c INTEGER
) PARTITION BY HASH(a) (
    PARTITION p1,
    PARTITION p2,
    PARTITION p3,
    PARTITION p4,
    PARTITION p5
);
CREATE TABLE

-- Set query_dop to 5 to enable SMP.
gaussdb=# SET query_dop = 5;
SET
gaussdb=# SET enable_force_smp = on;
SET

-- Disable the partition-wise join function in the SMP scenario.
gaussdb=# SET enable_smp_partitionwise = off;
SET

-- View a non-partition-wise join plan. According to the plan, after data scanning is completed using the Partition Iterator and Partitioned Seq Scan operators at two layers, data is redistributed using the Streaming(type: LOCAL REDISTRIBUTE) operator to ensure that data in the Join operator matches each other.
gaussdb=# EXPLAIN (costs off) SELECT * FROM hash_part t1, hash_part t2 WHERE t1.a=t2.a;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Streaming(type: LOCAL GATHER dop: 1/5)
   ->  Hash Join
         Hash Cond: (t1.a = t2.a)
         ->  Streaming(type: LOCAL REDISTRIBUTE dop: 5/5)
               ->  Partition Iterator
                     Iterations: 5
                     ->  Partitioned Seq Scan on hash_part t1
                           Selected Partitions:  1..5
         ->  Hash
               ->  Streaming(type: LOCAL REDISTRIBUTE dop: 5/5)
                     ->  Partition Iterator
                           Iterations: 5
                           ->  Partitioned Seq Scan on hash_part t2
                                 Selected Partitions:  1..5
(14 rows)

-- Enable the partition-wise join function in the SMP scenario.
gaussdb=# SET enable_smp_partitionwise = on;
SET

-- View the execution plan of partition-wise join. According to the plan, the partition-wise join plan eliminates the Streaming operator, that is, data does not need to be redistributed between threads, thereby reducing overheads of data transfer and improving performance of the Join operation.
gaussdb=#  EXPLAIN (costs off) SELECT * FROM hash_part t1, hash_part t2 WHERE t1.a=t2.a;
                          QUERY PLAN                          
--------------------------------------------------------------
 Streaming(type: LOCAL GATHER dop: 1/5)
   ->  Hash Join (Partition-wise Join)
         Hash Cond: (t1.a = t2.a)
         ->  Partition Iterator
               Iterations: 5
               ->  Partitioned Seq Scan on hash_part t1
                     Selected Partitions:  1..5
         ->  Hash
               ->  Partition Iterator
                     Iterations: 5
                     ->  Partitioned Seq Scan on hash_part t2
                           Selected Partitions:  1..5
(12 rows)

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