Dynamic Partition Pruning

Dynamic partition pruning supports the following three types:

The prepare-execute (bind variable) execution mode and dynamic pruning expression are similar to static pruning. The only difference is that one side of the static pruning expression is Const. One side of the dynamic pruning expression is external parameters or an expression composed of external parameters.
```
prepare ps(int) as select * from prune_tt01 where a > $1;
explain verbose execute ps(12);
```
A query statement contains subqueries, which are divided into two methods: pruning partitioned tables in the main query and pruning partitioned tables in the subquery:
- The partition table in the main query is pruned. The subquery is generally an unrelated subquery. The subquery works as one side of the partition pruning expression in the main query, and the other side is the partitioning key.
```
select * from prune_tt01 where a > (select a from t2 limit 1);
```
- The partition table in the subquery is pruned. The subquery is generally a correlation subquery, and the subquery does not support promotion. Note: The related subquery involves pruning of multiple partitioned table, the result of the last pruning is shown in Explain Analyze. any subquery:
```
explain analyze select * from t2 where a > any(select a from prune_tt01 where prune_tt01.a = t2.a);
```
  exists subquery:
```
explain analyze select * from t2 where exists (select a from prune_tt01 where prune_tt01.a = t2.a limit 1);
```
NestLoop parameterized query, that is, the query plan use the nestLoop+indexscan query method. The partition key of a partitioned table must be the join key of two tables and an index must be created on the partition key.

The following is the dynamic partition pruning example.

drop table if exists prune_tt01;
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)
);
INSERT INTO prune_tt01 VALUES (generate_series(1, 20), generate_series(1,20));
CREATE INDEX index_prune_tt01 ON prune_tt01 USING btree(a) LOCAL;

drop table if exists tt02;
create table tt02(a int, b int);
INSERT INTO tt02 VALUES (generate_series(1, 20), generate_series(1,20));

prepare-execute (bind variable) scenario

MogDB=# prepare ps(int) as select * from prune_tt01 where a > $1;
PREPARE
MogDB=# explain verbose execute ps(12);
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..36.86 rows=716 width=8)
   Output: a, b
   Iterations: PART
   Selected Partitions:  PART
   ->  Partitioned Seq Scan on public.prune_tt01  (cost=0.00..36.86 rows=716 width=8)
         Output: a, b
         Filter: (prune_tt01.a > $1)
(7 rows)

MogDB=# explain analyze execute ps(12);
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..36.86 rows=716 width=8) (actual time=0.099..0.110 rows=8 loops=1)
   Iterations: 2
   Selected Partitions:  3..4
   ->  Partitioned Seq Scan on prune_tt01  (cost=0.00..36.86 rows=716 width=8) (actual time=0.031..0.034 rows=8 loops=2)
         Filter: (a > $1)
         Rows Removed by Filter: 3
 Total runtime: 0.218 ms
(7 rows)

Subquery scenario

Prune the partition table in the main query

MogDB=# explain verbose select * from prune_tt01 where a > (select a from t2 where a > 12 limit 1);
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.04..36.90 rows=716 width=8)
   Output: prune_tt01.a, prune_tt01.b
   Iterations: PART
   Selected Partitions:  PART
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.04 rows=1 width=4)
           Output: t2.a
           ->  Seq Scan on public.t2  (cost=0.00..1.75 rows=49 width=4)
                 Output: t2.a
                 Filter: (t2.a > 12)
   ->  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)
(13 rows)

MogDB=# explain analyze select * from prune_tt01 where a > (select a from t2 where a > 12 limit 1);
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.04..36.90 rows=716 width=8) (actual time=0.172..0.180 rows=7 loops=1)
   Iterations: 2
   Selected Partitions:  3..4
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.04 rows=1 width=4) (actual time=0.093..0.093 rows=1 loops=1)
           ->  Seq Scan on t2  (cost=0.00..1.75 rows=49 width=4) (actual time=0.091..0.091 rows=1 loops=1)
                 Filter: (a > 12)
                 Rows Removed by Filter: 12
   ->  Partitioned Seq Scan on prune_tt01  (cost=0.00..36.86 rows=716 width=8) (actual time=0.020..0.020 rows=7 loops=2)
         Filter: (a > $0)
         Rows Removed by Filter: 4
 Total runtime: 0.301 ms
(12 rows)

Prune the partition table in the subquery:

any subquery:

MogDB=# explain verbose  select * from t2 where a > any(select a from prune_tt01 where prune_tt01.a = t2.a);
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Seq Scan on public.t2  (cost=0.00..582.83 rows=30 width=8)
   Output: t2.a, t2.b
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Partition Iterator  (cost=4.34..15.01 rows=11 width=4)
           Output: prune_tt01.a
           Iterations: PART
           Selected Partitions:  PART
           ->  Partitioned Bitmap Heap Scan on public.prune_tt01  (cost=4.34..15.01 rows=11 width=4)
                 Output: prune_tt01.a
                 Recheck Cond: (prune_tt01.a = t2.a)
                 ->  Partitioned Bitmap Index Scan on index_prune_tt01  (cost=0.00..4.33 rows=11 width=0)
                       Index Cond: (prune_tt01.a = t2.a)
(13 rows)

MogDB=# explain analyze select * from t2 where a > any(select a from prune_tt01 where prune_tt01.a = t2.a);
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on t2  (cost=0.00..582.83 rows=30 width=8) (actual time=2.130..2.130 rows=0 loops=1)
   Filter: (SubPlan 1)
   Rows Removed by Filter: 60
   SubPlan 1
     ->  Partition Iterator  (cost=4.34..15.01 rows=11 width=4) (actual time=1.860..1.877 rows=20 loops=60)
           Iterations: 1
           Selected Partitions:  4
           ->  Partitioned Bitmap Heap Scan on prune_tt01  (cost=4.34..15.01 rows=11 width=4) (actual time=0.566..0.576 rows=20 loops=60)
                 Recheck Cond: (a = t2.a)
                 Heap Blocks: exact=20
                 ->  Partitioned Bitmap Index Scan on index_prune_tt01  (cost=0.00..4.33 rows=11 width=0) (actual time=0.482..0.482 rows=20 loops=60)
                       Index Cond: (a = t2.a)
 Total runtime: 2.600 ms
(13 rows)

exists subquery:

MogDB=# explain verbose select * from t2 where exists (select a from prune_tt01 where prune_tt01.a = t2.a limit 1);
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Seq Scan on public.t2  (cost=0.00..319.92 rows=30 width=8)
   Output: t2.a, t2.b
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Limit  (cost=4.34..5.31 rows=1 width=4)
           Output: prune_tt01.a
           ->  Partition Iterator  (cost=4.34..15.01 rows=11 width=4)
                 Output: prune_tt01.a
                 Iterations: PART
                 Selected Partitions:  PART
                 ->  Partitioned Bitmap Heap Scan on public.prune_tt01  (cost=4.34..15.01 rows=11 width=4)
                       Output: prune_tt01.a
                       Recheck Cond: (prune_tt01.a = t2.a)
                       ->  Partitioned Bitmap Index Scan on index_prune_tt01  (cost=0.00..4.33 rows=11 width=0)
                             Index Cond: (prune_tt01.a = t2.a)
(15 rows)

MogDB=# explain analyze select * from t2 where exists (select a from prune_tt01 where prune_tt01.a = t2.a limit 1);
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on t2  (cost=0.00..319.92 rows=30 width=8) (actual time=0.058..0.875 rows=20 loops=1)
   Filter: (SubPlan 1)
   Rows Removed by Filter: 40
   SubPlan 1
     ->  Limit  (cost=4.34..5.31 rows=1 width=4) (actual time=0.826..0.826 rows=20 loops=60)
           ->  Partition Iterator  (cost=4.34..15.01 rows=11 width=4) (actual time=0.789..0.789 rows=20 loops=60)
                 Iterations: 1
                 Selected Partitions:  4
                 ->  Partitioned Bitmap Heap Scan on prune_tt01  (cost=4.34..15.01 rows=11 width=4) (actual time=0.162..0.162 rows=20 loops=60)
                       Recheck Cond: (a = t2.a)
                       Heap Blocks: exact=20
                       ->  Partitioned Bitmap Index Scan on index_prune_tt01  (cost=0.00..4.33 rows=11 width=0) (actual time=0.123..0.123 rows=20 loops=60)
                             Index Cond: (a = t2.a)
 Total runtime: 1.151 ms
(14 rows)

nestloop scenario:

The following parameters are set only to simulate the use of nestloop in SQL statements (normally the optimizer will choose the best access path based on the amount of data in the table)

MogDB=# SET enable_material = OFF;
SET
MogDB=# SET enable_mergejoin = OFF;
SET
MogDB=# SET enable_hashjoin = OFF;
SET

MogDB=# explain verbose select * from prune_tt01 inner join tt02 on prune_tt01.a = tt02.a;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..1501.91 rows=23091 width=16)
   Output: prune_tt01.a, prune_tt01.b, tt02.a, tt02.b
   ->  Seq Scan on public.tt02  (cost=0.00..31.49 rows=2149 width=8)
         Output: tt02.a, tt02.b
   ->  Partition Iterator  (cost=0.00..0.57 rows=11 width=8)
         Output: prune_tt01.a, prune_tt01.b
         Iterations: PART
         Selected Partitions:  PART
         ->  Partitioned Index Scan using index_prune_tt01 on public.prune_tt01  (cost=0.00..0.57 rows=11 width=8)
               Output: prune_tt01.a, prune_tt01.b
               Index Cond: (prune_tt01.a = tt02.a)
(11 rows)

MogDB=# explain analyze select * from prune_tt01 inner join tt02 on prune_tt01.a = tt02.a;
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..1501.91 rows=23091 width=16) (actual time=0.078..0.535 rows=20 loops=1)
   ->  Seq Scan on tt02  (cost=0.00..31.49 rows=2149 width=8) (actual time=0.023..0.030 rows=20 loops=1)
   ->  Partition Iterator  (cost=0.00..0.57 rows=11 width=8) (actual time=0.441..0.462 rows=20 loops=20)
         Iterations: 1
         Selected Partitions:  4
         ->  Partitioned Index Scan using index_prune_tt01 on prune_tt01  (cost=0.00..0.57 rows=11 width=8) (actual time=0.146..0.158 rows=20 loops=20)
               Index Cond: (a = tt02.a)
 Total runtime: 0.770 ms
(8 rows)

Issue