Quand le planificateur choisit des plans parallèles

PostgreSQL does not run every query in parallel. The planner only generates a parallel plan when it believes the extra cost of spinning up workers will be repaid by faster execution.

• Workers cost money: the planner adds startup and tuple-transfer overhead for each one.
• Parallelism only pays off when the table is large enough and the work is CPU- or IO-heavy enough.
• Tiny tables and cheap queries stay sequential — the overhead would dominate.

In this lesson you will learn exactly which thresholds and table sizes flip the planner into a parallel plan.

Three settings gate whether parallelism is even considered before any cost math runs:

• max_parallel_workers_per_gather — max workers a single Gather node may request (default 2). If 0, parallelism is disabled for that query.
• max_parallel_workers — cap across the whole instance (default 8).
• max_worker_processes — the hard OS-level ceiling on background workers (default 8).

If max_parallel_workers_per_gather = 0, the planner never produces a parallel path, regardless of table size.

The single most important table-size trigger is min_parallel_table_scan_size (default 8MB). A relation must be at least this large before the planner even considers a parallel sequential scan over it.

• For indexes, the analogous setting is min_parallel_index_scan_size (default 512kB).
• Below the threshold, the relation is treated as too small to bother parallelizing.

So a 6MB table will almost never get a parallel scan, while a 1GB table easily clears the bar.

The number of workers is not fixed — it grows logarithmically with table size. Each time the relation triples past the threshold, the planner allows one more worker (capped by max_parallel_workers_per_gather).

• 8MB to 24MB: up to 1 worker
• 24MB to 72MB: up to 2 workers
• 72MB to 216MB: up to 3 workers
• Each step multiplies the previous boundary by 3.

This is why doubling a small table rarely changes the worker count, but going from 10MB to 1GB does.

Once a parallel path is possible, the planner weighs two cost penalties against the sequential plan:

• parallel_setup_cost (default 1000) — a one-time charge for launching workers and tearing down the shared memory. This is large, which is why tiny queries stay sequential.
• parallel_tuple_cost (default 0.1) — charged per tuple passed from a worker up through the Gather node to the leader.

A parallel plan wins only when divided per-worker scan cost minus these penalties beats the sequential cost.

You confirm a parallel plan by looking for a Gather (or Gather Merge) node and Parallel-prefixed scan nodes underneath it.

• Gather collects rows from workers; Workers Planned shows how many were requested.
• Nodes below the Gather (e.g. Parallel Seq Scan) run once per worker plus the leader.
• Row counts under the Gather are per-worker estimates, not the total.

To observe a parallel plan on a smaller table during testing, lower the gating thresholds at the session level. This does not change the data — only the planner's willingness to parallelize.

• Set min_parallel_table_scan_size = 0 to remove the size gate.
• Set parallel_setup_cost = 0 and parallel_tuple_cost = 0 to remove the cost penalties.
• Set force_parallel_mode = on (renamed debug_parallel_query in PG16+) to force a parallel path where one exists.

These are diagnostic knobs — never leave them on in production.

Not every operation can run inside a parallel worker. The planner classifies pieces of the query:

• Parallel-safe — can run in a worker (most immutable/stable functions, plain scans).
• Parallel-restricted — must run only in the leader (e.g. references to temporary tables, correlated subplans, some CTEs).
• Parallel-unsafe — disables parallelism for the whole query (e.g. functions marked PARALLEL UNSAFE, writing CTEs, sequence calls).

A single VOLATILE or PARALLEL UNSAFE function in the query can suppress an otherwise-parallel plan.

Parallelism is not limited to scans. Once a driving relation is scanned in parallel, the planner can push joins and aggregation into the workers:

• Parallel Hash Join — workers cooperatively build one shared hash table.
• Parallel Nested Loop / Merge Join — each worker joins its slice of the outer relation.
• Partial Aggregate — each worker aggregates its rows, then a Finalize Aggregate above the Gather combines the partial results.

This is why a large GROUP BY over a big fact table is a classic parallel-plan candidate.

Even on a huge table, several conditions block a parallel plan entirely:

• The query is run inside a cursor/FETCH or with SELECT ... FOR UPDATE row locking.
• It is a data-modifying statement on PG versions before 11 (later versions allow parallel INSERT ... SELECT / index builds, but not parallel UPDATE/DELETE execution).
• It calls a PARALLEL UNSAFE function or runs inside a function that has already claimed all workers.
• max_parallel_workers_per_gather = 0 for the session.

When a plan you expect to be parallel is not, check these before tuning costs.

For the planner to pick a parallel plan, all of these must hold:

• Relation size at least min_parallel_table_scan_size (8MB) for scans, or the index size threshold.
• max_parallel_workers_per_gather > 0 and instance-wide worker budget available.
• The query contains only parallel-safe (or at worst parallel-restricted) elements.
• The estimated parallel cost, after parallel_setup_cost and parallel_tuple_cost, beats the best sequential plan.

Miss any one and you get a sequential plan — usually for a good reason.

Test your understanding of the parallel-plan triggers.

Key takeaways on when PostgreSQL chooses a parallel plan:

• Parallelism is a cost decision gated first by size, then by cost math.
• min_parallel_table_scan_size (8MB) is the primary table-size trigger; min_parallel_index_scan_size (512kB) covers indexes.
• Worker count grows logarithmically — one more worker each time size triples — capped by max_parallel_workers_per_gather.
• parallel_setup_cost (1000) and parallel_tuple_cost (0.1) must be outweighed by the per-worker speedup.
• Parallel-unsafe functions, row locking, and a zero worker setting can disable parallelism even on huge tables.
• Confirm plans by looking for Gather / Partial Aggregate nodes in EXPLAIN.