0Pricing
PostgreSQL Performance & Query Optimization · レッスン

並列集約とハッシュ結合

大規模なグループ化処理で、部分集約と並列対応の結合を活用します。

「並列集約とハッシュ結合」はCoddyKit上の無料PostgreSQL Performance & Query Optimizationレッスンです。 これはレッスン3/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはPostgreSQL Performance & Query Optimization学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

このレッスンの一部はまだ翻訳されておらず、英語で表示されています。

Why Parallelism for Aggregation

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.

Partial and Finalize Aggregate

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.

Reading a Parallel Aggregate Plan

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.

EXPLAIN (COSTS OFF)
SELECT customer_id, sum(amount) AS total
FROM orders
GROUP BY customer_id;

-- Finalize HashAggregate
--   Group Key: customer_id
--   -> Gather
--        Workers Planned: 4
--        -> Partial HashAggregate
--             Group Key: customer_id
--             -> Parallel Seq Scan on orders

Knobs That Gate Parallelism

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.
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 8;

SHOW min_parallel_table_scan_size;   -- 8MB default
SHOW parallel_setup_cost;            -- 1000 default

Forcing Parallelism to Experiment

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.

SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = '0';
SET max_parallel_workers_per_gather = 4;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT region, count(*)
FROM sales
GROUP BY region;

Parallel-Aware Hash Join

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.

EXPLAIN (COSTS OFF)
SELECT o.customer_id, sum(o.amount)
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'DE'
GROUP BY o.customer_id;

-- Finalize GroupAggregate
--   -> Gather
--        -> Partial HashAggregate
--             -> Parallel Hash Join
--                  Hash Cond: (o.customer_id = c.id)
--                  -> Parallel Seq Scan on orders o
--                  -> Parallel Hash
--                       -> Parallel Seq Scan on customers c

Shared Hash vs Per-Worker Hash

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.

work_mem and the Hash Table

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.

SET work_mem = '256MB';

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.product_id, count(*)
FROM orders o
JOIN products p ON p.id = o.product_id
GROUP BY o.product_id;

-- Look for: Parallel Hash  Batches: 1  Memory Usage: ...kB

What Disables Parallelism

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.

CREATE FUNCTION norm_region(txt text)
RETURNS text
LANGUAGE sql
IMMUTABLE
PARALLEL SAFE
AS $fn$ SELECT lower(trim(txt)) $fn$;

Leader Participation

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.

SET parallel_leader_participation = off;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT category_id, avg(price)
FROM products
GROUP BY category_id;

Tuning a Heavy Grouping Workload

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.

SET max_parallel_workers_per_gather = 6;
SET work_mem = '512MB';

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT c.region, count(*) AS n, sum(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.region;

Quick Check

Test your understanding of parallel-aware joins.

Recap

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.

よくある質問

「並列集約とハッシュ結合」レッスンは無料ですか？

はい。「並列集約とハッシュ結合」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、PostgreSQL Performance & Query Optimizationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

「並列集約とハッシュ結合」で何を学びますか？

大規模なグループ化処理で、部分集約と並列対応の結合を活用します。 ブラウザで直接実行するハンズオンコードでPostgreSQL Performance & Query Optimizationを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

PostgreSQL Performance & Query Optimizationを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのPostgreSQL Performance & Query Optimizationは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。

「並列集約とハッシュ結合」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このPostgreSQL Performance & Query Optimizationレッスンでコードを書いて実行できますか？

はい。すべてのPostgreSQL Performance & Query Optimizationレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. プランナーが並列実行計画を選ぶタイミング
  2. ワーカー数とGatherコストのチューニング
  3. 並列集約とハッシュ結合
  4. 並列処理が無効になった理由の診断
← PostgreSQL Performance & Query Optimizationに戻る