When tuning PostgreSQL parallel queries, it is tempting to let smaller tables use parallel scans in the hope of making them faster. Two settings with “minimum size” in their names seem like the obvious place to start.

Christophe Pettus examines a less obvious consequence in his discussion of parallel scan settings: lowering a threshold can change the requested worker count as well as which scans qualify.

How do you distinguish a query that cannot get parallel workers from one that requests too many? PostgreSQL 18 documentation and implementation provide a useful path from scan size to the processes that actually run.

Scan eligibility depends on how much data will be read

min_parallel_table_scan_size and min_parallel_index_scan_size set the minimum table and index scan amounts considered for parallel execution. Their defaults are 8MB and 512kB, respectively. The PostgreSQL 18 configuration reference describes both.

A sequential scan covers the table, whereas an index scan uses an estimate of the pages its conditions will touch. A large index can therefore remain below the threshold for a narrow lookup.

Values without units mean blocks. With standard 8kB blocks, 8 means 64kB, not 8MB. Keep the units visible when comparing settings.

Passing the size threshold only admits a candidate. Worker estimation and cost comparison still stand between that candidate and the chosen plan.

Lower thresholds also change the threefold worker calculation

For an ordinary standalone table scan without an explicit parallel_workers table setting, the minimum size also anchors the worker calculation. PostgreSQL 18.0 implements this in compute_parallel_worker: start with one worker and add one whenever the scan amount grows by another factor of three.

An 8MB threshold gives boundaries of 8MB, 24MB, 72MB, and 216MB for one through four workers. For a hypothetical 128MB scan, before applying the worker cap:

These are calculations, not measured query plans. The default max_parallel_workers_per_gather is 2, which caps the three- and four-worker results at two.

Setting the threshold to 0 leaves a one-block starting point. With 8kB blocks, the same 128MB scan calculates nine workers before the cap. A change intended for small queries can therefore affect larger scans too.

There are exceptions: an explicit parallel_workers replaces the size calculation, and inheritance or partition children can receive parallel paths below the minimum. Do not treat this table as a prediction for an entire complex plan.

The scan method determines which threshold applies

An ordinary parallel index scan uses both size conditions and takes the smaller of the table-based and index-based worker counts.

An Index Only Scan skips the heap-side check in this calculation, so a small estimated heap access does not by itself rule out parallelism. This branch appears in the cost_index implementation.

A Bitmap Heap Scan reverses the emphasis: one process builds the bitmap from indexes, then processes share the heap scan. The table threshold governs that parallel part. PostgreSQL 18 also supports parallel Index Scan and Index Only Scan only for B-tree indexes, as documented under parallel scans.

Using an index does not automatically make the index threshold the right setting to change. Identify the scan node first.

Use EXPLAIN to separate size, cost, and worker availability

If size permits parallelism but the plan remains serial, examine cost next. parallel_setup_cost models worker startup; parallel_tuple_cost models transferring rows between processes. The official parallel plan tips warn that reducing these costs can produce a parallel plan that runs slower.

First, inspect the target session:

-- Inspect scan thresholds and planning/runtime worker limits.
SHOW min_parallel_table_scan_size;
SHOW min_parallel_index_scan_size;
SHOW max_parallel_workers_per_gather;
SHOW max_parallel_workers;
SHOW max_worker_processes;

Use EXPLAIN on the target SELECT to inspect Gather or Gather Merge and Workers Planned. Planned workers are requests, not reservations: shared worker capacity may be unavailable at execution time. The parallel query execution model explains why requesting too few workers and failing to launch them are different problems.

In a suitable test environment, use EXPLAIN (ANALYZE, BUFFERS, VERBOSE) on the SELECT to compare Workers Launched, execution time, and buffer activity. The ANALYZE option actually executes the query.

Compare one candidate threshold at a time while holding other settings constant. SET LOCAL confines a change to the current transaction. Judge the result by runtime and CPU/I/O load under representative concurrency, not just the planned worker count.

Check index maintenance before changing global defaults

These settings also affect maintenance. The standard CREATE INDEX worker calculation uses the table threshold; plan_create_index_workers additionally applies maintenance worker and memory constraints.

Raising the table threshold to suppress parallel queries can consequently affect parallel index builds. VACUUM uses the index threshold too, but compares it with the size of the index itself rather than a query-specific scan estimate.

Minimum scan sizes are both admission thresholds and scales for requesting workers. Determine whether the unexpected behavior comes from size eligibility, cost selection, or failure to launch the planned workers before choosing a setting to change.

Measuring a change within the relevant session or operation makes it easier to preserve the intended behavior of both queries and maintenance.

Source

• Title: Christophe Pettus: All Your GUCs in a Row: min_parallel_index_scan_size and min_parallel_table_scan_size
• URL: https://postgr.es/p/9wW