# PBE动态剪枝
对于检索条件中分区键上带有参数的分区表查询语句，在优化器阶段只能判定"具备分区剪枝条件"，在执行启动完成绑参后，再进行动态剪枝。动态剪枝的触发行为与静态剪枝一致，只是触发阶段不同。
![](https://support.huaweicloud.com/distributed-devg-v10-gaussdb/public_sys-resources/notice_3.0-zh-cn.png)
- 对于部分stable函数，如to_timestamp等类型转换函数，其剪枝结果可能会因GUC参数的变动而受到影响。为了保持性能优化效果，可以通过对表执行analyze操作重新生成gplan来解决。
- 由于PBE动态剪枝是基于gplan来进行的，在判断语句是否能进行PBE动态剪枝时，建议将参数plan_cache_mode设置为'force_generic_plan'，排除cplan的干扰。

- 使用动态剪枝时，由于在生成计划阶段计划实际执行的分区尚未确定，不支持生成分类索引计划。
 
- PBE动态剪枝支持的典型场景具体示例如下：
  - 非透明多写特性下，比较表达式。
    ```
    --创建分区表
    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=# PREPARE p1(int) AS SELECT * FROM t1 WHERE c1 = $1;
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p1(1);
                             QUERY PLAN                         
    ------------------------------------------------------------
     Data Node Scan
       Output: t1.c1, t1.c2
       Node/s: datanode1
       Node expr: $1
       Remote query: SELECT c1, c2 FROM public.t1 WHERE c1 = $1
     Remote SQL: SELECT c1, c2 FROM public.t1 WHERE c1 = $1
     Datanode Name: datanode1
       Partitioned Seq Scan on public.t1
         Output: c1, c2
         Filter: (t1.c1 = $1)
         Selected Partitions:  1 (pbe-pruning)
    (13 rows)
    ```
    
  
  - 非透明多写特性下，逻辑表达式。
    ```
    gaussdb=# PREPARE p2(INT, INT) AS SELECT * FROM t1 WHERE c1 = $1 AND c2 = $2;
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p2(1, 2);
                                   QUERY PLAN                               
    ------------------------------------------------------------------------
     Data Node Scan
       Output: t1.c1, t1.c2
       Node/s: datanode1
       Node expr: $1
       Remote query: SELECT c1, c2 FROM public.t1 WHERE c1 = $1 AND c2 = $2
     Remote SQL: SELECT c1, c2 FROM public.t1 WHERE c1 = $1 AND c2 = $2
     Datanode Name: datanode1
       Partitioned Seq Scan on public.t1
         Output: c1, c2
         Filter: ((t1.c1 = $1) AND (t1.c2 = $2))
         Selected Partitions:  1 (pbe-pruning)
    (13 rows)
    ```
    
  
  - 非透明多写特性下，类型转换触发隐式转换。
    ```
    gaussdb=# set plan_cache_mode = 'force_generic_plan';
    gaussdb=# PREPARE p3(TEXT) AS SELECT * FROM t1 WHERE c1 = $1;
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p3('12');
                                 QUERY PLAN                             
    --------------------------------------------------------------------
     Data Node Scan
       Output: t1.c1, t1.c2
       Node/s: datanode1
       Node expr: $1
       Remote query: SELECT c1, c2 FROM public.t1 WHERE c1 = $1::bigint
     Remote SQL: SELECT c1, c2 FROM public.t1 WHERE c1 = $1::bigint
     Datanode Name: datanode1
       Partition Iterator
         Output: c1, c2
         Iterations: PART
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: (t1.c1 = ($1)::bigint)
               Selected Partitions:  2 (pbe-pruning)
    (16 rows)
    ```
    
   
- PBE动态剪枝不支持的典型场景具体示例如下：
  - 非透明多写特性下，子查询表达式。
    ```
    gaussdb=# PREPARE p4(INT) AS SELECT * FROM t1 WHERE c1 = ALL(SELECT c2 FROM t1 WHERE c1 > $1);
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p4(1);
                                   QUERY PLAN                                
    -------------------------------------------------------------------------
     Streaming (type: GATHER)
       Output: public.t1.c1, public.t1.c2
       Node/s: All datanodes
       ->  Partition Iterator
             Output: public.t1.c1, public.t1.c2
             Iterations: 3
             ->  Partitioned Seq Scan on public.t1
                   Output: public.t1.c1, public.t1.c2
                   Distribute Key: public.t1.c1
                   Filter: (SubPlan 1)
                   Selected Partitions:  1..3
                   SubPlan 1
                     ->  Materialize
                           Output: public.t1.c2
                           ->  Streaming(type: BROADCAST)
                                 Output: public.t1.c2
                                 Spawn on: All datanodes
                                 Consumer Nodes: All datanodes
                                 ->  Partition Iterator
                                       Output: public.t1.c2
                                       Iterations: PART
                                       ->  Partitioned Seq Scan on public.t1
                                             Output: public.t1.c2
                                             Distribute Key: public.t1.c1
                                             Filter: (public.t1.c1 > 1)
                                             Selected Partitions:  1..3
    (26 rows)
    ```
    
  
  - 非透明多写特性下，类型转换无法直接触发隐式转换。
    ```
    gaussdb=# PREPARE p5(name) AS SELECT * FROM t1 WHERE c1 = $1;
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p5('12');
                                    QUERY PLAN                                
    --------------------------------------------------------------------------
     Data Node Scan
       Output: t1.c1, t1.c2
       Node/s: All datanodes
       Remote query: SELECT c1, c2 FROM public.t1 WHERE c1::text = '12'::text
     Remote SQL: SELECT c1, c2 FROM public.t1 WHERE c1::text = '12'::text
     Datanode Name: datanode1
       Partition Iterator
         Output: c1, c2
         Iterations: 3
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: ((t1.c1)::text = '12'::text)
               Selected Partitions:  1..3
    (15 rows)
    ```
    
  
  - 非透明多写特性下，stable/volatile函数。
    ```
    gaussdb=# CREATE SEQUENCE seq;
    gaussdb=# PREPARE p6(TEXT) AS SELECT * FROM t1 WHERE c1 = currval($1);--volatile函数不支持剪枝。
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p6('seq');
                                     QUERY PLAN                                 
    ----------------------------------------------------------------------------
     Data Node Scan
       Output: t1.c1, t1.c2
       Node/s: All datanodes
       Remote query: SELECT c1, c2 FROM ONLY public.t1 WHERE true
       Coordinator quals: ((t1.c1)::numeric = currval(('seq'::text)::regclass))
     Remote SQL: SELECT c1, c2 FROM ONLY public.t1 WHERE true
     Datanode Name: datanode1
       Partition Iterator
         Output: c1, c2
         Iterations: 3
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Selected Partitions:  1..3
    (15 rows)
    --清理示例环境。
    gaussdb=# DROP TABLE t1;
    ```