0Pricing
PostgreSQL Performance & Query Optimization · Lektion

Diagnostizieren, warum Parallelität deaktiviert wurde

Ermitteln Sie Funktionen, Sperren und Einstellungen, die unbemerkt eine serielle Ausführung erzwingen.

Diagnostizieren, warum Parallelität deaktiviert wurde ist eine kostenlose PostgreSQL Performance & Query Optimization-Lektion auf CoddyKit. Dies ist Lektion 4 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des PostgreSQL Performance & Query Optimization-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der PostgreSQL Performance & Query Optimization-Kurs umfasst insgesamt 4 Lektionen.

Teile dieser Lektion wurden noch nicht übersetzt und werden auf Englisch angezeigt.

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.

Häufig gestellte Fragen

Ist die Lektion „Diagnostizieren, warum Parallelität deaktiviert wurde“ kostenlos?

Ja — der vollständige Text von „Diagnostizieren, warum Parallelität deaktiviert wurde“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des PostgreSQL Performance & Query Optimization-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der PostgreSQL Performance & Query Optimization-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Diagnostizieren, warum Parallelität deaktiviert wurde“?

Ermitteln Sie Funktionen, Sperren und Einstellungen, die unbemerkt eine serielle Ausführung erzwingen. Du übst PostgreSQL Performance & Query Optimization mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um PostgreSQL Performance & Query Optimization zu starten?

Keine Vorkenntnisse erforderlich. PostgreSQL Performance & Query Optimization auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 4 von 4.

Wie lange dauert die Lektion „Diagnostizieren, warum Parallelität deaktiviert wurde“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser PostgreSQL Performance & Query Optimization-Lektion Code schreiben und ausführen?

Ja. Jede PostgreSQL Performance & Query Optimization-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

  1. Wann der Planner parallele Pläne wählt
  2. Worker-Anzahlen und Gather-Kosten optimieren
  3. Parallele Aggregation und Hash-Joins
  4. Diagnostizieren, warum Parallelität deaktiviert wurde
← Zurück zu PostgreSQL Performance & Query Optimization