Parallel Query Analysis

Inspect Parallel Adoption

Start by listing the busiest queries that launched parallel workers. Compare the number planned versus launched to identify executor fallbacks (e.g., due to insufficient workers).

SELECT queryid,
       LEFT(query, 160) AS query_preview,
       calls,
       parallel_workers_launched,
       parallel_workers_planned,
       mean_exec_time,
       (parallel_workers_launched::numeric / NULLIF(calls, 0)) AS avg_workers
  FROM pg_stat_insights
 WHERE parallel_workers_launched > 0
 ORDER BY parallel_workers_launched DESC
 LIMIT 20;

Evaluate Parallel Speedup

Gauge efficiency by normalising execution time per worker. Small or negative gains suggest the workload is not parallel-friendly.

WITH stats AS (
  SELECT queryid,
         total_exec_time,
         total_plan_time,
         parallel_workers_launched,
         calls
    FROM pg_stat_insights
   WHERE parallel_workers_launched > 0
)
SELECT queryid,
       total_exec_time / NULLIF(calls, 0) AS avg_exec_ms,
       (total_exec_time - total_plan_time) / NULLIF(parallel_workers_launched, 0) AS exec_ms_per_worker
  FROM stats
 ORDER BY exec_ms_per_worker ASC
 LIMIT 15;

Highlight Sequential Fallbacks

SELECT queryid,
       parallel_workers_planned,
       parallel_workers_launched,
       mean_exec_time
  FROM pg_stat_insights
 WHERE parallel_workers_planned > 0
   AND parallel_workers_launched = 0
 ORDER BY mean_exec_time DESC
 LIMIT 15;

Reasons include disabled parallel_leader_participation, insufficient workers, or functions marked PARALLEL UNSAFE.

Tune Configuration

max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4
parallel_leader_participation = on

Ensure max_worker_processes is set higher than max_parallel_workers, and monitor background worker contention (logical replication, autovacuum) when raising limits.

Optimise Execution Plans