# 参数化路径动态剪枝 当分区表的分区键所在列生成参数化路径（分区键所在列和其他表的列进行关联，且分区表作为内表生成NestLoop索引扫描）时，会触发参数化路径动态剪枝。参数化路径动态剪枝会在每次外表扫描完成后，驱动内表扫描时进行一次。 ![](https://support.huaweicloud.com/centralized-devg-v10-gaussdb/public_sys-resources/notice_3.0-zh-cn.png) - 参数化路径动态剪枝在多数情况下不支持子查询表达式（剪枝算子选择参数化路径且分区键上包含了一个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; ```