2.4.7 Parallelism Hints

Table of Contents Previous Next


2 SQL Tutorial : 2.4 Optimizer Hints : 2.4.7 Parallelism Hints

The PARALLEL optimizer hint is used to force parallel scanning.
The NO_PARALLEL optimizer hint prevents usage of a parallel scan.
PARALLEL (table [ parallel_degree | DEFAULT ])
Parallel scanning is the usage of multiple background workers to simultaneously perform a scan of a table (that is, in parallel) for a given query. This process provides performance improvement over other methods such as the sequential scan.
parallel_degree | DEFAULT
parallel_degree is a positive integer that specifies the desired number of workers to use for a parallel scan. If specified, the lesser of parallel_degree and configuration parameter max_parallel_workers_per_gather is used as the planned number of workers. For information on the max_parallel_workers_per_gather parameter, please see Section 19.4.6 Asynchronous Behavior located in Section 19.4 Resource Consumption in the PostgreSQL core documentation available at:
If DEFAULT is specified, then the maximum possible parallel degree is used.
If both parallel_degree and DEFAULT are omitted, then the query optimizer determines the parallel degree. In this case, if table has been set with the parallel_workers storage parameter, then this value is used as the parallel degree, otherwise the optimizer uses the maximum possible parallel degree as if DEFAULT was specified. For information on the parallel_workers storage parameter, please see the Storage Parameters subsection located under CREATE TABLE in the PostgreSQL core documentation available at:
The following example shows the default scan on table pgbench_accounts. Note that a sequential scan is shown in the query plan.
The following example uses the PARALLEL hint. In the query plan, the Gather node, which launches the background workers, indicates that two workers are planned to be used.
Note: If trace_hints is set to on, the INFO: [HINTS] lines appear stating that PARALLEL has been accepted for pgbench_accounts as well as other hint information. For the remaining examples, these lines will not be displayed as they generally show the same output (that is, trace_hints has been reset to off).
Now, the max_parallel_workers_per_gather setting is increased:
The same query on pgbench_accounts is issued again with no parallel degree specification in the PARALLEL hint. Note that the number of planned workers has increased to 4 as determined by the optimizer.
Now, a value of 6 is specified for the parallel degree parameter of the PARALLEL hint. The planned number of workers is now returned as this specified value:
The same query is now issued with the DEFAULT setting for the parallel degree. The results indicate that the maximum allowable number of workers is planned.
Table pgbench_accounts is now altered so that the parallel_workers storage parameter is set to 3.
Note: This format of the ALTER TABLE command to set the parallel_workers parameter is not compatible with Oracle databases.
The parallel_workers setting is shown by the PSQL \d+ command.
Now, when the PARALLEL hint is given with no parallel degree, the resulting number of planned workers is the value from the parallel_workers parameter:
Specifying a parallel degree value or DEFAULT in the PARALLEL hint overrides the parallel_workers setting.
The following example shows the NO_PARALLEL hint. Note that with trace_hints set to on, the INFO: [HINTS] message states that the parallel scan was rejected due to the NO_PARALLEL hint.

2 SQL Tutorial : 2.4 Optimizer Hints : 2.4.7 Parallelism Hints

Table of Contents Previous Next