分区表静态剪枝

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

• 静态剪枝只支持常量。部分非常量条件会在计划生成前进行预处理，比如参数条件生成custom plan，immutable函数和stable函数带常量入参，strict函数入参为NULL等，此时均会转为常量，可以走静态剪枝。
• 为了支持分区表剪枝，在计划生成时会将分区键上的过滤条件强制转换为分区键类型，该操作与隐式类型转换规则存在差异，可能导致相同条件在分区键上转换报错，非分区键无报错的情况。

• 静态剪枝支持的典型场景具体示例如下：
  • 比较表达式

    --创建分区表
    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
    -------------------------------------------------------
     [Parameterized]
     Partitioned Seq Scan on public.t1
       Output: c1, c2
       Filter: (t1.c1 = 1), (Expression Flatten Optimized)
       Selected Partitions:  1
    (5 rows)
    
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 < 1;
                          QUERY PLAN
    -------------------------------------------------------
     [Parameterized]
     Partitioned Seq Scan on public.t1
       Output: c1, c2
       Filter: (t1.c1 < 1), (Expression Flatten Optimized)
       Selected Partitions:  1
    (5 rows)
    
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 > 11;
                              QUERY PLAN
    --------------------------------------------------------------
     [Parameterized]
     Partition Iterator
       Output: c1, c2
       Iterations: 2
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: (t1.c1 > 11), (Expression Flatten Optimized)
             Selected Partitions:  2..3
    (8 rows)
    
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 is NULL;
     [Parameterized]
     Partitioned Seq Scan on public.t1
       Output: c1, c2
       Filter: (t1.c1 IS NULL), (Expression Flatten Optimized)
       Selected Partitions:  3
    (5 rows)

  • 逻辑表达式

    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = 1 AND c2 = 2;
    )
                                   QUERY PLAN
    -------------------------------------------------------------------------
     [Parameterized]
     Partitioned Seq Scan on public.t1
       Output: c1, c2
       Filter: ((t1.c1 = 1) AND (t1.c2 = 2)), (Expression Flatten Optimized)
       Selected Partitions:  1
    (5 rows)
    
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = 1 OR c1 = 2;
                                   QUERY PLAN
    ------------------------------------------------------------------------
     [Parameterized]
     Partitioned Seq Scan on public.t1
       Output: c1, c2
       Filter: ((t1.c1 = 1) OR (t1.c1 = 2)), (Expression Flatten Optimized)
       Selected Partitions:  1
    (5 rows)
    
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE NOT c1 = 1;
                   QUERY PLAN                
    -----------------------------------------
    [Parameterized]
    Partition Iterator
       Output: c1, c2
       Iterations: 3
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: (t1.c1 <> 1)
             Selected Partitions:  1..3
    (8 rows)

  • 数组表达式

    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 IN (1, 2, 3);
                                       QUERY PLAN
    --------------------------------------------------------------------------------
     [Parameterized]
     Partitioned Seq Scan on public.t1
       Output: c1, c2
       Filter: (t1.c1 = ANY ('{1,2,3}'::integer[])), (Expression Flatten Optimized)
       Selected Partitions:  1
    (5 rows)
    
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = ALL(ARRAY[1, 2, 3]);
                                          QUERY PLAN
    --------------------------------------------------------------------------------------
     [Parameterized]
     Partition Iterator
       Output: c1, c2
       Iterations: 0
       ->  Partitioned Seq Scan on public.t1
             Output: c1, c2
             Filter: (t1.c1 = ALL ('{1,2,3}'::integer[])), (Expression Flatten Optimized)
             Selected Partitions:  NONE
    (8 rows)
    
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = ANY(ARRAY[1, 2, 3]);
                                       QUERY PLAN
    --------------------------------------------------------------------------------
     [Parameterized]
     Partitioned Seq Scan on public.t1
       Output: c1, c2
       Filter: (t1.c1 = ANY ('{1,2,3}'::integer[])), (Expression Flatten Optimized)
       Selected Partitions:  1
    (5 rows)
    
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = SOME(ARRAY[1, 2, 3]);
                                       QUERY PLAN
    --------------------------------------------------------------------------------
     [Parameterized]
     Partitioned Seq Scan on public.t1
       Output: c1, c2
       Filter: (t1.c1 = ANY ('{1,2,3}'::integer[])), (Expression Flatten Optimized)
       Selected Partitions:  1
    (5 rows)

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

  gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 WHERE c1 = ALL(SELECT c2 FROM t1 WHERE c1 > 10);
                                         QUERY PLAN
  -----------------------------------------------------------------------------------------
   [Parameterized]
   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: 2
                         ->  Partitioned Seq Scan on public.t1
                               Output: public.t1.c2, (Expression Flatten Optimized)
                               Filter: (public.t1.c1 > 10), (Expression Flatten Optimized)
                               Selected Partitions:  2..3
  (18 rows)
  
  gaussdb=# DROP TABLE t1;