PostgreSQL 慢查询排查:从 pg_stat_statements 到执行计划的三层定位法

举报
yd_252044305 发表于 2026/11/02 09:38:19 2026/11/02
【摘要】 PostgreSQL 慢查询排查:从 pg_stat_statements 到执行计划的三层定位法线上库突然变慢,第一反应往往是"加个索引",但慢查询的根因分布远比这个假设分散。这篇按三层结构整理一套可复用的排查路径:先看整体画像,再定位到具体语句,最后拆执行计划。全部以 PostgreSQL 14+ 为准。第一层:用 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 排行,让数据告诉你该看哪条语句,而不是被报警里最先出现的那条牵着走。

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。