PostgreSQL 查询突然变慢：区分锁等待、执行计划与 I/O

可再补一个容易造成“同一条 SQL 偶发变慢”的分支：预备语句的 generic plan 与 custom plan。PostgreSQL 12+ 支持 plan_cache_mode；应用长期复用 prepared statement 时，优化器可能在多次执行后选择通用计划。若参数分布偏斜，累计到同一 queryid 下的少数参数可能明显变慢，却不一定表现为统计信息整体失效。

在与应用一致的角色、search_path、参数类型和会话参数下，可先检查当前会话：

SHOW plan_cache_mode;

SELECT name, parameter_types, prepare_time,
       generic_plans, custom_plans, statement
FROM pg_prepared_statements;

pg_prepared_statements 只展示当前会话，不能用运维会话的空结果推断应用没有预备语句。复现时也不要简单把绑定参数替换成字面量，因为参数类型和计划选择路径可能随之改变。若能在隔离的只读回放会话中准备同一语句，可分别用 force_generic_plan 与 force_custom_plan 比较计划；设置应限定在事务或测试会话，避免修改全局值。使用 EXPLAIN ANALYZE 仍会真实执行语句，写语句和高成本查询应继续遵守正文中的限制。

闭环时建议按参数区间分别比较延迟、估算行数和块读取，而不是只看整个 queryid 的平均值；这样能区分参数敏感计划与普遍性的 I/O 或统计信息问题。

补充一个统计窗口的口径问题：pg_stat_database.stats_reset 不能作为 pg_stat_statements 的可靠重置时间。两套统计可以分别重置；如果只看前者，可能把扩展刚重置后的累计值误当成覆盖了整个数据库统计窗口。

PostgreSQL 14+ 随附的对应扩展版本可先确认：

SELECT extversion
FROM pg_extension
WHERE extname = 'pg_stat_statements';

SELECT stats_reset, dealloc
FROM pg_stat_statements_info;

stats_reset 才是该扩展统计的重置时间；dealloc 增长表示因 pg_stat_statements.max 等容量约束发生过条目淘汰，此时“窗口内没有某个 queryid”也不能直接解释为没有执行。PostgreSQL 13 没有 pg_stat_statements_info 这个视图，排障时更稳妥的办法是对 pg_stat_statements 做前后两次受控快照，按同一数据库、用户和 queryid 计算 calls、total_exec_time、块读取及临时块的增量，并确认期间没有执行扩展重置或发生实例重启。

快照最好只保存分析所需的标识和计数，不默认导出完整 query；规范化文本仍可能含对象名、注释等敏感信息。这样得到的是故障窗口内的增量，能避免累计历史和独立重置时间干扰正文里的计划与 I/O 判断。

再补一个 I/O 口径：BUFFERS 中的 shared read 表示 PostgreSQL 需要把块读入 shared buffers，不等于每个块都触发了物理盘读取；它仍可能命中操作系统页缓存。反过来，只有块计数也无法判断单次读取延迟，因此不能仅凭 shared_blks_read 较大就把根因归到存储。

PostgreSQL 13+ 可先确认是否采集 I/O 时间：

SHOW track_io_timing;

SELECT datname, blks_read, blks_hit,
       blk_read_time, blk_write_time, stats_reset
FROM pg_stat_database
WHERE datname = current_database();

track_io_timing=off 时，时间列为零不代表没有等待。该参数的计时开销取决于平台时钟实现；故障中不要未经评估就改全局配置，可先用随 PostgreSQL 提供的 pg_test_timing 在同类主机评估开销，并走既有变更流程。启用后，EXPLAIN (ANALYZE, BUFFERS) 才能提供相应的 I/O timing；正文所述真实执行风险仍然成立。

PostgreSQL 16+ 还可在两次受控快照间查看 pg_stat_io 的增量：

SELECT backend_type, object, context,
       reads, read_time, writes, write_time,
       fsyncs, fsync_time, stats_reset
FROM pg_stat_io
WHERE reads > 0 OR writes > 0 OR fsyncs > 0
ORDER BY read_time + write_time + fsync_time DESC;

时间列同样依赖相应计时参数，累计值也要检查 stats_reset。把增量与同一时间窗的块设备延迟、队列深度和 PostgreSQL 等待事件对齐：若数据库读块增加但设备读操作没有同步增加，操作系统缓存就是一个重要解释；若设备延迟和 read_time 同时抬升，才更支持底层 I/O 变慢。这样可以避免把“读得多”和“盘变慢”混成同一个结论。

还可以把临时文件溢写单独列为一个分支。排序或哈希超出内存后会写临时文件，表现为查询耗时和 I/O 同时上升，但根因未必是设备本身变慢；此时直接提高全局 work_mem 也有并发内存放大的风险。

PostgreSQL 13+ 可先读取当前设置，并从 pg_stat_statements 做故障窗口前后快照：

SHOW work_mem;
SHOW hash_mem_multiplier;

SELECT userid, dbid, queryid, calls, total_exec_time,
       temp_blks_read, temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_read > 0 OR temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;

这些仍是累计计数，需要按相同键计算增量，并检查扩展的独立重置时间。若已有 log_temp_files 配置，可把数据库日志中的临时文件大小、PID 和时间与慢请求对齐；不要为了单次故障未经评估就修改实例级日志或内存参数。

在确认可以真实执行的隔离环境中，EXPLAIN (ANALYZE, BUFFERS) 里可关注 Sort Method: external merge、Disk、哈希节点的 Batches 大于 1，以及临时块读写。work_mem 是每个排序或哈希操作的预算，不是每个会话的总预算；一个计划可能包含多个节点，哈希操作还受 hash_mem_multiplier 影响，并行 worker 与并发查询会继续放大总内存。因此修复应优先确认行数估算、索引和计划形态，再对目标会话或事务做受控调整，避免把单条查询的需求直接变成全局值。

验证时同时比较同一 queryid 的临时块增量、执行计划、延迟分位数和主机 I/O；临时块下降但总耗时未改善，说明仍需回到锁等待、行数估算或底层延迟分支。

再补一个数据库外排队的分支：应用记录的“SQL 耗时”有时包含从连接池取连接的等待，而这段时间不会出现在 pg_stat_activity.query_start 或 pg_stat_statements 中。于是数据库内执行很快，端到端请求仍可能突然变慢。

PostgreSQL 13+ 可先按固定间隔只读采样已进入服务端的会话：

SELECT clock_timestamp() AS observed_at,
       state, wait_event_type, wait_event, count(*) AS sessions
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state, wait_event_type, wait_event
ORDER BY sessions DESC;

这只能看到已经分配到 PostgreSQL 后端的连接，不能证明连接池没有排队。若链路使用 PgBouncer，可在获准访问其管理控制台、使用现有凭据的前提下记录版本和池状态：

psql "$PGBOUNCER_ADMIN_DSN" -X -v ON_ERROR_STOP=1 \
  -c 'SHOW VERSION;' \
  -c 'SHOW POOLS;'

SHOW POOLS 中持续非零的 cl_waiting 表示客户端正在等可用服务端连接；应同时看 sv_active、sv_idle，并与应用侧“获取连接”耗时和同一 UTC 时间窗对齐。管理控制台输出包含数据库名、用户名和地址等信息，共享前应脱敏。其他连接池的指标名不同，但仍应区分连接获取、数据库执行和结果返回三个阶段。

不要看到排队就直接扩大池上限：更多后端可能把瓶颈转移到数据库 CPU、锁或 I/O。修复应针对实际原因，例如过长事务、连接未及时归还或池容量与数据库并发能力不匹配；验证时在可比负载下确认连接获取 p95/p99 与 cl_waiting 同时下降，并检查 PostgreSQL 的活动会话、锁等待及主机资源没有出现新的饱和。

再补一个容易与查询自身读放大混淆的分支：检查点写入可能在同一时间窗制造写 I/O 竞争，使锁和执行计划都没有明显变化的查询一起变慢。相关统计是累计值，应做前后快照，不能只看单点总量。

PostgreSQL 13–16 可读取：

SELECT clock_timestamp() AS observed_at,
       checkpoints_timed, checkpoints_req,
       checkpoint_write_time, checkpoint_sync_time,
       buffers_checkpoint, buffers_clean, maxwritten_clean,
       buffers_backend, buffers_backend_fsync, stats_reset
FROM pg_stat_bgwriter;

PostgreSQL 17 起检查点统计拆到 pg_stat_checkpointer，先用 SELECT version(); 固定版本，再按对应视图取数，避免把旧版列名直接套用。两次快照间重点看 checkpoints_req、检查点写入/同步时间和写入块的增量，并与同一 UTC 窗口的块设备延迟、吞吐、队列以及 pg_stat_activity 等待事件对齐。checkpoints_req 快速增长说明检查点可能由 WAL 容量压力触发；但 checkpoint_write_time 增长也可能只是写入按 checkpoint_completion_target 被摊开，不能单独证明存储变慢。

还要保留 stats_reset，数据库重启或统计重置会破坏差分口径。若检查点增量与查询延迟峰值重合，而设备写延迟和队列也同步抬升，才更支持写 I/O 竞争；若只有累计计数增长，应继续查查询读块、临时文件和主机层证据。不要在故障中直接放大 max_wal_size 或延长 checkpoint_timeout：这会改变磁盘占用、恢复时间和检查点行为，应先在可比负载下评估，并验证调整后请求 p95/p99、检查点频率和设备延迟同时改善。