更新时间:2024-05-20 GMT+08:00

PBE动态剪枝

PBE动态剪枝支持范围如下所示:
  1. 支持分区级别:一级分区、二级分区
  2. 支持分区类型:范围分区、间隔分区、哈希分区、列表分区。
  3. 支持表达式类型:比较表达式(<,<=,=,>=,>)、逻辑表达式、数组表达式。
  4. 支持部分隐式类型转换和函数:对于类型可以相互转换的场景和immutable函数可以支持PBE动态剪枝
  • PBE动态剪枝支持表达式、隐式转换、immutable函数,stable函数,不支持子查询表达式和volatile函数。对于stable函数,如to_timestamp等类型转换函数,可能会受GUC参数变化,影响剪枝结果。为了保持性能优化,此情况可以通过analyze表重新生成gplan解决。
  • 由于PBE动态剪枝是基于generic plan的剪枝,所以判断语句是否能PBE动态剪枝时,需要设置参数 plan_cache_mode = 'force_generic_plan',排除custom plan的干扰。
  • 对于二级分区表指定一级分区的查询语句,不支持对二级分区键的过滤条件进一步剪枝。
  • PBE动态剪枝支持的典型场景具体示例如下:
    1. 比较表达式
      --创建分区表
      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                
      -----------------------------------------
       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 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)
    2. 逻辑表达式
      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                    
      -------------------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: PART
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: ((t1.c1 = $1) AND (t1.c2 = $2))
               Selected Partitions:  1 (pbe-pruning)
      (7 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))
               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)
               Selected Partitions:  1..3 (pbe-pruning)
      (7 rows)
    3. 数组表达式
      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]))
               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]))
               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                     
      ---------------------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: PART
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: (t1.c1 = ALL (ARRAY[$1, $2, $3]))
               Selected Partitions:  NONE (pbe-pruning)
      (7 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]))
               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]))
               Selected Partitions:  1 (pbe-pruning)
      (7 rows)
    4. 类型转换触发隐式转换
      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)
               Selected Partitions:  2 (pbe-pruning)
      (7 rows)
    5. 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))
               Selected Partitions:  1 (pbe-pruning)
      (7 rows)
  • PBE动态剪枝不支持的典型场景具体示例如下:
    1. 子查询表达式
      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)
               Selected Partitions:  1..3
      (7 rows)
    2. 类型转换无法直接触发隐式转换
      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)
               Selected Partitions:  1..3
      (7 rows)
    3. 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))
               Selected Partitions:  1..3
      (7 rows)
      
      --清理示例
      gaussdb=# DROP TABLE t1;