0Pricing
PostgreSQL Performance & Query Optimization · Урок

Диагностика отключения параллелизма

Определяйте функции, блокировки и параметры, незаметно принуждающие систему к последовательному выполнению.

«Диагностика отключения параллелизма» — бесплатный урок PostgreSQL Performance & Query Optimization на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения PostgreSQL Performance & Query Optimization, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.

Части этого урока еще не переведены и отображаются на английском.

When the Plan Goes Serial

You expect a parallel sequential scan, but EXPLAIN shows a plain serial plan. Before blaming the planner's cost model, learn that PostgreSQL has many hard gates that silently veto parallelism entirely.

  • Some are settings (max workers set to 0, low parallel_setup_cost still too high).
  • Some are query shape (parallel-unsafe functions, write CTEs).
  • Some are runtime (no free worker slots, or holding locks).

Our job in this lesson: systematically find which gate fired.

First Look: EXPLAIN ANALYZE

Start by confirming whether parallelism appeared at all. A parallel plan contains a Gather (or Gather Merge) node above the parallel-aware scan. If you only see plain Seq Scan, no workers were even considered viable.

The Workers Planned and Workers Launched lines tell you whether the planner wanted workers and whether it actually got them at runtime — two very different failures.

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

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

Planned vs Launched: Two Failure Modes

Distinguish the two symptoms precisely:

  • Workers Planned: 0 — the planner decided parallelism was illegal or not worth it. This is a planning-time gate (settings, parallel-unsafe query, cost).
  • Workers Planned: 2, Workers Launched: 0 — the plan is parallel, but at execution time no worker slots were free. This is a runtime exhaustion problem.

Confusing these wastes hours. Always read both lines before forming a hypothesis.

Gate 1: The Worker Settings

The most common cause of Workers Planned: 0 is configuration. Check these three first:

  • max_parallel_workers_per_gather — if 0, parallelism is globally off for queries. This is the #1 culprit.
  • max_parallel_workers — pool size; must be > 0 and ≤ max_worker_processes.
  • max_worker_processes — the absolute ceiling for all background workers.

Inspect them in one shot:

SELECT name, setting, source
FROM pg_settings
WHERE name IN (
  'max_parallel_workers_per_gather',
  'max_parallel_workers',
  'max_worker_processes',
  'max_parallel_maintenance_workers'
);

Gate 2: Table Size and Cost Thresholds

Even with workers enabled, the planner refuses parallelism when the relation looks too small. Two settings gate this:

  • min_parallel_table_scan_size (default 8MB) — a heap smaller than this is never scanned in parallel.
  • min_parallel_index_scan_size (default 512kB) — same idea for index scans.

Also, parallel_setup_cost (default 1000) and parallel_tuple_cost (default 0.1) penalize parallel plans; on small results the serial plan simply wins on cost. Check the table's real size:

SELECT pg_size_pretty(pg_relation_size('orders')) AS heap_size,
       (pg_relation_size('orders') / 1024.0 / 1024.0) AS heap_mb,
       current_setting('min_parallel_table_scan_size') AS min_scan;

Gate 3: Parallel-Unsafe Functions

A single PARALLEL UNSAFE function anywhere in the query forces the entire plan serial — no Gather at all. Every function has a parallel-safety label:

  • SAFE — may run in workers.
  • RESTRICTED — may appear in the plan but only in the leader, not below Gather.
  • UNSAFE — bans parallelism for the whole statement.

User-defined functions default to UNSAFE unless you explicitly mark them. Anything that writes, uses sequences, or touches temp tables is unsafe.

SELECT p.proname,
       p.proparallel  -- 's'=safe, 'r'=restricted, 'u'=unsafe
FROM pg_proc p
WHERE p.proname IN ('normalize_email', 'nextval', 'random', 'now')
ORDER BY p.proname;

Auditing Your Own Functions

To find UDFs that silently disable parallelism, list every function you own that is labelled unsafe or restricted. A function that is logically pure but defaults to UNSAFE is a frequent, invisible cause.

If the function truly is read-only and side-effect free, re-declare it as PARALLEL SAFE to unblock the planner.

SELECT n.nspname AS schema,
       p.proname AS function,
       CASE p.proparallel
         WHEN 's' THEN 'safe'
         WHEN 'r' THEN 'restricted'
         WHEN 'u' THEN 'unsafe'
       END AS parallel_safety
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND p.proparallel <> 's'
ORDER BY 1, 2;

Fixing an Unsafe UDF Label

If you confirm a function has no side effects and never writes, mark it safe so it can run beneath Gather. Be honest: anything calling nextval(), modifying tables, or using non-immutable session state must stay restricted or unsafe.

Marking a genuinely unsafe function as safe leads to wrong results or worker crashes — this label is a contract, not a hint.

-- Only if the body is truly read-only and deterministic across workers:
ALTER FUNCTION normalize_email(text) PARALLEL SAFE;

-- Verify the new label:
SELECT proname, proparallel
FROM pg_proc
WHERE proname = 'normalize_email';

Gate 4: Query Shapes That Block Parallelism

Some constructs are inherently parallel-restricted regardless of settings:

  • Data-modifying statements — INSERT/UPDATE/DELETE (parallel DML is limited; parallel writes are generally not used).
  • Writable / data-modifying CTEs — a CTE that writes forces serial.
  • SELECT ... FOR UPDATE / FOR SHARE — locking clauses are parallel-restricted.
  • Queries inside a function with PARALLEL UNSAFE, or called where the outer statement is already a write.
  • FULL OUTER JOIN historically, and correlated subqueries referencing the outer parallel node.

If your query has any of these, no setting will produce a parallel plan.

-- This SELECT can be parallel:
EXPLAIN SELECT count(*) FROM orders WHERE amount > 100;

-- This one cannot — the locking clause is parallel-restricted:
EXPLAIN SELECT * FROM orders WHERE amount > 100 FOR UPDATE;

Gate 5: Runtime Worker Exhaustion

When you see Workers Planned: 4 but Workers Launched: 1, the planner was right but the pool was empty. The cluster-wide pool is capped by max_parallel_workers, drawn from max_worker_processes, and shared with autovacuum and other parallel queries.

Under concurrency, queries grab whatever slots remain — sometimes zero. Watch live backends to confirm contention:

SELECT pid, backend_type, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE backend_type = 'parallel worker'
   OR query ILIKE '%Gather%'
ORDER BY backend_type;

Gate 6: Locks and force_parallel_mode

Two final, easily-missed gates:

  • Heavy locks — if a backend holds or waits on a conflicting lock, it may serialize; also a relation under an ACCESS EXCLUSIVE lock (DDL) won't get a parallel scan while blocked. Inspect pg_locks joined to pg_stat_activity.
  • debug_parallel_query (formerly force_parallel_mode) — a testing GUC. Setting it to on/regress forces a Gather even when it makes no sense, which can mask real diagnosis. Make sure it is off in normal investigation.
SELECT name, setting
FROM pg_settings
WHERE name IN ('debug_parallel_query', 'force_parallel_mode');

-- Who is blocking a parallel scan?
SELECT l.pid, l.mode, l.granted, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation = 'orders'::regclass
ORDER BY l.granted;

Quick Check: Diagnosing the Symptom

An analyst runs EXPLAIN ANALYZE and sees Workers Planned: 4 but Workers Launched: 0. All worker GUCs are non-zero and the table is 2 GB. What is the most likely cause?

Recap: A Diagnosis Checklist

To find why parallelism was disabled, work top-down:

  • Read both lines. Workers Planned: 0 = planning gate; Planned>0, Launched<Planned = runtime exhaustion.
  • Settings: check max_parallel_workers_per_gather (≠0), max_parallel_workers, max_worker_processes.
  • Size/cost: table above min_parallel_table_scan_size; parallel_setup_cost not dominating a tiny result.
  • Functions: hunt proparallel <> 's' in pg_proc; fix mislabelled UDFs.
  • Query shape: writes, data-modifying CTEs, FOR UPDATE locking clauses.
  • Runtime: pg_stat_activity for free slots; pg_locks for blockers; confirm debug_parallel_query = off.

Match the symptom to the gate, and the silent serial plan stops being a mystery.

Часто задаваемые вопросы

Урок «Диагностика отключения параллелизма» бесплатный?

Да — полный текст урока «Диагностика отключения параллелизма» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс PostgreSQL Performance & Query Optimization, подпишись на CoddyKit PRO. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.

Чему я научусь в уроке «Диагностика отключения параллелизма»?

Определяйте функции, блокировки и параметры, незаметно принуждающие систему к последовательному выполнению. Ты практикуешь PostgreSQL Performance & Query Optimization с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать PostgreSQL Performance & Query Optimization?

Предыдущий опыт не требуется. PostgreSQL Performance & Query Optimization на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Диагностика отключения параллелизма»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке PostgreSQL Performance & Query Optimization?

Да. Каждый урок PostgreSQL Performance & Query Optimization включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Когда планировщик выбирает параллельные планы
  2. Настройка числа рабочих процессов и стоимости сбора
  3. Параллельная агрегация и хеш-соединения
  4. Диагностика отключения параллелизма
← Назад к PostgreSQL Performance & Query Optimization