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 https://www.postgresql.org/docs/11/runtime-config-resource.html.

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 https://www.postgresql.org/docs/11/sql-createtable.html.

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.

Note If trace_hints is set to on, the INFO: [HINTS] lines are displayed to indicate that PARALLEL has been supported by pgbench_accounts and other hints. For the remaining examples, these lines are not displayed. These examples show the same output, where trace_hints is reset to off.
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.

Note This format in which the ALTER TABLE statement sets the parallel_workers parameter is not compatible with Oracle databases.

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)