PostgreSQL 16 并行查询深度调优：从执行计划到资源策略的实战解析

-- 查看当前会话的并行相关 GUC
SHOW max_parallel_workers_per_gather;  -- 默认 2
SHOW parallel_setup_cost;              -- 默认 1000
SHOW parallel_tuple_cost;              -- 默认 0.1
SHOW min_parallel_table_scan_size;     -- 默认 8MB (8*1024*1024 bytes)
SHOW min_parallel_index_scan_size;     -- 默认 512KB
SHOW parallel_leader_participation;    -- 默认 on

parallel_workers = Min(max_parallel_workers_per_gather,
                       Max(1, (relation_size / (1024 * 1024 * parallel_worker_granularity))));

SELECT * FROM pg_settings WHERE name LIKE '%parallel%' OR name LIKE '%worker%';

SET parallel_leader_participation = off;

CREATE TABLE orders (
    order_id    BIGSERIAL PRIMARY KEY,
    user_id     INT NOT NULL,
    product_id  INT NOT NULL,
    amount      DECIMAL(12,2),
    status      SMALLINT,
    created_at  TIMESTAMPTZ DEFAULT now()
);

-- 插入 5000 万行，模拟真实数据倾斜（user_id 集中在 1~10 万）
INSERT INTO orders (user_id, product_id, amount, status, created_at)
SELECT (random()*100000)::int,
       (random()*5000)::int,
       (random()*1000)::decimal,
       (random()*3)::int,
       now() - (random()*365*24*60*60)::int * interval '1 second'
FROM generate_series(1, 50000000);

CREATE INDEX idx_orders_created ON orders(created_at);
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT date_trunc('day', created_at) AS day,
       COUNT(*) AS cnt,
       SUM(amount) AS total_amt
FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-06-30'
GROUP BY 1
ORDER BY 1;

Finalize GroupAggregate  (actual time=38241.2..38241.3 rows=181 loops=1)
  Group Key: (date_trunc('day'::text, created_at))
  ->  Gather Merge  (actual time=38241.0..38241.2 rows=181 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial GroupAggregate  (actual time=38230.1..38230.1 rows=60 loops=3)
              Group Key: (date_trunc('day'::text, created_at))
              ->  Parallel Seq Scan on orders  (actual time=0.02..36210.4 rows=16666667 loops=3)
                    Filter: ((created_at >= '2026-01-01'::date) AND (created_at <= '2026-06-30'::date))
                    Rows Removed by Filter: 0  -- 注意：全表扫描！
                    Buffers: shared hit=0 read=1280000

SET enable_seqscan = off;
SET enable_parallel_seqscan = off;   -- 禁止并行顺序扫描
SET max_parallel_workers_per_gather = 4;
SET work_mem = '256MB';              -- 增大聚合内存

Finalize GroupAggregate  (actual time=12451.2..12451.4 rows=181 loops=1)
  ->  Gather Merge  (actual time=12450.8..12451.1 rows=181 loops=1)
        Workers Planned: 4
        Workers Launched: 4
        ->  Partial GroupAggregate  (actual time=12430.5..12430.5 rows=45 loops=5)
              Group Key: (date_trunc('day'::text, created_at))
              ->  Parallel Index Scan using idx_orders_created on orders  (actual time=0.03..9920.1 rows=3333333 loops=5)
                    Index Cond: ((created_at >= '2026-01-01'::date) AND (created_at <= '2026-06-30'::date))
                    Buffers: shared hit=0 read=320000

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name TEXT
);
INSERT INTO users SELECT generate_series(1, 100000), 'user_' || generate_series;

-- 订单中 90% 的 user_id 集中在 1~1000
UPDATE orders SET user_id = (random()*1000)::int WHERE random() < 0.9;

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, COUNT(o.order_id)
FROM users u JOIN orders o ON u.user_id = o.user_id
WHERE o.amount > 100
GROUP BY u.name;

CREATE STATISTICS stts_user_id (ndistinct, mcv) ON user_id FROM orders;
ANALYZE orders;

SET hash_mem_multiplier = 2.0;  -- 默认 1.0，允许哈希表使用更多 work_mem 倍数
SET parallel_worker_max_memory = '512MB';  -- 每个 Worker 最大内存

SELECT pid, query, wait_event_type, wait_event, state
FROM pg_stat_activity
WHERE backend_type = 'parallel worker';

-- 创建并行安全的假聚合
CREATE OR REPLACE FUNCTION my_sum_state(INT, INT) RETURNS INT AS $$
BEGIN
    RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql PARALLEL SAFE;

CREATE AGGREGATE my_sum(INT) (
    SFUNC = my_sum_state,
    STYPE = INT,
    PARALLEL = SAFE
);

SELECT proname, proparallel FROM pg_proc WHERE proname = 'sum';
-- 结果：sum 的 proparallel 为 's' (safe)

Gather  (actual time=0.5..12000 rows=1 loops=1)
  Workers Planned: 4
  Workers Launched: 1   -- 只启动了 1 个！