PricingLoginStart free
PostgreSQL Performance & Query Optimization · レッスン

並列処理が無効になった理由の診断

直列実行をひそかに強制する関数、ロック、設定を特定します。

レッスン 4/413 ステップ

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

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

When the Plan Goes Serial

You expect a parallel sequential scan, but EXPLAIN shows a plain serial plan. Before blaming the planner's cost model, learn that PostgreSQL has many hard gates that silently veto parallelism entirely.

  • Some are settings (max workers set to 0, low parallel_setup_cost still too high).
  • Some are query shape (parallel-unsafe functions, write CTEs).
  • Some are runtime (no free worker slots, or holding locks).

Our job in this lesson: systematically find which gate fired.

First Look: EXPLAIN ANALYZE

Start by confirming whether parallelism appeared at all. A parallel plan contains a Gather (or Gather Merge) node above the parallel-aware scan. If you only see plain Seq Scan, no workers were even considered viable.

The Workers Planned and Workers Launched lines tell you whether the planner wanted workers and whether it actually got them at runtime — two very different failures.

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT count(*)
FROM orders
WHERE amount > 100;

-- Look for:
--   Gather  (cost=...)
--     Workers Planned: 2
--     Workers Launched: 2
--     ->  Parallel Seq Scan on orders

Planned vs Launched: Two Failure Modes

Distinguish the two symptoms precisely:

  • Workers Planned: 0 — the planner decided parallelism was illegal or not worth it. This is a planning-time gate (settings, parallel-unsafe query, cost).
  • Workers Planned: 2, Workers Launched: 0 — the plan is parallel, but at execution time no worker slots were free. This is a runtime exhaustion problem.

Confusing these wastes hours. Always read both lines before forming a hypothesis.

Gate 1: The Worker Settings

The most common cause of Workers Planned: 0 is configuration. Check these three first:

  • max_parallel_workers_per_gather — if 0, parallelism is globally off for queries. This is the #1 culprit.
  • max_parallel_workers — pool size; must be > 0 and ≤ max_worker_processes.
  • max_worker_processes — the absolute ceiling for all background workers.

Inspect them in one shot:

SELECT name, setting, source
FROM pg_settings
WHERE name IN (
  'max_parallel_workers_per_gather',
  'max_parallel_workers',
  'max_worker_processes',
  'max_parallel_maintenance_workers'
);

Gate 2: Table Size and Cost Thresholds

Even with workers enabled, the planner refuses parallelism when the relation looks too small. Two settings gate this:

  • min_parallel_table_scan_size (default 8MB) — a heap smaller than this is never scanned in parallel.
  • min_parallel_index_scan_size (default 512kB) — same idea for index scans.

Also, parallel_setup_cost (default 1000) and parallel_tuple_cost (default 0.1) penalize parallel plans; on small results the serial plan simply wins on cost. Check the table's real size:

SELECT pg_size_pretty(pg_relation_size('orders')) AS heap_size,
       (pg_relation_size('orders') / 1024.0 / 1024.0) AS heap_mb,
       current_setting('min_parallel_table_scan_size') AS min_scan;

Gate 3: Parallel-Unsafe Functions

A single PARALLEL UNSAFE function anywhere in the query forces the entire plan serial — no Gather at all. Every function has a parallel-safety label:

  • SAFE — may run in workers.
  • RESTRICTED — may appear in the plan but only in the leader, not below Gather.
  • UNSAFE — bans parallelism for the whole statement.

User-defined functions default to UNSAFE unless you explicitly mark them. Anything that writes, uses sequences, or touches temp tables is unsafe.

SELECT p.proname,
       p.proparallel  -- 's'=safe, 'r'=restricted, 'u'=unsafe
FROM pg_proc p
WHERE p.proname IN ('normalize_email', 'nextval', 'random', 'now')
ORDER BY p.proname;

Auditing Your Own Functions

To find UDFs that silently disable parallelism, list every function you own that is labelled unsafe or restricted. A function that is logically pure but defaults to UNSAFE is a frequent, invisible cause.

If the function truly is read-only and side-effect free, re-declare it as PARALLEL SAFE to unblock the planner.

SELECT n.nspname AS schema,
       p.proname AS function,
       CASE p.proparallel
         WHEN 's' THEN 'safe'
         WHEN 'r' THEN 'restricted'
         WHEN 'u' THEN 'unsafe'
       END AS parallel_safety
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND p.proparallel <> 's'
ORDER BY 1, 2;

Fixing an Unsafe UDF Label

If you confirm a function has no side effects and never writes, mark it safe so it can run beneath Gather. Be honest: anything calling nextval(), modifying tables, or using non-immutable session state must stay restricted or unsafe.

Marking a genuinely unsafe function as safe leads to wrong results or worker crashes — this label is a contract, not a hint.

-- Only if the body is truly read-only and deterministic across workers:
ALTER FUNCTION normalize_email(text) PARALLEL SAFE;

-- Verify the new label:
SELECT proname, proparallel
FROM pg_proc
WHERE proname = 'normalize_email';

Gate 4: Query Shapes That Block Parallelism

Some constructs are inherently parallel-restricted regardless of settings:

  • Data-modifying statements — INSERT/UPDATE/DELETE (parallel DML is limited; parallel writes are generally not used).
  • Writable / data-modifying CTEs — a CTE that writes forces serial.
  • SELECT ... FOR UPDATE / FOR SHARE — locking clauses are parallel-restricted.
  • Queries inside a function with PARALLEL UNSAFE, or called where the outer statement is already a write.
  • FULL OUTER JOIN historically, and correlated subqueries referencing the outer parallel node.

If your query has any of these, no setting will produce a parallel plan.

-- This SELECT can be parallel:
EXPLAIN SELECT count(*) FROM orders WHERE amount > 100;

-- This one cannot — the locking clause is parallel-restricted:
EXPLAIN SELECT * FROM orders WHERE amount > 100 FOR UPDATE;

Gate 5: Runtime Worker Exhaustion

When you see Workers Planned: 4 but Workers Launched: 1, the planner was right but the pool was empty. The cluster-wide pool is capped by max_parallel_workers, drawn from max_worker_processes, and shared with autovacuum and other parallel queries.

Under concurrency, queries grab whatever slots remain — sometimes zero. Watch live backends to confirm contention:

SELECT pid, backend_type, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE backend_type = 'parallel worker'
   OR query ILIKE '%Gather%'
ORDER BY backend_type;

Gate 6: Locks and force_parallel_mode

Two final, easily-missed gates:

  • Heavy locks — if a backend holds or waits on a conflicting lock, it may serialize; also a relation under an ACCESS EXCLUSIVE lock (DDL) won't get a parallel scan while blocked. Inspect pg_locks joined to pg_stat_activity.
  • debug_parallel_query (formerly force_parallel_mode) — a testing GUC. Setting it to on/regress forces a Gather even when it makes no sense, which can mask real diagnosis. Make sure it is off in normal investigation.
SELECT name, setting
FROM pg_settings
WHERE name IN ('debug_parallel_query', 'force_parallel_mode');

-- Who is blocking a parallel scan?
SELECT l.pid, l.mode, l.granted, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation = 'orders'::regclass
ORDER BY l.granted;

Quick Check: Diagnosing the Symptom

An analyst runs EXPLAIN ANALYZE and sees Workers Planned: 4 but Workers Launched: 0. All worker GUCs are non-zero and the table is 2 GB. What is the most likely cause?

対話的なレッスンで答える

Recap: A Diagnosis Checklist

To find why parallelism was disabled, work top-down:

  • Read both lines. Workers Planned: 0 = planning gate; Planned>0, Launched<Planned = runtime exhaustion.
  • Settings: check max_parallel_workers_per_gather (≠0), max_parallel_workers, max_worker_processes.
  • Size/cost: table above min_parallel_table_scan_size; parallel_setup_cost not dominating a tiny result.
  • Functions: hunt proparallel <> 's' in pg_proc; fix mislabelled UDFs.
  • Query shape: writes, data-modifying CTEs, FOR UPDATE locking clauses.
  • Runtime: pg_stat_activity for free slots; pg_locks for blockers; confirm debug_parallel_query = off.

Match the symptom to the gate, and the silent serial plan stops being a mystery.

無料で開始

AI チューターと学ぶ SQL — 無料

ブラウザでリアルコードを書いて実行し、24/7 の AI チューターから瞬時にサポートを受け、ウェブまたはアプリで続きから学習できます。

コース
22
レッスン
88
無料で学習を始めるコース全体を表示

よくある質問

「並列処理が無効になった理由の診断」レッスンは無料ですか？

はい。「並列処理が無効になった理由の診断」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと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は初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。

「並列処理が無効になった理由の診断」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

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