从 3.2s 到 420ms：PG 双参数联合调优实录

为什么单调 shared_buffers 经常无效

很多团队把 PostgreSQL 性能优化简化为一句经验法则：shared_buffers 设成物理内存的 25%。在 64GB 机器上，这通常意味着 16GB。但压测时会发现，把 shared_buffers 从 4GB 提到 16GB，复杂查询可能只快了 5%；而一个多表 Hash Join 却仍然要 3 秒以上。

原因并不复杂：shared_buffers 解决的是“页是否需要反复从操作系统读入”，work_mem 解决的是“排序、Hash、物化是否被迫落盘”。前者影响缓存命中，后者影响执行算子。两个参数分别作用于查询生命周期的不同阶段，只调一个，很容易让另一个成为瓶颈。

本文记录一次 64GB 内存、CentOS、PostgreSQL 16 的只读分析型压测。数据规模约 1.8 亿行，热点表 45GB，包含范围过滤、排序分页、三表 Join、聚合查询。最终通过联合调整 shared_buffers、work_mem，并配合执行计划验证，将核心混合负载 P95 从 3.2s 降到 420ms。

参数作用边界：别把两类内存混为一谈

shared_buffers 是实例级共享缓存，所有会话共同使用。PostgreSQL 从磁盘读取的数据页、索引页会先进入这块区域。它越大，越能容纳热点表和索引，但不等于“越大越好”：过大可能挤压操作系统页缓存，降低检查点效率，也会让脏页管理压力上升。

work_mem 是算子级、会话可叠加的内存上限。一个查询如果有两个 Sort 或 Hash 节点，理论上可能同时使用多份 work_mem；高并发下还要乘以连接数。它主要影响以下算子：

• Sort：ORDER BY、CREATE INDEX、某些 DISTINCT；
• Hash：Hash Join、Hash Aggregate；
• Material：某些子查询、函数扫描与并行执行；
• 增量排序：前一段有界排序完成后，再对后续分组排序。

下面这张图概括了一次查询中两类内存的位置：

64GB 基线配置与压测模型

测试机为 16 vCPU、64GB 内存、NVMe SSD。初始配置来自某云厂商默认参数：shared_buffers=4GB，work_mem=4MB，max_connections=300，effective_cache_size=20GB。业务侧使用连接池，实际活跃连接约 30～50 个。

先构造订单、明细、用户三张表，并插入具备数据倾斜的记录：

-- 可直接在测试库执行：创建三张压测表
CREATE UNLOGGED TABLE users (
    user_id BIGINT PRIMARY KEY,
    city TEXT NOT NULL,
    reg_time TIMESTAMPTZ NOT NULL
);

CREATE UNLOGGED TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status INT NOT NULL,
    amount NUMERIC(12,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

CREATE UNLOGGED TABLE order_items (
    item_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    sku_id BIGINT NOT NULL,
    qty INT NOT NULL,
    price NUMERIC(12,2) NOT NULL
);

-- 生成 100 万用户
INSERT INTO users
SELECT g,
       (ARRAY['北京','上海','广州','深圳','杭州'])[1 + (g % 5)],
       now() - (g || ' second')::interval
FROM generate_series(1, 1000000) AS g;

-- 生成 3000 万订单，状态和时间均有倾斜
INSERT INTO orders
SELECT g,
       1 + (g * 7919) % 1000000,
       CASE WHEN g % 10 = 0 THEN 2 ELSE 1 END,
       round((random() * 500 + 20)::numeric, 2),
       now() - ((g % 8640000) || ' second')::interval
FROM generate_series(1, 30000000) AS g;

-- 生成 9000 万订单明细
INSERT INTO order_items
SELECT g,
       1 + (g * 13) % 30000000,
       1 + random() * 200000,
       1 + (g % 3),
       round((random() * 300 + 10)::numeric, 2)
FROM generate_series(1, 90000000) AS g;

CREATE INDEX idx_orders_created ON orders(created_at DESC);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_items_order ON order_items(order_id);
ANALYZE users;
ANALYZE orders;
ANALYZE order_items;

核心压测 SQL 有两类。第一类是大范围排序分页：

-- 排序 + Join：容易触发 Sort 临时文件
SELECT o.order_id, o.user_id, u.city, o.amount
FROM orders o
JOIN users u ON u.user_id = o.user_id
WHERE o.created_at >= now() - INTERVAL '60 days'
ORDER BY o.amount DESC
LIMIT 100;

第二类是聚合与三表 Join：

-- Hash Join + Hash Aggregate：work_mem 不足时容易分批 Hash
SELECT u.city, count(*) AS order_cnt, sum(o.amount) AS gmv
FROM orders o
JOIN users u ON u.user_id = o.user_id
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 1
GROUP BY u.city
ORDER BY gmv DESC;

第一轮诊断：缓存命中率掩盖了落盘问题

只看 pg_stat_bgwriter 或缓存命中率，会得出“4GB shared_buffers 还可以”的结论，因为热点索引确实能获得较高命中：

-- 查看数据库块读取与命中情况
SELECT datname,
       blks_read,
       blks_hit,
       round(blks_hit::numeric /
             NULLIF(blks_hit + blks_read, 0) * 100, 2) AS hit_ratio_pct
FROM pg_stat_database
WHERE datname = current_database();

但执行计划暴露了另一个问题。开启 BUFFERS 与 TIMING：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.order_id, o.user_id, u.city, o.amount
FROM orders o
JOIN users u ON u.user_id = o.user_id
WHERE o.created_at >= now() - INTERVAL '60 days'
ORDER BY o.amount DESC
LIMIT 100;

计划中出现了典型信号：

• Sort Method: external merge Disk: 182MB：排序溢出到磁盘；
• Hash Batches: 32：Hash 表被分成多批，反复读写；
• Temp Read/Write Blocks 明显增长；
• 共享页命中尚可，但执行时间主要耗在排序和 Hash 批次上。

查询临时文件累计量也能验证这一点：

-- 本会话的临时文件读写块数
SELECT temp_files,
       pg_size_pretty(temp_bytes) AS temp_bytes_total
FROM pg_stat_database
WHERE datname = current_database();

这就是单看 shared_buffers 的陷阱：页可能已经在操作系统缓存里，读取并不慢；但每个会话的排序和 Hash 仍然在小内存中被迫分批、落盘、回读。

第二轮：shared_buffers 与 work_mem 联合调整

64GB 内存不能只给 PostgreSQL 共享缓存。需要预留操作系统、连接后端、维护操作、并行 worker 与其他进程空间。最终采用如下基线：

# 64GB 内存、分析与交易混合负载的保守起点
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 32MB
maintenance_work_mem = 2GB
max_connections = 200
random_page_cost = 1.1
effective_io_concurrency = 200

为什么 work_mem 不是直接给 512MB？因为它不是实例总预算。假设 50 个活跃连接，每个查询包含 2～3 个内存密集算子，32MB 在多数场景下已经能消除大量排序落盘；极端报表再通过会话级或事务级参数单独放大，而不是全局拉高。

调整后重启或通过配置文件 reload。注意 shared_buffers 需要重启，work_mem 可对新会话生效：

-- 查看当前生效值
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('shared_buffers','work_mem',
               'maintenance_work_mem','effective_cache_size');

-- 只给当前报表会话提高 work_mem，不影响全局
SET work_mem = '256MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.city, count(*) AS order_cnt, sum(o.amount) AS gmv
FROM orders o
JOIN users u ON u.user_id = o.user_id
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 1
GROUP BY u.city
ORDER BY gmv DESC;

第二轮计划中，排序从 external merge disk 变为 top-N heapsort，Hash Batches 降为 1，三表聚合查询耗时从 3.2s 降到约 850ms。范围排序查询从 1.6s 降到约 420ms。

第三轮：用 pg_stat_statements 找到真正该放大的查询

参数调优不能只靠单条 SQL 的 EXPLAIN。上线前应使用 pg_stat_statements 观察总耗时、调用次数、临时块与读写特征，识别最值得使用会话级大 work_mem 的报表：

-- 如果尚未启用扩展，需要超级用户创建
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 找总耗时最高、可能产生大量临时块的 SQL
SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(total_exec_time::numeric, 2) AS total_ms,
       temp_blks_read,
       temp_blks_written,
       left(query, 120) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

压测对比结果如下：

配置	shared_buffers	work_mem	排序现象	Hash 批次	P95	风险
云默认	4GB	4MB	大量磁盘归并	16~64	3.2s	临时文件频繁
只扩缓存	16GB	4MB	仍落盘	16~32	2.8s	误以为缓存无效
联合调优	16GB	32MB	Top-N 内存排序	1~4	420ms	需控制并发
激进配置	24GB	256MB	多数内存完成	1	390ms	内存峰值不可控

激进配置只让 P95 再降约 7%，却在 100 并发压测中触发过一次内存逼近上限。考虑到收益与稳定性，16GB + 32MB 是更合理的默认值，个别报表使用 SET LOCAL work_mem 精确放大。

避坑总结：至少检查这五件事

1. work_mem 按算子倍增，不要按连接数平均分配

常见错误是直接设置 work_mem = 1GB，理由是“机器有 64GB”。现象是低并发报表很快，一到业务高峰，多个 Hash Join 查询同时运行，内存迅速上涨甚至触发 OOM。修正方法是先估算：活跃连接数 × 单查询内存密集算子数 × work_mem，再给 OS 和 shared_buffers 留足空间。全局保守、局部放大才是稳妥策略。

2. 不要只看缓存命中率就判断 shared_buffers 合理

命中率高但查询慢，通常意味着瓶颈不在数据页读取，而在排序、Hash、锁、网络或执行计划。应同时查看 EXPLAIN (ANALYZE, BUFFERS) 中的 Sort Method、Hash Batches、临时文件，以及 pg_stat_statements 的 temp_blks_written。本次 4GB 配置就是典型“命中率不难看、落盘很严重”。

3. 盲目把 shared_buffers 设到内存 50% 可能适得其反

PostgreSQL 依赖操作系统页缓存作为第二层缓存。shared_buffers 过大可能挤压 OS cache，也可能增加检查点和脏页刷写压力。64GB 机器上 OLTP 或混合负载通常从 16GB 起步，配合 effective_cache_size=48GB 告诉优化器系统有较大缓存，而不是真的分配 48GB。

4. work_mem 调大后要重新核对执行计划稳定性

增大 work_mem 可能让规划器从排序切换到 Hash Aggregate，或选择不同 Join 路径。大多数时候这是收益，但统计信息过旧时也可能选错计划。每次参数调整后应对核心 SQL 重新 ANALYZE 关键表，并保留基线计划。出现行数估计严重偏差时，优先修正统计信息与索引，而不是继续堆内存。

5. 连接池配置必须纳入内存公式

很多事故不是 PostgreSQL 单查询需要太多内存，而是应用突发建立数百个连接，每个连接都运行带多个 Hash 节点的 SQL。max_connections 不应作为性能手段。生产环境建议使用 PgBouncer 或内置连接池控制活跃会话，把并发限制在 CPU 与内存预算可承受范围内，再对报表队列单独限流。

最终建议是：64GB PostgreSQL 不要追求一张“万能参数表”，而应建立“全局安全基线 + EXPLAIN 证据 + 慢 SQL 局部覆盖”的调优闭环。shared_buffers 负责让热点数据少走 IO，work_mem 负责让排序和 Hash 少落盘，两者联合验证，压测数字才具备真实参考价值。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。