Parallel Query Workers Not Scaling (Gather Bottleneck)

Scenario

A data engineering team configures max_parallel_workers_per_gather = 4 expecting a 4× speedup on a large aggregation. After the change, EXPLAIN ANALYZE on their query shows Workers Planned: 4 but Workers Launched: 1, and the query runs no faster than before. The table is 200 MB, well above what they think is the threshold. The query calls a custom UDF that was not explicitly marked as parallel-safe.

How to Identify

Conditions:

• EXPLAIN ANALYZE shows a Gather (or Gather Merge) node with Workers Planned: N but Workers Launched: M where M < N
• Query performance does not improve despite increasing max_parallel_workers_per_gather
• Table may be smaller than min_parallel_table_scan_size (default 8 MB) — parallel plan not triggered
• A PARALLEL UNSAFE function anywhere in the query silently disables all parallelism
• max_worker_processes or max_parallel_workers is exhausted by other queries

Analysis Steps

-- 1. Check EXPLAIN output for Workers Planned vs Workers Launched
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, FORMAT TEXT)
SELECT region, COUNT(*), SUM(amount)
FROM   large_fact_table
GROUP  BY region;

-- 2. Check all parallel configuration parameters
SELECT name, setting
FROM   pg_settings
WHERE  name IN (
    'max_parallel_workers_per_gather',
    'max_parallel_workers',
    'max_worker_processes',
    'min_parallel_table_scan_size',
    'min_parallel_index_scan_size',
    'parallel_setup_cost',
    'parallel_tuple_cost'
)
ORDER BY name;

-- 3. Check if any function in the query is PARALLEL UNSAFE
SELECT proname, proparallel,
       CASE proparallel WHEN 's' THEN 'SAFE'
                        WHEN 'r' THEN 'RESTRICTED'
                        WHEN 'u' THEN 'UNSAFE' END AS safety
FROM   pg_proc
WHERE  pronamespace = 'public'::regnamespace
  AND  proparallel != 's'
ORDER  BY proname;

-- 4. Check table size vs min_parallel_table_scan_size
SELECT relname,
       pg_size_pretty(pg_relation_size(oid)) AS table_size,
       pg_relation_size(oid)                 AS size_bytes
FROM   pg_class
WHERE  relname = 'large_fact_table';

-- 5. Check per-table parallel_workers storage parameter
SELECT relname, reloptions
FROM   pg_class
WHERE  relname = 'large_fact_table';

Pitfalls

• A single PARALLEL UNSAFE function anywhere in the query (including in WHERE, SELECT, ORDER BY) disables parallelism for the entire query — no warning is issued
• Each parallel worker gets its own work_mem allocation; with 4 workers, peak memory = 4× work_mem plus the leader process
• Setting max_parallel_workers_per_gather higher than physical CPU cores causes OS context-switching that degrades performance
• min_parallel_table_scan_size (default 8 MB) prevents parallel plans on small tables even if workers are available
• Partitioned tables require enable_partitionwise_aggregate = on to run each partition in a separate worker effectively

Resolution Approach

When workers are planned but not launched, address causes in this order: