Parallel query and JIT compilation

conceptThe concept

A parallel plan has a Gather or Gather Merge node. Below it, the leader and up to N workers each run the same plan fragment over a share of the data (Parallel Seq Scan, Parallel Index Scan, Parallel Hash Join), and aggregates are split into Partial (per worker) and Finalize (in the leader).

The plan says Workers Planned; EXPLAIN ANALYZE also says Workers Launched. If the pool is exhausted at run time, fewer workers start and the query silently runs slower. Queries do not go parallel when they write (except CREATE TABLE AS, CREATE INDEX, REFRESH MATERIALIZED VIEW), use a cursor, lock rows, or call a function marked PARALLEL UNSAFE (the default for user functions).

JIT compiles WHERE expressions, projections and tuple deforming to native code. It is on by default and triggers when the plan cost exceeds jit_above_cost (100000), with inlining and optimisation above 500000. EXPLAIN ANALYZE shows a JIT: block with the time spent compiling.

whyWhy it matters

A reporting query over 50 million rows can run four times faster with four workers. The same machinery can also starve OLTP traffic of CPU, and JIT can add 50 to 200 ms of compilation to a query that executes in 5 ms when a bad estimate pushes its cost over the threshold. Both show up as "the query is slow sometimes".

Use caseReal-world scenario

A mixed workload server keeps max_parallel_workers_per_gather = 2 globally, gives the analytics role 4 with ALTER ROLE analytics SET max_parallel_workers_per_gather = 4, and turns JIT off for the OLTP application role after seeing JIT blocks in auto_explain output for its sub-10 ms queries.

Try itExamples

Run and edit these in the interactive lesson.

SELECT name, setting
FROM pg_settings
WHERE name IN ('max_worker_processes', 'max_parallel_workers', 'max_parallel_workers_per_gather',
               'min_parallel_table_scan_size', 'parallel_setup_cost', 'jit', 'jit_above_cost')
ORDER BY name;

BEGIN;
SET LOCAL max_parallel_workers_per_gather = 4;
SET LOCAL parallel_setup_cost = 0;
SET LOCAL parallel_tuple_cost = 0;
SET LOCAL min_parallel_table_scan_size = 0;
EXPLAIN (COSTS OFF)
SELECT kind, count(*) FROM events GROUP BY kind;
ROLLBACK;

BEGIN;
SET LOCAL max_parallel_workers_per_gather = 4;
SET LOCAL parallel_setup_cost = 0;
SET LOCAL parallel_tuple_cost = 0;
SET LOCAL min_parallel_table_scan_size = 0;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events;
ROLLBACK;

SELECT DISTINCT proname, proparallel
FROM pg_proc
WHERE proname IN ('lower', 'now', 'random', 'nextval', 'setval')
ORDER BY proname;

Over 200 ms was spent compiling. On an 800 ms query that may pay off; on a 20 ms query it is pure overhead.

EXPLAIN (ANALYZE) SELECT sum(total * 1.2) FROM orders_archive WHERE created_at < '2024-01-01';

 Finalize Aggregate  (actual time=812.4..815.1 rows=1 loops=1)
   ->  Gather  (actual time=811.9..815.0 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ...
 JIT:
   Functions: 11
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 2.1 ms, Inlining 41.7 ms, Optimization 96.3 ms, Emission 70.8 ms, Total 210.9 ms

ALTER ROLE analytics SET max_parallel_workers_per_gather = 4;
ALTER ROLE app_oltp SET jit = off;
ALTER TABLE events SET (parallel_workers = 6);
CREATE FUNCTION net_price(p numeric) RETURNS numeric
  LANGUAGE sql IMMUTABLE PARALLEL SAFE
  RETURN round(p / 1.2, 2);

internalsUnder the hood

Workers are separate processes started through the background worker machinery; they attach to a dynamic shared memory segment with the plan, parameters and a snapshot, then return tuples to the leader through shared memory queues. The leader also executes the plan unless parallel_leader_participation = off. The planner picks the worker count from the table size: one more worker each time the table is three times larger than min_parallel_table_scan_size, capped by the per-gather setting or the table's parallel_workers storage parameter. Function safety is proparallel in pg_proc: s (safe), r (restricted, leader only) or u (unsafe).

Avoid / preferDo this, not that

-- OLTP server, every query
SET max_parallel_workers_per_gather = 16;

-- keep the global default small
ALTER ROLE analytics SET max_parallel_workers_per_gather = 4;

CREATE FUNCTION tax(p numeric) RETURNS numeric LANGUAGE sql AS $$ SELECT p * 0.2 $$;
SELECT sum(tax(total)) FROM orders;  -- never parallel

CREATE FUNCTION tax(p numeric) RETURNS numeric
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$ SELECT p * 0.2 $$;

PitfallsCommon mistakes

• Raising max_parallel_workers_per_gather without raising max_worker_processes and max_parallel_workers, so workers are planned but not launched.
• Marking a function that writes or uses sequences as PARALLEL SAFE, which can give wrong results or errors.
• Blaming the query when an OLTP latency spike is really JIT compilation triggered by a bad row estimate.
• Expecting INSERT ... SELECT or UPDATE to use parallel workers.

LimitsWhen not to use it

Turn parallelism down or off for latency-critical OLTP roles and for servers already CPU-bound; it spends more total CPU to finish one query sooner. Turn JIT off for workloads of short queries, where compilation time is rarely recovered.

SecuritySecurity notes

Parallel workers run as the same role with the same snapshot and privileges as the leader, so they do not change who can see what. The risk is resource exhaustion: an untrusted role running large parallel queries can starve others, so set per-role limits and statement_timeout for reporting users.

ChallengeTry it yourself

BEGIN;
SET LOCAL max_parallel_workers_per_gather = 2;
SET LOCAL parallel_setup_cost = 0;
SET LOCAL parallel_tuple_cost = 0;
SET LOCAL min_parallel_table_scan_size = 0;
EXPLAIN (COSTS OFF)
SELECT status, sum(total) FROM orders GROUP BY status;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• pg_stat_statements
• auto_explain