PostgreSQL Performance & Query Optimization · Lesson

Tuning Worker Counts and Gather Costs

Adjust parallel worker settings and per-tuple costs to balance speedup against overhead.

Tuning Worker Counts and Gather Costs is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Parallel Query Needs Tuning

PostgreSQL can split a single query across multiple CPU cores using parallel workers. The leader process spawns helper workers, each scans a slice of the data, and results are merged back at a Gather node.

Parallelism is not free. Spawning workers, copying tuples through shared memory, and synchronizing at the gather point all cost time. For small result sets the overhead can exceed the speedup.

CPU-bound workloads (large sequential scans, aggregates, hash joins) benefit most.
Latency-sensitive short queries usually do not.

This lesson covers the knobs that decide how many workers run and when the planner thinks parallelism is worth it.

The Plan Shape: Gather and Partial Nodes

A parallel plan has a distinctive shape. Below the Gather (or Gather Merge) node sit the partial operations that workers run in parallel; above it everything is serial in the leader.

Read the plan with EXPLAIN (ANALYZE, VERBOSE) and look for Workers Planned vs Workers Launched. A gap between them means the system ran out of available worker slots at runtime.

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

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

max_parallel_workers_per_gather

The single most important per-query knob is max_parallel_workers_per_gather. It caps how many workers a single Gather node may request. The default is 2.

Setting it to 0 disables parallelism entirely for that session. Raising it lets large scans use more cores, but only up to what the table size and other limits allow.

This is a per-Gather cap, not a per-query cap. A query with two Gather nodes can use up to 2× this many workers.
It can be changed per session with SET, so you can tune one heavy report without touching global config.

-- Inspect current value
SHOW max_parallel_workers_per_gather;

-- Allow up to 4 workers per gather for this session
SET max_parallel_workers_per_gather = 4;

-- Disable parallelism for a latency-critical query
SET max_parallel_workers_per_gather = 0;

The Three-Layer Worker Budget

Worker counts are limited by three nested settings. The effective number of workers is the minimum of all three plus the planner's own size-based estimate.

max_parallel_workers_per_gather — per Gather node (default 2).
max_parallel_workers — cluster-wide pool dedicated to parallel query (default 8).
max_worker_processes — total background workers for the whole server, shared with extensions and replication (default 8).

If you raise the per-gather cap but leave max_parallel_workers low, concurrent queries will compete and many will run with fewer workers than planned.

SHOW max_worker_processes;        -- server-wide ceiling
SHOW max_parallel_workers;        -- pool for parallel query
SHOW max_parallel_workers_per_gather;  -- per-gather cap

-- A sane production starting point on an 8-core box:
-- max_worker_processes = 16
-- max_parallel_workers = 8
-- max_parallel_workers_per_gather = 4

How the Planner Picks a Default Worker Count

Even with high caps, the planner scales workers by relation size using a logarithmic rule. A table must be at least min_parallel_table_scan_size (default 8MB) before any parallel worker is considered. Roughly, each 3× increase in size adds one worker.

8MB to 24MB to 72MB to 216MB... → 1, 2, 3... workers.
The result is then clamped by max_parallel_workers_per_gather.

This is why a tiny table never goes parallel no matter how high you set the caps — and why you sometimes must lower min_parallel_table_scan_size to test parallel plans on small data.

SHOW min_parallel_table_scan_size;   -- default 8MB
SHOW min_parallel_index_scan_size;   -- default 512kB

-- Force consideration of parallelism on smaller tables (testing)
SET min_parallel_table_scan_size = '0';

parallel_setup_cost: The Fixed Overhead

parallel_setup_cost models the one-time price of launching workers and setting up shared memory. The default is 1000 — a large number in planner cost units, deliberately so the optimizer avoids parallelism for cheap queries.

If your hardware spawns workers quickly and you find good parallel plans being rejected, lowering this value makes the planner more willing to parallelize. Raise it to discourage parallelism on a busy OLTP box.

SHOW parallel_setup_cost;   -- default 1000

-- Make the planner more eager to go parallel
SET parallel_setup_cost = 200;

-- Then compare plans
EXPLAIN SELECT count(*) FROM orders WHERE shipped_at IS NULL;

parallel_tuple_cost: The Per-Tuple Gather Price

parallel_tuple_cost models the cost of transferring each tuple from a worker back to the leader through the gather queue. Default is 0.1 per tuple.

This is the key reason parallelism loses on queries that return many rows: every returned tuple is taxed. Parallel plans win when workers reduce the data — filtering hard or aggregating — so few tuples cross the Gather.

High row count out of Gather + high parallel_tuple_cost → planner prefers serial.
Lower it cautiously if your shared-memory tuple transfer is genuinely cheap.

SHOW parallel_tuple_cost;   -- default 0.1

-- A query that aggregates (few tuples cross Gather) loves parallelism;
-- a query that returns millions of raw rows pays parallel_tuple_cost on each.
EXPLAIN
SELECT customer_id, count(*)
FROM orders
GROUP BY customer_id;   -- partial aggregate shrinks data before Gather

Per-Table Override: parallel_workers Storage Parameter

You can pin a worker count to a specific table with the parallel_workers storage parameter. This overrides the planner's size-based formula for scans of that table, while still being clamped by the global caps.

Useful for a hot fact table that is queried with heavy aggregates, where you always want, say, 6 workers regardless of the logarithmic default.

-- Pin parallel scans of this table to 6 workers
ALTER TABLE orders SET (parallel_workers = 6);

-- Remove the override, return to size-based defaults
ALTER TABLE orders RESET (parallel_workers);

-- Verify the setting
SELECT reloptions FROM pg_class WHERE relname = 'orders';

Measuring the Sweet Spot

Tuning is empirical. Run the same query under different worker counts and compare actual execution time, not just planner cost. Speedup is sublinear and eventually flattens or regresses as coordination overhead grows.

Watch Workers Launched — if it is below Workers Planned, your pool is exhausted and adding the per-gather cap won't help.
Diminishing returns: going from 4 to 8 workers often yields far less than the 1-to-2 jump.

SET max_parallel_workers_per_gather = 2;
EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM orders WHERE order_total > 100;

SET max_parallel_workers_per_gather = 4;
EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM orders WHERE order_total > 100;

SET max_parallel_workers_per_gather = 8;
EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM orders WHERE order_total > 100;

Concurrency: When Workers Run Out

The parallel worker pool is shared across the whole cluster. Under concurrency, many queries planned for 4 workers each may collectively demand more than max_parallel_workers allows, so some run with reduced or zero workers.

On OLTP-heavy systems this is a feature: you want short transactions to win CPU, not lose it to a reporting query that grabbed 8 workers. Strategies:

Keep max_parallel_workers_per_gather modest (2-4) globally.
Raise it per session only for known batch/report jobs.
Ensure max_worker_processes is high enough to also leave room for extensions and replication.

A Practical Tuning Recipe

Putting it together for a CPU-bound analytics workload on an 8-core server:

Set max_worker_processes = 16 (headroom for extensions).
Set max_parallel_workers = 8 (one per core).
Keep global max_parallel_workers_per_gather = 2 to protect OLTP latency.
For nightly report sessions, SET max_parallel_workers_per_gather = 6.
Lower parallel_setup_cost / parallel_tuple_cost only after EXPLAIN ANALYZE proves good plans are being rejected.

Always validate with real timings, and pin per-table counts only for stable, well-understood hot tables.

-- Per-session profile for a heavy nightly aggregation job
SET max_parallel_workers_per_gather = 6;
SET parallel_setup_cost = 200;
SET min_parallel_table_scan_size = '4MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT region, date_trunc('day', created_at) AS d, sum(amount)
FROM sales
GROUP BY region, d;

Quick Check: Diagnosing a Worker Shortfall

You run a heavy aggregate query. EXPLAIN ANALYZE shows Workers Planned: 4 but Workers Launched: 1, and execution is barely faster than serial. Which single change is most likely to fix the shortfall?

Recap: Balancing Speedup and Overhead

You now have a mental model for tuning parallel query:

Worker budget is layered: max_parallel_workers_per_gather ≤ max_parallel_workers ≤ max_worker_processes, further scaled by table size.
Costs gate the decision: parallel_setup_cost is the fixed launch tax; parallel_tuple_cost taxes every tuple crossing the Gather — so parallelism wins when workers shrink the data.
Plan-reading is essential: compare Workers Planned vs Workers Launched to tell a planning problem from a runtime-pool problem.
Tune per session, not globally, to protect OLTP latency while letting batch jobs grab cores.

Measure with EXPLAIN ANALYZE and real timings; never trust planner cost alone.

Frequently asked questions

Is the “Tuning Worker Counts and Gather Costs” lesson free?

Yes — the full text of “Tuning Worker Counts and Gather Costs” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Tuning Worker Counts and Gather Costs”?

Adjust parallel worker settings and per-tuple costs to balance speedup against overhead. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Tuning Worker Counts and Gather Costs” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to PostgreSQL Performance & Query Optimization