Parallelism Hints v10
PARALLEL optimizer hint is used to force parallel scanning.
NO_PARALLEL optimizer hint prevents usage of a parallel scan.
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.
The table to which the parallel hint is to be applied.
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, see
Asynchronous Behavior located under
Resource Consumption in the PostgreSQL core documentation available at:
DEFAULT is specified, then the maximum possible parallel degree is used.
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, see the
Storage Parameters located under
CREATE TABLE in the PostgreSQL core documentation available at:
Regardless of the circumstance, the parallel degree never exceeds the setting of configuration parameter
The following configuration parameter settings are in effect:
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.
trace_hints is set to
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
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.
pgbench_accounts is now altered so that the
parallel_workers storage parameter is set to
This format of the
ALTER TABLE command to set the
parallel_workers parameter is not compatible with Oracle databases.
parallel_workers setting is shown by the PSQL
Now, when the
PARALLEL hint is given with no parallel degree, the resulting number of planned workers is the value from the
Specifying a parallel degree value or
DEFAULT in the
PARALLEL hint overrides the
The following example shows the
NO_PARALLEL hint. Note that with
trace_hints set to
INFO: [HINTS] message states that the parallel scan was rejected due to the