Tuning Worker Counts and Gather Costs
Adjust parallel worker settings and per-tuple costs to balance speedup against overhead.
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 ordersAll lessons in this course
- When the Planner Chooses Parallel Plans
- Tuning Worker Counts and Gather Costs
- Parallel Aggregation and Hash Joins
- Diagnosing Why Parallelism Was Disabled