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.
- The
PARALLEL
optimizer hint forces parallel scanning. - The
NO_PARALLEL
optimizer hint prevents use of a parallel scan.
Synopsis
Parameters
table
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
parallel_degree
andDEFAULT
, then the query optimizer determines the parallel degree. In this case, iftable
was set with theparallel_workers
storage parameter, then this value is used as the parallel degree. Otherwise, the optimizer uses the maximum possible parallel degree as ifDEFAULT
were specified. For information on theparallel_workers
storage parameter, seeStorage Parameters
underCREATE TABLE
in the PostgreSQL core documentation.Regardless of the circumstance, the parallel degree never exceeds the setting of configuration parameter
max_parallel_workers_per_gather
.
About the examples
For these exammples, the following configuration parameter settings are in effect:
Example: Default scan
This example shows the default scan on table pgbench_accounts
. A sequential scan is shown in the query plan.
Example: PARALLEL hint
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:
Note
If trace_hints
is set to on
, the 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 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. 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.
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 isn't compatible with Oracle databases.
The parallel_workers
setting is shown by the PSQL \d+
command.
Example: PARALLEL hint is given with no parallel degree
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.
Example: NO_PARALLEL hint
This example shows the NO_PARALLEL
hint. With trace_hints
set to on
, the INFO: [HINTS]
message states that the parallel scan was rejected due to the NO_PARALLEL
hint.