PostgreSQL 慢查询排查:从 pg_stat_statements 到执行计划的三层定位法
PostgreSQL 慢查询排查:从 pg_stat_statements 到执行计划的三层定位法
线上库突然变慢,第一反应往往是"加个索引",但慢查询的根因分布远比这个假设分散。这篇按三层结构整理一套可复用的排查路径:先看整体画像,再定位到具体语句,最后拆执行计划。全部以 PostgreSQL 14+ 为准。
第一层:用 pg_stat_statements 建立全局画像
不要在慢的当下逐条猜。先拿到按总耗时排序的语句榜,总耗时比单次耗时更能反映对系统的影响面。
启用扩展需要改 postgresql.conf 并重启:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
重启后执行 CREATE EXTENSION pg_stat_statements; 然后用下面这条查总耗时前 20:
SELECT
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
calls,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
rows,
left(query, 120) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
三个读法值得记住:
一是 pct 之和到 80% 的那几条才是战场,其余先放着。经验上十几个语句通常能覆盖八成耗时。
二是 mean_ms 高但 calls 少的,多半是报表类查询或缺失索引的全表扫描;mean_ms 一般但 calls 极多的,往往是高频小查询累积,优化点在减少调用或加缓存,而不是改 SQL。
三是 rows 和 calls 的比值异常时要注意。比如 calls 是 10 万而 rows 只有 10 万,说明每次只取一行,是典型的 N+1;反过来 rows 远大于预期则可能是统计信息陈旧导致的行数估算错误。
注意 pg_stat_statements 里相同结构、不同常量的语句会被归一化成同一条(常量替换为 $1),所以看到的是一类语句而不是一次执行。另外 total_exec_time 不含解析与计划时间,解析开销大的场景还要看 total_plan_time。
第二层:抓到具体语句后用 EXPLAIN 分析
从画像里选出目标语句,接下来必须看执行计划。这里最容易犯的错是只用 EXPLAIN 而不用 EXPLAIN ANALYZE —— 前者是基于统计信息的估算,后者才是实际执行数据。
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...;
BUFFERS 这个选项经常被忽略,但它能直接告诉你 I/O 代价:shared hit 是命中缓存,shared read 是真正落到磁盘。一条语句显示 read 很高而 hit 很低,说明工作集大于 shared_buffers,此时加索引的收益远不如先扩内存或减少扫描范围。
看计划时分三步走。
第一步对比估算行数和实际行数。rows=100 而 actual rows=500000 这种数量级偏差是根本性问题,通常意味着统计信息不准或列之间存在相关性。先跑 ANALYZE 表名 再重新 explain;如果是多列相关性导致的偏差,需要建多列统计:
CREATE STATISTICS stt_orders_status_user (dependencies, ndistinct)
ON status, user_id FROM orders;
ANALYZE orders;
第二步找计划里最贵的节点。EXPLAIN ANALYZE 会给出每个节点的 actual time,但注意它是累计值(包含子节点),真正的自身耗时要用 actual time 减去子节点之和。常见的高代价节点是 Seq Scan(大表无索引或索引用不上)、Nested Loop(内表没走索引时行数一放大就爆)、以及 Sort(work_mem 不够会落盘,表现为 external merge Disk)。
第三步确认索引到底有没有被用上。这里有个高频陷阱:索引存在但计划里走 Seq Scan,原因通常有四类 —— 类型不匹配导致隐式转换(如 varchar 列和 int 参数比较)、列上套了函数(WHERE date(created_at) = '2026-01-01' 会让索引失效,应改写为范围条件)、选择性太差(返回行数占比过大的表该走全表扫)、以及统计信息过于陈旧。
第三层:几个容易被忽略的放大因素
表膨胀与 autovacuum。PostgreSQL 的 MVCC 决定了 UPDATE 不原地修改而是写新版本,死元组堆积后表和索引都会膨胀,同样的查询要扫更多页面。查膨胀情况:
SELECT relname, n_live_tup, n_dead_tup,
round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;
dead_pct 长期高于 20% 的表需要关注,尤其是高频更新的表要单独调低 autovacuum_vacuum_scale_factor。
连接数打满。max_connections 用尽时新连接会排队,现象看起来像"数据库整体变慢",但实际慢在连接获取而非 SQL。用 pg_stat_activity 按状态分组看:
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
如果 idle in transaction 数量很多,说明应用层有事务没及时提交,持锁不放会连锁阻塞。这类问题查 SQL 永远查不出来。
锁等待。排查时先看有没有等待事件:
SELECT pid, wait_event_type, wait_event, state, left(query, 80)
FROM pg_stat_activity
WHERE wait_event_type IS NOT NULL;
wait_event_type 为 Lock 时用 pg_blocking_pids(pid) 找出阻塞源头,顺着链往上追到真正的持有者。
一个小结
慢查询排查的顺序应该是全局画像 → 单条语句 → 微观放大因素,而不是一上来就加索引。加索引的成本不只是存储,还有写入放大和后续维护;而很多"慢"的根因其实在统计信息、表膨胀、连接管理这些非 SQL 层面。
真正值得养成的习惯是:每次遇到慢查询,先花两分钟跑一遍 pg_stat_statements 排行,让数据告诉你该看哪条语句,而不是被报警里最先出现的那条牵着走。
- 点赞
- 收藏
- 关注作者
评论(0)