PostgreSQL Performance & Query Optimization · Lección

Diagnóstico de la desactivación del paralelismo

Identifique las funciones, los bloqueos y la configuración que fuerzan silenciosamente la ejecución en serie.

Lección 4 de 413 pasos

Diagnóstico de la desactivación del paralelismo es una lección gratuita de PostgreSQL Performance & Query Optimization en CoddyKit. Esta es la lección 4 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de PostgreSQL Performance & Query Optimization, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de PostgreSQL Performance & Query Optimization incluye 4 lecciones en total.

Partes de esta lección aún no han sido traducidas y se muestran en 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?

Responde en la lección interactiva

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.

Gratis para empezar

Aprende SQL con un tutor de IA — gratis

Escribe y ejecuta código real en tu navegador, obtén ayuda instantánea de un tutor de IA disponible 24/7 y continúa donde lo dejaste en la web o en la aplicación.

Cursos: 22
Lecciones: 88

Empezar a aprender gratis Ver el curso completo

Preguntas frecuentes

¿La lección «Diagnóstico de la desactivación del paralelismo» es gratis?

Sí — el texto completo de «Diagnóstico de la desactivación del paralelismo» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de PostgreSQL Performance & Query Optimization, actualiza a CoddyKit PRO. El curso de PostgreSQL Performance & Query Optimization incluye 4 lecciones en total.

¿Qué aprenderé en «Diagnóstico de la desactivación del paralelismo»?

Identifique las funciones, los bloqueos y la configuración que fuerzan silenciosamente la ejecución en serie. Practicas PostgreSQL Performance & Query Optimization con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar PostgreSQL Performance & Query Optimization?

No se requiere experiencia previa. PostgreSQL Performance & Query Optimization en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 4 de 4.

¿Cuánto tiempo toma la lección «Diagnóstico de la desactivación del paralelismo»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de PostgreSQL Performance & Query Optimization?

Sí. Cada lección de PostgreSQL Performance & Query Optimization incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

Cuándo el planificador elige planes paralelos
Ajuste del número de workers y los costes de Gather
Agregación paralela y combinaciones hash
Diagnóstico de la desactivación del paralelismo

← Volver a PostgreSQL Performance & Query Optimization