Postgresql 中文操作指南

15.3. Parallel Plans #

由于每个工作程序都会完整地执行计划的并行部分，因此无法只采用一个普通查询计划并使用多个工作程序运行它。每个工作程序都会生成输出结果集的一个完整副本，因此查询不会比正常情况下运行得更快，但会产生不正确的结果。相反，计划的并行部分必须是查询优化器内部所称 partial plan 的部分；也就是说，它必须以这样的方式构建，以至于执行该计划的每个进程只生成输出行的一个子集，从而保证每个必需的输出行都恰好由一个协作进程生成。通常，这意味着查询的驱动表上的扫描必须是并行扫描。

Because each worker executes the parallel portion of the plan to completion, it is not possible to simply take an ordinary query plan and run it using multiple workers. Each worker would produce a full copy of the output result set, so the query would not run any faster than normal but would produce incorrect results. Instead, the parallel portion of the plan must be what is known internally to the query optimizer as a partial plan; that is, it must be constructed so that each process that executes the plan will generate only a subset of the output rows in such a way that each required output row is guaranteed to be generated by exactly one of the cooperating processes. Generally, this means that the scan on the driving table of the query must be a parallel-aware scan.

15.3.1. Parallel Scans #

目前支持以下类型的具有并行感知能力的表扫描。

The following types of parallel-aware table scans are currently supported.

其他扫描类型（例如非 btree 索引的扫描）将来可能会支持并行扫描。

Other scan types, such as scans of non-btree indexes, may support parallel scans in the future.

15.3.2. Parallel Joins #

就像在非并行计划中一样，可以使用嵌套循环、哈希联接或合并联接将驱动表与一个或多个其他表联接。联接的内部可以是任何类型的非并行计划，只要规划程序支持该计划，并且在并行工作程序中运行该计划是安全的。根据联接类型，内部也可能是并行计划。

Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.

15.3.3. Parallel Aggregation #

PostgreSQL 通过两个阶段聚合来支持并行聚合。首先，参与查询并行部分的每个进程都执行一个聚合步骤，为该进程所知的每个组生成一个部分结果。这在计划中反映为 Partial Aggregate 节点。其次，部分结果通过 Gather 或 Gather Merge 传输到领导者。最后，领导者重新聚合所有工作程序中的结果以生成最终结果。这在计划中反映为 Finalize Aggregate 节点。

PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge. Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.

由于 Finalize Aggregate 节点在领导者进程上运行，因此与输入行数相比生成大量组的查询对于查询规划程序来说似乎不太有利。例如，在最坏的情况下，Finalize Aggregate 节点看到的组数可能多达 Partial Aggregate 阶段所有工作程序进程看到的输入行数。对于这种情况，使用并行聚合显然不会带来性能优势。在规划过程中，查询规划程序会考虑这一点，并且在这种情况下不太可能选择并行聚合。

Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.

并非在所有情况下都支持并行聚合。每个聚合必须为 safe 以实现并行性，并且必须有一个组合函数。如果聚合具有 internal 类型的转换状态，则它必须具有序列化和反序列化函数。有关更多详细信息，请参见 CREATE AGGREGATE 。如果聚合函数调用包含 DISTINCT 或 ORDER BY 子句，或者当查询涉及 GROUPING SETS 时，不支持并行聚合，也不适用于有序集合聚合。只有当查询中涉及的所有联接也是计划的并行部分时，才可以执行并行聚合。

Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal, it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS. It can only be used when all joins involved in the query are also part of the parallel portion of the plan.

15.3.4. Parallel Append #

每当 PostgreSQL 需要将来自多个源的行合并到单个结果集中时，它都会使用 Append 或 MergeAppend 计划节点。这种情况通常发生在实现 UNION ALL 或扫描分区表时。这些节点可以在并行计划中使用，就像在任何其他计划中一样。然而，在一个并行计划中，规划程序可能会改为使用 Parallel Append 节点。

Whenever PostgreSQL needs to combine rows from multiple sources into a single result set, it uses an Append or MergeAppend plan node. This commonly happens when implementing UNION ALL or when scanning a partitioned table. Such nodes can be used in parallel plans just as they can in any other plan. However, in a parallel plan, the planner may instead use a Parallel Append node.

当 Append 节点用于并行计划中时，每个进程都会按它们出现的顺序执行子计划，以便所有参与进程协同执行第一个子计划直到它完成，然后在大致同一时间移动到第二个计划。当改为使用 Parallel Append 时，执行器会尽可能均匀地将参与进程分布到其子计划中，以便同时执行多个子计划。这避免了争用，还避免了在从未执行子计划的进程中支付子计划的启动成本。

When an Append node is used in a parallel plan, each process will execute the child plans in the order in which they appear, so that all participating processes cooperate to execute the first child plan until it is complete and then move to the second plan at around the same time. When a Parallel Append is used instead, the executor will instead spread out the participating processes as evenly as possible across its child plans, so that multiple child plans are executed simultaneously. This avoids contention, and also avoids paying the startup cost of a child plan in those processes that never execute it.

此外，与只能在并行计划中使用时具有部分子项的常规 Append 节点不同，Parallel Append 节点可以同时具有部分和非部分子项。非部分子项将只由一个进程扫描，因为扫描它们多次会产生重复结果。因此，涉及追加多个结果集的计划即使在没有有效的局部计划时也能实现粗粒度并行处理。例如，考虑一个对分区表的查询，该查询只能通过使用不支持并行扫描的索引来有效实现。规划程序可能会选择常规 Index Scan 计划的 Parallel Append；每个单独的索引扫描都必须由一个进程执行完毕，但是不同的进程可以同时执行不同的扫描。

Also, unlike a regular Append node, which can only have partial children when used within a parallel plan, a Parallel Append node can have both partial and non-partial child plans. Non-partial children will be scanned by only a single process, since scanning them more than once would produce duplicate results. Plans that involve appending multiple results sets can therefore achieve coarse-grained parallelism even when efficient partial plans are not available. For example, consider a query against a partitioned table that can only be implemented efficiently by using an index that does not support parallel scans. The planner might choose a Parallel Append of regular Index Scan plans; each individual index scan would have to be executed to completion by a single process, but different scans could be performed at the same time by different processes.

可使用 enable_parallel_append 禁用此功能。

enable_parallel_append can be used to disable this feature.

15.3.5. Parallel Plan Tips #

如果预期执行但并未生成并行计划的查询，则可以尝试减小 parallel_setup_cost 或 parallel_tuple_cost 。当然，该计划可能比计划程序首选的串行计划慢，但这并不总是如此。即使将这些设置的值设得非常小（例如，将它们都设置为零），仍然无法获得并行计划，则可能是查询计划程序无法为查询生成并行计划的原因。请参阅 Section 15.2 和 Section 15.4 了解可能出现这种情况的原因。

If a query that is expected to do so does not produce a parallel plan, you can try reducing parallel_setup_cost or parallel_tuple_cost. Of course, this plan may turn out to be slower than the serial plan that the planner preferred, but this will not always be the case. If you don’t get a parallel plan even with very small values of these settings (e.g., after setting them both to zero), there may be some reason why the query planner is unable to generate a parallel plan for your query. See Section 15.2 and Section 15.4 for information on why this may be the case.

在执行并行计划时，您可以使用 EXPLAIN (ANALYZE, VERBOSE) 为每个计划节点显示每个工作程序的统计信息。这可能有助于确定工作是否在所有计划节点之间均匀分布，并且更普遍地了解计划的性能特征。

When executing a parallel plan, you can use EXPLAIN (ANALYZE, VERBOSE) to display per-worker statistics for each plan node. This may be useful in determining whether the work is being evenly distributed between all plan nodes and more generally in understanding the performance characteristics of the plan.