The PARALLEL optimizer hint is used to force parallel scanning.The NO_PARALLEL optimizer hint prevents usage of a parallel scan.
Synopsis
PARALLEL (table [ parallel_degree | DEFAULT ])
NO_PARALLEL (table)
Description
Parallel scanning allows multiple background workers to simultaneously scan a table in a specified query. Compared with other methods such as a sequential scan, this scan provides improved performance.
Parameters
Parameter | Description |
---|---|
table | The table in which a parallel hint is used. |
parallel_degree | DEFAULT | The value of the parallel_degree parameter is a positive integer that specifies the
desired number of workers to be used in a parallel scan. If this parameter is set,
the smaller value between this parameter and the configuration parameter max_parallel_workers_per_gather
is used as the planned number of workers. For more information about the max_parallel_workers_per_gather
parameter, visit max_parallel_workers_per_gather.
If DEFAULT is set, the maximum possible parallel degree is used. If both parallel_degree and DEFAULT are omitted, the query optimizer determines the parallel degree. In this case, if the table parameter has been set with the parallel_workers storage parameter, the value of parallel_workers is used as the parallel degree. Otherwise, the optimizer uses the maximum possible parallel degree specified by DEFAULT. For more information about the parallel_workers storage parameter, visit parallel_workers. Regardless of the circumstance, the parallel degree never exceeds the value of max_parallel_workers_per_gather. |
Examples
The following configuration parameter settings are valid:
SHOW max_worker_processes;
max_worker_processes
----------------------
8
(1 row)
SHOW max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
2
(1 row)
The following example shows the default scan on the pgbench_accounts table. A sequential scan is shown in the query plan.
SET trace_hints TO on;
EXPLAIN SELECT * FROM pgbench_accounts;
QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on pgbench_accounts (cost=0.00..53746.15 rows=2014215 width=97)
(1 row)
The following example uses the PARALLEL hint. In the query plan, the Gather node that launches the background workers specifies that two workers are planned to be used.
EXPLAIN SELECT /*+ PARALLEL(pgbench_accounts) */ * FROM pgbench_accounts;
INFO: [HINTS] SeqScan of [pgbench_accounts] rejected due to PARALLEL hint.
INFO: [HINTS] PARALLEL on [pgbench_accounts] accepted.
INFO: [HINTS] Index Scan of [pgbench_accounts].[pgbench_accounts_pkey] rejected due to PARALLEL hint.
QUERY PLAN
-----------------------------------------------------------------------------------------
Gather (cost=1000.00..244418.06 rows=2014215 width=97)
Workers Planned: 2
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..41996.56 rows=839256 width=97)
(3 rows)
The following example shows an increased value of max_parallel_workers_per_gather:
SET max_parallel_workers_per_gather TO 6;
SHOW max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
6
(1 row)
The same query on pgbench_accounts is used again with no specified parallel degree in the PARALLEL hint. The number of planned workers has been determined by the optimizer and increased to 4.
EXPLAIN SELECT /*+ PARALLEL(pgbench_accounts) */ * FROM pgbench_accounts;
QUERY PLAN
-----------------------------------------------------------------------------------------
Gather (cost=1000.00..241061.04 rows=2014215 width=97)
Workers Planned: 4
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..38639.54 rows=503554 width=97)
(3 rows)
A value of 6 is specified for the parallel degree parameter of the PARALLEL hint. The value is returned as the planned number of workers in the following example:
EXPLAIN SELECT /*+ PARALLEL(pgbench_accounts 6) */ * FROM pgbench_accounts;
QUERY PLAN
-----------------------------------------------------------------------------------------
Gather (cost=1000.00..239382.52 rows=2014215 width=97)
Workers Planned: 6
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..36961.03 rows=335702 width=97)
(3 rows)
The same query is used with the DEFAULT setting for the parallel degree. The results indicate that the maximum allowable number of workers is planned.
EXPLAIN SELECT /*+ PARALLEL(pgbench_accounts DEFAULT) */ * FROM pgbench_accounts;
QUERY PLAN
-----------------------------------------------------------------------------------------
Gather (cost=1000.00..239382.52 rows=2014215 width=97)
Workers Planned: 6
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..36961.03 rows=335702 width=97)
(3 rows)
The pgbench_accounts table is modified. In this table, the parallel_workers storage parameter is set to 3.
The parallel_workers parameter is set by the PSQL \d+ statement.
ALTER TABLE pgbench_accounts SET (parallel_workers=3);
\d+ pgbench_accounts
Table "public.pgbench_accounts"
Column | Type | Modifiers | Storage | Stats target | Description
----------+---------------+-----------+----------+--------------+-------------
aid | integer | not null | plain | |
bid | integer | | plain | |
abalance | integer | | plain | |
filler | character(84) | | extended | |
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Options: fillfactor=100, parallel_workers=3
If the PARALLEL hint is provided with no parallel degree, the returned number of planned workers is the value of the parallel_workers parameter.
EXPLAIN SELECT /*+ PARALLEL(pgbench_accounts) */ * FROM pgbench_accounts;
QUERY PLAN
-----------------------------------------------------------------------------------------
Gather (cost=1000.00..242522.97 rows=2014215 width=97)
Workers Planned: 3
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..40101.47 rows=649747 width=97)
(3 rows)
The parallel degree value or DEFAULT in the PARALLEL hint overwrites the parallel_workers setting.
The following example shows the NO_PARALLEL hint. If trace_hints is set to on, the INFO: [HINTS] message is displayed to indicate that the parallel scan has been rejected due to the NO_PARALLEL hint.
EXPLAIN SELECT /*+ NO_PARALLEL(pgbench_accounts) */ * FROM pgbench_accounts;
INFO: [HINTS] Parallel SeqScan of [pgbench_accounts] rejected due to NO_PARALLEL hint.
QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on pgbench_accounts (cost=0.00..53746.15 rows=2014215 width=97)
(1 row)