In PostgreSQL, which settings decide how many parallel workers a query plans and actually gets, and why can it get fewer?
answer
- per-Gather cap and a shared pool
- table size sets planned count
- one more worker per tripling
- some statements never go parallel
basics
~10 smax_parallel_workers_per_gather (default 2) caps workers per Gather node and table size sets the planned count; max_parallel_workers and max_worker_processes (8 each) cap the shared pool, so a busy server launches fewer or none.
solid answer
~40 sAt plan time PostgreSQL considers a parallel plan only if `max_parallel_workers_per_gather` is above zero (default 2) and the scanned table or index reaches `min_parallel_table_scan_size` (8MB) or `min_parallel_index_scan_size` (512kB). It plans one worker at that size and one more each time the size triples, capped by the per-Gather limit, unless the table's `parallel_workers` storage parameter sets the number. The plan must also beat the serial one after `parallel_setup_cost` (1000) and `parallel_tuple_cost` (0.1). At run time the workers are background processes drawn from a pool capped by `max_parallel_workers` and `max_worker_processes`, both 8, the latter changeable only with a restart. If the pool is busy the query gets fewer workers, or the leader runs the parallel part alone. Queries that write or lock rows, cursors and PL/pgSQL FOR loops over a query are never planned in parallel.
code
sql · 9 lines-- request more workers for one large table
ALTER TABLE loyalty_ledger SET (parallel_workers = 4);
-- the per-Gather cap still applies
SET max_parallel_workers_per_gather = 4;
EXPLAIN (ANALYZE)
SELECT date_trunc('month', earned_at) AS month, sum(points)
FROM loyalty_ledger
GROUP BY 1;go deeper
Recall that parallel query is on by default with two workers per Gather, and that workers are separate processes.
Explain the plan-time rules (size thresholds, tripling, the per-Gather cap, cost constants) and the run-time pool limits that can cut the count.
Diagnose planned-versus-launched gaps, size the worker pool with other background workers in mind, and account for per-worker memory.
Decide how much CPU parallel query may take from concurrent OLTP traffic, and give reporting roles a different budget from the application.
## Two separate decisions Parallel query in PostgreSQL is decided twice. At **plan time** the planner decides whether a parallel plan is worth it and how many workers to ask for. At **run time** the executor asks for that many background worker processes and gets what the pool can spare. A plan's worker count is a request, not a reservation. ## Plan time: whether, and how many | setting | default | needs restart | role | |---|---|---|---| | `max_parallel_workers_per_gather` | 2 | no | cap per Gather / Gather Merge node; 0 disables parallel query | | `min_parallel_table_scan_size` | 8MB | no | smaller table scans are not parallelised | | `min_parallel_index_scan_size` | 512kB | no | the same threshold for index scans | | `parallel_setup_cost` | 1000 | no | planner's price for starting workers | | `parallel_tuple_cost` | 0.1 | no | price per row passed from worker to leader | | `parallel_leader_participation` | on | no | the leader also runs the parallel part | | `max_parallel_workers` | 8 | no | cluster-wide cap on parallel workers | | `max_worker_processes` | 8 | **yes** | cap on all background worker processes | The planned count comes from size. At `min_parallel_table_scan_size` the planner plans one worker, and it adds one each time the table is three times larger: about 24MB gives two, 72MB three, 216MB four. The result is then capped by `max_parallel_workers_per_gather`, so with defaults no Gather plans more than two. Setting the table's `parallel_workers` storage parameter with `ALTER TABLE ... SET (parallel_workers = 4)` replaces the size rule for that table, but the per-Gather cap still applies. The parallel plan must also win on cost. `parallel_setup_cost` makes it unattractive for short queries, and `parallel_tuple_cost` penalises plans that send many rows back to the leader. That is why queries that read a lot but return little benefit most. ## When no parallel plan is considered The planner does not produce a parallel plan at all when: - the query **writes data or locks rows**, at the top level or inside a CTE. The exceptions are `CREATE TABLE ... AS`, `SELECT INTO`, `CREATE MATERIALIZED VIEW` and `REFRESH MATERIALIZED VIEW`, which may run their `SELECT` part in parallel; - the query **might be suspended** mid-execution, as with a cursor opened by `DECLARE CURSOR` or a PL/pgSQL `FOR x IN query LOOP`; - the query calls a function marked `PARALLEL UNSAFE`, which is the default label for user-defined functions; - the query already runs **inside another parallel query**; - the server is in single-user mode. ## Run time: why fewer workers arrive 1. **The pool is busy.** Parallel workers come from the processes allowed by `max_worker_processes`, which other background workers such as logical replication workers share, and the subset for parallel query is capped by `max_parallel_workers`. Concurrent parallel queries compete for the same slots. 2. **The client fetches in batches.** If a client sends an Execute message with a non-zero row limit, the plan runs without workers. libpq never does this, but some drivers do when fetching rows in pages. 3. **The leader carries on alone.** With zero workers the leader executes the part below Gather by itself; the query is still correct, just slower, and a plan chosen for three processes may be a poor serial plan. `EXPLAIN (ANALYZE)` reports both the planned and the launched number of workers, so the gap is visible in the plan output. ## Sizing the knobs - **Each worker is a full process.** It has roughly the impact of another session, and memory limits such as `work_mem` apply to each process separately. The manual's example: a query with four workers may use up to five times the CPU, memory and I/O of a serial run. - **Keep the caps consistent.** A `max_parallel_workers` above `max_worker_processes` has no effect, and the latter needs a restart, so plan it with headroom for other background workers. - **OLTP versus reporting.** Short OLTP queries rarely pass the size and cost thresholds anyway; a reporting role may warrant a higher per-Gather cap than the default 2. - **Raise caps together.** Lifting `max_parallel_workers_per_gather` without enough room in `max_parallel_workers` only produces more plans that launch fewer workers than they planned. A monthly points report over a 300MB ledger table, for example, would size to four workers by the tripling rule (216MB is the four-worker step) but plans only two at defaults; raising the per-Gather cap to four for the reporting session lets it plan four, and whether four actually start depends on how many slots the pool has free at that moment.
- A PostgreSQL report plans four workers but EXPLAIN ANALYZE shows one launched. What do you check?The worker pool: compare max_parallel_workers and max_worker_processes with what is running, counting other parallel queries and background workers such as logical replication workers; pg_stat_activity shows each as its own backend type. Either enlarge the pool, where max_worker_processes needs a restart, or lower the per-Gather cap so plans ask for what is usually available.
- Why does a PostgreSQL query read through DECLARE CURSOR never get a parallel plan?A cursor can be suspended between fetches, and the parallel machinery cannot keep workers attached to a query that may pause and resume, so the planner does not generate a parallel plan for it. The same applies to a PL/pgSQL FOR loop over a query. A driver that sends Execute with a row limit gets a similar result at run time: the leader runs the plan alone.
- Does ALTER TABLE ... SET (parallel_workers = 6) guarantee six workers in PostgreSQL?No. It replaces the size-based estimate for that table, but the planner still caps the request at max_parallel_workers_per_gather, and at run time the workers must be free in the pool bounded by max_parallel_workers and max_worker_processes.
- Parallel query is off by default and must be enabled per query.
- max_parallel_workers_per_gather = 4 guarantees four workers at run time.
- PostgreSQL runs parallel workers as threads inside one backend process.
- UPDATE and DELETE scan their target table in parallel like a SELECT.
- work_mem is one budget shared by all workers of a parallel query.