Aggregazioni parallele e hash join

Heavy grouping queries such as GROUP BY over hundreds of millions of rows are usually CPU-bound: most time is spent hashing keys and combining values, not waiting on I/O.

A single backend process can only saturate one core. PostgreSQL's parallel query machinery lets the planner split the scan and the aggregation across several parallel workers, each running on its own core, then combine their results.

• The launching backend is the leader.
• Extra processes are parallel workers.
• Work is divided at the table-scan level and merged at the top.

This lesson focuses on two cooperating pieces: parallel aggregation (partial aggregates) and parallel-aware hash joins.

Parallel aggregation works by splitting each aggregate into two phases:

• Partial Aggregate — each worker aggregates its own slice of rows into a partial state (e.g. a running sum and count).
• Finalize Aggregate — the leader combines those partial states into the final result.

This is possible because aggregates like count, sum, avg, min and max are combinable: a partial result from one worker can be merged with another via a combine function.

You see this in plans as a Partial Aggregate node under Gather and a Finalize Aggregate node above it.

Run EXPLAIN on a large grouped query and look for the Finalize / Gather / Partial sandwich. The Gather node is where worker results flow back to the leader.

Note Workers Planned: the planner's intended parallelism. At execution, EXPLAIN ANALYZE also reports Workers Launched, which can be lower if the system ran out of worker slots.

The planner only considers parallel plans when certain GUCs allow it and when the table is big enough to be worth it.

• max_parallel_workers_per_gather — max workers a single Gather may use (0 disables parallel query for that node).
• max_parallel_workers — cap across the whole instance.
• max_worker_processes — hard OS-level ceiling for all background workers.
• min_parallel_table_scan_size (default 8MB) — table must exceed this for a parallel scan to be considered.
• parallel_setup_cost and parallel_tuple_cost — model the overhead of starting workers and shipping tuples.

On small test tables the planner may decide parallelism is not worth the setup cost. To study plans you can bias it heavily, then measure on realistic data.

Setting parallel_setup_cost and parallel_tuple_cost to 0 makes the planner ignore worker startup overhead, so it picks parallel plans even on modest inputs. This is a diagnostic trick, not a production setting.

A Hash Join builds an in-memory hash table from the smaller (build) side, then probes it with rows from the larger side. In a Parallel Hash Join, the join itself is parallel-aware.

• The plan node is Parallel Hash Join with an inner Parallel Hash node.
• All workers cooperate to build one shared hash table in dynamic shared memory.
• Each worker then probes that shared table with its slice of the outer relation.

This avoids every worker re-building its own private copy of the hash table, saving both CPU and memory.

Be careful distinguishing two superficially similar plans:

• Parallel Hash Join (with Parallel Hash): workers jointly build one shared hash table. Build cost and memory are shared.
• Hash Join under Gather (plain Hash): each worker builds its own complete copy of the hash table. The build work and memory are multiplied by the number of workers.

For a large build side, the parallel-aware variant is dramatically cheaper. The planner chooses it when both sides can be scanned in parallel and the join is parallel-safe.

Hash joins and hash aggregates live inside work_mem. If the hash table does not fit, PostgreSQL spills to disk in batches, which is far slower.

In EXPLAIN (ANALYZE, BUFFERS) watch for Batches: N where N > 1 and Disk Usage on hash nodes — signs that work_mem is too small for the build side.

For Parallel Hash, the shared table can use a larger effective budget: the per-worker work_mem allotments are pooled for the one shared hash table, which is another reason the parallel-aware join scales well.

The planner refuses parallel plans when the query contains parallel-unsafe elements. Common blockers:

• Calling a function marked PARALLEL UNSAFE (the default for user-defined functions unless you label them).
• Writing data: INSERT, UPDATE, DELETE targets (the modifying part runs serially).
• Cursors / FOR UPDATE row locking in many cases.
• max_parallel_workers_per_gather = 0.

Mark pure, side-effect-free functions as PARALLEL SAFE so they don't block parallel plans.

By default the leader process does double duty: it both gathers worker output and helps execute the parallel plan. This is controlled by parallel_leader_participation (default on).

For a query with N planned workers, effective parallelism is roughly N+1 when the leader participates. But if the leader gets bottlenecked gathering a flood of tuples, turning leader participation off can sometimes help workers run unimpeded — measure both ways.

Putting it together for a CPU-bound grouped join:

• Raise max_parallel_workers_per_gather so the planner can split the scan (start with the number of spare cores).
• Ensure max_parallel_workers and max_worker_processes are high enough that workers are actually launched, not throttled.
• Raise work_mem until hash nodes show Batches: 1 (no spill).
• Confirm the plan shows Parallel Hash Join + Partial/Finalize Aggregate, and that Workers Launched equals Workers Planned.

Always validate with EXPLAIN (ANALYZE, BUFFERS) on production-sized data — costs at small scale lie.

You learned how PostgreSQL accelerates CPU-bound grouping workloads:

• Parallel aggregation splits work into Partial Aggregate per worker and Finalize Aggregate at the leader, possible because aggregates are combinable.
• Parallel Hash Join builds one shared hash table across workers, avoiding per-worker duplication of build cost and memory.
• The Finalize / Gather / Partial sandwich and Parallel Hash nodes are how you recognize these plans in EXPLAIN.
• Gate parallelism with max_parallel_workers_per_gather, max_parallel_workers, and table-size thresholds; size hash tables with work_mem to avoid spilling to disk.
• Parallel-unsafe functions and data-modifying statements disable parallel plans; mark pure functions PARALLEL SAFE.

Always confirm Workers Launched matches Workers Planned and verify with EXPLAIN (ANALYZE, BUFFERS) on real data.