0Pricing
PostgreSQL Performance & Query Optimization · レッスン

プランナーが並列実行計画を選ぶタイミング

並列スキャンと並列結合を発生させるコストのしきい値とテーブルサイズを理解します。

「プランナーが並列実行計画を選ぶタイミング」はCoddyKit上の無料PostgreSQL Performance & Query Optimizationレッスンです。 これはレッスン1/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはPostgreSQL Performance & Query Optimization学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

このレッスンの一部はまだ翻訳されておらず、英語で表示されています。

Parallelism Is a Cost Decision

PostgreSQL does not run every query in parallel. The planner only generates a parallel plan when it believes the extra cost of spinning up workers will be repaid by faster execution.

  • Workers cost money: the planner adds startup and tuple-transfer overhead for each one.
  • Parallelism only pays off when the table is large enough and the work is CPU- or IO-heavy enough.
  • Tiny tables and cheap queries stay sequential — the overhead would dominate.

In this lesson you will learn exactly which thresholds and table sizes flip the planner into a parallel plan.

The Three Gating GUCs

Three settings gate whether parallelism is even considered before any cost math runs:

  • max_parallel_workers_per_gather — max workers a single Gather node may request (default 2). If 0, parallelism is disabled for that query.
  • max_parallel_workers — cap across the whole instance (default 8).
  • max_worker_processes — the hard OS-level ceiling on background workers (default 8).

If max_parallel_workers_per_gather = 0, the planner never produces a parallel path, regardless of table size.

SHOW max_parallel_workers_per_gather;
SHOW max_parallel_workers;
SHOW max_worker_processes;

The Size Threshold: min_parallel_table_scan_size

The single most important table-size trigger is min_parallel_table_scan_size (default 8MB). A relation must be at least this large before the planner even considers a parallel sequential scan over it.

  • For indexes, the analogous setting is min_parallel_index_scan_size (default 512kB).
  • Below the threshold, the relation is treated as too small to bother parallelizing.

So a 6MB table will almost never get a parallel scan, while a 1GB table easily clears the bar.

SHOW min_parallel_table_scan_size;
SHOW min_parallel_index_scan_size;

How Worker Count Scales With Size

The number of workers is not fixed — it grows logarithmically with table size. Each time the relation triples past the threshold, the planner allows one more worker (capped by max_parallel_workers_per_gather).

  • 8MB to 24MB: up to 1 worker
  • 24MB to 72MB: up to 2 workers
  • 72MB to 216MB: up to 3 workers
  • Each step multiplies the previous boundary by 3.

This is why doubling a small table rarely changes the worker count, but going from 10MB to 1GB does.

The Per-Tuple and Setup Costs

Once a parallel path is possible, the planner weighs two cost penalties against the sequential plan:

  • parallel_setup_cost (default 1000) — a one-time charge for launching workers and tearing down the shared memory. This is large, which is why tiny queries stay sequential.
  • parallel_tuple_cost (default 0.1) — charged per tuple passed from a worker up through the Gather node to the leader.

A parallel plan wins only when divided per-worker scan cost minus these penalties beats the sequential cost.

SHOW parallel_setup_cost;
SHOW parallel_tuple_cost;

Reading a Parallel Plan in EXPLAIN

You confirm a parallel plan by looking for a Gather (or Gather Merge) node and Parallel-prefixed scan nodes underneath it.

  • Gather collects rows from workers; Workers Planned shows how many were requested.
  • Nodes below the Gather (e.g. Parallel Seq Scan) run once per worker plus the leader.
  • Row counts under the Gather are per-worker estimates, not the total.
EXPLAIN (ANALYZE, VERBOSE)
SELECT count(*)
FROM orders
WHERE amount > 100;

Forcing Parallelism for Testing

To observe a parallel plan on a smaller table during testing, lower the gating thresholds at the session level. This does not change the data — only the planner's willingness to parallelize.

  • Set min_parallel_table_scan_size = 0 to remove the size gate.
  • Set parallel_setup_cost = 0 and parallel_tuple_cost = 0 to remove the cost penalties.
  • Set force_parallel_mode = on (renamed debug_parallel_query in PG16+) to force a parallel path where one exists.

These are diagnostic knobs — never leave them on in production.

SET min_parallel_table_scan_size = 0;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET max_parallel_workers_per_gather = 4;

Parallel-Aware vs Parallel-Restricted

Not every operation can run inside a parallel worker. The planner classifies pieces of the query:

  • Parallel-safe — can run in a worker (most immutable/stable functions, plain scans).
  • Parallel-restricted — must run only in the leader (e.g. references to temporary tables, correlated subplans, some CTEs).
  • Parallel-unsafe — disables parallelism for the whole query (e.g. functions marked PARALLEL UNSAFE, writing CTEs, sequence calls).

A single VOLATILE or PARALLEL UNSAFE function in the query can suppress an otherwise-parallel plan.

CREATE FUNCTION score(x int) RETURNS int
LANGUAGE sql IMMUTABLE PARALLEL SAFE
AS 'SELECT x * 2';

Parallel Joins and Aggregates

Parallelism is not limited to scans. Once a driving relation is scanned in parallel, the planner can push joins and aggregation into the workers:

  • Parallel Hash Join — workers cooperatively build one shared hash table.
  • Parallel Nested Loop / Merge Join — each worker joins its slice of the outer relation.
  • Partial Aggregate — each worker aggregates its rows, then a Finalize Aggregate above the Gather combines the partial results.

This is why a large GROUP BY over a big fact table is a classic parallel-plan candidate.

EXPLAIN
SELECT customer_id, count(*)
FROM orders
GROUP BY customer_id;

Things That Quietly Disable Parallelism

Even on a huge table, several conditions block a parallel plan entirely:

  • The query is run inside a cursor/FETCH or with SELECT ... FOR UPDATE row locking.
  • It is a data-modifying statement on PG versions before 11 (later versions allow parallel INSERT ... SELECT / index builds, but not parallel UPDATE/DELETE execution).
  • It calls a PARALLEL UNSAFE function or runs inside a function that has already claimed all workers.
  • max_parallel_workers_per_gather = 0 for the session.

When a plan you expect to be parallel is not, check these before tuning costs.

Putting the Thresholds Together

For the planner to pick a parallel plan, all of these must hold:

  • Relation size at least min_parallel_table_scan_size (8MB) for scans, or the index size threshold.
  • max_parallel_workers_per_gather > 0 and instance-wide worker budget available.
  • The query contains only parallel-safe (or at worst parallel-restricted) elements.
  • The estimated parallel cost, after parallel_setup_cost and parallel_tuple_cost, beats the best sequential plan.

Miss any one and you get a sequential plan — usually for a good reason.

EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(amount)
FROM orders
WHERE created_at >= DATE '2024-01-01';

Quick Check

Test your understanding of the parallel-plan triggers.

Recap

Key takeaways on when PostgreSQL chooses a parallel plan:

  • Parallelism is a cost decision gated first by size, then by cost math.
  • min_parallel_table_scan_size (8MB) is the primary table-size trigger; min_parallel_index_scan_size (512kB) covers indexes.
  • Worker count grows logarithmically — one more worker each time size triples — capped by max_parallel_workers_per_gather.
  • parallel_setup_cost (1000) and parallel_tuple_cost (0.1) must be outweighed by the per-worker speedup.
  • Parallel-unsafe functions, row locking, and a zero worker setting can disable parallelism even on huge tables.
  • Confirm plans by looking for Gather / Partial Aggregate nodes in EXPLAIN.

よくある質問

「プランナーが並列実行計画を選ぶタイミング」レッスンは無料ですか？

はい。「プランナーが並列実行計画を選ぶタイミング」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、PostgreSQL Performance & Query Optimizationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

「プランナーが並列実行計画を選ぶタイミング」で何を学びますか？

並列スキャンと並列結合を発生させるコストのしきい値とテーブルサイズを理解します。 ブラウザで直接実行するハンズオンコードでPostgreSQL Performance & Query Optimizationを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

PostgreSQL Performance & Query Optimizationを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのPostgreSQL Performance & Query Optimizationは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。

「プランナーが並列実行計画を選ぶタイミング」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このPostgreSQL Performance & Query Optimizationレッスンでコードを書いて実行できますか？

はい。すべてのPostgreSQL Performance & Query Optimizationレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. プランナーが並列実行計画を選ぶタイミング
  2. ワーカー数とGatherコストのチューニング
  3. 並列集約とハッシュ結合
  4. 並列処理が無効になった理由の診断
← PostgreSQL Performance & Query Optimizationに戻る