Postgres Parallel Query Worker Tuning Prompt
Work out why a big analytical query isn't going parallel — or is spawning too many workers and starving everything else — and set the parallel GUCs from evidence in the plan instead of copy-pasted blog values.
- Target user
- DBAs and data engineers tuning large analytical or reporting queries on Postgres
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior PostgreSQL performance engineer. You tune parallel query from the execution plan and the server's real CPU/memory budget, never from generic "set max_parallel_workers_per_gather = 8" advice. I will paste: - The query: [SQL QUERY] - The plan from `EXPLAIN (ANALYZE, BUFFERS, VERBOSE)`: [EXPLAIN OUTPUT] - Current settings: max_parallel_workers_per_gather, max_parallel_workers, max_worker_processes, parallel_setup_cost, parallel_tuple_cost, min_parallel_table_scan_size, work_mem: [SETTINGS] - Server CPU count and how many concurrent heavy queries run: [HARDWARE / CONCURRENCY] - Postgres version: [VERSION] Work through this in order: 1. **Determine whether the query even went parallel.** Look for a Gather/Gather Merge node and `Workers Planned` vs `Workers Launched`. If planned < requested or launched < planned, name why: pool exhaustion (max_parallel_workers / max_worker_processes), table below min_parallel_table_scan_size, a parallel-restricted construct (certain CTEs, non-parallel-safe functions, FOR UPDATE), or the planner deciding it isn't worth it. 2. **Diagnose "launched fewer than planned"** specifically — this usually means the global worker pool is saturated by concurrent queries, not a per-query limit. Tie the conclusion to max_parallel_workers vs the number of concurrent gathers. 3. **Right-size the GUCs** for THIS server: relate max_parallel_workers_per_gather and max_parallel_workers to core count and expected concurrency, and explain the memory multiplier — each worker gets its own work_mem, so parallelism multiplies memory pressure and spill risk. Warn when raising workers will cause OOM or disk spills. 4. **Tune the cost knobs when appropriate**: lowering parallel_setup_cost / parallel_tuple_cost or min_parallel_table_scan_size to encourage parallelism, with the trade-off (small queries wastefully parallelized). 5. **Give a verification step**: the exact re-run, what should change (Workers Launched rises, per-worker rows drop, wall time falls without new disk spills), and how to confirm you didn't just move the bottleneck to CPU or memory. Output: (a) one-line verdict on why parallelism is off/over; (b) a ranked change table [setting | new value | why | risk]; (c) exact ALTER SYSTEM / SET commands; (d) the verification query. Guardrails: prefer per-session `SET` to test before `ALTER SYSTEM`. Remember that raising parallelism multiplies work_mem usage across workers — model peak memory before applying. Never set max_parallel_workers above what max_worker_processes and CPU allow.
Run this prompt with AI
Test it, get an AI-improved version, or compare models — live in the Prompt Workspace. No copy-paste.
Why this prompt works
Parallel query tuning goes wrong in two opposite directions: queries that should parallelize don’t, and servers that spray workers until memory and CPU collapse. The naive fix — bumping max_parallel_workers_per_gather — ignores that the number actually launched depends on a shared global pool and a cost model. This prompt forces the model to read Workers Planned vs Workers Launched first, which is the single most diagnostic line for telling a per-query limit apart from a saturated pool.
The memory multiplier is the trap most guides omit: every worker gets its own work_mem, so more parallelism can turn a fast in-memory sort into a disk spill or an OOM. By making the prompt tie worker counts to core count, concurrency, and peak memory — and by closing with a verification plan — it produces settings sized for the actual server rather than a copied snippet.
Related prompts
-
Postgres Keyset (Cursor) Pagination Optimization Prompt
Convert a slow OFFSET/LIMIT pagination query into stable keyset (seek) pagination that stays fast on deep pages — with a matching index and a correct tuple-comparison predicate, not a broken one-column WHERE.
-
Postgres Prepared Statement Generic vs Custom Plan Tuning Prompt
Diagnose why a parameterized/prepared query flipped to a bad generic plan after the sixth execution — and decide between plan_cache_mode, query rewrites, or letting Postgres re-plan — so latency stops spiking under skewed parameters.
-
Postgres Temp File Spill & work_mem Tuning Prompt
Diagnose why queries spill sorts, hashes, and aggregates to temp files on disk, then right-size work_mem per-query and per-role instead of globally — so slow queries stop thrashing disk without blowing up total memory under concurrency.
-
Postgres Planner Mis-Estimation & Extended Statistics Prompt
Fix bad query plans caused by wrong row estimates — correlated columns, skewed distributions, or stale stats — using ANALYZE, statistics targets, and CREATE STATISTICS rather than blunt planner hacks.
More Postgres prompts & error guides
Browse every Postgres prompt and troubleshooting guide in one place.
Reading prompts? Get all 500 in one free PDF
500 battle-tested, copy-paste AI prompts engineered by a senior systems engineer — every one with fill-in placeholders and safety/back-out notes. Drop your email and it's yours.
- 500 prompts: Linux · Kubernetes · Terraform · OpenStack · GitLab · Docker · Monitoring · Incident Response
- Instant PDF download — yours free, forever
- Plus one practical AI-workflow email a week (no spam)
Single opt-in · unsubscribe anytime · no spam.