检查并行查询容量和当前 worker 使用情况

命令

-- Parallel worker pool settings
SELECT name, current_setting(name) AS value
FROM pg_settings
WHERE name IN ('max_worker_processes', 'max_parallel_workers', 'max_parallel_workers_per_gather',
               'max_parallel_maintenance_workers', 'parallel_setup_cost', 'parallel_tuple_cost',
               'min_parallel_table_scan_size', 'min_parallel_index_scan_size')
ORDER BY name;

-- Queries currently running in parallel and their worker counts
SELECT a.pid AS leader_pid,
       count(w.pid) AS workers_running,
       current_setting('max_parallel_workers')::int AS max_parallel_workers,
       now() - a.query_start AS runtime,
       left(a.query, 80) AS query
FROM pg_stat_activity a
JOIN pg_stat_activity w ON w.leader_pid = a.pid AND w.pid <> a.pid
GROUP BY a.pid, a.query_start, a.query
ORDER BY workers_running DESC;

-- Pin / reset the planned worker count for a single table
ALTER TABLE orders SET (parallel_workers = 4);
ALTER TABLE orders RESET (parallel_workers);

作用

显示并行查询的限制以及每个 leader 进程当前正在使用多少个并行 worker；演示按表设置 worker 数量。

适用场景

当分析型查询比预期慢、计划显示 'Workers Planned' > 'Workers Launched'，或 CPU 核心空闲时。

注意

max_parallel_workers 受 max_worker_processes 限制（需要重启），且该池与逻辑复制 worker 及其他后台 worker 共享。在 OLTP 中，较高的 max_parallel_workers_per_gather 会在并发下使 CPU 饱和；每个 worker 还会使用自己的 work_mem。写入数据或锁定行的查询（CREATE TABLE AS / SELECT INTO 除外）、游标以及调用 PARALLEL UNSAFE 函数的查询不会得到并行计划。ALTER TABLE ... SET (parallel_workers) 会短暂获取 ShareUpdateExclusiveLock。