参数化路径动态剪枝

当分区表的分区键所在列生成参数化路径（分区键所在列和其他表的列进行关联，且分区表作为内表生成NestLoop索引扫描）时，会触发参数化路径动态剪枝。参数化路径动态剪枝会在每次外表扫描完成后，驱动内表扫描时进行一次。

• 参数化路径动态剪枝在多数情况下不支持子查询表达式（剪枝算子选择参数化路径且分区键上包含了一个InitPlan子查询条件除外），不支持stable和volatile函数，不支持跨QueryBlock参数化路径，不支持BitmapOr、BitmapAnd算子。
• 使用EXPLAIN显示计划时，参数化路径剪枝计划中显示的分区数只代表“可能访问的最大总分区数”，实际访问分区数可能会小很多。
• 使用EXPLAIN ANALYZE显示计划时，分区表参数化路径剪枝计划中会出现“ppi-pruning, actual scanned:XXX”信息，ppi-pruning代表触发了参数化路径剪枝，每次参数化路径剪枝结果可能不相同，所以actual scanned只能代表最后一次参数化路径剪枝结果。

• 参数化路径动态剪枝支持的典型场景具体示例如下：
  • 比较表达式

    --创建分区表和索引
    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=# 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)
    );
    gaussdb=# CREATE INDEX t1_c1 ON t1(c1) LOCAL;
    gaussdb=# CREATE INDEX t2_c1 ON t2(c1) LOCAL;
    gaussdb=# CREATE INDEX t1_c2 ON t1(c2) LOCAL;
    gaussdb=# CREATE INDEX t2_c2 ON t2(c2) LOCAL;
    
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT /*+ nestloop(t1 t2) */* FROM t2 JOIN t1 ON t1.c1 = t2.c2;
                             QUERY PLAN                          
    -------------------------------------------------------------
     [Parameterized]
     Nested Loop
       Output: t2.c1, t2.c2, t1.c1, t1.c2, (Expression Flatten Optimized)
       ->  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 t1_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                          
    -------------------------------------------------------------
     [Parameterized]
     Nested Loop
       Output: t2.c1, t2.c2, t1.c1, t1.c2, (Expression Flatten Optimized)
       ->  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                          
    -------------------------------------------------------------
     [Parameterized]
     Nested Loop
       Output: t2.c1, t2.c2, t1.c1, t1.c2, (Expression Flatten Optimized)
       ->  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 AND t1.c2 = 2;
                                   QUERY PLAN
    -------------------------------------------------------------------------
     [Parameterized]
     Nested Loop
       Output: t2.c1, t2.c2, t1.c1, t1.c2, (Expression Flatten Optimized)
       ->  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), (Expression Flatten Optimized)
                   Selected Partitions:  1..3
                   ->  Partitioned Bitmap Index Scan on t1_c2
                         Index Cond: (t1.c2 = 2)
       ->  Partition Iterator
             Output: t2.c1, t2.c2
             Iterations: 3
             ->  Partitioned Index Scan using t2_c2 on public.t2
                   Output: t2.c1, t2.c2
                   Index Cond: (t2.c2 = t1.c1)
                   Selected Partitions:  1..3
    (19 rows)

• 参数化路径动态剪枝不支持的典型场景具体示例如下：
  • BitmapOr/BitmapAnd算子

    gaussdb=# SET enable_seqscan=off;
    gaussdb=# SET enable_indexscan = off;
    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t2 JOIN t1 ON t1.c1 = t2.c2 OR t1.c1 = 2;
                               QUERY PLAN                            
    -----------------------------------------------------------------
     [Parameterized]
     Nested Loop
       Output: t2.c1, t2.c2, t1.c1, t1.c2, (Expression Flatten Optimized)
       ->  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)), (Expression Flatten Optimized)
                   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)

  • 隐式转换

    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                          
    -------------------------------------------------------------
     [Parameterized]
     Nested Loop
       Output: t1.c1, t1.c2, t3.c1, t3.c2, (Expression Flatten Optimized)
       ->  Seq Scan on public.t3
             Output: t3.c1, t3.c2
       ->  Partition Iterator
             Output: t1.c1, t1.c2
             Iterations: PART
             ->  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 (ppi-pruning)
    (11 rows)

  • 函数

    gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM t1 JOIN t3 ON t1.c1 = LENGTHB(t3.c1);
                             QUERY PLAN                          
    -------------------------------------------------------------
     [Parameterized]
     Nested Loop
       Output: t1.c1, t1.c2, t3.c1, t3.c2, (Expression Flatten Optimized)
       ->  Seq Scan on public.t3
             Output: t3.c1, t3.c2
       ->  Partition Iterator
             Output: t1.c1, t1.c2
             Iterations: PART
             ->  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 (ppi-pruning)
    (11 rows)
    
    gaussdb=# DROP TABLE t1;
    gaussdb=# DROP TABLE t2;
    gaussdb=# DROP TABLE t3;