更新时间:2024-06-07 GMT+08:00

分区表静态剪枝

对于检索条件中分区键上带有常数的分区表查询语句,在优化器阶段将对indexscan、bitmap indexscan、indexonlyscan等算子中包含的检索条件作为剪枝条件,完成分区的筛选。算子包含的检索条件中需要至少包含一个分区键字段,对于含有多个分区键的分区表,包含任意分区键子集即可。

静态剪枝支持范围如下所示:
  1. 支持分区级别:一级分区、二级分区。
  2. 支持分区类型:范围分区、间隔分区、哈希分区、列表分区。
  3. 支持表达式类型:比较表达式(<,<=,=,>=,>)、逻辑表达式、数组表达式。

目前静态剪枝不支持子查询表达式。

为了支持分区表剪枝,在计划生成时会将分区键上的过滤条件强制转换为分区键类型,该操作与隐式类型转换规则存在差异,可能导致相同条件在分区键上转换报错,非分区键无报错的情况。

  • 静态剪枝支持的典型场景具体示例如下:
    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=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = 1;
                     QUERY PLAN                
      -----------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: 1
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: (t1.c1 = 1)
               Selected Partitions:  1
      (7 rows)
      
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 < 1;
                     QUERY PLAN                
      -----------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: 1
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: (t1.c1 < 1)
               Selected Partitions:  1
      (7 rows)
      
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 > 11;
                     QUERY PLAN                
      -----------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: 2
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: (t1.c1 > 11)
               Selected Partitions:  2..3
      (7 rows)
      
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 is NULL;
                     QUERY PLAN                
      -----------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: 1
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: (t1.c1 IS NULL)
               Selected Partitions:  3
      (7 rows)
    2. 逻辑表达式
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = 1 AND c2 = 2;
                        QUERY PLAN                   
      -----------------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: 1
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: ((t1.c1 = 1) AND (t1.c2 = 2))
               Selected Partitions:  1
      (7 rows)
      
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = 1 OR c1 = 2;
                        QUERY PLAN                  
      ----------------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: 1
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: ((t1.c1 = 1) OR (t1.c1 = 2))
               Selected Partitions:  1
      (7 rows)
      
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE NOT c1 = 1;
                     QUERY PLAN                
      -----------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: 3
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: (t1.c1 <> 1)
               Selected Partitions:  1..3
      (7 rows)
    3. 数组表达式
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 IN (1, 2, 3);
                            QUERY PLAN                      
      ------------------------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: 1
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: (t1.c1 = ANY ('{1,2,3}'::integer[]))
               Selected Partitions:  1
      (7 rows)
      
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = ALL(ARRAY[1, 2, 3]);
                            QUERY PLAN                      
      ------------------------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: 0
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: (t1.c1 = ALL ('{1,2,3}'::integer[]))
               Selected Partitions:  NONE
      (7 rows)
      
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = ANY(ARRAY[1, 2, 3]);
                            QUERY PLAN                      
      ------------------------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: 1
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: (t1.c1 = ANY ('{1,2,3}'::integer[]))
               Selected Partitions:  1
      (7 rows)
      
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = SOME(ARRAY[1, 2, 3]);
                            QUERY PLAN                      
      ------------------------------------------------------
       Partition Iterator
         Output: c1, c2
         Iterations: 1
         ->  Partitioned Seq Scan on public.t1
               Output: c1, c2
               Filter: (t1.c1 = ANY ('{1,2,3}'::integer[]))
               Selected Partitions:  1
      (7 rows)
  • 静态剪枝不支持的典型场景具体示例如下:
    子查询表达式
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = ALL(SELECT c2 FROM t1 WHERE c1 > 10);
                             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)
    
    gaussdb=# DROP TABLE t1;