# 参数化路径动态剪枝
当分区表的分区键所在列生成参数化路径（分区键所在列和其他表的列进行关联，且分区表作为内表生成NestLoop索引扫描）时，会触发参数化路径动态剪枝。参数化路径动态剪枝会在每次外表扫描完成后，驱动内表扫描时进行一次。
![](https://support.huaweicloud.com/distributed-devg-v10-gaussdb/public_sys-resources/notice_3.0-zh-cn.png)
- 参数化路径动态剪枝在多数情况下不支持子查询表达式（剪枝算子选择参数化路径且分区键上包含了一个InitPlan子查询条件除外），不支持stable和volatile函数，不支持跨QueryBlock参数化路径，不支持BitmapOr、BitmapAnd算子。
- 使用EXPLAIN显示计划时，参数化路径剪枝计划中显示的分区数只代表"可能访问的最大总分区数"，实际访问分区数可能会小很多。
- 使用EXPLAIN ANALYZE显示计划时，分区表参数化路径剪枝计划中会出现"ppi-pruning, actual scanned:XXX"信息，ppi-pruning代表触发了参数化路径剪枝；actual scanned并不代表参数化路径剪枝的结果，在非透明多写场景下，由于每个节点剪枝结果可能不相同，所以actual scanned无法代表参数化路径剪枝结果，该字段只表示静态剪枝和动态剪枝之后的结果。
 
- 参数化路径动态剪枝支持的典型场景具体示例如下：
  - 非透明多写特性下，比较表达式
    ```
    --创建分区表和索引
    gaussdb=# CREATE TABLE t1 (c1 INT, c2 INT)
    PARTITION BY RANGE (c1)
    (
        PARTITION p1 VALUES LESS THAN(10),
        PARTITION p2 VALUES LESS THAN(20),
        PARTITION p3 VALUES LESS THAN(MAXVALUE)
    );
    gaussdb=# CREATE TABLE t2 (c1 INT, c2 INT)
    PARTITION BY RANGE (c1)
    (
        PARTITION p1 VALUES LESS THAN(10),
        PARTITION p2 VALUES LESS THAN(20),
        PARTITION p3 VALUES LESS THAN(MAXVALUE)
    );
    gaussdb=# CREATE INDEX t1_c1 ON t1(c1) LOCAL;
    gaussdb=# CREATE INDEX t2_c1 ON t2(c1) LOCAL;
    gaussdb=# CREATE INDEX t1_c2 ON t1(c2) LOCAL;
    gaussdb=# CREATE INDEX t2_c2 ON t2(c2) LOCAL;
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT /*+ nestloop(t1 t2) indexscan(t1) indexscan(t2) */ * FROM t2 JOIN t1 ON t1.c1 = t2.c1;
                                                                        QUERY PLAN                                                                     
    ---------------------------------------------------------------------------------------------------------------------------------------------------
     Data Node Scan
       Output: t2.c1, t2.c2, t1.c1, t1.c2
       Node/s: All datanodes
       Remote query: SELECT/*+ NestLoop(t1 t2) IndexScan(t1) IndexScan(t2)*/ t2.c1, t2.c2, t1.c1, t1.c2 FROM public.t2 JOIN public.t1 ON t1.c1 = t2.c1
     Remote SQL: SELECT/*+ NestLoop(t1 t2) IndexScan(t1) IndexScan(t2)*/ t2.c1, t2.c2, t1.c1, t1.c2 FROM public.t2 JOIN public.t1 ON t1.c1 = t2.c1
     Datanode Name: datanode1
       Nested Loop
         Output: t2.c1, t2.c2, t1.c1, t1.c2
         ->  Partition Iterator
               Output: t2.c1, t2.c2
               Iterations: 3
               ->  Partitioned Index Scan using t2_c1 on public.t2
                     Output: t2.c1, t2.c2
                     Selected Partitions:  1..3
         ->  Partition Iterator
               Output: t1.c1, t1.c2
               Iterations: PART
               ->  Partitioned Index Scan using t1_c1 on public.t1
                     Output: t1.c1, t1.c2
                     Index Cond: (t1.c1 = t2.c1)
                     Selected Partitions:  1..3 (ppi-pruning)
    (23 rows)
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT /*+ nestloop(t1 t2) indexscan(t1) indexscan(t2) */ * FROM t2 JOIN t1 ON t1.c1 < t2.c1;
                                QUERY PLAN                             
    -------------------------------------------------------------------
     Streaming (type: GATHER)
       Output: t2.c1, t2.c2, t1.c1, t1.c2
       Node/s: All datanodes
       ->  Nested Loop
             Output: t2.c1, t2.c2, t1.c1, t1.c2
             ->  Streaming(type: BROADCAST)
                   Output: t2.c1, t2.c2
                   Spawn on: All datanodes
                   Consumer Nodes: All datanodes
                   ->  Partition Iterator
                         Output: t2.c1, t2.c2
                         Iterations: 3
                         ->  Partitioned Seq Scan on public.t2
                               Output: t2.c1, t2.c2
                               Distribute Key: t2.c1
                               Selected Partitions:  1..3
             ->  Partition Iterator
                   Output: t1.c1, t1.c2
                   Iterations: PART
                   ->  Partitioned Index Scan using t1_c1 on public.t1
                         Output: t1.c1, t1.c2
                         Distribute Key: t1.c1
                         Index Cond: (t1.c1 < t2.c1)
                         Selected Partitions:  1..3 (ppi-pruning)
    (24 rows)
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT /*+ nestloop(t1 t2) indexscan(t1) indexscan(t2) */ * FROM t2 JOIN t1 ON t1.c1 < t2.c1;
                                QUERY PLAN                             
    -------------------------------------------------------------------
     Streaming (type: GATHER)
       Output: t2.c1, t2.c2, t1.c1, t1.c2
       Node/s: All datanodes
       ->  Nested Loop
             Output: t2.c1, t2.c2, t1.c1, t1.c2
             ->  Streaming(type: BROADCAST)
                   Output: t2.c1, t2.c2
                   Spawn on: All datanodes
                   Consumer Nodes: All datanodes
                   ->  Partition Iterator
                         Output: t2.c1, t2.c2
                         Iterations: 3
                         ->  Partitioned Seq Scan on public.t2
                               Output: t2.c1, t2.c2
                               Distribute Key: t2.c1
                               Selected Partitions:  1..3
             ->  Partition Iterator
                   Output: t1.c1, t1.c2
                   Iterations: PART
                   ->  Partitioned Index Scan using t1_c1 on public.t1
                         Output: t1.c1, t1.c2
                         Distribute Key: t1.c1
                         Index Cond: (t1.c1 > t2.c1)
                         Selected Partitions:  1..3 (ppi-pruning)
    (24 rows)
    ```
    
  
  - 非透明多写特性下，逻辑表达式
    ```
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT /*+ nestloop(t1 t2) indexscan(t1) indexscan(t2) */ * FROM t2 JOIN t1 ON t1.c1 = t2.c1 AND t1.c2 = 2;
                                                                               QUERY PLAN                                                                            
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------
     Data Node Scan
       Output: t2.c1, t2.c2, t1.c1, t1.c2
       Node/s: All datanodes
       Remote query: SELECT/*+ NestLoop(t1 t2) IndexScan(t1) IndexScan(t2)*/ t2.c1, t2.c2, t1.c1, t1.c2 FROM public.t2 JOIN public.t1 ON t1.c1 = t2.c1 AND t1.c2 = 2
     Remote SQL: SELECT/*+ NestLoop(t1 t2) IndexScan(t1) IndexScan(t2)*/ t2.c1, t2.c2, t1.c1, t1.c2 FROM public.t2 JOIN public.t1 ON t1.c1 = t2.c1 AND t1.c2 = 2
     Datanode Name: datanode1
       Nested Loop
         Output: t2.c1, t2.c2, t1.c1, t1.c2
         ->  Partition Iterator
               Output: t1.c1, t1.c2
               Iterations: 3
               ->  Partitioned Index Scan using t1_c2 on public.t1
                     Output: t1.c1, t1.c2
                     Index Cond: (t1.c2 = 2)
                     Selected Partitions:  1..3
         ->  Partition Iterator
               Output: t2.c1, t2.c2
               Iterations: PART
               ->  Partitioned Index Scan using t2_c1 on public.t2
                     Output: t2.c1, t2.c2
                     Index Cond: (t2.c1 = t1.c1)
                     Selected Partitions:  1..3 (ppi-pruning)
    (24 rows)
    ```
    
   
- 参数化路径动态剪枝不支持的典型场景具体示例如下：
  - BitmapOr/BitmapAnd算子
    ```
    gaussdb=# set enable_seqscan=off;
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT /*+ nestloop(t1 t2) */ * FROM t2 JOIN t1 ON t1.c1 = t2.c1 OR t1.c2 = 2;
    WARNING:  Statistics in some tables or columns(public.t2.c1, public.t1.c1, public.t1.c2) are not collected.
    HINT:  Do analyze for them in order to generate optimized plan.
                                  QUERY PLAN
    ----------------------------------------------------------------------
     Streaming (type: GATHER)
       Output: t2.c1, t2.c2, t1.c1, t1.c2
       Node/s: All datanodes
       ->  Nested Loop
             Output: t2.c1, t2.c2, t1.c1, t1.c2
             ->  Streaming(type: BROADCAST)
                   Output: t2.c1, t2.c2
                   Spawn on: All datanodes
                   Consumer Nodes: All datanodes
                   ->  Partition Iterator
                         Output: t2.c1, t2.c2
                         Iterations: 3
                         ->  Partitioned Seq Scan on public.t2
                               Output: t2.c1, t2.c2
                               Distribute Key: t2.c1
                               Selected Partitions:  1..3
             ->  Partition Iterator
                   Output: t1.c1, t1.c2
                   Iterations: 3
                   ->  Partitioned Bitmap Heap Scan on public.t1
                         Output: t1.c1, t1.c2
                         Distribute Key: t1.c1
                         Recheck Cond: ((t1.c1 = t2.c1) OR (t1.c2 = 2))
                         Selected Partitions:  1..3
                         ->  BitmapOr
                               ->  Partitioned Bitmap Index Scan on t1_c1
                                     Index Cond: (t1.c1 = t2.c1)
                               ->  Partitioned Bitmap Index Scan on t1_c2
                                     Index Cond: (t1.c2 = 2)
    (29 rows)
    ```
    
  
  - 非透明多写特性下，隐式转换
    ```
    gaussdb=# CREATE TABLE t3(c1 TEXT, c2 INT);
    CREATE TABLE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 JOIN t3 ON t1.c1 = t3.c1;
    WARNING:  Statistics in some tables or columns(public.t1.c1, public.t3.c1) are not collected.
    HINT:  Do analyze for them in order to generate optimized plan.
                                QUERY PLAN
    -------------------------------------------------------------------
     Streaming (type: GATHER)
       Output: t1.c1, t1.c2, t3.c1, t3.c2
       Node/s: All datanodes
       ->  Nested Loop
             Output: t1.c1, t1.c2, t3.c1, t3.c2
             Join Filter: (t1.c1 = (lengthb(t3.c1)))
             ->  Partition Iterator
                   Output: t1.c1, t1.c2
                   Iterations: 3
                   ->  Partitioned Index Scan using t1_c1 on public.t1
                         Output: t1.c1, t1.c2
                         Distribute Key: t1.c1
                         Selected Partitions:  1..3
             ->  Materialize
                   Output: t3.c1, t3.c2, (lengthb(t3.c1))
                   ->  Streaming(type: REDISTRIBUTE)
                         Output: t3.c1, t3.c2, (lengthb(t3.c1))
                         Distribute Key: (lengthb(t3.c1))
                         Spawn on: All datanodes
                         Consumer Nodes: All datanodes
                         ->  Seq Scan on public.t3
                               Output: t3.c1, t3.c2, lengthb(t3.c1)
                               Distribute Key: t3.c1
    (23 rows)
    ```
    
  
  - 非透明多写特性下，函数
    ```
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 JOIN t3 ON t1.c1 = LENGTHB(t3.c1);
                            QUERY PLAN
    -----------------------------------------------------------
     Streaming (type: GATHER)
       Output: t1.c1, t1.c2, t3.c1, t3.c2
       Node/s: All datanodes
       ->  Hash Join
             Output: t1.c1, t1.c2, t3.c1, t3.c2
             Hash Cond: ((lengthb(t3.c1)) = t1.c1)
             ->  Streaming(type: REDISTRIBUTE)
                   Output: t3.c1, t3.c2, (lengthb(t3.c1))
                   Distribute Key: (lengthb(t3.c1))
                   Spawn on: All datanodes
                   Consumer Nodes: All datanodes
                   ->  Seq Scan on public.t3
                         Output: t3.c1, t3.c2, lengthb(t3.c1)
                         Distribute Key: t3.c1
             ->  Hash
                   Output: t1.c1, t1.c2
                   ->  Partition Iterator
                         Output: t1.c1, t1.c2
                         Iterations: 3
                         ->  Partitioned Seq Scan on public.t1
                               Output: t1.c1, t1.c2
                               Distribute Key: t1.c1
                               Selected Partitions:  1..3
    (23 rows)
    --删除表。
    gaussdb=# DROP TABLE t1;
    gaussdb=# DROP TABLE t2;
    gaussdb=# DROP TABLE t3;
    ```