Parallelism hints v16

Suggest edits

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

PARALLEL (<table> [ <parallel_degree> | DEFAULT ])

NO_PARALLEL (<table>)

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 and DEFAULT, then the query optimizer determines the parallel degree. In this case, if table was 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 were specified. For information on the parallel_workers storage parameter, see Storage Parameters under CREATE 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:

SHOW max_worker_processes;
Output
 max_worker_processes
----------------------
 8
(1 row)
SHOW max_parallel_workers_per_gather;
Output
 max_parallel_workers_per_gather
---------------------------------
 2
(1 row)

Example: Default scan

This example shows the default scan on table pgbench_accounts. A sequential scan is shown in the query plan.

SET trace_hints TO on;

EXPLAIN SELECT * FROM pgbench_accounts;
Output
                             QUERY PLAN
---------------------------------------------------------------------------
 Seq Scan on pgbench_accounts  (cost=0.00..53746.15 rows=2014215 width=97)
(1 row)

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.

EXPLAIN SELECT /*+ PARALLEL(pgbench_accounts) */ * FROM pgbench_accounts;
Output
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)

Now, the max_parallel_workers_per_gather setting is increased:

SET max_parallel_workers_per_gather TO 6;

SHOW max_parallel_workers_per_gather;
Output
 max_parallel_workers_per_gather
---------------------------------
 6
(1 row)

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.

EXPLAIN SELECT /*+ PARALLEL(pgbench_accounts) */ * FROM pgbench_accounts;
Output
                                    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)

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:

EXPLAIN SELECT /*+ PARALLEL(pgbench_accounts 6) */ * FROM pgbench_accounts;
Output
                                    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 now issued 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;
Output
                                    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)

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.

ALTER TABLE pgbench_accounts SET (parallel_workers=3);
Output
\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

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:

EXPLAIN SELECT /*+ PARALLEL(pgbench_accounts) */ * FROM pgbench_accounts;
Output
                                    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)

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.

EXPLAIN SELECT /*+ NO_PARALLEL(pgbench_accounts) */ * FROM pgbench_accounts;
Output
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)
← Prev

APPEND optimizer hint

↑ Up

Using optimizer hints

Next →

Conflicting hints