Parallel Worker Allocation Strategies: Diagnosing Under-Parallelization #

When a query that should exploit multiple CPU cores instead runs on a single thread, the execution plan tells you exactly where the allocation logic broke down. The gap between workers_planned and workers_launched is the primary diagnostic signal, and closing it requires understanding the four distinct constraints that govern how PostgreSQL assigns workers. This page walks through each constraint, how to read it in plan output, and how to resolve it without destabilizing the rest of the system.

The Planner Condition That Triggers Allocation #

PostgreSQL decides whether to parallelize a query by comparing the estimated cost of a parallel plan against the cost of a serial plan, adjusted by two parameters: parallel_setup_cost (the fixed overhead of spawning workers) and parallel_tuple_cost (the per-tuple overhead of routing rows through a Gather node). If the projected savings fall short of those thresholds, the planner produces a serial plan and workers_planned stays at zero.

When the planner does commit to a parallel plan, the executor is still subject to a separate pool constraint: max_parallel_workers caps the total number of background workers that can run simultaneously across the entire instance. Even if max_parallel_workers_per_gather is set to eight, the executor silently launches fewer workers when the pool is already in use.

For the full context on how the optimizer weighs parallel plans, see Parallel Query Execution. Understanding cost estimation models also helps explain why the planner’s math sometimes surprises you.

Annotated EXPLAIN Evidence #

Run this before tuning anything:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT customer_id, SUM(order_total)
FROM orders
WHERE status = 'active'
GROUP BY customer_id;

A plan showing under-allocation looks like this:

Finalize GroupAggregate  (cost=98432.00..99120.00 rows=5420 width=40)
                          (actual time=1840.2..1853.6 rows=5420 loops=1)
  ->  Gather  (cost=98432.00..99010.00 rows=5330 width=40)
               (actual time=1835.1..1848.9 rows=5420 loops=1)
        Workers Planned: 4        -- ← planner requested four workers
        Workers Launched: 1       -- ← executor only got one; investigate why
        ->  Partial HashAggregate (cost=97432.00..97477.00 rows=5330 width=40)
                                   (actual time=1210.3..1218.7 rows=5420 loops=2)
              ->  Parallel Seq Scan on orders
                                   (cost=0.00..89200.00 rows=1646400 width=16)
                                   (actual time=0.12..860.4 rows=1646400 loops=2)

The two fields to inspect first are Workers Planned and Workers Launched on the Gather node. A mismatch — four planned, one launched — means a runtime constraint intervened after the planner finished. The loops value on child nodes equals workers_launched + 1 because the leader process also participates; here loops=2 confirms only one background worker ran.

If workers_planned itself is zero, the planner never chose a parallel plan at all — the issue is a cost threshold or a table too small to qualify.

Parallel worker allocation: planner gate then executor gate Two sequential decision points determine how many parallel workers a query receives. The planner's cost gate compares estimated savings against parallel_setup_cost and parallel_tuple_cost. If it passes, the executor's pool gate checks max_parallel_workers availability at runtime. Query submitted Planner cost gate Serial plan workers_planned = 0 cost savings < threshold parallel plan chosen Executor pool gate Reduced launch workers_launched < workers_planned pool exhausted Full parallel launch slots free STAGE 1: PLAN TIME STAGE 2: EXECUTE TIME

Step-by-Step Resolution Workflow #

Step 1 — Capture the baseline plan #

-- Full diagnostic: cost estimates, buffer activity, per-worker timing
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT customer_id, SUM(order_total)
FROM orders
WHERE status = 'active'
GROUP BY customer_id;

Record workers_planned, workers_launched, and Total Runtime. These are your before-state values.

Step 2 — Rule out a table-size veto #

Tables smaller than min_parallel_table_scan_size (default 8 MB) never receive parallel workers regardless of other settings:

-- Check physical table size; multiply relpages by 8192 for bytes
SELECT relname,
       relpages,
       relpages * 8192          AS size_bytes,
       relpages * 8192 / 1048576 AS size_mb
FROM pg_class
WHERE relname = 'orders';

If size_mb is below 8, the table is legitimately too small for parallel benefit. For larger tables that are still being skipped, check the current threshold:

SHOW min_parallel_table_scan_size;
-- Returns e.g. '8MB'

Step 3 — Lower cost thresholds in isolation #

Use SET LOCAL to scope changes to the current transaction, so you can compare plans without touching the server configuration:

BEGIN;

SET LOCAL max_parallel_workers_per_gather = 4;
SET LOCAL parallel_setup_cost = 500;   -- default is 1000
SET LOCAL parallel_tuple_cost = 0.05;  -- default is 0.1

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(order_total)
FROM orders
WHERE status = 'active'
GROUP BY customer_id;

ROLLBACK;

Re-run after each threshold change; stop when workers_planned reaches the target count. Do not lower both parameters simultaneously — move one at a time to isolate which threshold is blocking parallelism.

Step 4 — Verify the worker pool has capacity #

Even with the right cost settings, the executor is bounded by the global worker budget:

-- Current pool limits
SHOW max_parallel_workers;          -- total background worker slots
SHOW max_parallel_workers_per_gather; -- per-query limit

-- Workers currently in use
SELECT count(*) AS active_parallel_workers
FROM pg_stat_activity
WHERE backend_type = 'parallel worker';

If active_parallel_workers is close to max_parallel_workers, the pool is exhausted and other queries are consuming all slots. Either increase max_parallel_workers (requires a server restart) or schedule the heavy query for off-peak hours.

Step 5 — Prevent per-worker memory spills #

When work_mem is too low for a sort or hash operation inside each worker, the executor may shed workers to stay within memory bounds. Raise it for the session:

SET LOCAL work_mem = '64MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(order_total)
FROM orders
WHERE status = 'active'
GROUP BY customer_id;

Look for the disappearance of Sort Method: external merge Disk lines in the plan — that confirms the spill was the limiting factor. For hash-based operations, watch Batches on Hash nodes: a value greater than 1 indicates a spill.

Step 6 — Validate system-wide impact #

After any change that improves workers_launched, confirm the overall system is not worse:

-- Spot active parallel workers and what they are doing
SELECT pid, query_start, state, wait_event_type, wait_event,
       LEFT(query, 80) AS query_snippet
FROM pg_stat_activity
WHERE backend_type = 'parallel worker'
ORDER BY query_start;

If CPU utilization spikes without a proportional drop in query duration, the bottleneck is I/O or lock contention — more workers amplify the wait rather than eliminate it.

Before/After Plan Comparison #

The clearest proof that the allocation changed is a direct comparison of the two key metrics on the Gather node:

-- BEFORE (pool exhausted, serial fallback)
Gather  (actual time=1840..1854 rows=5420 loops=1)
  Workers Planned: 4
  Workers Launched: 1

-- AFTER (pool capacity freed, thresholds lowered)
Gather  (actual time=520..534 rows=5420 loops=1)
  Workers Planned: 4
  Workers Launched: 4

actual time dropped from ~1854 ms to ~534 ms — roughly a 3.5× speedup from four workers instead of one. The loops value on child nodes will change from 2 (leader + 1 worker) to 5 (leader + 4 workers).

Common Pitfalls #

Treating workers_launched = 0 as a planner bug. In most cases it is a correct cost decision or a runtime constraint. Always check workers_planned first — if it is also zero, the planner itself chose a serial plan; if it is non-zero, the executor could not fulfil the request.

Raising max_parallel_workers_per_gather without raising max_parallel_workers. The per-gather limit is capped by the global pool. Setting max_parallel_workers_per_gather = 8 while max_parallel_workers = 4 produces at most four workers.

Tuning cost thresholds globally. Lowering parallel_setup_cost in postgresql.conf affects every query on the instance, including short OLTP queries that now pay the parallelization overhead they could not previously recover. Prefer SET LOCAL per session or ALTER ROLE ... SET per workload role.

Ignoring data skew inside workers. If actual rows varies dramatically across workers in EXPLAIN (ANALYZE, VERBOSE) output, the query is distributing data unevenly. More workers will not fix skew — you need updated statistics via ANALYZE or a different partitioning strategy. See identifying plan bottlenecks for the row-estimate diagnosis workflow.

Frequently Asked Questions #

Why does EXPLAIN show workers_planned but workers_launched equals zero? #

Runtime conditions forced a serial fallback. Common causes: insufficient available worker slots (max_worker_processes or max_parallel_workers fully consumed), the query running inside a transaction holding explicit locks, or the table falling below min_parallel_table_scan_size. The planner’s cost estimate was valid at plan time, but the executor detected the constraint and fell back to a single thread.

How do parallel_setup_cost and parallel_tuple_cost influence worker allocation? #

parallel_setup_cost is the fixed overhead the planner assigns to spawning workers — lowering it makes the optimizer more willing to choose parallel plans for moderately sized queries. parallel_tuple_cost is added per tuple passed through the Gather node — lowering it reduces the communication penalty and encourages wider parallelism. Both are unitless planner cost units, not milliseconds.

Can increasing max_parallel_workers_per_gather degrade overall database performance? #

Yes. A query consuming eight workers leaves fewer slots for concurrent sessions. On write-heavy OLTP workloads this causes queue buildup and higher average latency. Always verify CPU and I/O utilization after any change and use pg_stat_activity to confirm that other queries are not starving for worker slots.