Parallel Query Tuning

The curriculum imagines 16 idle CPU cores. This VM has exactly one, confirmed directly. Tune parallel query anyway — and measure the honest result.

Three GUCs work together to bound parallel query: max_worker_processes caps the total background worker processes PostgreSQL may ever launch for any purpose, max_parallel_workers caps how many of those may be used for parallel query specifically, and max_parallel_workers_per_gather caps how many any single query's Gather node may request. All three already default to real, sane values on this server (8, 8, and 2), which this lab checks directly rather than assuming.\n\nThe real constraint this lab has to be honest about is not any GUC at all — it is that nproc on this VM reports exactly 1, confirmed back in Certificate 3's very first block. A parallel plan can still genuinely build here — real worker processes really launch, real work really gets divided — but with only one physical core to actually run on, that division has nothing real to parallelize onto, and the coordination overhead becomes a net cost rather than a net win, exactly as measured directly back in Lesson 3.1.5. This lab re-confirms that finding on a different query shape, and adds the curriculum's other real, checkable claim: a genuinely small table declines parallelism entirely, regardless of hardware.

The Three GUCs, Checked Together

SHOW max_worker_processes;          -- 8
SHOW max_parallel_workers;          -- 8
SHOW max_parallel_workers_per_gather; -- 2

All three already at real, sane defaults on this server — max_worker_processes bounds the total background workers PostgreSQL may ever launch for anything; max_parallel_workers bounds how many of those go to parallel query; max_parallel_workers_per_gather bounds any one query's own request.

A Real Parallel Plan, Measured Honestly

SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, TIMING OFF) SELECT category, sum(val) FROM sql_parallel_demo GROUP BY category;
-- HashAggregate + Seq Scan, Execution Time: 1619.553 ms

SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 0; SET parallel_tuple_cost = 0;  -- forced, diagnostic only
EXPLAIN (ANALYZE, TIMING OFF) SELECT category, sum(val) FROM sql_parallel_demo GROUP BY category;

Finalize GroupAggregate
  ->  Gather Merge
        Workers Planned: 1
        Workers Launched: 1
        ->  Sort
              ->  Partial HashAggregate
                    ->  Parallel Seq Scan on sql_parallel_demo
Execution Time: 1767.936 ms

A genuine parallel plan — a real worker process launches and does real, divided work. And it is slower, not faster, than the serial version: 1767.936 ms versus 1619.553 ms. This is the same honest single-vCPU finding already measured directly in Lesson 3.1.5, confirmed again here on a different query shape — with only one real physical core, the coordination overhead of splitting work across processes has no genuine concurrency underneath it to pay that cost back.

A Genuinely Small Table, Correctly Declining

SET max_parallel_workers_per_gather = 4;
EXPLAIN SELECT sum(val) FROM sql_parallel_small;  -- 50 rows

Aggregate
  ->  Seq Scan on sql_parallel_small  (cost=0.00..1.50 rows=50 width=12)

Still fully serial, even with 4 workers allowed — parallel_tuple_cost and the other parallel cost GUCs correctly recognize that a 50-row table is nowhere near large enough to be worth the coordination overhead, on any hardware.

parallel_setup_cost / parallel_tuple_cost

Cost constants the planner adds to account for the real overhead of launching parallel workers (parallel_setup_cost) and passing each row back through the Gather node (parallel_tuple_cost). Zeroing them out, as this lab does, removes that overhead from the planner's calculation entirely — a diagnostic technique for forcing a parallel plan to appear so it can be inspected and measured, never a setting to leave changed on a production server, since it would make the planner blind to the coordination overhead that genuinely exists.

Workers Planned vs Workers Launched

Workers Planned is how many parallel workers the planner intended to use for a given Gather node; Workers Launched is how many the operating system actually started at execution time. The two can differ if background worker slots are unavailable at that moment — checking both is how to confirm a parallel plan is not just theoretically chosen but genuinely executed with real worker processes.

🔧 Check the Parallel Query GUCs in Concert

Check the three GUCs that together bound parallel query on this server.

psql -U postgres -d beer_db -c "SHOW max_worker_processes;" -c "SHOW max_parallel_workers;" -c "SHOW max_parallel_workers_per_gather;" -c "SHOW parallel_setup_cost;" -c "SHOW parallel_tuple_cost;"

student@lab:~$ psql -U postgres -d beer_db -c "SHOW max_worker_processes;" -c "SHOW max_parallel_workers;" -c "SHOW max_parallel_workers_per_gather;" -c "SHOW parallel_setup_cost;" -c "SHOW parallel_tuple_cost;" SET max_worker_processes ----------------------- 8 (1 row) max_parallel_workers ------------------------ 8 (1 row) max_parallel_workers_per_gather ------------------------------------ 2 (1 row) parallel_setup_cost ----------------------- 1000 (1 row) parallel_tuple_cost ----------------------- 0.1 (1 row)

🧵 Force a Real Parallel Plan and Measure It Honestly

Measure a real serial baseline, then force a real parallel plan on the same query and compare honestly.

psql -U postgres -d beer_db -c "CREATE TABLE sql_parallel_demo (id int PRIMARY KEY, category int, val numeric);" -c "INSERT INTO sql_parallel_demo SELECT g, g%20, random()*1000 FROM generate_series(1,300000) g;" -c "ANALYZE sql_parallel_demo;"

psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 0;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT category, sum(val) FROM sql_parallel_demo GROUP BY category;"

psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 4;" -c "SET parallel_setup_cost = 0;" -c "SET parallel_tuple_cost = 0;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT category, sum(val) FROM sql_parallel_demo GROUP BY category;"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_parallel_demo (id int PRIMARY KEY, category int, val numeric);" -c "INSERT INTO sql_parallel_demo SELECT g, g%20, random()*1000 FROM generate_series(1,300000) g;" -c "ANALYZE sql_parallel_demo;" SET CREATE TABLE INSERT 0 300000 ANALYZE student@lab:~$ psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 0;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT category, sum(val) FROM sql_parallel_demo GROUP BY category;" SET SET QUERY PLAN ---------------------------------------------------------------------------------------------------------------- HashAggregate (cost=6281.00..6281.25 rows=20 width=36) (actual rows=20.00 loops=1) Group Key: category Batches: 1 Memory Usage: 32kB Buffers: shared hit=1781 -> Seq Scan on sql_parallel_demo (cost=0.00..4781.00 rows=300000 width=15) (actual rows=300000.00 loops=1) Buffers: shared hit=1781 Planning: Buffers: shared hit=69 read=9 Planning Time: 12.797 ms Execution Time: 1619.553 ms (10 rows) student@lab:~$ psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 4;" -c "SET parallel_setup_cost = 0;" -c "SET parallel_tuple_cost = 0;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT category, sum(val) FROM sql_parallel_demo GROUP BY category;" SET SET SET SET QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=4428.75..4429.56 rows=20 width=36) (actual rows=20.00 loops=1) Group Key: category Buffers: shared hit=1789 -> Gather Merge (cost=4428.75..4429.06 rows=34 width=36) (actual rows=40.00 loops=1) Workers Planned: 1 Workers Launched: 1 Buffers: shared hit=1789 -> Sort (cost=4428.74..4428.79 rows=20 width=36) (actual rows=20.00 loops=2) Sort Key: category Sort Method: quicksort Memory: 18kB Buffers: shared hit=1789 Worker 0: Sort Method: quicksort Memory: 18kB -> Partial HashAggregate (cost=4428.06..4428.31 rows=20 width=36) (actual rows=20.00 loops=2) Group Key: category Batches: 1 Memory Usage: 32kB Buffers: shared hit=1781 Worker 0: Batches: 1 Memory Usage: 32kB -> Parallel Seq Scan on sql_parallel_demo (cost=0.00..3545.71 rows=176471 width=15) (actual rows=150000.00 loops=2) Buffers: shared hit=1781 Planning: Buffers: shared hit=89 read=1 Planning Time: 9.419 ms Execution Time: 1767.936 ms (2 rows)

🪶 Confirm a Genuinely Small Table Declines Parallelism

Allow generous parallel workers and run an aggregate on a genuinely tiny table, confirming it correctly stays serial.

psql -U postgres -d beer_db -c "CREATE TABLE sql_parallel_small (id int PRIMARY KEY, val numeric);" -c "INSERT INTO sql_parallel_small SELECT g, random()*100 FROM generate_series(1,50) g;" -c "ANALYZE sql_parallel_small;"

psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 4;" -c "EXPLAIN SELECT sum(val) FROM sql_parallel_small;"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_parallel_small (id int PRIMARY KEY, val numeric);" -c "INSERT INTO sql_parallel_small SELECT g, random()*100 FROM generate_series(1,50) g;" -c "ANALYZE sql_parallel_small;" SET CREATE TABLE INSERT 0 50 ANALYZE student@lab:~$ psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 4;" -c "EXPLAIN SELECT sum(val) FROM sql_parallel_small;" SET SET QUERY PLAN ---------------------------------------------------------------------- Aggregate (cost=1.63..1.64 rows=1 width=32) -> Seq Scan on sql_parallel_small (cost=0.00..1.50 rows=50 width=12) (2 rows)

Lab 3.5.3 complete. Parallel query tuning, measured honestly on this VM's real single core:\n\n\n Parallel GUCs checked in concert : ✅ 8 / 8 / 2, already sensible\n Forced parallel plan, measured : ✅ real worker, genuinely slower here\n Small table declines parallelism : ✅ confirmed, any hardware\n

Enable JavaScript to run the live terminal and track your progress.