Benefits of Partition Pruning

Partition pruning is a common performance optimization method in partitioned tables. Before scanning a partition table, you can check partition constraints and the definition of each partition to exclude partitions that do not need to be scanned in advance, which can greatly improve scan performance. In the query planning stage, if the partition constraint is a definite expression, the partition that does not need to be scanned can be cut off according to the partition constraint expression in the query planning stage. This partition pruning method is generally called static partition pruning. Static partition pruning result can be seen from the EXPLAIN VERBOSE output, as shown below.

explain verbose select * from prune_tt01 where a<8;

MogDB=# drop table if exists prune_tt01;
DROP TABLE
MogDB=# CREATE TABLE prune_tt01(a int, b int)
PARTITION BY RANGE(a)
(
        PARTITION prune_tt01_p1 VALUES LESS THAN(5),
        PARTITION prune_tt01_p2 VALUES LESS THAN(10),
        PARTITION prune_tt01_p3 VALUES LESS THAN(15),
        PARTITION prune_tt01_p4 VALUES LESS THAN(MAXVALUE)
);
CREATE TABLE
MogDB=# INSERT INTO prune_tt01 VALUES (generate_series(1, 20), generate_series(1,20));
INSERT 0 20
MogDB=# CREATE INDEX index_prune_tt01 ON prune_tt01 USING btree(a) LOCAL;
CREATE INDEX
MogDB=# explain verbose select * from prune_tt01 where a<8 ;
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Partition Iterator  (cost=13.80..27.75 rows=716 width=8)
   Output: a, b
   Iterations: 2
   Selected Partitions:  1..2
   ->  Partitioned Bitmap Heap Scan on public.prune_tt01  (cost=13.80..27.75 rows=716 width=8)
         Output: a, b
         Recheck Cond: (prune_tt01.a < 8)
         ->  Partitioned Bitmap Index Scan on index_prune_tt01  (cost=0.00..13.62 rows=716 width=0)
               Index Cond: (prune_tt01.a < 8)
(9 rows)

However, in many scenarios such as the prepare-execute execution mode and the scenario in which the partition constraint expression contains subqueries, the partition constraint expression is uncertain or contains unknown parameters in the query planning phase, and cannot be tailored in the query planning phase. Partitioning expression is determined by external parameters and the results of subqueries for pruning, and usually pruning in the execution phase is called dynamic partitioning pruning. Dynamic partition pruning allows you to see pruning information from the execution through Explain Verbose (selected partitions: PART)

explain verbose select * from prune_tt01 where a < (select 8);

MogDB=# explain verbose select * from prune_tt01 where a < (select 8);
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.01..36.87 rows=716 width=8)
   Output: prune_tt01.a, prune_tt01.b
   Iterations: PART
   Selected Partitions:  PART
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=0)
           Output: 8
   ->  Partitioned Seq Scan on public.prune_tt01  (cost=0.00..36.86 rows=716 width=8)
         Output: prune_tt01.a, prune_tt01.b
         Filter: (prune_tt01.a < $0)
(10 rows)

MogDB 3.0 introduces dynamic pruning of partitioned tables, which greatly reduces the amount of data retrieved from disk and reduces processing time, thus improving query performance and optimizing resource utilization.