七月 4, 2024
摘要：在本教程中，您将了解查询执行期间的分区剪枝。
目录
PostgreSQL 11 通过在查询执行期间启用分区消除策略，以增强查询优化能力。它还添加了一个参数 enable_partition_pruning，用于控制执行器进行分区剪枝的能力，该功能默认处于打开状态。
何时会进行运行时剪枝？
在使用分区键的 WHERE 条件中带有常量时，第一次尝试剪枝发生在规划阶段，然后对于可变参数，可以在执行过程中的两个阶段进行运行时剪枝：在执行器启动或初始化时，以及在实际执行期间。
执行器初始化阶段
在某些情况下，例如在执行预备查询时，我们可以在初始化期间知道称为外部参数的参数值，从而避免初始化不需要的子计划。在这种情况下，EXPLAIN 输出中不会列出已消除的子计划，而只会给出已删除的子计划的数量。
prepare tprt_q1 (int, int, int) as
select * from tprt where a between $1 and $2 and b <= $3;
explain execute tprt_q1 (25000, 30000, 20000);
输出：
Append (cost=0.00..2007.54 rows=153 width=8)
Subplans Removed: 7
-> Seq Scan on tprt_a3_b1 (cost=0.00..222.98 rows=17 width=8)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on tprt_a3_b2 (cost=0.00..222.98 rows=17 width=8)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
EXPLAIN 输出表明已删除了 7 个子计划；这意味着不需要访问相应的分区，因此连初始化都没有必要。
实际执行阶段
与子查询和参数化的嵌套循环连接的情况一样；称为执行参数的参数值仅在实际执行时可知。在这种情况下，所有分区的子计划都已初始化，然后执行器会根据参数值，确定需要扫描哪些分区。如果有任何分区在整个运行阶段都不需要，则在 EXPLAIN ANALYZE 输出中会将其标记为 “never executed”。
下面是一个参数化嵌套循环示例，其中连接的是两个具有 5000 行的表。外部表的值介于 2001 到 7000 之间，分区表的值介于 1 到 5000 之间。分区表有 5 个分区，每个分区的容量为 1000 个值。
enable_partition_pruning = off 时，EXPLAIN ANALYZE 输出：
Nested Loop (actual rows=3000 loops=1)
-> Seq Scan on t1 (actual rows=5000 loops=1)
-> Append (actual rows=1 loops=5000)
-> Index Scan using tp_a1_idx on tp_a1 (actual rows=0 loops=5000)
Index Cond: (a = t1.col1)
-> Index Scan using tp_a2_idx on tp_a2 (actual rows=0 loops=5000)
Index Cond: (a = t1.col1)
-> Index Scan using tp_a3_idx on tp_a3 (actual rows=0 loops=5000)
Index Cond: (a = t1.col1)
-> Index Scan using tp_a4_idx on tp_a4 (actual rows=0 loops=5000)
Index Cond: (a = t1.col1)
-> Index Scan using tp_a5_idx on tp_a5 (actual rows=0 loops=5000)
Index Cond: (a = t1.col1)
Planning Time: 0.319 ms
Execution Time: 114.823 ms
enable_partition_pruning = on 时，EXPLAIN ANALYZE 输出：
Nested Loop (actual rows=3000 loops=1)
-> Seq Scan on t1 (actual rows=5000 loops=1)
-> Append (actual rows=1 loops=5000)
-> Index Scan using tp_a1_idx on tp_a1 (never executed)
Index Cond: (a = t1.col1)
-> Index Scan using tp_a2_idx on tp_a2 (never executed)
Index Cond: (a = t1.col1)
-> Index Scan using tp_a3_idx on tp_a3 (actual rows=1 loops=1000)
Index Cond: (a = t1.col1)
-> Index Scan using tp_a4_idx on tp_a4 (actual rows=1 loops=1000)
Index Cond: (a = t1.col1)
-> Index Scan using tp_a5_idx on tp_a5 (actual rows=1 loops=1000)
Index Cond: (a = t1.col1)
Planning Time: 0.384 ms
Execution Time: 36.572 ms
性能提升的原因
涉及分区表的查询会有明显的性能改进，但其改进程度由分区键参数决定，该参数控制可以跳过扫描的分区数。
考虑上面的嵌套循环连接情况，在禁用剪枝的情况下，将会扫描所有分区，以查找外部表 t1 中的 5000 个值中的每一个值（loops=5000）。启用剪枝后，仅会针对外部表中的每个值扫描相应的分区（loops=1000）。在两个分区中，根本没有执行扫描（never executed），因为外部表没有与这些分区（1-2000）的条目匹配的值。由于每个分区上的扫描次数大大减少，我们可以看到执行时间从 115 毫秒缩短到 37 毫秒，提升了 67%。
当外部表中的数据量翻倍到 10000 行（值为 2001 - 12000）时，行为是相似的，但在未剪枝的情况下，每个分区中的扫描次数是 10000 而不是 5000，但性能提升会更多，从 239 毫秒到 40 毫秒，为 83%。
内部原理
分区按它们可以存放的值的递增顺序进行内部排序和存储。最初，在版本 10 中，不需要的分区要通过规划器中繁琐的线性搜索来消除，但在版本 11 中，这已更新为对列表进行更快地二分搜索。
要对分区表执行扫描，会使用一个 Append 节点，每个叶子分区上的扫描都是其下的一个子计划。这些子计划会被构建索引，执行器通过此索引在内部访问它们。
为了帮助执行器在 Append 节点下选择正确的子计划，会使用一个具有相应子计划索引的分区映射表。规划器首先会创建此映射表，处理由它修剪的所有分区。当执行器检测到可以进行修剪时，它会获取可以满足给定参数的分区列表，并从映射表中找出相应的子计划索引。如果没有子计划索引，则表示分区已在上一阶段（规划器或执行器初始化阶段）中进行了修剪。
如果在执行器启动期间进行了修剪，因为未初始化被消除的分区，映射表会更新，并更改保留分区的子计划索引。这是必要的，以让映射表对执行器稍后要完成的修剪有效。