Parallel Query and JIT

This VM has exactly one CPU. Find out, with real measurements, whether parallel query and JIT compilation still do anything on it.

Parallel query lets PostgreSQL split a large scan or aggregate across multiple background worker processes, each handling a slice of the table, with a Gather node combining their results back into one stream. It is a genuine capability, not a simulation — real worker processes launch and do real work. But it is not free: starting a worker, sending it a slice of work, and merging the results back all cost something, and the planner only chooses a parallel plan when its cost model expects that overhead to be worth paying. On a server with a single physical CPU, "worth paying" can come out negative — the workers still launch and still do real work, they just have to take turns on the one CPU that exists, and the coordination overhead is pure loss with no true concurrency underneath it.\n\nJIT compilation is a different kind of question entirely: not whether it helps, but whether it can run at all. jit_provider defaults to llvmjit whether or not the underlying library is actually installed — a configured GUC value is not proof that the feature is functional, and the only way to know for certain is to check the filesystem directly.

Raising the Limit Alone Changes Nothing

SET max_parallel_workers_per_gather = 2;
EXPLAIN (ANALYZE, TIMING OFF) SELECT val, count(*) FROM big_a GROUP BY val;

The plan comes back identical to max_parallel_workers_per_gather = 0: a plain HashAggregate over a Seq Scan, no Gather node anywhere. Allowing more workers does not force the planner to use them — it still has to judge the parallel version worth the coordination overhead, using its normal cost model.

Forcing a Real Parallel Plan

SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;
EXPLAIN (ANALYZE, TIMING OFF) SELECT val, count(*) FROM big_a GROUP BY val;

HashAggregate  (cost=680.33..1180.33 rows=50000 width=12) (actual rows=50000.00 loops=1)
  ->  Gather  (cost=0.00..430.33 rows=50000 width=4) (actual rows=50000.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Parallel Seq Scan on big_a  (actual rows=16666.67 loops=3)
Execution Time: 1905.923 ms

With the overhead costs zeroed out to force the planner's hand, this is a genuine parallel plan — two real background workers launch (loops=3 counts the leader too), each scanning roughly a third of the table.

The Honest Measurement

| Plan | Execution Time | |------|-----------------| | Serial (max_parallel_workers_per_gather=0) | 1171.199 ms | | Forced parallel, 2 workers launched | 1905.923 ms |

Slower, not faster — on this single-vCPU VM, two worker processes and a leader are all sharing one physical core, so "parallel" here means taking turns with added coordination overhead, not simultaneous execution. This is exactly why the planner does not choose this plan on its own: its cost model is not being fooled, it is correctly avoiding overhead that would not pay off with the CPU resources actually available.

A Table Too Small to Bother With

SET max_parallel_workers_per_gather = 4;
EXPLAIN SELECT val, count(*) FROM small_agg GROUP BY val;

Even with four workers allowed, a 100-row table produces a plain serial plan — no Gather at all. parallel_tuple_cost and the other parallel cost GUCs correctly recognize that a table this small is not worth splitting up under any circumstances.

JIT: Configured Is Not the Same as Functional

SHOW jit_provider;        -- llvmjit
SET jit = on;
SET jit_above_cost = 0;   -- force JIT to be considered for even a cheap query
EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM big_a;

No JIT: section appears anywhere in the output, no matter how aggressively the cost thresholds are lowered. find / -iname '*llvmjit*' confirms why: zero results. The shared library JIT compilation actually depends on is not present on this image — jit_provider = llvmjit is simply the compiled-in default value of a GUC, not evidence the feature can run.

Gather / Gather Merge

The plan node where a parallel query's worker processes hand their partial results back to the leader process to be combined into a single output stream. Gather Merge additionally preserves a sort order across workers; plain Gather does not guarantee any particular order. "Workers Planned" is what the planner intended; "Workers Launched" is how many the operating system actually started at execution time — the two can differ if background worker slots are unavailable.

JIT (Just-In-Time compilation)

PostgreSQL can compile expressions and tuple deforming code for a specific query into native machine code at execution time, via LLVM, instead of using the normal generic interpreted evaluation — worthwhile only for expensive, long-running queries, which is why it only activates above jit_above_cost by default. It requires the llvmjit shared library to actually be present and loadable; jit_provider naming "llvmjit" is only the configured default, not proof that library exists on a given install.

📏 Measure the Honest Serial Baseline

Disable parallel workers entirely and measure the real serial execution time for a GROUP BY over all 50,000 rows of big_a.

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

student@lab:~$ psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 0;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT val, count(*) FROM big_a GROUP BY val;" SET SET QUERY PLAN -------------------------------------------------------------------------------------------------- HashAggregate (cost=972.00..1472.00 rows=50000 width=12) (actual rows=50000.00 loops=1) Group Key: val Batches: 1 Memory Usage: 3089kB Buffers: shared hit=222 -> Seq Scan on big_a (cost=0.00..722.00 rows=50000 width=4) (actual rows=50000.00 loops=1) Buffers: shared hit=222 Planning: Buffers: shared hit=65 read=4 Planning Time: 14.962 ms Execution Time: 1171.199 ms (10 rows)

🤔 Raise the Worker Limit — and Get Nothing

Allow up to 2 parallel workers and rerun the identical query, to see that the plan does not actually change.

psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 2;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT val, count(*) FROM big_a GROUP BY val;"

student@lab:~$ psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 2;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT val, count(*) FROM big_a GROUP BY val;" SET SET QUERY PLAN -------------------------------------------------------------------------------------------------- HashAggregate (cost=972.00..1472.00 rows=50000 width=12) (actual rows=50000.00 loops=1) Group Key: val Batches: 1 Memory Usage: 3089kB Buffers: shared hit=222 -> Seq Scan on big_a (cost=0.00..722.00 rows=50000 width=4) (actual rows=50000.00 loops=1) Buffers: shared hit=222 Planning: Buffers: shared hit=69 Planning Time: 13.886 ms Execution Time: 992.162 ms (10 rows)

⚙️ Force a Real Parallel Plan and Measure It Honestly

Zero out the parallel overhead cost GUCs to force a genuine parallel plan, and measure it against the serial baseline.

psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 2;" -c "SET parallel_setup_cost = 0;" -c "SET parallel_tuple_cost = 0;" -c "SET min_parallel_table_scan_size = 0;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT val, count(*) FROM big_a GROUP BY val;"

student@lab:~$ psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 2;" -c "SET parallel_setup_cost = 0;" -c "SET parallel_tuple_cost = 0;" -c "SET min_parallel_table_scan_size = 0;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT val, count(*) FROM big_a GROUP BY val;" SET SET SET SET QUERY PLAN --------------------------------------------------------------------------------------------------------------- HashAggregate (cost=680.33..1180.33 rows=50000 width=12) (actual rows=50000.00 loops=1) Group Key: val Batches: 1 Memory Usage: 3089kB Buffers: shared hit=222 -> Gather (cost=0.00..430.33 rows=50000 width=4) (actual rows=50000.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=222 -> Parallel Seq Scan on big_a (cost=0.00..430.33 rows=20833 width=4) (actual rows=16666.67 loops=3) Buffers: shared hit=222 Planning: Buffers: shared hit=67 read=5 Planning Time: 30.741 ms Execution Time: 1905.923 ms (14 rows)

🪶 A Table Too Small to Parallelize

Allow 4 parallel workers and run the same kind of query against a 100-row table, to see the planner correctly decline to bother.

psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 4;" -c "EXPLAIN SELECT val, count(*) FROM small_agg GROUP BY val;"

student@lab:~$ psql -U postgres -d beer_db -c "SET max_parallel_workers_per_gather = 4;" -c "EXPLAIN SELECT val, count(*) FROM small_agg GROUP BY val;" SET SET QUERY PLAN ----------------------------------------------------------------- HashAggregate (cost=2.50..3.50 rows=100 width=12) Group Key: val -> Seq Scan on small_agg (cost=0.00..2.00 rows=100 width=4) (3 rows)

🔍 Check Whether JIT Actually Works, Rather Than Assuming It

Confirm jit_provider is configured, force JIT to be considered for even a cheap query, and check the filesystem directly for the library it depends on.

psql -U postgres -d beer_db -c "SHOW jit_provider;" -c "SET jit = on;" -c "SET jit_above_cost = 0;" -c "SET jit_optimize_above_cost = 0;" -c "SET jit_inline_above_cost = 0;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM big_a;"

find / -iname '*llvmjit*' 2>/dev/null; echo "search complete"

student@lab:~$ psql -U postgres -d beer_db -c "SHOW jit_provider;" -c "SET jit = on;" -c "SET jit_above_cost = 0;" -c "SET jit_optimize_above_cost = 0;" -c "SET jit_inline_above_cost = 0;" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM big_a;" SET jit_provider -------------- llvmjit (1 row) SET SET SET SET QUERY PLAN -------------------------------------------------------------------------------------------------- Aggregate (cost=847.00..847.01 rows=1 width=8) (actual rows=1.00 loops=1) Buffers: shared hit=222 -> Seq Scan on big_a (cost=0.00..722.00 rows=50000 width=0) (actual rows=50000.00 loops=1) Buffers: shared hit=222 Planning: Buffers: shared hit=57 read=1 Planning Time: 12.361 ms Execution Time: 201.339 ms (8 rows) student@lab:~$ find / -iname '*llvmjit*' 2>/dev/null; echo "search complete" search complete

Lab 3.1.5 complete — and Block 3.1 complete. Parallel query and JIT, measured honestly rather than assumed:\n\n\n Serial baseline measured : ✅ 1171.199 ms\n Raising worker limit alone : ✅ no plan change — cost model unconvinced\n Forced parallel plan measured : ✅ real Gather, 2 workers, 1905.923 ms — slower here\n Small table correctly stays serial: ✅ 100 rows, no Gather even with 4 workers allowed\n JIT checked, not assumed : ✅ llvmjit configured, library absent, confirmed via find\n

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