PBE动态剪枝

对于检索条件中分区键上带有参数的分区表查询语句，在优化器阶段只能判定“具备分区剪枝条件”，在执行启动完成绑参后，再进行动态剪枝。动态剪枝的触发行为与静态剪枝一致，只是触发阶段不同。

• 对于部分stable函数，如to_timestamp等类型转换函数，可能会受GUC参数变化，影响剪枝结果。为了保持性能优化，此情况可以通过analyze表重新生成gplan解决。
• 由于PBE动态剪枝是基于generic plan的剪枝，所以判断语句是否能PBE动态剪枝时，建议设置参数plan_cache_mode = 'force_generic_plan'，排除custom plan的干扰。
• 使用动态剪枝时，由于在生成计划阶段计划实际执行的分区尚未确定，不支持生成分类索引计划。

• 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=# SET plan_cache_mode = 'force_generic_plan';
    
    gaussdb=# PREPARE p1(int) AS SELECT * FROM t1 WHERE c1 = $1;
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p1(1);
                           QUERY PLAN
    --------------------------------------------------------
     Partitioned Seq Scan on public.t1
       Output: c1, c2
       Filter: (t1.c1 = $1), (Expression Flatten Optimized)
       Selected Partitions:  1 (pbe-pruning)
    (4 rows)
    
    gaussdb=# PREPARE p2(int) AS SELECT * FROM t1 WHERE c1 < $1;
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p2(1);
                   QUERY PLAN                
    -----------------------------------------
     Partition Iterator
       Output: c1, c2
       Iterations: PART
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: (t1.c1 < $1)
             Selected Partitions:  1 (pbe-pruning)
    (7 rows)
    
    gaussdb=# PREPARE p3(int) AS SELECT * FROM t1 WHERE c1 > $1;
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p3(1);
                   QUERY PLAN                
    -----------------------------------------
     Partition Iterator
       Output: c1, c2
       Iterations: PART
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: (t1.c1 > $1)
             Selected Partitions:  1..3 (pbe-pruning)
    (7 rows)

  • 逻辑表达式

    gaussdb=# PREPARE p5(INT, INT) AS SELECT * FROM t1 WHERE c1 = $1 AND c2 = $2;
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p5(1, 2);
                                    QUERY PLAN
    ---------------------------------------------------------------------------
     Partitioned Seq Scan on public.t1
       Output: c1, c2
       Filter: ((t1.c1 = $1) AND (t1.c2 = $2)), (Expression Flatten Optimized)
       Selected Partitions:  1 (pbe-pruning)
    (4 rows)
    
    gaussdb=# PREPARE p6(INT, INT) AS SELECT * FROM t1 WHERE c1 = $1 OR c2 = $2;
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p6(1, 2);
                                       QUERY PLAN
    --------------------------------------------------------------------------------
     Partition Iterator
       Output: c1, c2
       Iterations: PART
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: ((t1.c1 = $1) OR (t1.c2 = $2)), (Expression Flatten Optimized)
             Selected Partitions:  1..3 (pbe-pruning)
    (7 rows)
    gaussdb=# PREPARE p7(INT) AS SELECT * FROM t1 WHERE NOT c1 = $1;
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) execute p7(1);
                              QUERY PLAN
    ---------------------------------------------------------------
     Partition Iterator
       Output: c1, c2
       Iterations: PART
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: (t1.c1 <> $1), (Expression Flatten Optimized)
             Selected Partitions:  1..3 (pbe-pruning)
    (7 rows)

  • 数组表达式

    gaussdb=# PREPARE p8(INT, INT, INT) AS SELECT * FROM t1 WHERE c1 IN ($1, $2, $3);
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p8(1, 2, 3);
                        QUERY PLAN                     
    ---------------------------------------------------
     Partition Iterator
       Output: c1, c2
       Iterations: PART
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: (t1.c1 = ANY (ARRAY[$1, $2, $3])), (Expression Flatten Optimized)
             Selected Partitions:  1 (pbe-pruning)
    (7 rows)
    gaussdb=# PREPARE p9(INT, INT, INT) AS SELECT * FROM t1 WHERE c1 NOT IN ($1, $2, $3);
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p9(1, 2, 3);
                                         QUERY PLAN
    ------------------------------------------------------------------------------------
     Partition Iterator
       Output: c1, c2
       Iterations: PART
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: (t1.c1 <> ALL (ARRAY[$1, $2, $3])), (Expression Flatten Optimized)
             Selected Partitions:  1..3 (pbe-pruning)
    (7 rows)
    gaussdb=# PREPARE p10(INT, INT, INT) AS SELECT * FROM t1 WHERE c1 = ALL(ARRAY[$1, $2, $3]);
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p10(1, 2, 3);
                        QUERY PLAN                     
    ---------------------------------------------------
                                     QUERY PLAN
    -----------------------------------------------------------------------------
     Partitioned Seq Scan on public.t1
       Output: c1, c2
       Filter: (t1.c1 = ALL (ARRAY[$1, $2, $3])), (Expression Flatten Optimized)
       Selected Partitions:  NONE (pbe-pruning)
    (4 rows)
    gaussdb=# PREPARE p11(INT, INT, INT) AS SELECT * FROM t1 WHERE c1 = ANY(ARRAY[$1, $2, $3]);
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p11(1, 2, 3);
                        QUERY PLAN                     
    ---------------------------------------------------
     Partition Iterator
       Output: c1, c2
       Iterations: PART
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: (t1.c1 = ANY (ARRAY[$1, $2, $3])), (Expression Flatten Optimized)
             Selected Partitions:  1 (pbe-pruning)
    (7 rows)
    gaussdb=# PREPARE p12(INT, INT, INT) AS SELECT * FROM t1 WHERE c1 = SOME(ARRAY[$1, $2, $3]);
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p12(1, 2, 3);
                        QUERY PLAN                     
    ---------------------------------------------------
     Partition Iterator
       Output: c1, c2
       Iterations: PART
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: (t1.c1 = ANY (ARRAY[$1, $2, $3])), (Expression Flatten Optimized)
             Selected Partitions:  1 (pbe-pruning)
    (7 rows)

  • 类型转换触发隐式转换

    gaussdb=# SET plan_cache_mode = 'force_generic_plan';
    gaussdb=# PREPARE p13(TEXT) AS SELECT * FROM t1 WHERE c1 = $1;
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p13('12');
                                   QUERY PLAN
    ------------------------------------------------------------------------
     Partition Iterator
       Output: c1, c2
       Iterations: PART
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: (t1.c1 = ($1)::bigint), (Expression Flatten Optimized)
             Selected Partitions:  2 (pbe-pruning)
    (7 rows)

  • immutable函数

    gaussdb=# PREPARE p14(TEXT) AS SELECT * FROM t1 WHERE c1 = LENGTHB($1);
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p14('hello');
                        QUERY PLAN
    --------------------------------------------------
     Partition Iterator
       Output: c1, c2
       Iterations: PART
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: (t1.c1 = lengthb($1)), (Expression Flatten Optimized)
             Selected Partitions:  1 (pbe-pruning)
    (7 rows)

• PBE动态剪枝不支持的典型场景具体示例如下：
  • 子查询表达式

    gaussdb=# PREPARE p15(INT) AS SELECT * FROM t1 WHERE c1 = ALL(SELECT c2 FROM t1 WHERE c1 > $1);
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p15(1);
                                           QUERY PLAN
    -----------------------------------------------------------------------------------------
     Partition Iterator
       Output: public.t1.c1, public.t1.c2
       Iterations: 3
       ->  Partitioned Seq Scan on public.t1
             Output: public.t1.c1, public.t1.c2
             Filter: (SubPlan 1), (Expression Flatten Optimized)
             Selected Partitions:  1..3
             SubPlan 1
               ->  Materialize
                     Output: public.t1.c2
                     ->  Partition Iterator
                           Output: public.t1.c2
                           Iterations: PART
                           ->  Partitioned Seq Scan on public.t1
                                 Output: public.t1.c2, (Expression Flatten Optimized)
                                 Filter: (public.t1.c1 > $1), (Expression Flatten Optimized)
                                 Selected Partitions:  1..3 (pbe-pruning)
    (17 rows)

  • 类型转换无法直接触发隐式转换

    gaussdb=# PREPARE p16(name) AS SELECT * FROM t1 WHERE c1 = $1;
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p16('12');
                      QUERY PLAN
    ----------------------------------------------
     Partition Iterator
       Output: c1, c2
       Iterations: 3
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: ((t1.c1)::text = ($1)::text), (Expression Flatten Optimized)
             Selected Partitions:  1..3
    (7 rows)

  • stable/volatile函数

    gaussdb=# CREATE SEQUENCE seq;
    gaussdb=# PREPARE p17(TEXT) AS SELECT * FROM t1 WHERE c1 = currval($1);--volatile函数不支持剪枝
    PREPARE
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p17('seq');
                              QUERY PLAN
    --------------------------------------------------------------
     Partition Iterator
       Output: c1, c2
       Iterations: 3
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: ((t1.c1)::numeric = currval(($1)::regclass)), (Expression Flatten Optimized)
             Selected Partitions:  1..3
    (7 rows)
    
    gaussdb=# DROP TABLE t1;