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

参数化路径动态剪枝

参数化路径动态剪枝支持范围如下所示:
  1. 支持分区级别:一级分区、二级分区
  2. 支持分区类型:范围分区、间隔分区、哈希分区、列表分区。
  3. 支持算子类型:indexscan、indexonlyscan、bitmapscan。
  4. 支持表达式类型:比较表达式(<,<=,=,>=,>)、逻辑表达式。

参数化路径动态剪枝不支持子查询表达式,不支持stable和volatile函数,不支持跨QueryBlock参数化路径,不支持BitmapOr、BitmapAnd算子。

  • 参数化路径动态剪枝支持的典型场景具体示例如下:
    1. 比较表达式
      gaussdb=#
      --创建分区表和索引
      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)
      );
      CREATE TABLE t2 (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)
      );
      CREATE INDEX t1_c1 ON t1(c1) LOCAL;
      CREATE INDEX t2_c1 ON t2(c1) LOCAL;
      CREATE INDEX t1_c2 ON t1(c2) LOCAL;
      CREATE INDEX t2_c2 ON t2(c2) LOCAL;
      
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t2 JOIN t1 ON t1.c1 = t2.c2;
                               QUERY PLAN                          
      -------------------------------------------------------------
       Hash Join
         Output: t2.c1, t2.c2, t1.c1, t1.c2
         Hash Cond: (t2.c2 = t1.c1)
         ->  Partition Iterator
               Output: t2.c1, t2.c2
               Iterations: 3
               ->  Partitioned Seq Scan on public.t2
                     Output: t2.c1, t2.c2
                     Selected Partitions:  1..3
         ->  Hash
               Output: t1.c1, t1.c2
               ->  Partition Iterator
                     Output: t1.c1, t1.c2
                     Iterations: 3
                     ->  Partitioned Seq Scan on public.t1
                           Output: t1.c1, t1.c2
                           Selected Partitions:  1..3
      (17 rows)
      
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t2 JOIN t1 ON t1.c1 < t2.c2;
                               QUERY PLAN                          
      -------------------------------------------------------------
       Nested Loop
         Output: t2.c1, t2.c2, t1.c1, t1.c2
         ->  Partition Iterator
               Output: t2.c1, t2.c2
               Iterations: 3
               ->  Partitioned Seq Scan on public.t2
                     Output: t2.c1, t2.c2
                     Selected Partitions:  1..3
         ->  Partition Iterator
               Output: t1.c1, t1.c2
               Iterations: PART
               ->  Partitioned Index Scan using t2_c1 on public.t1
                     Output: t1.c1, t1.c2
                     Index Cond: (t1.c1 < t2.c2)
                     Selected Partitions:  1..3 (ppi-pruning)
      (15 rows)
      
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t2 JOIN t1 ON t1.c1 > t2.c2;
                               QUERY PLAN                          
      -------------------------------------------------------------
       Nested Loop
         Output: t2.c1, t2.c2, t1.c1, t1.c2
         ->  Partition Iterator
               Output: t2.c1, t2.c2
               Iterations: 3
               ->  Partitioned Seq Scan on public.t2
                     Output: t2.c1, t2.c2
                     Selected Partitions:  1..3
         ->  Partition Iterator
               Output: t1.c1, t1.c2
               Iterations: PART
               ->  Partitioned Index Scan using t2_c1 on public.t1
                     Output: t1.c1, t1.c2
                     Index Cond: (t1.c1 > t2.c2)
                     Selected Partitions:  1..3 (ppi-pruning)
      (15 rows)
    2. 逻辑表达式
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t2 JOIN t1 ON t1.c1 = t2.c2 AND t1.c2 = 2;
                               QUERY PLAN                          
      -------------------------------------------------------------
       Hash Join
         Output: t2.c1, t2.c2, t1.c1, t1.c2
         Hash Cond: (t2.c2 = t1.c1)
         ->  Partition Iterator
               Output: t2.c1, t2.c2
               Iterations: 3
               ->  Partitioned Seq Scan on public.t2
                     Output: t2.c1, t2.c2
                     Selected Partitions:  1..3
         ->  Hash
               Output: t1.c1, t1.c2
               ->  Partition Iterator
                     Output: t1.c1, t1.c2
                     Iterations: 3
                     ->  Partitioned Bitmap Heap Scan on public.t1
                           Output: t1.c1, t1.c2
                           Recheck Cond: (t1.c2 = 2)
                           Selected Partitions:  1..3
                           ->  Partitioned Bitmap Index Scan on t1_c2
                                 Index Cond: (t1.c2 = 2)
      (20 rows)
  • 参数化路径动态剪枝不支持的典型场景具体示例如下:
    1. BitmapOr/BitmapAnd算子
      gaussdb=# set enable_seqscan=off;
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t2 JOIN t1 ON t1.c1 = t2.c2 OR t1.c1 = 2;
                                 QUERY PLAN                            
      -----------------------------------------------------------------
       Nested Loop
         Output: t2.c1, t2.c2, t1.c1, t1.c2
         ->  Partition Iterator
               Output: t2.c1, t2.c2
               Iterations: 3
               ->  Partitioned Seq Scan on public.t2
                     Output: t2.c1, t2.c2
                     Selected Partitions:  1..3
         ->  Partition Iterator
               Output: t1.c1, t1.c2
               Iterations: 3
               ->  Partitioned Bitmap Heap Scan on public.t1
                     Output: t1.c1, t1.c2
                     Recheck Cond: ((t1.c1 = t2.c2) OR (t1.c1 = 2))
                     Selected Partitions:  1..3
                     ->  BitmapOr
                           ->  Partitioned Bitmap Index Scan on t1_c1
                                 Index Cond: (t1.c1 = t2.c2)
                           ->  Partitioned Bitmap Index Scan on t1_c1
                                 Index Cond: (t1.c1 = 2)
      (20 rows)
    2. 隐式转换
      gaussdb=# CREATE TABLE t3(c1 TEXT, c2 INT);
      CREATE TABLE
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 JOIN t3 ON t1.c1 = t3.c1;
                               QUERY PLAN                          
      -------------------------------------------------------------
       Nested Loop
         Output: t1.c1, t1.c2, t3.c1, t3.c2
         ->  Seq Scan on public.t3
               Output: t3.c1, t3.c2
         ->  Partition Iterator
               Output: t1.c1, t1.c2
               Iterations: 3
               ->  Partitioned Index Scan using t1_c1 on public.t1
                     Output: t1.c1, t1.c2
                     Index Cond: (t1.c1 = (t3.c1)::bigint)
                     Selected Partitions:  1..3
      (11 rows)
    3. 函数
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 JOIN t3 ON t1.c1 = LENGTHB(t3.c1);
                               QUERY PLAN                          
      -------------------------------------------------------------
       Nested Loop
         Output: t1.c1, t1.c2, t3.c1, t3.c2
         ->  Seq Scan on public.t3
               Output: t3.c1, t3.c2
         ->  Partition Iterator
               Output: t1.c1, t1.c2
               Iterations: 3
               ->  Partitioned Index Scan using t1_c1 on public.t1
                     Output: t1.c1, t1.c2
                     Index Cond: (t1.c1 = lengthb(t3.c1))
                     Selected Partitions:  1..3
      (11 rows)
      
      gaussdb=# DROP TABLE t1;
      gaussdb=# DROP TABLE t2;
      gaussdb=# DROP TABLE t3;