PostgreSQL慢查询定位:pg_stat_statements安装与top SQL排序实战

    发布时间:2026-09-30 04:31 更新时间:2026-09-30 04:31 阅读量:14

    线上 PostgreSQL 变慢时,很多人第一反应是翻日志,但默认的 log_min_duration_statement 只记录超过阈值的语句,且同类 SQL 会重复刷屏,很难看出"哪一类语句拖垮了整体"。pg_stat_statements 解决的就是这个问题:它把 SQL 归一化后累计统计,让你按总耗时、平均耗时、调用次数排出真正的 top 消耗者。本文从扩展安装讲起,到读懂每个统计字段,最后给出可直接套用的排序查询。

    一、安装与加载:为什么必须重启

    pg_stat_statements 是 PostgreSQL 自带的 contrib 扩展,但它的统计能力依赖预加载共享库,只有通过 shared_preload_libraries 在实例启动时载入,才能分配共享内存区收集所有会话的语句。这也是它不能像普通扩展那样 CREATE EXTENSION 后立刻生效的原因。

    先确认扩展文件是否随安装包提供。用 psql 连上实例后执行:

    # 查看当前实例的配置参数与已加载库
    psql -U postgres -c "SHOW shared_preload_libraries;"
    psql -U postgres -c "SHOW config_file;"
    
    

    确认扩展控制文件存在(路径随发行版与版本不同,以实际为准)

    ls $(pg_config --sharedir)/extension/ | grep pg_stat_statements

    如果文件不存在,Debian/Ubuntu 系一般要装 postgresql-contrib 包,RHEL 系装 postgresql-contrib 或对应版本的 contrib 包,具体包名以官方文档和仓库为准。文件就位后编辑 postgresql.conf:

    # 追加或修改(多个库用逗号分隔,注意别覆盖已有项)
    shared_preload_libraries = 'pg_stat_statements'
    
    

    统计的语句条数上限,默认 5000,按需调大

    pg_stat_statements.max = 10000

    记录哪些语句:top 只记耗时 top,all 记全部,none 不记

    pg_stat_statements.track = top

    是否统计嵌套语句(函数内部调用的 SQL)

    pg_stat_statements.track_utility = on

    改完执行 pg_ctl restart 或 systemctl restart postgresql 重启实例,单纯 reload 不足以加载共享库。重启后用 SHOW shared_preload_libraries; 确认已生效,再在每个需要统计的数据库里建扩展:

    psql -U postgres -d your_db -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
    psql -U postgres -d your_db -c "SELECT extname, extversion FROM pg_extension WHERE extname='pg_stat_statements';"

    注意:扩展是按库创建的,跨库统计需要分别在目标库执行。视图本身只读,普通业务账号默认无权查看,建议只授予运维账号,避免暴露 SQL 文本中的敏感信息。

    二、读懂核心字段:别只盯着 total_time

    视图 pg_stat_statements 的每一行代表一类"归一化"后的语句:常量被替换成 $1、$2,因此同结构的 SQL 会合并统计。常见字段含义如下:

    calls:该类语句被执行的总次数。total_exec_time(旧版本为 total_time)是累计执行时间,单位毫秒,是判断"谁最吃资源"的第一指标。mean_exec_time(旧版 mean_time)是平均单次耗时,反映单条语句本身的快慢。rows 是累计返回或影响的行数,和 calls 相除能看出平均处理规模。

    还有几个容易被忽略但很有用的字段:shared_blks_hit / shared_blks_read 体现缓存命中情况,命中率低说明语句频繁读盘;temp_blks_written 不为零,往往意味着排序或聚合落到了临时文件,是内存参数偏紧的信号;wal_bytes 则能看出写放大,对写密集语句很有参考价值。

    要提醒一点:total_exec_time 高不一定代表语句写得差。一个每秒执行上万次的简单查询,累计时间也会很大,此时优化方向是减少调用次数或加缓存,而不是改 SQL。反之 mean_exec_time 高的语句,哪怕调用不多,也值得单独看执行计划。

    三、实战:把 top SQL 排出来

    按总耗时排序,是最常用的入口,适合先找"整体负担最大"的语句:

    SELECT
      calls,
      round(total_exec_time::numeric, 2) AS total_ms,
      round(mean_exec_time::numeric, 2) AS mean_ms,
      round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
      left(query, 120) AS query_snippet
    FROM pg_stat_statements
    ORDER BY total_exec_time DESC
    LIMIT 20;

    如果怀疑"慢在单条",换成按平均耗时排,并过滤掉调用次数太少的噪声:

    SELECT
      calls,
      round(mean_exec_time::numeric, 2) AS mean_ms,
      round(total_exec_time::numeric, 2) AS total_ms,
      shared_blks_hit,
      shared_blks_read,
      temp_blks_written,
      left(query, 120) AS query_snippet
    FROM pg_stat_statements
    WHERE calls > 10
    ORDER BY mean_exec_time DESC
    LIMIT 20;

    排查线上突发卡顿时,统计是累计值,直接用总量排序会被历史数据干扰。可以先重置再观察一段窗口:执行 SELECT pg_stat_statements_reset(); 清空统计,等业务跑上几分钟后重新排序,这样看到的才是"最近这段时间"的真实分布。重置需要相应权限,生产环境建议记录重置时间点,避免误判。

    拿到 top 语句后,把其中的 $1、$2 换成真实参数,用 EXPLAIN (ANALYZE, BUFFERS) 看执行计划,重点确认是否走了不合适的扫描方式、是否存在大表嵌套循环。统计视图告诉你"谁慢",执行计划才告诉你"为什么慢",两者配合才完整。

    最后是几个实操细节。一是 pg_stat_statements.max 满了之后旧条目会被淘汰,长期观察建议适当调大;二是视图本身有少量开销,但通常可以接受,具体以压测结果为准;三是升级大版本时字段名可能变化(total_time 与 total_exec_time 就是典型),写监控脚本前先查对应版本的官方文档;四是该扩展只做统计,不采集绑定变量值,涉及敏感 SQL 的库要控制访问权限。

    小结一下:安装三件事——装 contrib 包、写 shared_preload_libraries、重启后 CREATE EXTENSION;排序两个视角——total_exec_time 找整体负担,mean_exec_time 找单条顽疾;定位靠 EXPLAIN 收尾。把这三步固化成例行巡检,PostgreSQL 的慢查询排查会轻松很多。

    继续阅读

    📑 📅
    Docker容器ulimit不生效排查:句柄继承链路与优先级 2026-09-30
    Nginx 与后端 HTTP/2 与 HTTP/1.1 混用排查:协议协商与日志字段 2026-09-30
    MySQL 8 表被锁等待排查:从 INNODB_TRX 到阻塞源头 2026-09-30
    PostgreSQL备库查询被长事务阻塞排查与配置取舍 2026-09-29
    MySQL慢查询Rows_examined远大于Rows_sent:回表与覆盖索引优化 2026-09-29
    nohup 与 systemd-run 后台任务取舍:SSH 断开进程被杀排查 2026-09-30
    Nginx 与后端 POST 请求体丢失排查:缓冲与转发链路 2026-09-30
    MySQL 8 慢查询日志查不到 SQL:digest 归并与真实语句还原 2026-09-30
    Redis连接数暴涨却无慢命令:连接泄漏定位实战 2026-09-30
    Linux磁盘满了但df与du不一致:挂载点遮蔽排查 2026-09-30