PricingLoginStart free
PostgreSQL Performance & Query Optimization · 课时

规划器何时选择并行计划

了解会触发并行扫描和并行连接的成本阈值与表大小。

第 1 / 4 课13 个步骤

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

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

Parallelism Is a Cost Decision

PostgreSQL does not run every query in parallel. The planner only generates a parallel plan when it believes the extra cost of spinning up workers will be repaid by faster execution.

  • Workers cost money: the planner adds startup and tuple-transfer overhead for each one.
  • Parallelism only pays off when the table is large enough and the work is CPU- or IO-heavy enough.
  • Tiny tables and cheap queries stay sequential — the overhead would dominate.

In this lesson you will learn exactly which thresholds and table sizes flip the planner into a parallel plan.

The Three Gating GUCs

Three settings gate whether parallelism is even considered before any cost math runs:

  • max_parallel_workers_per_gather — max workers a single Gather node may request (default 2). If 0, parallelism is disabled for that query.
  • max_parallel_workers — cap across the whole instance (default 8).
  • max_worker_processes — the hard OS-level ceiling on background workers (default 8).

If max_parallel_workers_per_gather = 0, the planner never produces a parallel path, regardless of table size.

SHOW max_parallel_workers_per_gather;
SHOW max_parallel_workers;
SHOW max_worker_processes;

The Size Threshold: min_parallel_table_scan_size

The single most important table-size trigger is min_parallel_table_scan_size (default 8MB). A relation must be at least this large before the planner even considers a parallel sequential scan over it.

  • For indexes, the analogous setting is min_parallel_index_scan_size (default 512kB).
  • Below the threshold, the relation is treated as too small to bother parallelizing.

So a 6MB table will almost never get a parallel scan, while a 1GB table easily clears the bar.

SHOW min_parallel_table_scan_size;
SHOW min_parallel_index_scan_size;

How Worker Count Scales With Size

The number of workers is not fixed — it grows logarithmically with table size. Each time the relation triples past the threshold, the planner allows one more worker (capped by max_parallel_workers_per_gather).

  • 8MB to 24MB: up to 1 worker
  • 24MB to 72MB: up to 2 workers
  • 72MB to 216MB: up to 3 workers
  • Each step multiplies the previous boundary by 3.

This is why doubling a small table rarely changes the worker count, but going from 10MB to 1GB does.

The Per-Tuple and Setup Costs

Once a parallel path is possible, the planner weighs two cost penalties against the sequential plan:

  • parallel_setup_cost (default 1000) — a one-time charge for launching workers and tearing down the shared memory. This is large, which is why tiny queries stay sequential.
  • parallel_tuple_cost (default 0.1) — charged per tuple passed from a worker up through the Gather node to the leader.

A parallel plan wins only when divided per-worker scan cost minus these penalties beats the sequential cost.

SHOW parallel_setup_cost;
SHOW parallel_tuple_cost;

Reading a Parallel Plan in EXPLAIN

You confirm a parallel plan by looking for a Gather (or Gather Merge) node and Parallel-prefixed scan nodes underneath it.

  • Gather collects rows from workers; Workers Planned shows how many were requested.
  • Nodes below the Gather (e.g. Parallel Seq Scan) run once per worker plus the leader.
  • Row counts under the Gather are per-worker estimates, not the total.
EXPLAIN (ANALYZE, VERBOSE)
SELECT count(*)
FROM orders
WHERE amount > 100;

Forcing Parallelism for Testing

To observe a parallel plan on a smaller table during testing, lower the gating thresholds at the session level. This does not change the data — only the planner's willingness to parallelize.

  • Set min_parallel_table_scan_size = 0 to remove the size gate.
  • Set parallel_setup_cost = 0 and parallel_tuple_cost = 0 to remove the cost penalties.
  • Set force_parallel_mode = on (renamed debug_parallel_query in PG16+) to force a parallel path where one exists.

These are diagnostic knobs — never leave them on in production.

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

Parallel-Aware vs Parallel-Restricted

Not every operation can run inside a parallel worker. The planner classifies pieces of the query:

  • Parallel-safe — can run in a worker (most immutable/stable functions, plain scans).
  • Parallel-restricted — must run only in the leader (e.g. references to temporary tables, correlated subplans, some CTEs).
  • Parallel-unsafe — disables parallelism for the whole query (e.g. functions marked PARALLEL UNSAFE, writing CTEs, sequence calls).

A single VOLATILE or PARALLEL UNSAFE function in the query can suppress an otherwise-parallel plan.

CREATE FUNCTION score(x int) RETURNS int
LANGUAGE sql IMMUTABLE PARALLEL SAFE
AS 'SELECT x * 2';

Parallel Joins and Aggregates

Parallelism is not limited to scans. Once a driving relation is scanned in parallel, the planner can push joins and aggregation into the workers:

  • Parallel Hash Join — workers cooperatively build one shared hash table.
  • Parallel Nested Loop / Merge Join — each worker joins its slice of the outer relation.
  • Partial Aggregate — each worker aggregates its rows, then a Finalize Aggregate above the Gather combines the partial results.

This is why a large GROUP BY over a big fact table is a classic parallel-plan candidate.

EXPLAIN
SELECT customer_id, count(*)
FROM orders
GROUP BY customer_id;

Things That Quietly Disable Parallelism

Even on a huge table, several conditions block a parallel plan entirely:

  • The query is run inside a cursor/FETCH or with SELECT ... FOR UPDATE row locking.
  • It is a data-modifying statement on PG versions before 11 (later versions allow parallel INSERT ... SELECT / index builds, but not parallel UPDATE/DELETE execution).
  • It calls a PARALLEL UNSAFE function or runs inside a function that has already claimed all workers.
  • max_parallel_workers_per_gather = 0 for the session.

When a plan you expect to be parallel is not, check these before tuning costs.

Putting the Thresholds Together

For the planner to pick a parallel plan, all of these must hold:

  • Relation size at least min_parallel_table_scan_size (8MB) for scans, or the index size threshold.
  • max_parallel_workers_per_gather > 0 and instance-wide worker budget available.
  • The query contains only parallel-safe (or at worst parallel-restricted) elements.
  • The estimated parallel cost, after parallel_setup_cost and parallel_tuple_cost, beats the best sequential plan.

Miss any one and you get a sequential plan — usually for a good reason.

EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(amount)
FROM orders
WHERE created_at >= DATE '2024-01-01';

Quick Check

Test your understanding of the parallel-plan triggers.

在交互式课程中回答

Recap

Key takeaways on when PostgreSQL chooses a parallel plan:

  • Parallelism is a cost decision gated first by size, then by cost math.
  • min_parallel_table_scan_size (8MB) is the primary table-size trigger; min_parallel_index_scan_size (512kB) covers indexes.
  • Worker count grows logarithmically — one more worker each time size triples — capped by max_parallel_workers_per_gather.
  • parallel_setup_cost (1000) and parallel_tuple_cost (0.1) must be outweighed by the per-worker speedup.
  • Parallel-unsafe functions, row locking, and a zero worker setting can disable parallelism even on huge tables.
  • Confirm plans by looking for Gather / Partial Aggregate nodes in EXPLAIN.
免费开始

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

「规划器何时选择并行计划」课时需要多长时间？

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

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

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

此课程中的所有课时

  1. 规划器何时选择并行计划
  2. 调整工作进程数量与汇总成本
  3. 并行聚合与哈希连接
  4. 诊断并行执行为何被禁用
← 返回 PostgreSQL Performance & Query Optimization