分区表静态剪枝

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

• 目前静态剪枝不支持子查询表达式。
• 对于二级分区表指定一级分区的查询语句，不支持对二级分区键的过滤条件进一步剪枝。
• 为了支持分区表剪枝，在计划生成时会将分区键上的过滤条件强制转换为分区键类型，和隐式类型转换规则存在差异，可能导致相同条件在分区键上转换报错，非分区键上无报错。

• 静态剪枝支持的典型场景具体示例如下：
  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)

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

     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;