PostgreSQL 16 并行查询与 JIT 深度解析与 EXPLAIN ANALYZE 可验证实践指南

## 摘要

PostgreSQL 16 在并行查询与 JIT（即时编译）方面持续完善。本文提供基于 `EXPLAIN (ANALYZE, BUFFERS)` 的可验证实践，覆盖参数配置、数据准备、基准方法与观测指标，帮助在不同数据规模与硬件环境下判断并行与 JIT 的收益与边界。

## 环境与版本

• 数据库: PostgreSQL 16.x（建议 >= 16.2）
• 扩展: `pg_stat_statements`（用于采集查询指标）
• 操作系统: Linux/Windows 任一；需稳定的 CPU/内存与磁盘环境
• 校验版本: `SELECT version();`

## 关键参数与建议值

• `max_parallel_workers` 与 `max_parallel_workers_per_gather`: 控制并行 worker 总量与每次 Gather 上限（常用 2–4，视 CPU 核心与负载而定）
• `parallel_tuple_cost` 与 `parallel_setup_cost`: 并行开销估算，默认即可，如需微调按实际成本测算
• `jit`: on/off（建议在 CPU 资源充裕、复杂算子较多时开启）
• `work_mem`: 结合查询复杂度与并行度设定，避免过小导致频繁溢写，过大造成内存压力

示例：

ALTER SYSTEM SET max_parallel_workers = 8;
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET jit = on;
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

## 数据准备（可复现实验）

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
  id BIGSERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  quantity INT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
);

-- 生成 2,000 万行示例数据（可分批插入以降低锁与 IO 压力）
INSERT INTO sales (customer_id, product_id, price, quantity, created_at)
SELECT (random()*100000)::INT,
       (random()*10000)::INT,
       round(10 + random()*990, 2),
       1 + (random()*10)::INT,
       NOW() - (random()* interval '365 days')
FROM generate_series(1, 20000000);

ANALYZE sales;

-- 常用索引
CREATE INDEX idx_sales_customer_product ON sales(customer_id, product_id);
CREATE INDEX idx_sales_created_at ON sales(created_at);

## 基准查询与开关对比

1) 聚合与过滤（适合并行）：

SET jit = off;
EXPLAIN (ANALYZE, BUFFERS) 
SELECT customer_id, sum(price*quantity) AS total
FROM sales
WHERE created_at >= NOW() - interval '30 days'
GROUP BY customer_id;

SET jit = on;
EXPLAIN (ANALYZE, BUFFERS) 
SELECT customer_id, sum(price*quantity) AS total
FROM sales
WHERE created_at >= NOW() - interval '30 days'
GROUP BY customer_id;

2) 连接与聚合（并行 + JIT 常见增益场景）：

WITH recent AS (
  SELECT * FROM sales WHERE created_at >= NOW() - interval '7 days'
)
EXPLAIN (ANALYZE, BUFFERS)
SELECT r.product_id, count(*)
FROM recent r
JOIN sales s ON s.product_id = r.product_id
GROUP BY r.product_id;

3) 控制并行度对比：

SET max_parallel_workers_per_gather = 0; -- 禁用并行
EXPLAIN (ANALYZE, BUFFERS) SELECT sum(price) FROM sales;

SET max_parallel_workers_per_gather = 4; -- 启用并行
EXPLAIN (ANALYZE, BUFFERS) SELECT sum(price) FROM sales;

## 观测指标解读

• `Gather/Gather Merge`: 观察 `Workers Planned/Launched` 与每 worker 时间占比
• `Buffers`: 命中率与读写模式（shared hit/read/write），评估 IO 与缓存效果
• `JIT`: `Functions: N`、`Options:` 字段，关注编译耗时与执行收益的权衡
• `Planning/Execution Time`: 总体与阶段性耗时，结合并行开销判断收益
• `pg_stat_statements`: 采集 `calls`、`mean_time`、`rows` 来评估稳定性与抖动

## 验证要点与结论

• 并行对 CPU 密集型与大规模聚合/连接收益显著；窄索引点查收益有限
• JIT 在复杂表达式与聚合场景更显著；简短查询可能因编译开销不划算
• `work_mem` 与并行度需联动调优，避免过度内存占用或外部排序溢写
• 在高并发 OLTP 环境谨慎开启高并行度，可对分析型/批处理窗口单独调优

## 生产注意事项

• 分库分表或热数据分层时，优先在分析型节点开启并行与 JIT
• 配合 `auto_explain` 与采样，持续观察查询族的收益曲线
• 回归验证：升级小版本后复跑基准，避免计划器改动带来偏差

## 参考

• PostgreSQL 官方文档：Parallel Query、JIT、EXPLAIN
• pg_stat_statements 使用指南