PostgreSQL Performance & Query Optimization · Aula

Diagnóstico da desativação do paralelismo

Identifique as funções, os bloqueios e as configurações que silenciosamente forçam a execução serial.

Aula 4 de 413 etapas

Diagnóstico da desativação do paralelismo é uma aula grátis de PostgreSQL Performance & Query Optimization no CoddyKit. Esta é a aula 4 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de PostgreSQL Performance & Query Optimization, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de PostgreSQL Performance & Query Optimization inclui 4 aulas no total.

Partes desta aula ainda não foram traduzidas e aparecem em inglês.

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?

Responda na aula interativa

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.

Grátis para começar

Aprenda SQL com um tutor de IA — grátis

Escreva e execute código real no seu navegador, obtenha ajuda instantânea de um tutor de IA 24/7 e continue de onde parou na web ou no app.

Cursos: 22
Aulas: 88

Começar a aprender grátis Ver o curso completo

Perguntas Frequentes

A aula “Diagnóstico da desativação do paralelismo” é grátis?

Sim — o texto completo de “Diagnóstico da desativação do paralelismo” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de PostgreSQL Performance & Query Optimization, atualize para CoddyKit PRO. O curso de PostgreSQL Performance & Query Optimization inclui 4 aulas no total.

O que vou aprender em “Diagnóstico da desativação do paralelismo”?

Identifique as funções, os bloqueios e as configurações que silenciosamente forçam a execução serial. Você pratica PostgreSQL Performance & Query Optimization com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar PostgreSQL Performance & Query Optimization?

Nenhuma experiência prévia é necessária. PostgreSQL Performance & Query Optimization no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 4 de 4.

Quanto tempo leva a aula “Diagnóstico da desativação do paralelismo”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de PostgreSQL Performance & Query Optimization?

Sim. Cada aula de PostgreSQL Performance & Query Optimization inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

Quando o planejador escolhe planos paralelos
Ajuste da quantidade de trabalhadores e dos custos de agregação
Agregação paralela e junções por hash
Diagnóstico da desativação do paralelismo

← Voltar para PostgreSQL Performance & Query Optimization