性能异常调查方法论：从现象到内核根因的五层调查链

一个慢查询摆在面前，新手的第一反应是 EXPLAIN，然后盯着 cost 和 rows 猜问题。但生产环境的性能异常很少孤立存在——同样的 SQL 有时快有时慢？CPU 满载但 pg_stat_statements 里没有慢查询？shared_buffers 命中率 99% 但磁盘 IO 快被打穿？

这些问题不是一条命令能回答的。本文定义一条五层调查链，每一层聚焦一个具体问题，层与层之间有明确的”什么时候往下一层走”的决策条件。它不是工具清单，而是从现象推到内核根因的排查路径。

一、调查链全景

五层调查链的推进逻辑：从应用层可见的 SQL 统计开始，逐步往下钻到 PG 内部执行细节、等待事件、锁关系和 OS 物理资源。

flowchart TD
  L1["第一层：pg_stat_statements<br/>哪个查询是瓶颈？"]
  L2["第二层：EXPLAIN (ANALYZE, BUFFERS, SETTINGS)<br/>执行计划哪里出了问题？"]
  L3["第三层：pg_stat_activity + wait_event<br/>查询在等什么？"]
  L4["第四层：pg_locks + pg_blocking_pids()<br/>谁在锁谁？"]
  L5["第五层：OS 层工具<br/>物理瓶颈是什么？"]

  L1 -->|"定位高耗时/高频率 queryid"| L2
  L2 -->|"计划行数 vs 实际行数差异大<br/>或 buffer 访问异常"| L3
  L3 -->|"wait_event_type=Lock 或 LWLock"| L4
  L3 -->|"wait_event_type=IO<br/>或没有明显等待但 query 慢"| L5
  L4 -->|"锁等待链已定位<br/>需确认根因"| L5
```text

每层的决策条件：

- **第一层**：总是第一步。从全局统计中定位哪个 `queryid` 是瓶颈，避免随机抓取。
- **第二层**：对定位到的 queryid 做单条解剖。如果估算行数和实际行数差异超过一个数量级，或者 `Buffers: read` 占比异常高，进入第三层。
- **第三层**：查询慢但第二层看不出明显计划错误时，看它在等什么。`Lock` 和 `LWLock` 进入第四层；`IO` 进入第五层；`ClientRead` 说明不是 PG 的问题。
- **第四层**：确认锁等待链的根节点——谁持锁、等谁释放。
- **第五层**：当以上四层都用尽（计划正确、没有明显等待、没有锁竞争），问题在物理层面——磁盘带宽耗尽、CPU 自旋、内核调用栈。

---

## 二、第一层：pg_stat_statements 定位可疑查询

### 核心思路

不要从"哪个查询慢"开始，要从"哪个查询消耗的总时间最多"开始。一个执行 100000 次每次 1ms 的查询，总消耗远大于一个执行 1 次每次 1000ms 的查询。

```sql
-- 按总耗时排序，定位 top-N
SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       round(total_exec_time::numeric, 2) AS total_ms,
       round((total_exec_time / sum(total_exec_time) OVER ()) * 100, 1) AS pct,
       left(query, 200) AS query_snippet
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;

输出解读：

 queryid  | calls  | mean_ms | stddev_ms | total_ms  | pct  | query_snippet
----------+--------+---------+-----------+-----------+------+---------------
 12345678 |  50000 |   45.20 |    210.30 | 2260000.00| 62.3 | SELECT ... FROM orders JOIN ...
 23456789 | 200000 |    1.80 |      1.50 |  360000.00|  9.9 | SELECT ... FROM products WHERE id = $1
```text

这里有一个关键判断：`queryid=12345678` 的 `stddev_ms`（210.30）远大于 `mean_ms`（45.20），说明这个查询**偶尔非常慢**——执行的 50000 次中，大部分较快，但少数几次消耗了大量时间。如果 `stddev_ms` 接近 `mean_ms`，说明这个查询**一直慢**。

### 区分"一直慢"和"偶尔慢"

| 特征 | mean_ms 和 stddev_ms 的关系 | 调查方向 |
|------|---------------------------|----------|
| 一直慢 | `stddev_ms` 接近 `mean_ms`（变异系数 < 0.5） | 计划问题、索引缺失、统计信息不准 → 进入第二层 |
| 偶尔慢 | `stddev_ms` 远大于 `mean_ms`（变异系数 > 1.5） | 锁等待、计划缓存切换、checkpoint IO 风暴 → 进入第三层 |
| 频率爆发 | `calls` 突然飙升 | 应用端变更、缓存到期、连接池参数变化 → 检查应用和连接池 |

这里引入的变异系数（Coefficient of Variation）定义为 $CV = \sigma / \mu$，即 `stddev_exec_time / mean_exec_time`。CV < 0.5 说明波动在合理的随机噪声范围内；CV > 1.5 说明存在系统性的间歇性因素。

### 配合查询频率判断

高频低耗查询（如上例中 `queryid=23456789`，200000 次调用，每次 1.8ms）的优化方向不是让单次更快，而是减少调用次数——检查应用层是否有 N+1 查询、是否缺缓存层、是否可以用批量查询替代循环。

```sql
-- 按调用频率排序
SELECT queryid, calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

如果某个查询的 calls 在时间窗口内异常高，说明它处于一个循环路径中——这通常是应用代码层面的问题，不是 PG 内核能解决的。

pg_stat_statements 的边界

pg_stat_statements 基于 queryid（查询文本标准化后的 hash）聚合。它不区分同一 SQL 的不同参数值——SELECT * FROM t WHERE id = 1 和 SELECT * FROM t WHERE id = 999999 会被聚合到同一条记录。如果参数值分布极端（比如一个 id 匹配 10000 行，另一个匹配 1 行），聚合后 mean_exec_time 和 stddev_exec_time 就会反映出这个偏差。这时候需要进入第二层，用具体参数值做 EXPLAIN ANALYZE 对比。

另一个边界：pg_stat_statements 只保留 pg_stat_statements.max 条记录（默认 5000），超出后最旧的记录被淘汰。在查询量很大的系统上，低频但昂贵的查询可能被淘汰出统计窗口。这时候需要配合 log_min_duration_statement 做日志侧的慢查询捕获。

三、第二层：EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 解剖执行计划

定位到可疑的 queryid 后，用完整的 EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 获取精确的执行信息。

三个关键选项

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING, FORMAT TEXT)
SELECT ...
```text

`TIMING=on`（默认）会测量每个算子的实际耗时，但引入微小开销——对极高频查询（>10000 QPS）可关闭。

### 计划行数与实际行数的差异

这是规划器估算错误的直接证据：

```text
Hash Join  (cost=... rows=1000 width=...) (actual time=... rows=896000 loops=1)
  Hash Cond: (...)
  ->  Seq Scan on orders  (cost=... rows=50000 width=...)
        (actual time=... rows=896000 loops=1)

规划器估计 orders 表返回 50000 行，实际返回了 896000 行——差了 17 倍。这意味着：

1. 统计信息过期——ANALYZE 没跟上表的增长。
2. 统计信息精度不够——大表上 default_statistics_target（默认 100）的采样比例不足以捕捉真实分布。
3. 多列相关性未被捕获——单独看两列的分布都正常，但它们的组合有强相关性。需要用 CREATE STATISTICS 创建扩展统计。

-- 检查统计信息的更新时间
SELECT schemaname, relname, last_analyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';
```text

如果 `n_mod_since_analyze`（自上次 ANALYZE 以来修改的行数）远大于 `n_live_tup`，统计信息已严重过期。

### Buffers 输出解读

```text
Sort  (cost=... rows=...) (actual time=... rows=... loops=1)
  Sort Key: ...
  Sort Method: external merge  Disk: 15360kB
  Buffers: shared hit=128 read=32160, temp read=1920 written=1920

关键信号：

• Sort Method: external merge Disk: 15360kB：排序结果大于 work_mem，溢写到磁盘。15360kB 的临时文件读写在 NVMe 上可能几十毫秒，在 HDD 上可能几百毫秒。
• shared read=32160：这个算子从磁盘读了 32160 个 8KB 页面（约 251MB）。如果 shared_buffers 里没有这些页面，每次执行都得重新读。
• shared hit=128：只有 128 个页面在 shared_buffers 中找到，其余都是从磁盘读的。

对比一个 Buffer 友好的查询：

Seq Scan on t  (cost=... rows=...) (actual time=... rows=... loops=1)
  Buffers: shared hit=8432 read=16
```text

`shared hit=8432`、`shared read=16`——绝大多数页面在 `shared_buffers` 中，磁盘读取很少。但如果这个 t 是一个 200GB 的大表而 `shared_buffers` 只有 8GB，那么每次全表扫描都会把 buffer pool 冲一遍（这会在第五层展开讨论）。

### 实际行数与 Buffer 访问的关联

当 `actual rows` 比 `estimated rows` 大得多时，`shared read` 也往往异常高——因为规划器以为返回行数很少选择了 NestLoop + IndexScan，实际大量行触发了大量随机 I/O：

```text
Nested Loop  (cost=... rows=10 width=...) (actual time=... rows=500000 loops=1)
  Buffers: shared hit=120 read=490200

rows=10 是估算，rows=500000 是实际。NestLoop 的 Inner 端被调用了 500000 次而不是 10 次——每次都可能触发一次随机 I/O，这就是 read=490200 的来源。如果把 NestLoop 换成 HashJoin（规划器本来应该选的，如果它知道实际行数），I/O 会从 490200 次随机读变成一次顺序扫描。

四、第三层：pg_stat_activity + wait_event 诊断等待类型

如果 EXPLAIN 显示执行计划合理（估算行数接近实际、buffer 访问正常），但查询仍然慢，问题在”等”——查询在执行，但在等某个资源。

查询当前等待状态

SELECT pid, state, wait_event_type, wait_event,
       left(query, 150) AS query_snippet,
       now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY query_start;
```text

`wait_event_type` 和 `wait_event` 的组合精确描述了查询在等什么：

### wait_event 对照表

| wait_event_type | 常见 wait_event | 含义 |
|-----------------|-----------------|------|
| `Lock` | `relation` | 等表级锁（ALTER TABLE、DROP TABLE、LOCK TABLE）——查询被一个持有 `AccessExclusiveLock` 的会话阻塞 |
| `Lock` | `transactionid` | 等事务完成——查询在等另一个事务 COMMIT 或 ROLLBACK，因为需要看到它的结果 |
| `Lock` | `tuple` | 等元组锁——两个事务同时想更新同一行，第二个在等第一个提交或回滚 |
| `LWLock` | `WALWrite` | 等 WAL 写入——WAL Writer 在刷盘，Backend 在等 WAL flush 完成才能继续 |
| `LWLock` | `BufferMapping` | 等 Buffer 映射表的分区锁——大量并发访问 buffer pool 的同一个分区 |
| `LWLock` | `lock_manager` | 等锁管理器的内部锁——高并发下 Lock hash table 的分区争用 |
| `LWLock` | `ProcArrayLock` | 等 PGPROC 数组的锁——快照构建的争用（大量连接+短事务的典型瓶颈） |
| `IO` | `DataFileRead` | 等数据文件读取——磁盘 I/O |
| `IO` | `WALRead` | 等 WAL 文件读取——standby 端恢复 WAL 时的读取等待 |
| `IO` | `DataFileWrite` | 等数据文件写入——Backend 在写临时文件或做 buffer 替换时触发 |
| `BufferPin` | `BufferPin` | 等 Buffer Pin——另一个 Backend 持有一个 buffer page 的 pin 未释放 |
| `Client` | `ClientRead` | 等客户端发送下一条 SQL——**这不是 PG 的问题**，是客户端在"想"（应用处理结果、网络延迟、或客户端忘了发请求） |
| `Activity` | `LogicalLauncherMain` 等 | PG 内部辅助进程的常规等待 |

### 按等待类型快速分类

```sql
-- 统计每种 wait_event 的数量
SELECT wait_event_type, wait_event, count(*) AS sessions
FROM pg_stat_activity
WHERE state = 'active'
  AND wait_event_type IS NOT NULL
GROUP BY 1, 2
ORDER BY 3 DESC;

大量 Lock/relation 说明有 DDL 在阻塞查询；大量 IO/DataFileRead 说明磁盘不够快或 buffer 不够大；大量 LWLock/WALWrite 说明写入负载压力大。

最常见的误判：ClientRead

wait_event_type=Client, wait_event=ClientRead 表示 PG 已经处理完上一条 SQL，正在等客户端发下一条。如果大量连接处于这个状态，问题不在 PG——在应用端连接池配置、网络延迟，或者最危险的情况是 idle in transaction：连接持有锁和事务资源，但什么也不做。

-- 找出 idle in transaction 超过 5 分钟的连接
SELECT pid, usename, datname,
       state, wait_event,
       now() - xact_start AS xact_age,
       left(query, 150) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '5 minutes';
```text

`idle in transaction` 的危害不只在当前——它持有的事务快照阻止 VACUUM 回收自事务开始后产生的 dead tuple，连锁导致表膨胀。排查这个问题时，同时需要检查它持有的锁（第四层会展开）。

### pg_wait_sampling 扩展：采样而不是快照

`pg_stat_activity` 是瞬时快照——你看到的是查询瞬间的状态，而不是它在过去一分钟里的等待分布。`pg_wait_sampling` 扩展通过定期采样（默认 10ms）记录每个进程的等待事件，能捕捉到短暂的等待尖峰：

```sql
-- pg_wait_sampling 的 profile 视图：采样到的等待事件分布
SELECT event_type, event, count(*) AS samples
FROM pg_wait_sampling_profile
WHERE pid = 12345
GROUP BY 1, 2
ORDER BY 3 DESC;

如果一个 Backend 在 IO/DataFileRead 上有大量采样但在 pg_stat_activity 中碰巧不是，pg_wait_sampling 会在 profile 中捕获到。

五、第四层：pg_locks + pg_blocking_pids() 追踪锁等待链

当第三层显示 wait_event_type=Lock 时，需要追踪”谁在锁谁”。

单层锁查询

SELECT blocked.pid AS blocked_pid,
       blocked.usename AS blocked_user,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.usename AS blocking_user,
       blocking.query AS blocking_query,
       blocked.wait_event_type,
       blocked.wait_event
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.pid <> pg_backend_pid();
```text

`pg_blocking_pids()` 返回阻塞指定 pid 的所有会话的 pid 数组。但孤立的一层查询无法展示多级锁等待链——A 等 B，B 等 C，C 等 D。

### 递归 CTE 追踪锁等待树

```sql
WITH RECURSIVE lock_tree AS (
    -- 基础：所有等待锁的会话
    SELECT blocking.pid AS blocked_by,
           blocked.pid AS waiting_pid,
           blocked.wait_event,
           1 AS depth
    FROM pg_stat_activity blocked
    JOIN pg_stat_activity blocking
      ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
    WHERE blocked.pid <> pg_backend_pid()

    UNION ALL

    -- 递归：如果 blocking 会话自己也在等
    SELECT blocking2.pid AS blocked_by,
           lt.waiting_pid,
           blocked2.wait_event,
           lt.depth + 1
    FROM lock_tree lt
    JOIN pg_stat_activity blocked2
      ON blocked2.pid = lt.blocked_by
    JOIN pg_stat_activity blocking2
      ON blocking2.pid = ANY(pg_blocking_pids(blocked2.pid))
    WHERE lt.depth < 20  -- 防止无限递归
)
SELECT blocked_by, waiting_pid, wait_event, depth
FROM lock_tree
ORDER BY depth, blocked_by;

输出解读：

 blocked_by | waiting_pid | wait_event  | depth
------------+-------------+-------------+-------
      10345 |       10789 | relation    |     1
      10789 |       10812 | transactionid |     1
      10021 |       10345 | tuple       |     1
```text

这里 `pid=10812` 等 `pid=10789` 的事务锁，`pid=10789` 等 `pid=10345` 的表级锁，`pid=10345` 等 `pid=10021` 的元组锁。锁等待链的根节点是 `pid=10021`——终止它或者等它 COMMIT，才能解开整棵树。

### 查看持有的锁详情

```sql
SELECT l.pid, l.locktype, l.mode, l.granted,
       l.relation::regclass AS relation,
       a.state, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.pid IN (10021, 10345, 10789, 10812)
  AND NOT l.granted
ORDER BY l.pid;

locktype=relation, mode=AccessExclusiveLock, granted=true 且 state=idle in transaction——这个会话在某条 DDL 或 LOCK TABLE 之后忘了 COMMIT，阻塞了所有后续 DML。这是生产环境中锁等待的最常见根因。

六、第五层：OS 层确认物理瓶颈

当前四层都没有发现明显异常时，瓶颈在 OS 层面——磁盘带宽、CPU 调度、内核调用栈。这些是 PG 内核无法通过自己的统计暴露出来的。

iostat 确认磁盘

# -x 扩展统计，-m 以 MB/s 为单位，1 秒采样
iostat -xm 1
```text

关注列：

| 列 | 含义 | 危险阈值 |
|-----|------|---------|
| `r/s` / `w/s` | 每秒读/写请求数 | 随机 I/O 时 > 磁盘 IOPS 上限 |
| `rMB/s` / `wMB/s` | 每秒读/写带宽 | > 磁盘顺序带宽的 80% |
| `await` | 每个 I/O 请求的平均等待时间（ms） | SSD > 10ms，HDD > 50ms |
| `r_await` / `w_await` | 分别的读写等待时间 | 同上 |
| `%util` | 设备繁忙时间占比 | 持续 > 90% 意味着 I/O 成为瓶颈 |

如果 `%util` 接近 100% 且 `await` 高，说明磁盘是瓶颈。但要注意：`%util` 对于 NVMe（支持多队列并行）不完全准确——NVMe 可能 `%util` 100% 但仍有并行处理能力。更可靠的指标是 `r_await` 和 `w_await`。

### iotop 定位 I/O 大户

```bash
# 需要 root 权限
iotop -o -b -n 1

这会按当前 I/O 使用量排列进程。如果发现某个 Postgres Backend 的 DISK READ 或 DISK WRITE 异常高，记下它的 PID，回到 pg_stat_activity 看它在执行什么查询。

perf 追踪 CPU 调用栈

当 CPU 使用率高但没有明显的慢查询（第二层和第三层都找不到）时，用 perf 看 CPU 到底花在哪：

# 采样所有 CPU 10 秒，按函数聚合调用栈
perf record -g -p $(pgrep -f "postgres: user") -- sleep 10
perf report --stdio --sort=overhead,symbol
```text

输出示例：

```text
Overhead  Symbol
  12.30%  s_lock
   5.20%  hash_search_with_hash_value
   4.80%  LWLockAcquire
   3.10%  AllocSetAlloc

如果 s_lock（spinlock）在栈顶，说明正在自旋等待一个短临界区——这通常是 LWLock 争用的下游表现。LWLockAcquire 占比高直接说明 LWLock 争用严重。两个场景会在第七节和第八节展开。

bpftrace 追踪内核调用栈

当 I/O 等待高时，用 bpftrace 追踪 PG 进程在哪些内核函数上阻塞：

# 追踪 postgres 进程的 I/O 阻塞调用栈
bpftrace -e '
kprobe:blk_mq_make_request /comm == "postgres"/ {
    @io_pid[pid] = count();
}'
```text

```bash
# 追踪 fsync 调用（确认是否由 checkpoint/WAL 导致 IO 尖峰）
bpftrace -e '
kprobe:__x64_sys_fsync /comm == "postgres"/ {
    printf("pid=%d tid=%d\n", pid, tid);
    print(ustack);
}'

如果大量 fsync 来自 checkpointer 进程（postgres: checkpointer），说明 checkpoint 正在刷脏页——IO 尖峰的根因在第 4 章解释的 checkpoint 流程中（checkpoint_completion_target 的摊平策略和 max_wal_size 的触发边界）。

七、特殊场景一：有时快有时慢 —— 计划缓存与 generic/custom plan

现象

同一个 queryid，stddev_exec_time 是 mean_exec_time 的 3 倍以上。EXPLAIN 给出的计划看起来很好——但在慢的时候 PG 实际用了另一个计划。

根因：generic plan 与 custom plan 的切换

PG 对 prepared statement 使用两种计划：

• Custom plan：每次执行时根据具体参数值做规划。参数化查询的第一到第四次（默认五次前）都使用 custom plan。
• Generic plan：对参数值做平均化假设，生成一个对所有参数值都”不太差”的计划，缓存后重复使用。第五次执行时，如果 generic plan 的代价不比 custom plan 高太多，PG 会选择 generic plan 并缓存它。

问题出在”参数值分布极端倾斜”的场景：90% 的查询用 status = 'active' 返回 1000 行，10% 用 status = 'pending' 返回 1000000 行。Generic plan 假设平均返回行数，如果 planner 选择了 NestLoop（适合 1000 行的），那 10% 的 “pending” 查询就会跑出灾难性的性能。

-- 查看当前 plan_cache_mode 设置
SHOW plan_cache_mode;
-- 可选值：auto（默认）、force_custom_plan、force_generic_plan
```bash

### 诊断方法：auto_explain 捕获慢查询的实际计划

```conf
# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '2s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = on
auto_explain.log_nested_statements = on

auto_explain.log_analyze=on 会在查询超过阈值时把 EXPLAIN (ANALYZE, BUFFERS) 的输出写入 PG 日志。对比同一 queryid 的快慢两次日志：

• 快的那次：Nested Loop，actual rows=1020
• 慢的那次：Nested Loop，actual rows=986000——但 planner 以为只有 1000 行

这就是 generic plan 对倾斜数据的选择偏差。

解决方案顺序

1. 如果参数倾斜严重，SET plan_cache_mode = force_custom_plan 让每次都用实参规划。代价是每次调用 planner，对极高频查询（>10000 QPS）的 planner CPU 开销不可忽略。
2. 如果倾斜在特定参数，用扩展统计（CREATE STATISTICS）让 planner 知道列之间的相关性，使 generic plan 也能做出合理选择。
3. 如果计划不稳定但 force_custom_plan 的 CPU 开销不可接受，考虑 PG 17+ 的 plan_cache_mode = auto 配合 CUSTOM_PLAN_THRESHOLD 调大 custom plan 的使用次数。

八、特殊场景二：CPU 100% 但没慢查询 —— LWLock 自旋与隐藏开销

现象

top 显示 PG 进程的 CPU 使用率接近 100%，但 pg_stat_statements 中单个查询的 mean_exec_time 都很低（<5ms），只是 calls 很高。pg_stat_activity 中大量会话的 wait_event_type=LWLock。

根因一：LWLock 自旋

LWLock 使用自旋（spin）+ 等待的混合策略：先自旋一定次数（默认 SPIN_DELAY），如果还获取不到锁，才通过 semop() 进入内核等待。在高并发短查询场景下，大量 Backend 可能在同一个 LWLock 上自旋，导致 CPU 满载但查询本身不慢——CPU 花在了等锁而不是执行上。

# 用 perf 确认
perf top -p $(pgrep -f "postgres:")
```text

如果 `LWLockAcquire`（或内部的自旋函数）出现在栈顶，说明 LWLock 争用是 CPU 的主要消耗。

PG 14 之前的 snapshot scalability 问题就是典型：`GetSnapshotData()` 需要 `ProcArrayLock` 的共享锁，1000 个并发连接意味着每次获取快照都要竞争这个锁。PG 14 将 `xid`/`xmin` 的更新改为 per-backend 原子操作，大幅减少了持锁路径。

PG 17 中 LWLock 的实现使用了 `LWLockWaitListLock` 优化——等待者不再自旋在同一个锁上，而是自旋在 per-lock 的等待列表锁上，减少了 cache line 的 ping-pong。

### 根因二：Extended Statistics 构建

如果在 `perf top` 中看到大量的 `statext_mcv_serialize` 或 `mcv_list_build`，说明 `CREATE STATISTICS` 的扩展统计构建在消耗 CPU。扩展统计的 MCV（Most Common Values）列表在 `ANALYZE` 时计算，对于多列组合的大表，构建组合 MCV 的 CPU 开销可能非常大。

这通常发生在配置了 `CREATE STATISTICS ON a, b, c FROM t` 但三列的基数都很高时——MCV 的组合数可能是指数级的。

### 根因三：表达式索引维护

表达式索引（如 `CREATE INDEX ON t ((lower(name)))`）在每次 INSERT/UPDATE 时都要计算表达式值。如果表达式计算本身很重（如复杂的字符串处理或 JSON 操作），高频率的写入会导致 CPU 被表达式计算消耗。

```sql
-- 检查表达式索引及其在 pg_stat_user_indexes 中的扫描次数
SELECT indexrelid::regclass, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelid::regclass::text LIKE '%expr%';

idx_scan=0 但 pg_stat_user_tables.n_tup_ins 很大——这个索引从没被查询用上，但一直在消耗维护 CPU。这种情况考虑删除该索引或用生成列替代表达式索引（PG 12+ 支持 GENERATED ALWAYS AS ... STORED，表达式只算一次）。

九、特殊场景三：IO 打满但命中率 99% —— 命中率骗局

现象

pg_stat_bgwriter 或 Prometheus 的 shared_buffers 命中率（buffers_alloc / (buffers_alloc + buffers_backend_fsync + ...) 的变形）显示 99%+ 的命中率，但磁盘 iostat 显示 %util 接近 100%，await 很高。

为什么命中率会撒谎

命中率（buffer hit ratio）说的是当 Backend 请求一个页面时，有多少比例在 shared_buffers 中找到。这个指标的陷阱在于：它把所有请求同等看待，但一个频繁访问的”热”页面的命中量可以淹没大量不频繁的”冷”页面的未命中。

设想一个场景：有一个 200GB 的 logs 表，每天做一次全表顺序扫描用于报表。另外有一批 100MB 的”热”表（用户表、配置表、状态表）被频繁查询。shared_buffers = 8GB。热表的页面全在 shared_buffers 中，每次命中。报表的 seqscan 每次都要把 200GB 扫描一遍——这些页面走 shared_buffers 的 clock sweep 路径，用完就淘汰。但热表每秒产生数万次命中，报表的 seqscan 只产生几千次未命中。分子（hit）远大于分母（hit+read）——命中率 99%。但磁盘的 I/O 带宽被 seqscan 占满。

验证：pg_statio_user_tables 定位 I/O 源头

SELECT relid::regclass AS table_name,
       heap_blks_read, heap_blks_hit,
       round(heap_blks_hit * 100.0 /
             NULLIF(heap_blks_hit + heap_blks_read, 0), 1) AS hit_pct,
       idx_blks_read, idx_blks_hit
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 10;
```text

输出：

```text
 table_name | heap_blks_read | heap_blks_hit | hit_pct | idx_blks_read | idx_blks_hit
------------+----------------+---------------+---------+---------------+--------------
 logs       |      12000000  |      8000000  |    40.0 |             0 |            0
 orders     |         300000 |     12000000  |    97.6 |         15000 |     20000000
 users      |           1200 |      5000000  |    99.9 |           100 |      3000000

logs 表的 hit_pct=40.0%——它的 buffer 命中率很低，而且 heap_blks_read=12000000（约 93GB）是绝对的 I/O 大户。orders 和 users 的命中率都超过 97%，但由于它们的页面数远少于 logs，它们对整体命中率的贡献被稀释了。

解决方案方向

1. 如果是报表类的 seqscan 导致 IO 打满，BAS_BULKREAD buffer access strategy 会让 PG 使用”小圈”替换——seqscan 读取的页面在 clock sweep 中替换一个小的 buffer ring（默认 256KB），不会污染整个 buffer pool。但 200GB 的表仍需 200GB 的磁盘读取。
2. 如果表中有一个”总是递增”的索引字段（如 created_at），考虑用 BRIN 索引替代 B-Tree——BRIN 用范围摘要而不是逐行索引，对顺序访问的大表 I/O 压力小得多。
3. 如果是频繁的 SELECT COUNT(*) 或类似的聚合导致 seqscan，考虑用物化视图或定期快照。
4. 如果 shared_buffers 足够大（> 16GB）但 seqscan 仍然打满 IO，增配 shared_buffers 的真正收益是减少重复 seqscan 的磁盘读取——前提是同一份数据被多次扫描，且 buffer pool 足够容纳它。

十、工具链集成：pg_stat_statements + pg_wait_sampling + pg_stat_kcache

三个扩展组合使用，覆盖了查询统计、等待事件采样和 OS 层资源消耗三个维度：

pg_wait_sampling

pg_wait_sampling 以固定频率（默认 10ms）采样每个 Backend 的等待事件并写入共享内存中的环形缓冲区，支持两种查询模式：

-- 当前所有进程的等待事件
SELECT pid, event_type, event, query
FROM pg_wait_sampling_current;

-- 历史采样 profile（累计统计）
SELECT pid, event_type, event, count(*) AS samples
FROM pg_wait_sampling_profile
GROUP BY 1, 2, 3
ORDER BY 4 DESC;
```text

与 `pg_stat_activity` 的瞬时快照不同，`pg_wait_sampling_profile` 是累计视图——一个查询在 60 秒执行期间如果只有 2 秒阻塞在 `IO/DataFileRead`，瞬时快照 90% 抓不到，但 profile 会准确反映这 2 秒的占比。

### pg_stat_kcache

`pg_stat_kcache` 是 `pg_stat_statements` 的扩展，为每个 queryid 增加操作系统层的统计维度：

- `reads` / `writes`：该查询触发的 OS 层 read/write 系统调用次数
- `read_bytes` / `write_bytes`：读取/写入的字节总数
- `user_time` / `system_time`：用户态和内核态的 CPU 时间
- `minflts` / `majflts`：minor page fault（内存不足触发 page fault）和 major page fault（磁盘 I/O 触发的 page fault）

这些指标直接把 queryid 和 OS 层的物理资源消耗关联起来：

```sql
SELECT pss.queryid,
       pss.calls,
       round(pss.mean_exec_time::numeric, 2) AS mean_ms,
       kc.read_bytes / NULLIF(kc.calls, 0) AS avg_read_bytes_per_call,
       kc.system_time / NULLIF(kc.calls, 0) AS avg_system_time_per_call
FROM pg_stat_statements pss
JOIN pg_stat_kcache kc ON pss.queryid = kc.queryid
ORDER BY kc.read_bytes DESC
LIMIT 10;

如果一个查询的 system_time 占比异常高（system_time >> user_time），说明它把大量时间花在内核调用上——通常是 read() / write() 系统调用。结合 avg_read_bytes_per_call 可以判断是 I/O 密集型查询。如果大量 majflts（major page fault），说明 shared_buffers 不足或 OS page cache 不够，查询在读磁盘。

三者的组合使用场景

```mermaid flowchart TD PSS[“pg_stat_statements
哪个 queryid 耗 CPU/时间最多？”] KC[“pg_stat_kcache
该 queryid 的 OS 层读写量、
系统调用时间比？”] WS[“pg_wait_sampling
该 Backend 在采样窗口内
等待事件分布？”] ACT[“pg_stat_activity
当前瞬间该 Backend
在等什么？”]

PSS –>|“定位 queryid 和 pid”| KC PSS –>|“定位 queryid 和 pid”| WS WS –>|“怀疑当前正在等时”| ACT KC –>|“read_bytes 很大但
wait_event 不是 IO？”| ACT ```text

典型调查路径：

1. pg_stat_statements 定位 queryid 和对应的 userid/dbid。
2. pg_stat_kcache 看该 queryid 的 read_bytes、system_time 比例——确认真的是 I/O 问题还是 CPU 问题。
3. pg_wait_sampling_profile 看该 queryid 对应 Backend 的等待事件历史分布——是 IO/DataFileRead、LWLock、还是 Lock。
4. 如果需要实时确认，pg_stat_activity 看当前瞬间的 wait_event。

十一、调查清单总结

五层调查链的核心原则：每一层给出一个明确的判断，告诉你问题在本层还是需要往下走。不要跳过层级——先看全局统计，再看单条计划，再看等待原因，再看锁关系，最后才上 OS 工具。跳层排查的典型错误是在还没确定是 CPU 还是 I/O 问题的时候就去调 shared_buffers 大小。

十二、关键要点

1. 五层调查链按顺序走——pg_stat_statements 定位 query → EXPLAIN (ANALYZE, BUFFERS) 看计划偏差 → wait_event 看等什么 → pg_locks 追锁链 → OS 工具确认硬件/内核栈。
2. stddev_exec_time 区分”一直慢”和”偶尔慢”——CV 高说明计划或锁竞争不稳定，需要配合 auto_explain 抓慢实例。
3. 命中率 99% 可能是假象——聚合命中率掩盖大表 seqscan 和 buffer churn；必须分表看 pg_statio_user_tables。
4. CPU 100% 无慢查询时查 LWLock 自旋和短查询风暴——perf top 看 LWLockAcquire / s_lock，pg_stat_statements 看 calls 极高的轻量查询。
5. 扩展工具组合使用——pg_wait_sampling 补历史等待分布，pg_stat_kcache 把 queryid 关联到 OS 层 read/write 字节。

上一章：经典故障模式与排查手册 下一章：数据恢复与损坏应对

参考资料

源码（PG 17）

• src/backend/utils/adt/pgstatfuncs.c：pg_stat_statements、pg_stat_activity 等统计视图的 C 端实现
• src/backend/storage/lmgr/lwlock.c：LWLockAcquire()、LWLockRelease()、spinlock 等待循环
• src/backend/storage/lmgr/lock.c：LockAcquire()、LockRelease()、heavyweight lock 管理
• src/backend/storage/lmgr/deadlock.c：DeadLockCheck() 死锁检测算法
• src/backend/storage/buffer/bufmgr.c：ReadBuffer()、Buffer Access Strategy（BAS_BULKREAD）
• src/backend/utils/cache/plancache.c：plan cache 逻辑、generic vs custom plan 选择
• src/backend/postmaster/autovacuum.c：autovacuum launcher 的触发逻辑

官方文档

• PostgreSQL Documentation, Chapter 27: Monitoring Database Activity（pg_stat_activity、pg_locks、pg_stat_statements）
• PostgreSQL Documentation, Chapter 14.4: Populating a Database（ANALYZE 和统计信息）
• PostgreSQL Documentation, Chapter 19: Server Configuration（plan_cache_mode、shared_buffers、work_mem）
• PostgreSQL Documentation, Chapter 61: Index Access Methods（BRIN 索引的场景定位）

扩展文档

• pg_stat_statements：PostgreSQL contrib 模块，查询统计聚合
• pg_wait_sampling：PostgresPro 开源扩展，等待事件采样（GitHub: postgrespro/pg_wait_sampling）
• pg_stat_kcache：Dalibo 开源扩展，OS 层资源统计关联到 queryid（GitHub: dalibo/pg_stat_kcache）
• auto_explain：PostgreSQL contrib 模块，自动记录慢查询的执行计划