-- 查看 buffer 命中率（pg_stat_bgwriter 的口径）
SELECT
  buffers_checkpoint,
  buffers_clean,
  buffers_backend,
  buffers_alloc,
  ROUND(100.0 * (buffers_checkpoint + buffers_clean + buffers_backend) /
        NULLIF(buffers_checkpoint + buffers_clean + buffers_backend + buffers_alloc, 0), 2)
    AS sync_io_pct
FROM pg_stat_bgwriter;
```text

`sync_io_pct` 越接近 0，说明命中率越高——但这个指标存在"命中率骗局"：如果大量 shared_buffers 都用在重复访问同一小批热页面，而大表 Seq Scan 每次都读磁盘，命中率仍然可以接近 99% 但查询可能很慢。

**Step 2：用 `pg_buffercache` 观察 buffer 的实际分布。**

```sql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_buffercache;

-- 每个表占用了多少 shared_buffers
SELECT
  c.relname,
  COUNT(*) AS buffers,
  pg_size_pretty(COUNT(*) * 8192) AS size
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY COUNT(*) DESC
LIMIT 20;

# 查看 OS 层面的 page cache
free -h
# 查看 PG 的共享内存段大小
ipcs -m | grep postgres
# 用 pg_buffercache 统计 shared_buffers 中脏页比例
```text

```sql
SELECT
  isdirty,
  COUNT(*) AS buffers,
  pg_size_pretty(COUNT(*) * 8192) AS size
FROM pg_buffercache
GROUP BY isdirty;

SELECT datname, temp_files, temp_bytes,
       pg_size_pretty(temp_bytes) AS temp_size,
       stats_reset
FROM pg_stat_database
WHERE datname = current_database();
```text

如果 `temp_files` 增长很快，且你知道当前负载中有 hash join 或大 sort，说明 `work_mem` 太小——数据在 spill 到磁盘。

**Step 2：估计当前负载的实际内存需求。**

```sql
-- 当前有哪些查询在执行 hash join / sort
SELECT pid, query,
       wait_event_type, wait_event,
       state
FROM pg_stat_activity
WHERE state = 'active'
  AND (query ILIKE '%hash%' OR query ILIKE '%sort%')
  AND pid != pg_backend_pid();

-- 当前活跃连接数
SELECT COUNT(*) AS active_connections
FROM pg_stat_activity
WHERE state = 'active';

-- 每个数据库的临时文件统计
SELECT datname, temp_files,
       pg_size_pretty(CASE WHEN temp_bytes > 0
         THEN temp_bytes / NULLIF(temp_files, 0) ELSE 0 END) AS avg_temp_file_size
FROM pg_stat_database;
```text

如果 `avg_temp_file_size` 很小但 `temp_files` 很多，说明操作经常只超出 `work_mem` 一点就 spill——稍微增大 `work_mem` 就能消除大部分 spill。但如果 `avg_temp_file_size` 很大，意味着部分操作的数据量远超 `work_mem`——需要从查询优化角度减少参与 hash/sort 的行数，而不是无脑增 `work_mem`。

**Step 3：`hash_mem_multiplier` 的联动检查。**

```sql
SHOW hash_mem_multiplier;
SHOW work_mem;

-- 以人类可读的方式显示 hash 操作的实际内存上限
SELECT setting, unit,
       CASE WHEN unit = 'MB' THEN setting::numeric * 1024 * 1024
            WHEN unit = 'kB' THEN setting::numeric * 1024
            WHEN unit = 'GB' THEN setting::numeric * 1024 * 1024 * 1024
            ELSE setting::numeric
       END AS bytes
FROM pg_settings
WHERE name = 'work_mem' OR name = 'hash_mem_multiplier';
```text

**正确做法**：`work_mem` 不要全局设大。用 `ALTER DATABASE`、`ALTER USER` 或连接池的 session 级别参数，为不同负载类型设定不同值：OLTP 报告查询 → 16-64MB，日常轻查询 → 4-16MB。上线前在 staging 环境用真实负载跑一轮，观察 `temp_files` 增量和物理内存占用，确认 `work_mem × max_connections × 平均并行度 × 操作数` 不超过物理内存的 60%。

---

## 三、effective_cache_size：不分配内存，只影响优化器的决策

### 这个参数在内核里控制什么

`effective_cache_size` 是优化器代价模型中用于估算 I/O 代价的参数。它本身**不分配任何内存**——它只是告诉优化器："你可以假设查询用到的数据页中，有这么多能在这层缓存（shared_buffers + OS page cache）中被找到。"

在 `cost_index()` 函数（`src/backend/optimizer/path/costsize.c`）中，索引扫描的 I/O 代价计算依赖于一个关键假设：索引页中有一部分已经在缓存中，不需要物理 I/O。这个"已经在缓存中的比例"由以下公式估算：

$$\text{cache\_hit\_fraction} = \min\left(1, \frac{\text{effective\_cache\_size}}{\text{所有参与查询的表的页面数}}\right)$$

如果 `effective_cache_size` 远小于表大小，优化器认为大部分索引页和 heap 页需要从磁盘读取——于是索引扫描的估算代价偏高，优化器倾向于 Seq Scan。如果 `effective_cache_size` 大到能覆盖全部数据，优化器认为几乎所有页面都在缓存中——索引扫描代价显著降低，优化器倾向于使用索引。

（代价模型的完整拆解见[第 10 章：统计信息与代价模型](../10-statistics-cost/10-statistics-cost.html)。）

### 为什么默认值在你的场景下可能是错的

默认值 4GB 在内存只有 4-8GB 的系统上偏高，在内存 256GB 的服务器上则严重偏低。

关键误区：`effective_cache_size` 应该反映真实可用的缓存大小——包括 shared_buffers 和 OS page cache。在典型 PG 部署中，OS page cache 通常远大于 shared_buffers（因为 PG 开发者建议 shared_buffers 只占物理内存的 15-25%）。所以**真实的"缓存"主要是 OS page cache，不是 shared_buffers。**

设太小（如默认 4GB 在 128GB 服务器上）的后果：优化器不相信索引页能被缓存，高估索引扫描代价 → 对大于内存的表倾向于 Seq Scan → `EXPLAIN` 里看到大量不该出现 Seq Scan 的查询计划。

### 配错后的症状

- `EXPLAIN` 输出中，优化器对有明显索引可用且有过滤条件的查询仍然选择 Seq Scan，且代价估算的前几行就显示 `cost=0.00..XXXX`（Seq Scan 的启动代价为 0）。
- 查询响应时间比预期慢，且 `EXPLAIN (ANALYZE, BUFFERS)` 显示 Seq Scan 读取了大量 `shared read`（从磁盘读），而你知道索引存在且选择性很好。
- 使用 `SET enable_seqscan = off;` 后查询显著变快——说明优化器选 Seq Scan 不是因为索引差，而是代价估算失衡。

### 查验方法

```sql
-- 当前值
SHOW effective_cache_size;

-- 查看系统真实可用内存
-- 在 shell 中：free -h

-- 检查某张表是否被优化器错误估算
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT * FROM large_table WHERE indexed_column = 'rare_value';
```text

关注输出中的 `Settings: effective_cache_size`、`Buffers: shared hit/read` 对比，以及 `rows` 估算与实际行数的偏差——如果偏差一个数量级以上，问题可能不只在 `effective_cache_size`，也在统计信息（参见[统计信息漂移的诊断](../10-statistics-cost/10-statistics-cost.html)）。

**正确做法**：设 `effective_cache_size` 为系统可用内存的 50-75%（不是物理总内存——要扣除其他应用和 OS 本身的开销）。例如 128GB 服务器上，减去 OS 和其他进程约 16GB，剩余 112GB 的 60% 约为 67GB——设 `effective_cache_size = 64GB` 是一个合理起点。最终依据是 `EXPLAIN` 输出的计划是否与实际最优计划一致。

---

## 四、random_page_cost：默认 4.0 在 NVMe 上严重高估随机 I/O

### 这个参数在内核里控制什么

`random_page_cost` 是优化器代价模型中的核心常量之一，表示从磁盘随机读取一个页面的相对代价。它的默认值 4.0，意味着优化器假定：随机读一页的代价是顺序读一页（`seq_page_cost`，默认 1.0）的 4 倍。

这个值直接影响所有索引扫描（Index Scan、Index-Only Scan、Bitmap Scan）的代价估算。索引扫描的本质是"索引页的随机读 + heap 页的随机读"——如果 `random_page_cost` 偏高，这两个操作的估算代价就偏高，优化器就倾向于放弃索引选择 Seq Scan。

历史上 4.0 对应的是机械硬盘（HDD）的寻道延迟（~4ms random seek vs ~1ms sequential read）。但 NVMe SSD 的随机读延迟通常在 0.1-0.3ms，与顺序读的差异很小——此时 `random_page_cost = 4.0` 意味着优化器认为索引扫描的代价是实际代价的 10-40 倍。

### 为什么默认值在你的场景下可能是错的

在 NVMe SSD（甚至高端 SATA SSD）上，默认值 4.0 严重偏差。`pg_test_fsync` 工具的实测数据可以帮助量化差距：

```bash
# PG 自带的 fsync 性能测试
pg_test_fsync
# 输出示例（NVMe SSD）：
# Compare file sync methods using one 8kB write:
#         open_datasync                 n/a
#         fdatasync                     n/a   (fsync 约为 0.1ms)
# 磁盘寻道时间的主导因子已经降到微秒级

fio --name=randread --ioengine=libaio --iodepth=1 --rw=randread \
    --bs=8k --direct=1 --size=1G --numjobs=1 --runtime=10 \
    --group_reporting
fio --name=seqread --ioengine=libaio --iodepth=1 --rw=read \
    --bs=8k --direct=1 --size=1G --numjobs=1 --runtime=10 \
    --group_reporting
```text

取随机读延迟与顺序读延迟的比值作为 `random_page_cost` 的基准。

### 配错后的症状

- 优化器在有索引且过滤条件选择性极好时仍然走 Seq Scan——与 `effective_cache_size` 偏小的症状类似，但根因不同。
- `EXPLAIN (ANALYZE, BUFFERS)` 强行走 Index Scan（`SET enable_seqscan = off`）后，实际执行时间远低于优化器为 Seq Scan 估算的代价——说明优化器对索引扫描的代价估算高了数倍。
- 多个小查询的 `mean_exec_time` 偏高，`pg_stat_statements` 中 `shared_blks_read` 远高于期望。

### 查验方法

```sql
-- 当前值
SHOW random_page_cost;

-- 强制索引扫描对比
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM large_table WHERE indexed_column = 'rare_value';
-- 记录实际执行时间

SET enable_seqscan = on;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM large_table WHERE indexed_column = 'rare_value';
-- 对比两者的 actual time 和 optimizer cost

-- 批量检查：pg_stat_statements 中哪些查询的 shared_blks_read 异常高
SELECT queryid, query,
       shared_blks_read, shared_blks_hit,
       mean_exec_time,
       ROUND(100.0 * shared_blks_read /
             NULLIF(shared_blks_read + shared_blks_hit, 0), 2) AS read_pct
FROM pg_stat_statements
WHERE shared_blks_read + shared_blks_hit > 100
ORDER BY shared_blks_read DESC
LIMIT 10;
```text

**正确做法**：NVMe SSD → `random_page_cost = 1.1`；高端 SATA SSD → `1.5-2.0`；SAN/NAS → 需要实测。如果用 `fio` 测得随机读延迟是顺序读的 1.5 倍，设 `random_page_cost = 1.5`。上线后用上面的强制索引扫描对比法验证。

---

## 五、fsync=off：最危险的配置

### 这个参数在内核里控制什么

`fsync` 控制 PG 是否调用 `fsync()` 系统调用来保证 WAL 和数据文件写入已落到持久化存储（磁盘或等效设备）。当 `fsync=on`（默认）时，每次 WAL 写入和 checkpoint 的数据文件写入最终都会通过 `fsync()`（或 `fdatasync()`，取决于 `wal_sync_method`）确保数据不只在 OS buffer 中。

关掉它意味着：`COMMIT` 时 WAL 数据写到了 OS buffer 但没有调用 `fsync()` 刷盘。操作系统会在它觉得方便时（通常在几秒到几十秒内）把脏页写回磁盘——但在那之前，如果掉电或内核 crash，这些数据就丢了。

（WAL 的完整写入路径和 `wal_sync_method` 的语义差异见[第 4 章：WAL 内部机制](../04-wal-internals/04-wal-internals.html)。）

### 为什么有人会动它

`fsync` 是 PG 中最大的性能开关之一——关掉它，所有 WAL 写入的 `fsync()` 系统调用延迟消失，`COMMIT` 延迟从 ~0.1ms（NVMe）降到 ~0.01ms。在批量数据导入（`COPY`、`pg_restore`）场景中，导入速度可能提升 10-50 倍。

但代价是：**crash 之后 PG 可能无法恢复，或者恢复后数据不一致。** 这不是丢"最近几秒"数据的问题——这是整个集群可能在 crash 之后不可用的问题。WAL 恢复的逻辑依赖 WAL 记录的持久化顺序和完整性——如果 OS buffer 中的 WAL 没有完整刷盘，crash recovery 时 `StartupXLOG()` 可能遇到截断的 WAL 记录、缺失的 checkpoint record、或者 WAL 序列号不连续，导致恢复失败，数据库无法启动。

### 唯一合法的使用场景

唯一能接受 `fsync=off` 的场景是：**你正在做一次性批量数据导入，导入后有完整的源数据可以重建整个集群，并且你在导入完成后用 `pg_dump` 重新导出并恢复到 `fsync=on` 的干净集群中。**

即使在这个场景下，也有更安全的替代方案：用 `synchronous_commit=off`（见下一节）代替 `fsync=off`——`synchronous_commit=off` 只影响事务提交的 WAL 刷盘，不影响 checkpoint 的 `fsync()`。crash 后丢的是已提交事务，但集群本身是可恢复的。

### 查验方法

```sql
SHOW fsync;

SHOW synchronous_commit;

-- 查看当前 WAL writer 的配置
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('synchronous_commit', 'wal_writer_delay', 'wal_writer_flush_after');
```text

```sql
-- 查看 WAL 写入速率——如果写入量极大，WAL writer 的刷新频率可能跟不上
SELECT pg_current_wal_lsn(), pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') AS current_offset;
-- 两次查询间隔中，WAL LSN 的增量可以估算丢失窗口的数据量

# 查看系统大页状态
grep -E "HugePages|Hugepagesize" /proc/meminfo
# HugePages_Total: 0   <-- 系统没有配置大页
# HugePages_Free:  0
# Hugepagesize:    2048 kB
```text

```bash
# 查看 PG 启动日志中关于 huge page 的信息
# 如果是物理机部署：
grep -i "huge" /var/log/postgresql/postgresql-*.log
# 或用 journalctl
journalctl -u postgresql | grep -i huge

# 查看容器的内存限制和 huge page 配置
# Docker：
docker inspect <container> | jq '.[0].HostConfig.Memory, .[0].HostConfig.MemoryReservation'
# K8s：
kubectl describe pod <pod> | grep -A5 -E "Limits|hugepage"
```text

输出为空或 "huge pages: 0" 即说明退化。

```bash
# 确认当前 PG 实际使用的页大小
# PG 没有直接 GUC 暴露此信息，但可以通过共享内存段大小推算
# 如果 shared_buffers=8GB 但 ipcs -m 显示多个小段，很可能没用大页
ipcs -m | grep postgres

SHOW maintenance_work_mem;

-- 查看最近一次 VACUUM 是否有多趟索引扫描
-- 手动跑一次 verbose VACUUM 来诊断（在低峰期）
VACUUM (VERBOSE) target_table;
-- 关注输出中的：
-- "index scans: N"  -- N>1 说明 maintenance_work_mem 不够
-- "pages: X removed, Y remain"
```text

```sql
-- 查看当前 VACUUM 的进度
SELECT
  relid::regclass AS table_name,
  phase,
  heap_blks_total, heap_blks_scanned,
  index_vacuum_count,
  max_dead_tuples
FROM pg_stat_progress_vacuum;

-- 预估表上的 dead tuple 量
SELECT relname,
       n_dead_tup,
       n_live_tup,
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 100
ORDER BY n_dead_tup DESC
LIMIT 10;
```text

**正确做法**：`maintenance_work_mem` 可以设得比较大（1GB-8GB 在 64GB+ 服务器上合理），因为 **`maintenance_work_mem` 不在每个连接的 work_mem 乘数公式中**——它只在执行维护操作的进程中使用，而 VACUUM worker 的数量由 `autovacuum_max_workers`（默认 3）限制。最坏情况是 `autovacuum_max_workers` 个 VACUUM worker 同时做索引清理，峰值内存为 `maintenance_work_mem × autovacuum_max_workers`——如果设 1GB × 3 = 3GB，在 64GB 服务器上完全可控。

也可以只增大 `autovacuum_work_mem`（PG 9.4+）——它只影响 autovacuum worker，不影响手动的 `VACUUM` 和 `CREATE INDEX`。

---

## 九、idle_in_transaction_session_timeout：为什么必须设

### 这个参数在内核里控制什么

`idle_in_transaction_session_timeout` 控制一个 session 在事务中但空闲（没有执行任何查询）多久后被自动终止。事务中空闲意味着：session 执行了 `BEGIN` 或前一条 SQL 开启了事务，但在那之后没有再发任何查询——通常是应用程序 `BEGIN` → 查询 → 在代码中做了非 DB 操作（调 API、读取文件、等待用户输入）→ 忘了 `COMMIT` → 事务就这么开着。

这个参数默认值是 0——表示禁用。这就是问题。

### 为什么默认值在你的场景下可能是错的

一个忘了 `COMMIT` 的 session（哪怕是开发者的 `psql` 连接）可以造成连锁灾难：

1. **持锁不释放。** 事务中获取的锁（行级锁、表级 `ACCESS EXCLUSIVE` 等）在事务结束前不会释放。如果这个事务曾经 `UPDATE` 了一行（哪怕没修改任何列值），它持有该行的 `RowExclusiveLock` 和对应的事务 ID。其他 session 想对同一行做 `UPDATE` 或 `DELETE` 会阻塞。更糟的是，如果这个事务曾执行过 `ALTER TABLE`（哪怕是 `ALTER TABLE t ADD COLUMN x INT DEFAULT 0`，它需要 `ACCESS EXCLUSIVE` 锁），所有后续对该表的任何 DML/DDL 都会被阻塞。

2. **阻止 VACUUM 回收死行。** VACUUM 只能回收那些"在所有活跃事务开始之前就 dead"的元组（参见[第 8 章：VACUUM 与 Freezing](../08-vacuum-freezing/08-vacuum-freezing.html)）。一个长时间开着的 `idle in transaction` session 带着一个旧的 `xmin`——VACUUM 在那个 `xmin` 之后产生的 dead tuple 都不能被回收。一天不提交，一天的死 tuple 都在累积膨胀。

3. **阻止 HOT 更新。** HOT（Heap-Only Tuple）更新的前提是更新的列不在任何索引中，且更新后的 tuple 能放在同一页面。但即使满足这些条件，如果更新涉及的行被另一个事务锁定，HOT 优化也可能无法生效——从而产生额外的索引更新和膨胀。

### 配错后的症状

- `pg_stat_activity` 中出现大量 `state = 'idle in transaction'` 且 `xact_start` 时间很久的 session。
- `pg_locks` 中有大量 `granted = true` 但 `transactionid` 类型的锁——每个 `idle in transaction` session 持有一个事务 ID 锁，阻止 VACUUM 推进。
- 表膨胀加速——`pg_stat_user_tables.n_dead_tup` 持续增长，但 `last_autovacuum` 也正常在执行（说明 VACUUM 在跑，但无法回收死行）。
- DDL 操作（`ALTER TABLE`、`CREATE INDEX CONCURRENTLY`）hang 住，`pg_stat_activity.wait_event = 'Lock'`。

### 查验方法

```sql
SHOW idle_in_transaction_session_timeout;

-- 查找所有 idle in transaction 的 session 及其持锁情况
SELECT
  pid,
  usename,
  application_name,
  state,
  xact_start,
  NOW() - xact_start AS xact_duration,
  query,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND pid != pg_backend_pid()
ORDER BY xact_start;

-- 查找被 idle in transaction session 阻塞的查询
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  blocking.state AS blocking_state,
  NOW() - blocking.xact_start AS blocking_xact_duration
FROM pg_stat_activity blocked
JOIN pg_locks bl ON blocked.pid = bl.pid AND NOT bl.granted
JOIN pg_locks lb ON bl.locktype = lb.locktype
  AND bl.database = lb.database
  AND bl.relation = lb.relation
  AND lb.granted
JOIN pg_stat_activity blocking ON lb.pid = blocking.pid
WHERE blocked.wait_event_type = 'Lock';
```text

**正确做法**：`idle_in_transaction_session_timeout` 必须设一个非零值。典型值是 `5min`-`15min`（取决于业务中合法长事务的最大持续时间）。设置后，超时的 session 会被终止并释放其持有的所有锁和事务 ID——错误日志中会记录类似 `terminating connection due to idle-in-transaction timeout` 的信息，方便你回溯是哪个应用连接出了问题。

---

## 十、log_lock_waits + deadlock_timeout 的组合

### 这些参数在内核里控制什么

`deadlock_timeout`（默认 1s）有两个作用：

1. **死锁检测的触发间隔。** 死锁检测器（`DeadLockCheck()`，`src/backend/storage/lmgr/deadlock.c`）不是每发生一次锁等待就跑——那样太贵。它每隔 `deadlock_timeout` 被调度一次，检测当前等待图中是否有环。有环 → 选一个事务 abort。

2. **`log_lock_waits` 的报告阈值。** 当 `log_lock_waits = on`（默认 `off`）时，如果一个 session 等待某个锁超过 `deadlock_timeout`，PG 会在日志中写入一条 WARNING，记录等待信息（等待的锁类型、持锁者的 PID 等）。

关键细节：**你不是只能等满 `deadlock_timeout` 才知道死锁了。** 即使 `log_lock_waits = off`，死锁检测仍然按 `deadlock_timeout` 间隔运行——只是不会在日志中记录"非死锁"的锁等待超时。

### 为什么默认值在排查场景下是错的

`log_lock_waits = off`（默认）意味着：当一个 session 被锁住了很久，你不知道——直到用户投诉"系统卡住了"。而设了 `log_lock_waits = on` 之后，你可以在日志中看到两种关键信号：

- **锁等待超时但最终获取到锁**：说明存在临时的锁竞争——可能是某个 DDL 短暂锁住了表，或者某个 UPDATE 的事务持续时间稍长。这不是死锁，但可以帮你定位性能异常的源头。
- **锁等待超时且最终被死锁检测 abort**：说明检测到了死锁——日志中会先有锁等待超时的 WARNING，再有死锁检测的 ERROR。

`deadlock_timeout` 的默认值 1s 也值得重新评估：如果设太短（如 100ms），高并发下正常的短暂锁等待也会频繁触发日志写入，增加 I/O 开销且产生噪声。如果设太长（如 10s），死锁检测间隔太长，死锁的 session 要等 10s 才被 abort——这段时间持锁的 session 可能阻塞了一大片其他 session。

### 配错后的症状

- 用户报告"查询卡住"，但你不知道是锁等待、慢 I/O 还是网络延迟——因为没有 `log_lock_waits` 日志。
- 死锁检测的 ERROR 日志出现频率高——但你需要它们之外的信息（哪个 session 持锁、持了多久）来定位根因。

### 查验方法

```sql
SHOW log_lock_waits;
SHOW deadlock_timeout;

-- 查看当前的锁等待情况
SELECT
  l.pid, l.locktype, l.mode, l.granted,
  a.usename, a.query, a.state,
  NOW() - a.query_start AS query_duration,
  a.wait_event_type, a.wait_event
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
  AND a.pid != pg_backend_pid()
ORDER BY a.query_start;

-- 追踪锁等待链
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocked.wait_event_type AS blocked_wait_type,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  blocking.state AS blocking_state,
  NOW() - blocking.query_start AS blocking_duration
FROM pg_locks bl
JOIN pg_stat_activity blocked ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks lb ON bl.locktype = lb.locktype
  AND bl.database = lb.database
  AND bl.relation = lb.relation
  AND lb.granted
JOIN pg_stat_activity blocking ON lb.pid = blocking.pid
WHERE blocked.pid != pg_backend_pid();
```text

**正确做法**：`log_lock_waits = on`。`deadlock_timeout` 保持 1s 或适当缩短到 500ms——前提是你的日志系统能承受这个频率的写入。同时配 `log_line_prefix` 带 `%p`（PID）和 `%x`（事务 ID），以便在日志中关联锁等待事件与特定 session。

---

## 十一、log_min_duration_statement + auto_explain 的组合陷阱

### 这些参数在内核里控制什么

`log_min_duration_statement`（默认 `-1`，即禁用）：执行时间超过此值（毫秒）的 SQL 会被记录到日志中。

`auto_explain`（PG 的 contrib 扩展）是一个自动 EXPLAIN 工具：在 `shared_preload_libraries` 中加载后，当查询的执行时间超过 `auto_explain.log_min_duration` 时自动记录查询的 EXPLAIN 输出。它的关键选项：

- `auto_explain.log_min_duration`：记录阈值（-1 = 禁用）
- `auto_explain.log_analyze`：是否实际执行查询并收集 `ANALYZE` 数据（`EXPLAIN ANALYZE`）
- `auto_explain.log_buffers`：是否统计 buffer 访问
- `auto_explain.log_timing`：是否记录每个节点的实际执行时间
- `auto_explain.log_level`：输出日志的级别

### 为什么组合在一起是陷阱

**陷阱 1：阈值设太低导致日志洪水。** `log_min_duration_statement = 0`（记录所有查询）和 `auto_explain.log_min_duration = 0`（为所有查询生成 EXPLAIN）——在你的本地开发环境可能无害（几百条查询/分钟），但在生产环境有 10000 QPS 时，日志写入 I/O 会吃掉存储带宽，而且多数日志是你不需要看的正常快速查询。

**陷阱 2：`auto_explain.log_analyze = on` + 低阈值的双重执行。** 注意 `auto_explain.log_analyze = on` 意味着 `auto_explain` 不仅输出查询计划——它需要**实际执行这个查询**来收集 `ANALYZE` 数据（`actual time`、`rows`、`loops`）。对于本身就是慢查询的操作，这等于**把慢查询跑了两次**——一次是用户请求的正常执行，一次是 `auto_explain` 的 ANALYZE 执行。一个本来要跑 5s 的慢查询，现在跑了两遍 5s，消耗翻倍。

**陷阱 3：`log_min_duration_statement` 和 `auto_explain.log_min_duration` 不一致。** 如果 `log_min_duration_statement = 1000`（1s）但 `auto_explain.log_min_duration = 5000`（5s），你会看到日志中有很多"慢查询"（1-5s）但没有对应的 EXPLAIN 输出——因为它们跑得还不够慢，未触发 `auto_explain`。排查时你不知道这些查询为什么慢。反过来，如果 `auto_explain.log_min_duration` 小于 `log_min_duration_statement`，你会看到 EXPLAIN 输出但看不到原始查询的日志行——日志中的信息断片了。

### 配错后的症状

- PG 日志文件快速增长，`pg_current_logfile()` 的大小持续增加。
- 观察到的查询延迟远高于 `pg_stat_statements` 中记录的值——可能是因为 `auto_explain.log_analyze` 的二次执行放大了延迟。
- `pg_stat_statements` 中 `calls` 计数比预期高一倍（正常查询 + auto_explain 的 ANALYZE 执行各计数一次——虽然 PG 15+ 中 auto_explain 的 ANALYZE 不再更新 `pg_stat_statements`，但 CPU 和 IO 的双重消耗仍然存在）。

### 查验方法

```sql
-- 当前设置
SHOW log_min_duration_statement;

-- 检查 auto_explain 扩展是否加载
SELECT * FROM pg_extension WHERE extname = 'auto_explain';

-- 如果加载了，检查其设置
SELECT name, setting FROM pg_settings WHERE name LIKE 'auto_explain%';

-- 如果没有加载但需要排查，在 session 中临时加载
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 5000;  -- 5s
SET auto_explain.log_analyze = true;
SET auto_explain.log_buffers = true;
SET auto_explain.log_timing = true;

# 检查日志当前大小和增长速度
du -sh /var/log/postgresql/
```text

```sql
-- 从 pg_stat_statements 观察 overhead
SELECT query,
       calls, mean_exec_time, max_exec_time,
       ROUND(stddev_exec_time, 2) AS stddev_ms,
       shared_blks_read, shared_blks_hit
FROM pg_stat_statements
WHERE query LIKE '%auto_explain%'
ORDER BY total_exec_time DESC
LIMIT 5;