PostgreSQL Performance & Query Optimization · 课时

调整工作进程数量与汇总成本

调整并行工作进程设置和逐元组成本，在加速效果与额外开销之间取得平衡。

第 2 / 4 课13 个步骤

调整工作进程数量与汇总成本 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。这是第 2 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

Why Parallel Query Needs Tuning

PostgreSQL can split a single query across multiple CPU cores using parallel workers. The leader process spawns helper workers, each scans a slice of the data, and results are merged back at a Gather node.

Parallelism is not free. Spawning workers, copying tuples through shared memory, and synchronizing at the gather point all cost time. For small result sets the overhead can exceed the speedup.

CPU-bound workloads (large sequential scans, aggregates, hash joins) benefit most.
Latency-sensitive short queries usually do not.

This lesson covers the knobs that decide how many workers run and when the planner thinks parallelism is worth it.

The Plan Shape: Gather and Partial Nodes

A parallel plan has a distinctive shape. Below the Gather (or Gather Merge) node sit the partial operations that workers run in parallel; above it everything is serial in the leader.

Read the plan with EXPLAIN (ANALYZE, VERBOSE) and look for Workers Planned vs Workers Launched. A gap between them means the system ran out of available worker slots at runtime.

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT count(*)
FROM orders
WHERE order_total > 100;

-- Look for:
--   Gather  (cost=... rows=...)
--     Workers Planned: 2
--     Workers Launched: 2
--     ->  Partial Aggregate
--           ->  Parallel Seq Scan on orders

max_parallel_workers_per_gather

The single most important per-query knob is max_parallel_workers_per_gather. It caps how many workers a single Gather node may request. The default is 2.

Setting it to 0 disables parallelism entirely for that session. Raising it lets large scans use more cores, but only up to what the table size and other limits allow.

This is a per-Gather cap, not a per-query cap. A query with two Gather nodes can use up to 2× this many workers.
It can be changed per session with SET, so you can tune one heavy report without touching global config.

-- Inspect current value
SHOW max_parallel_workers_per_gather;

-- Allow up to 4 workers per gather for this session
SET max_parallel_workers_per_gather = 4;

-- Disable parallelism for a latency-critical query
SET max_parallel_workers_per_gather = 0;

The Three-Layer Worker Budget

Worker counts are limited by three nested settings. The effective number of workers is the minimum of all three plus the planner's own size-based estimate.

max_parallel_workers_per_gather — per Gather node (default 2).
max_parallel_workers — cluster-wide pool dedicated to parallel query (default 8).
max_worker_processes — total background workers for the whole server, shared with extensions and replication (default 8).

If you raise the per-gather cap but leave max_parallel_workers low, concurrent queries will compete and many will run with fewer workers than planned.

SHOW max_worker_processes;        -- server-wide ceiling
SHOW max_parallel_workers;        -- pool for parallel query
SHOW max_parallel_workers_per_gather;  -- per-gather cap

-- A sane production starting point on an 8-core box:
-- max_worker_processes = 16
-- max_parallel_workers = 8
-- max_parallel_workers_per_gather = 4

How the Planner Picks a Default Worker Count

Even with high caps, the planner scales workers by relation size using a logarithmic rule. A table must be at least min_parallel_table_scan_size (default 8MB) before any parallel worker is considered. Roughly, each 3× increase in size adds one worker.

8MB to 24MB to 72MB to 216MB... → 1, 2, 3... workers.
The result is then clamped by max_parallel_workers_per_gather.

This is why a tiny table never goes parallel no matter how high you set the caps — and why you sometimes must lower min_parallel_table_scan_size to test parallel plans on small data.

SHOW min_parallel_table_scan_size;   -- default 8MB
SHOW min_parallel_index_scan_size;   -- default 512kB

-- Force consideration of parallelism on smaller tables (testing)
SET min_parallel_table_scan_size = '0';

parallel_setup_cost: The Fixed Overhead

parallel_setup_cost models the one-time price of launching workers and setting up shared memory. The default is 1000 — a large number in planner cost units, deliberately so the optimizer avoids parallelism for cheap queries.

If your hardware spawns workers quickly and you find good parallel plans being rejected, lowering this value makes the planner more willing to parallelize. Raise it to discourage parallelism on a busy OLTP box.

SHOW parallel_setup_cost;   -- default 1000

-- Make the planner more eager to go parallel
SET parallel_setup_cost = 200;

-- Then compare plans
EXPLAIN SELECT count(*) FROM orders WHERE shipped_at IS NULL;

parallel_tuple_cost: The Per-Tuple Gather Price

parallel_tuple_cost models the cost of transferring each tuple from a worker back to the leader through the gather queue. Default is 0.1 per tuple.

This is the key reason parallelism loses on queries that return many rows: every returned tuple is taxed. Parallel plans win when workers reduce the data — filtering hard or aggregating — so few tuples cross the Gather.

High row count out of Gather + high parallel_tuple_cost → planner prefers serial.
Lower it cautiously if your shared-memory tuple transfer is genuinely cheap.

SHOW parallel_tuple_cost;   -- default 0.1

-- A query that aggregates (few tuples cross Gather) loves parallelism;
-- a query that returns millions of raw rows pays parallel_tuple_cost on each.
EXPLAIN
SELECT customer_id, count(*)
FROM orders
GROUP BY customer_id;   -- partial aggregate shrinks data before Gather

Per-Table Override: parallel_workers Storage Parameter

You can pin a worker count to a specific table with the parallel_workers storage parameter. This overrides the planner's size-based formula for scans of that table, while still being clamped by the global caps.

Useful for a hot fact table that is queried with heavy aggregates, where you always want, say, 6 workers regardless of the logarithmic default.

-- Pin parallel scans of this table to 6 workers
ALTER TABLE orders SET (parallel_workers = 6);

-- Remove the override, return to size-based defaults
ALTER TABLE orders RESET (parallel_workers);

-- Verify the setting
SELECT reloptions FROM pg_class WHERE relname = 'orders';

Measuring the Sweet Spot

Tuning is empirical. Run the same query under different worker counts and compare actual execution time, not just planner cost. Speedup is sublinear and eventually flattens or regresses as coordination overhead grows.

Watch Workers Launched — if it is below Workers Planned, your pool is exhausted and adding the per-gather cap won't help.
Diminishing returns: going from 4 to 8 workers often yields far less than the 1-to-2 jump.

SET max_parallel_workers_per_gather = 2;
EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM orders WHERE order_total > 100;

SET max_parallel_workers_per_gather = 4;
EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM orders WHERE order_total > 100;

SET max_parallel_workers_per_gather = 8;
EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM orders WHERE order_total > 100;

Concurrency: When Workers Run Out

The parallel worker pool is shared across the whole cluster. Under concurrency, many queries planned for 4 workers each may collectively demand more than max_parallel_workers allows, so some run with reduced or zero workers.

On OLTP-heavy systems this is a feature: you want short transactions to win CPU, not lose it to a reporting query that grabbed 8 workers. Strategies:

Keep max_parallel_workers_per_gather modest (2-4) globally.
Raise it per session only for known batch/report jobs.
Ensure max_worker_processes is high enough to also leave room for extensions and replication.

A Practical Tuning Recipe

Putting it together for a CPU-bound analytics workload on an 8-core server:

Set max_worker_processes = 16 (headroom for extensions).
Set max_parallel_workers = 8 (one per core).
Keep global max_parallel_workers_per_gather = 2 to protect OLTP latency.
For nightly report sessions, SET max_parallel_workers_per_gather = 6.
Lower parallel_setup_cost / parallel_tuple_cost only after EXPLAIN ANALYZE proves good plans are being rejected.

Always validate with real timings, and pin per-table counts only for stable, well-understood hot tables.

-- Per-session profile for a heavy nightly aggregation job
SET max_parallel_workers_per_gather = 6;
SET parallel_setup_cost = 200;
SET min_parallel_table_scan_size = '4MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT region, date_trunc('day', created_at) AS d, sum(amount)
FROM sales
GROUP BY region, d;

Quick Check: Diagnosing a Worker Shortfall

You run a heavy aggregate query. EXPLAIN ANALYZE shows Workers Planned: 4 but Workers Launched: 1, and execution is barely faster than serial. Which single change is most likely to fix the shortfall?

在交互式课程中回答

Recap: Balancing Speedup and Overhead

You now have a mental model for tuning parallel query:

Worker budget is layered: max_parallel_workers_per_gather ≤ max_parallel_workers ≤ max_worker_processes, further scaled by table size.
Costs gate the decision: parallel_setup_cost is the fixed launch tax; parallel_tuple_cost taxes every tuple crossing the Gather — so parallelism wins when workers shrink the data.
Plan-reading is essential: compare Workers Planned vs Workers Launched to tell a planning problem from a runtime-pool problem.
Tune per session, not globally, to protect OLTP latency while letting batch jobs grab cores.

Measure with EXPLAIN ANALYZE and real timings; never trust planner cost alone.

免费开始

用 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 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 2 节课，共 4 节。

「调整工作进程数量与汇总成本」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 PostgreSQL Performance & Query Optimization