PostgreSQL 18 preview - 优化分区表结果估算和Append扫描策略
digoal约 7 分钟PostgreSQL 18 preview - 优化分区表结果估算和Append扫描策略
PostgreSQL 18 这个提交旨在改进 PostgreSQL 在处理分区表时，Append 节点选择最优执行计划的策略，特别是关于索引扫描和参数化嵌套循环的使用。 简单来说，它让 PostgreSQL 在决定如何扫描分区表时，更聪明地利用 tuple_fraction (元组比例) 这个信息。
例如, 在返回结果较少时, 更积极的使用index scan, nestloop join等.
+--
+-- Test Append's fractional paths
+--
+CREATE INDEX pht1_c_idx ON pht1(c);
+-- SeqScan might be the best choice if we need one single tuple
+EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1;
+ QUERY PLAN
+--------------------------------------------------
+ Limit
+ -> Append
+ -> Nested Loop
+ Join Filter: (p1_1.c = p2_1.c)
+ -> Seq Scan on pht1_p1 p1_1
+ -> Materialize
+ -> Seq Scan on pht1_p1 p2_1
+ -> Nested Loop
+ Join Filter: (p1_2.c = p2_2.c)
+ -> Seq Scan on pht1_p2 p1_2
+ -> Materialize
+ -> Seq Scan on pht1_p2 p2_2
+ -> Nested Loop
+ Join Filter: (p1_3.c = p2_3.c)
+ -> Seq Scan on pht1_p3 p1_3
+ -> Materialize
+ -> Seq Scan on pht1_p3 p2_3
+(17 rows)
+
+-- Increase number of tuples requested and an IndexScan will be chosen
+EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
+ QUERY PLAN
+------------------------------------------------------------------------
+ Limit
+ -> Append
+ -> Nested Loop
+ -> Seq Scan on pht1_p1 p1_1
+ -> Memoize
+ Cache Key: p1_1.c
+ Cache Mode: logical
+ -> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
+ Index Cond: (c = p1_1.c)
+ -> Nested Loop
+ -> Seq Scan on pht1_p2 p1_2
+ -> Memoize
+ Cache Key: p1_2.c
+ Cache Mode: logical
+ -> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
+ Index Cond: (c = p1_2.c)
+ -> Nested Loop
+ -> Seq Scan on pht1_p3 p1_3
+ -> Memoize
+ Cache Key: p1_3.c
+ Cache Mode: logical
+ -> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
+ Index Cond: (c = p1_3.c)
+(23 rows)
+
+-- If almost all the data should be fetched - prefer SeqScan
+EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1000;
+ QUERY PLAN
+--------------------------------------------------
+ Limit
+ -> Append
+ -> Hash Join
+ Hash Cond: (p1_1.c = p2_1.c)
+ -> Seq Scan on pht1_p1 p1_1
+ -> Hash
+ -> Seq Scan on pht1_p1 p2_1
+ -> Hash Join
+ Hash Cond: (p1_2.c = p2_2.c)
+ -> Seq Scan on pht1_p2 p1_2
+ -> Hash
+ -> Seq Scan on pht1_p2 p2_2
+ -> Hash Join
+ Hash Cond: (p1_3.c = p2_3.c)
+ -> Seq Scan on pht1_p3 p1_3
+ -> Hash
+ -> Seq Scan on pht1_p3 p2_3
+(17 rows)
+
+SET max_parallel_workers_per_gather = 1;
+SET debug_parallel_query = on;
+-- Partial paths should also be smart enough to employ limits
+EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Gather
+ Workers Planned: 1
+ Single Copy: true
+ -> Limit
+ -> Append
+ -> Nested Loop
+ -> Seq Scan on pht1_p1 p1_1
+ -> Memoize
+ Cache Key: p1_1.c
+ Cache Mode: logical
+ -> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
+ Index Cond: (c = p1_1.c)
+ -> Nested Loop
+ -> Seq Scan on pht1_p2 p1_2
+ -> Memoize
+ Cache Key: p1_2.c
+ Cache Mode: logical
+ -> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
+ Index Cond: (c = p1_2.c)
+ -> Nested Loop
+ -> Seq Scan on pht1_p3 p1_3
+ -> Memoize
+ Cache Key: p1_3.c
+ Cache Mode: logical
+ -> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
+ Index Cond: (c = p1_3.c)
+(26 rows)
+
+RESET debug_parallel_query;
+-- Remove indexes from the partitioned table and its partitions
+DROP INDEX pht1_c_idx CASCADE;
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fae535da0ac2a8d0bb279cc66d62b0dcc4b5409b
Teach Append to consider tuple_fraction when accumulating subpaths.
author Alexander Korotkov <[email protected]>
Mon, 10 Mar 2025 11:38:39 +0000 (13:38 +0200)
committer Alexander Korotkov <[email protected]>
Mon, 10 Mar 2025 11:38:39 +0000 (13:38 +0200)
commit fae535da0ac2a8d0bb279cc66d62b0dcc4b5409b
tree 8040d2e6c2fd5e6d4a4497b8cdcbdeea9b8f4ea4 tree
parent b83e8a2ca2eb381ea0a48e5b2d4e4cdb74febc45 commit | diff
Teach Append to consider tuple_fraction when accumulating subpaths. This change is dedicated to more active usage of IndexScan and parameterized
NestLoop paths in partitioned cases under an Append node, as it already works
with plain tables. As newly added regression tests demonstrate, it should
provide more smartness to the partitionwise technique.
With an indication of how many tuples are needed, it may be more meaningful
to use the 'fractional branch' subpaths of the Append path list, which are
more optimal for this specific number of tuples. Planning on a higher level,
if the optimizer needs all the tuples, it will choose non-fractional paths.
In the case when, during execution, Append needs to return fewer tuples than
declared by tuple_fraction, it would not be harmful to use the 'intermediate'
variant of paths. However, it will earn a considerable profit if a sensible
set of tuples is selected.
The change of the existing regression test demonstrates the positive outcome
of this feature: instead of scanning the whole table, the optimizer prefers
to use a parameterized scan, being aware of the only single tuple the join
has to produce to perform the query.
Discussion: https://www.postgresql.org/message-id/flat/CAN-LCVPxnWB39CUBTgOQ9O7Dd8DrA_tpT1EY3LNVnUuvAX1NjA%40mail.gmail.com
Author: Nikita Malakhov <[email protected]>
Author: Andrei Lepikhov <[email protected]>
Reviewed-by: Andy Fan <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
核心问题：
在处理分区表时，Append 节点负责将来自各个分区的数据合并在一起。 之前的版本在决定如何扫描每个分区时，可能不够智能，没有充分利用 tuple_fraction 这个信息。 tuple_fraction 指示了查询只需要返回多少比例的元组。 如果查询只需要少量元组，那么扫描整个分区表可能效率不高。
补丁的解决方案：
这个补丁让 Append 节点在累积子路径（即扫描每个分区的不同方式）时，考虑 tuple_fraction。 这意味着：
更积极地使用索引扫描和参数化嵌套循环： 如果 tuple_fraction指示只需要少量元组，那么优化器会更倾向于使用索引扫描和参数化嵌套循环。 这些方法可以更快地找到需要的元组，而不需要扫描整个分区。选择“分数分支”子路径： Append节点可以有多个子路径，每个子路径代表一种扫描分区的方式。 有些子路径是为返回所有元组设计的（“非分数分支”），而有些子路径是为返回少量元组设计的（“分数分支”）。 这个补丁让优化器更倾向于选择“分数分支”子路径，如果tuple_fraction指示只需要少量元组。平衡考虑： 如果优化器需要所有元组，它仍然会选择“非分数分支”路径。 即使在执行过程中， Append节点需要返回的元组数量少于tuple_fraction声明的数量，使用“中间”变体的路径也不会造成损害。 但是，如果选择了合理的元组集合，则会获得可观的利润。
为什么这个补丁重要：
提高分区表的查询性能： 通过更智能地选择扫描分区的方式，这个补丁可以显著提高分区表的查询性能，特别是当查询只需要少量元组时。 更智能的分区技术： 这个补丁使得 PostgreSQL 的分区技术更加智能和高效。 优化器更智能： 这个补丁增强了优化器的能力，使其能够更好地理解查询的需求，并选择最优的执行计划。
回归测试的例子：
补丁中提到的回归测试展示了该功能的积极成果：优化器不再扫描整个表，而是更倾向于使用参数化扫描，因为它知道连接只需要生成单个元组来执行查询。 这意味着，通过考虑 tuple_fraction，优化器能够避免不必要的扫描，从而提高查询性能。
总结：
这个补丁通过让 Append 节点在选择扫描分区的方式时，更智能地利用 tuple_fraction 信息，从而提高了 PostgreSQL 在处理分区表时的查询性能。 它使得优化器能够更好地理解查询的需求，并选择最优的执行计划，特别是当查询只需要少量元组时。