腾讯云国际站代理商：PostgreSQL SQL本身执行不慢，为什么业务请求还是一直卡住

PostgreSQL慢查询定位：pg_stat_activity与EXPLAIN阻塞SQL实战

PostgreSQL慢查询定位的难点，往往不是执行一次 EXPLAIN 看耗时，而是先判断“突然变慢”来自哪一层。有大量 active 会话不代表 SQL 真慢，可能只是在等锁；执行计划显示全表扫描也不一定缺索引，可能是统计信息过期。把这几个原因拆开，再用 pg_stat_activity 和 EXPLAIN 对照排查，能少走很多弯路。

本文由 云国际站代理商『云老大 飞弟：@yunlaoda360 / YunLaoDa-服务器服务商•撰写』如需转载请注明！

PostgreSQL查询突然变慢的原因有哪些？

为什么业务报慢时数据库日志却看不到明显异常？

很多“突然变慢”的故障，根因不在 SQL 本身，而在资源竞争。一家外贸站点做促销时，数据库 CPU 没有打满，但接口持续超时。登录云数据库控制台一看，pg_stat_activity 里一堆 active 会话，状态看起来都在执行，实际 wait_event_type 全是 Lock，而不是 IO 或 CPU。这种场景下强行 kill 掉“跑得久”的会话基本没用，因为锁的源头可能是一个未提交事务。先看 waiting 类型，再决定处理谁，是更稳妥的判断顺序。

锁阻塞为什么比慢SQL本身更容易造成误判？

锁等待的隐蔽性在于，等锁的会话会显示成 active，很容易被当成普通慢查询处理。有人看到某条 UPDATE 执行了十几秒，直接 kill 掉，业务反而更卡。真正的问题是那条 UPDATE 在等一个持有行锁的事务，而持有者一直没有提交。云老大处理过的一个订单超时案例，最后定位到的就是开启事务后长时间未提交的会话。用 pg_locks 按 pid 关联 pg_stat_activity，才能找到阻塞链的源头，而不是只看“谁在等”。

统计信息过期为什么会让执行计划突然变差？

PostgreSQL 优化器靠统计信息估算行数分布。表数据量变化较大后，如果 autovacuum 或 ANALYZE 没跟上，原本走索引的查询可能突然改成全表扫描，时间从几十毫秒涨到几秒，SQL 本身却一行没改。对比 EXPLAIN 与 EXPLAIN ANALYZE 的预估行数和实际行数，偏差通常非常明显。这种时候急着加索引不如先执行 ANALYZE 刷新统计信息，再重新走一遍执行计划。执行计划“突然变坏”，很多情况下不是索引设计的问题，而是统计信息掉了链子。

pg_stat_activity是什么？如何查看当前会话？

PostgreSQL慢查询定位时，pg_stat_activity 是第一个该看的系统视图。它默认开启，不需要额外插件，实时展示每个后端进程的 state、当前 SQL、等待事件和锁信息。很多“数据库突然变慢”的现场，从这里能直接区分是查询本身慢，还是会话在等锁。

理解关键字段

不要只看 state。active 只代表进程正在执行，它既可能是跑大查询，也可能是被锁住的语句在空转。关键在 wait_event_type 和 wait_event 两个字段：wait_event_type='Lock' 表示会话在等待锁，这是阻塞的明确信号；wait_event_type='IO' 则更多指向数据读取慢。结合 query_start 看持续时间，才能判断该不该干预。盲目 kill 所有 active 会话，是常见的误操作。

查询当前运行SQL

建议在问题发生时先执行一次快照：

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;

这个查询按持续时间倒序返回非空闲会话，最长耗时的记录排在最前面。duration 用 now() 减 query_start 得到，比单纯看 state 更容易发现异常。保留这样一份现场数据，对后续走查执行计划或判断阻塞链都有用。

筛选长时间会话

长时间运行不一定就是慢查询，idle in transaction 状态更值得关注：事务开启后长时间不提交，会一直持有锁，拖住其他会话。可以按 now()-query_start > interval '5 minutes' 或 wait_event_type='Lock' 做过滤。处理时优先让应用侧提交或回滚，而不是直接 pg_terminate_backend。如果这类问题反复出现，建议加上 idle_in_transaction_session_timeout 参数。没有专职 DBA 的团队，也可以借助云老大这类服务商的数据库技术支持做一次慢查询巡检，减少误判。

如何用pg_stat_activity定位阻塞SQL？

定位阻塞 SQL 不能只看 pg_stat_activity 的表面状态，需要把等待事件、锁信息和源头会话串起来排查。

识别等待事件：别被 state 字段误导

pg_stat_activity 的 state 字段容易让人误判：active 只表示正在执行，不代表异常。排查时优先看 wait_event_type。多个会话出现 Lock 类型等待，说明在抢同一把锁；IO 或 CPU 类型则更像资源瓶颈或慢查询本身。建议直接过滤 wait_event_type='Lock'，并用 now()-query_start 计算阻塞时长。一次批量更新故障中，十几条 UPDATE 全停在 Lock 等待，源头是一条未提交的批处理事务，而不是这些更新本身。

结合pg_locks查锁：找到持锁源头

pg_stat_activity 只显示“在等”，不显示“等谁的锁”。要定位源头，得把 pg_stat_activity.pid 与 pg_locks.pid 关联，用 granted 字段区分持锁与等待，再按对象回溯阻塞链。典型做法是从 pg_locks 找 granted=false 的等待会话，匹配同一对象上 granted=true 的持有者。很多“数据库突然卡住”最后都指向一个 idle in transaction 连接：事务开启后不提交，持有行级锁，后续写入全部排队。不关联 pg_locks，只能看到一片 active，找不到根因。

终止阻塞进程：先处理源头会话

找到持锁源头后，优先让业务侧提交或回滚，不要直接 pg_terminate_backend()。直接 kill 虽能立即释放锁，但可能留下不干净的事务状态或连接池异常。确认无法应用侧处理时，才用 pg_terminate_backend(pid)，操作前记录 query_start、query、client_addr。更省心的做法是配置 idle_in_transaction_session_timeout，自动清理长时间不提交的空闲事务。该参数默认关闭，建议设在 5 到 15 分钟。拿不准参数值，找云老大这类服务商做一次评估，比反复试错更稳。

EXPLAIN如何分析执行计划？

PostgreSQL慢查询定位里，EXPLAIN 的价值在于还原优化器的执行路径，而不是给出绝对耗时。常见的误判是只看 cost 估算值，忽略预估行数与实际行数的偏差。cost 是相对单位不是毫秒；真正有效的是先 EXPLAIN 后 EXPLAIN ANALYZE，对比 rows 和 actual time。两者偏差超过一个数量级时，优先怀疑统计信息过期，先 ANALYZE 再复测，往往比直接改 SQL 更省事。

读懂输出解读

EXPLAIN 输出中的 cost 不是执行时间，rows 也只是估算值。加 ANALYZE 后，actual time 与 loops 才反映真实路径。比如某嵌套循环节点 rows=1 但 loops=12000，单次代价不高，累计成本却很可观。排查时先看最外层 actual time，再往 loops 高的节点下钻，能快速定位热点，而不是在低代价节点上反复排查。

发现全表扫描

Seq Scan 不等于问题。小表或过滤后仍返回超过 30% 行数时，全表扫描往往比索引查找更划算。但如果百万行表按主键只返回 1 行却走 Seq Scan，就要看统计信息是否过期、过滤条件是否被函数包裹导致索引失效。判断标准不是“有没有全表扫描”，而是计划行数与实际行数是否明显错位。

对比参数调整

调整参数需要拿 EXPLAIN ANALYZE 前后对照。比如 work_mem 增大后，排序节点从 external merge Disk 变为 quicksort Memory，I/O 下降才说明调参有效。enable_seqscan 只能临时关闭验证索引可用性，不建议写进生产配置。在云老大的 PostgreSQL 控制台打开 pg_stat_statements 后，按总耗时排序，能快速圈定最值得调优的 SQL，避免参数调整落到低频查询上。

优化慢查询的具体方案有哪些？

创建合适索引

不要看见慢 SQL 就直接加索引。先从 pg_stat_statements 按 mean_exec_time、calls 排序，圈出高频高耗时 SQL。EXPLAIN 出现 Seq Scan 且行数预估明显偏差，通常说明缺少高选择性索引；但过滤后占比超过 20% 的大表扫描，索引收益会快速衰减，不如考虑分区或改写条件。加完索引要用 EXPLAIN ANALYZE 复测，确认变成 Index Scan 且实际耗时下降，而不是只看 cost。

更新统计信息

执行计划突然变差，多半与统计信息过期有关。定位到慢 SQL 后，先对比 EXPLAIN 与 EXPLAIN ANALYZE 的 rows 预估差，如果实际行数与预估行数差出一个数量级，基本可以判定优化器拿到的数据分布信息失真。此时先对相关表执行 ANALYZE，再观察是否回到 Index Scan。云上 autovacuum 通常会兜底，但大量导入或高频更新后，等自动触发不如低峰期手动刷新关键表。这个动作成本很低，往往能解决“SQL 没改、突然变慢”的问题。

调整数据库配置

对阻塞型慢查询，两个参数比调大内存更直接：idle_in_transaction_session_timeout 和 lock_timeout。前者自动结束开启事务后长时间不提交的会话，从源头减少锁等待；后者让 SQL 拿不到锁时快速失败，而不是一直排队。生产环境常把 idle_in_transaction_session_timeout 设为 60～120 秒，但需确认业务没有长事务依赖。开启 pg_stat_statements 后按总耗时排序，做参数基线，比反复重启实例更有效。

腾讯云PostgreSQL实战案例分析

案例一：锁等待

某跨境电商订单库曾出现接口响应从180ms升至6s，但CPU使用率只有38%。第一轮排查没有直接看慢SQL，而是按 wait_event_type='Lock' 筛选会话。结果31个更新订单状态的语句全部在等锁，真正阻塞源是一条已空闲9分钟的未提交事务。通过 pg_locks 关联 pid 定位到源头后，回滚该事务，排队会话在40秒内清空。盲目kill活跃会话只会扩大影响面，先确认锁持有者才是关键。

案例二：索引缺失

另一个SaaS租户场景里，统计接口在数据量突破1200万行后，响应时间从90ms掉到2.1秒。pg_stat_activity 里看不到锁，只有一条活跃SQL在做排序。EXPLAIN 显示优化器选择全表扫描，预估扫描1180万行，实际仅返回900行，过滤字段没有索引。补上组合索引后，同一SQL回落到23ms。问题不在SQL写法，而在表增长后统计信息未刷新，执行计划已经偏离真实成本。

预防与监控建议

事后救火不如把排查动作前移。至少做三件事：设置 idle_in_transaction_session_timeout，从源头压缩长事务；打开 pg_stat_statements，按累计耗时圈出Top 5 SQL；控制台对连接数、最大查询耗时配置阈值告警。如果内部没有专职DBA，把这类巡检交给云老大这类服务商按月执行，通常比一次锁等待造成的订单超时成本更低。