PricingLoginStart free
PostgreSQL Performance & Query Optimization · 课时

并行聚合与哈希连接

利用部分聚合和并行感知连接处理繁重的分组工作负载。

第 3 / 4 课13 个步骤

并行聚合与哈希连接 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 3 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 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.

免费开始

用 AI 导师学习 SQL — 免费

在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。

课程
22
课程
88
免费开始学习查看完整课程

常见问题解答

「并行聚合与哈希连接」课时是免费的吗？

是的 — 「并行聚合与哈希连接」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「并行聚合与哈希连接」这节课中我会学到什么？

利用部分聚合和并行感知连接处理繁重的分组工作负载。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 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. 调整工作进程数量与汇总成本
  3. 并行聚合与哈希连接
  4. 诊断并行执行为何被禁用
← 返回 PostgreSQL Performance & Query Optimization