Parallel Plans
| PostgreSQL 9.6.2 Documentation | |||
|---|---|---|---|
| Prev | Up | Chapter 15. Parallel Query | Next |
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 which 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.
15.3.1. Parallel Scans
Currently, the only type of scan which has been modified to work with parallel query is a sequential scan. Therefore, the driving table in a parallel plan will always be scanned using a Parallel Seq Scan . The relation's blocks will be divided among the cooperating processes. Blocks are handed out one at a time, so that access to the relation remains sequential. Each process will visit every tuple on the page assigned to it before requesting a new page.
15.3.2. Parallel Joins
The driving table may be joined to one or more other tables using nested loops or hash joins. The outer 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. For example, it may be an index scan which looks up a value based on a column taken from the inner table. Each worker will execute the outer side of the plan in full, which is why merge joins are not supported here. The outer side of a merge join will often involve sorting the entire inner table; even if it involves an index, it is unlikely to be productive to have multiple processes each conduct a full index scan of the inner table.
15.3.3. Parallel Aggregation
It is not possible to perform the aggregation portion of a query entirely in parallel. For example, if a query involves selecting COUNT(*) , each worker could compute a total, but those totals would need to combined in order to produce a final answer. If the query involved a GROUP BY clause, a separate total would need to be computed for each group. Even though aggregation can't be done entirely in parallel, queries involving aggregation are often excellent candidates for parallel query, because they typically read many rows but return only a few rows to the client. Queries that return many rows to the client are often limited by the speed at which the client can read the data, in which case parallel query cannot help very much.
PostgreSQL supports parallel aggregation by aggregating twice. 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 PartialAggregate node. Second, the partial results are transferred to the leader via the Gather node. 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 FinalizeAggregate node.
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 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 Plan Tips
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 which 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.
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.