Parallelism hints v16
Parallel scanning is the use 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.
PARALLELoptimizer hint forces parallel scanning.
NO_PARALLELoptimizer hint prevents use of a parallel scan.
The table to which to apply the parallel hint.
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 under Resource Consumption in the PostgreSQL core documentation.
If you specify
DEFAULT, then the maximum possible parallel degree is used.
If you omit both
DEFAULT, then the query optimizer determines the parallel degree. In this case, if
tablewas set with the
parallel_workersstorage parameter, then this value is used as the parallel degree. Otherwise, the optimizer uses the maximum possible parallel degree as if
DEFAULTwere specified. For information on the
parallel_workersstorage parameter, see
CREATE TABLEin the PostgreSQL core documentation.
Regardless of the circumstance, the parallel degree never exceeds the setting of configuration parameter
For these exammples, the following configuration parameter settings are in effect:
This example shows the default scan on table
pgbench_accounts. A sequential scan is shown in the query plan.
This example uses the
PARALLEL hint. In the query plan, the Gather node, which launches the background workers, indicates the plan to use two workers:
trace_hints is set to
INFO: [HINTS] lines appear stating that
PARALLEL was accepted for
pgbench_accounts and other hint information. For the remaining examples, these lines aren't displayed as they generally show the same output, that is,
trace_hints was 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. 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 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 isn't compatible with Oracle databases.
parallel_workers setting is shown by the PSQL
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
This example shows the
NO_PARALLEL hint. With
trace_hints set to
INFO: [HINTS] message states that the parallel scan was rejected due to the