PostgreSQL慢SQL统计

慢SQL统计不是目的，而是手段。很多DBA一上来就盯着pg_stat_statements里的mean_time排序，揪出最慢的几条SQL丢给开发，结果改完上线，整体性能纹丝不动。问题出在统计维度上——单次执行耗时最长的SQL，往往不是对系统伤害最大的。真正需要优先处理的是总耗时（execution time × calls）最高的那些查询，它们才是吃掉服务器资源的元凶。

开启统计扩展，这是所有工作的前提

PostgreSQL自带的统计信息收集器默认只记录数据库级别的粗略指标，要定位到具体SQL，必须启用pg_stat_statements扩展。这个扩展会标准化查询文本，把参数替换为占位符后聚合统计，让你看到的是同一类SQL的汇总数据，而不是每条具体执行的记录。在postgresql.conf中设置shared_preload_libraries，加上pg_stat_statements，重启实例后在目标库执行CREATE EXTENSION pg_stat_statements即可。注意，扩展必须配置在共享预加载库中，单纯在库里创建扩展而不改配置，重启后统计信息是空的。

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

track参数建议设为all，这样连嵌套函数内部的语句也能捕获。max控制追踪的语句数量上限，默认5000在稍微复杂的系统中根本不够用，建议直接拉到10000以上，但要留意共享内存的额外开销。如果业务SQL种类特别多，可以配合pg_stat_statements.track_utility开关，决定是否记录DDL和工具类命令。

总耗时排序，找出真正的资源杀手

连接目标库，执行下面这条查询，你立刻就能看到哪些SQL是系统资源的头号消耗者。mean_time排序只能告诉你单次执行慢，而total_time排序揭示的是全局影响。一条单次执行只要0.5毫秒但每秒调用十万次的查询，总耗时远超一条偶尔执行一次跑10秒的分析SQL。

SELECT 
    queryid,
    left(query, 120) AS query_snippet,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 4) AS avg_ms,
    round((total_exec_time / sum(total_exec_time) over()) * 100, 2) AS pct_total,
    rows,
    shared_blks_hit,
    shared_blks_read,
    shared_blks_dirtied,
    wal_bytes
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

这个结果集里，pct_total这一列非常直观，告诉你这条SQL消耗了整体查询时间的百分之几。如果前五条SQL加起来占比超过80%，那优化方向就极其明确了。shared_blks_read代表从磁盘读取的数据块数，这个值高说明缓存命中率低，索引可能缺失或者查询扫描范围过大。wal_bytes是写入WAL日志的字节数，大量写操作的SQL在这里会暴露出来，对主从复制延迟也有直接影响。

扩展深度分析，关联等待事件

pg_stat_statements只能告诉你SQL跑了多久、读写多少块，但它说不清楚时间花在了哪里。PostgreSQL从9.6版本开始支持等待事件统计，通过pg_stat_activity可以实时看到当前正在执行的语句卡在哪个等待事件上。把这两张表关联起来，就能构建出慢SQL的完整画像。下面这个查询会展示当前正在运行超过5秒的语句，以及它们此刻的等待状态。

SELECT 
    pid,
    now() - query_start AS duration,
    wait_event_type,
    wait_event,
    left(query, 200) AS query_snippet,
    state
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
  AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC;

常见的等待事件类型中，LWLock和Lock代表锁争用，IO类的DataFileRead说明磁盘读取慢，WALWrite则是写WAL的瓶颈。如果大量慢SQL集中在DataFileRead，那问题大概率出在存储层或者shared_buffers配置太小。如果集中在LWLock，说明并发争用严重，可能需要调整应用逻辑减少热点更新。把等待事件和pg_stat_statements的总耗时数据交叉分析，你就能区分出哪些SQL是自身执行慢，哪些是被其他因素拖累的。

日志辅助，捕获执行计划

统计信息告诉你哪条SQL有问题，但要搞清楚为什么慢，必须看执行计划。auto_explain模块可以在SQL执行超过设定阈值时，自动把执行计划输出到日志。这比事后用EXPLAIN ANALYZE重放要准确得多，因为重放时的参数和数据分布可能已经变了。

# postgresql.conf
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = on
auto_explain.log_nested_statements = on
auto_explain.log_triggers = on

log_min_duration单位是毫秒，设为1000表示执行超过1秒的SQL都会记录执行计划。log_analyze一定要开，否则你拿到的只是预估计划，没有实际行数和真实耗时。log_buffers会展示各节点的缓存命中情况，这对判断索引扫描效率至关重要。生产环境开启auto_explain会有额外开销，建议先设一个较高的阈值比如5000毫秒，观察一段时间再逐步调低。如果担心性能影响，可以只在需要排查问题的会话级别开启，用SET auto_explain.log_min_duration = 100在事务开始前设置。

构建慢SQL采集流水线

单靠手工查询pg_stat_statements有个致命缺陷——这个视图的数据是累积的，而且会在重启或手动重置后清零。你需要一套自动化的采集和存储机制，把每次快照保存下来，才能做趋势分析和同比环比。最轻量的方案是用cron定时执行采集脚本，把结果写入一张历史表。

-- 创建快照存储表
CREATE TABLE stmt_history (
    snap_time timestamptz DEFAULT now(),
    queryid bigint,
    query_text text,
    calls bigint,
    total_exec_time double precision,
    mean_exec_time double precision,
    rows bigint,
    shared_blks_read bigint,
    shared_blks_hit bigint,
    wal_bytes bigint
);

-- 采集快照
INSERT INTO stmt_history 
(snap_time, queryid, query_text, calls, total_exec_time, mean_exec_time, rows, shared_blks_read, shared_blks_hit, wal_bytes)
SELECT 
    now(),
    queryid,
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    shared_blks_read,
    shared_blks_hit,
    wal_bytes
FROM pg_stat_statements;

有了历史数据，你就可以计算两次快照之间的增量，定位哪些SQL的执行频率或耗时在最近一个时间段内突然恶化。用窗口函数lag()取上一次快照的值，当前值减去上次值就是增量，再除以时间间隔得到速率。这种方法比直接看累积值敏感得多，能抓到刚出现的性能劣化。

WITH snap_diff AS (
    SELECT 
        queryid,
        snap_time,
        total_exec_time - lag(total_exec_time) OVER (PARTITION BY queryid ORDER BY snap_time) AS delta_time,
        calls - lag(calls) OVER (PARTITION BY queryid ORDER BY snap_time) AS delta_calls
    FROM stmt_history
    WHERE queryid = 某个可疑queryid
)
SELECT * FROM snap_diff WHERE delta_time > 0 ORDER BY delta_time DESC;

参数化查询的坑，统计失真的根源

pg_stat_statements会把相似的查询归并，参数用$1、$2替代。这个机制在绝大多数情况下是好事，但有时候会掩盖真正的问题。比如一个查询在某个参数值下走索引飞快，在另一个参数值下因为数据倾斜走了全表扫描极慢。统计视图里你只能看到平均耗时，无法感知到这种方差。这时候需要结合auto_explain日志，把log_min_duration设低一些，让那些偶发的慢执行被单独记录下来。日志里会保留具体的参数值，你可以拿着这些参数值手动执行，验证执行计划的差异。如果确认是数据倾斜导致的计划漂移，可以考虑用pg_hint_plan绑定执行计划，或者在应用层对特殊参数值做分支处理。

锁等待导致的“伪慢SQL”

有一种情况很容易误判：SQL本身执行很快，但因为等待锁释放，在统计中表现为高耗时。pg_stat_statements记录的是从语句开始到结束的端到端时间，包含等待锁的时间。如果你发现某条UPDATE或DELETE语句平均耗时很高，但shared_blks_read和shared_blks_hit都不大，那就要怀疑是锁等待。查询pg_locks和pg_stat_activity的组合视图，看这条语句历史上是否频繁处于waiting状态。PostgreSQL 14以后pg_stat_statements增加了wal_bytes和temp_blk相关字段，但锁等待时间仍然没有单独剥离，需要靠等待事件采样来推断。如果你的系统版本在13以上，可以结合pg_stat_progress_*系列视图，实时观察VACUUM、ANALYZE、CREATE INDEX等操作的进度，这些后台任务如果和你的DML语句冲突，也会造成锁等待。

IO统计的深层解读

shared_blks_hit和shared_blks_read这两个字段，很多人只是简单算一下命中率就完事了。实际上，hit很高不一定代表索引设计好，也可能是shared_buffers设得过大，把大量不常用的数据也缓存在内存里，挤占了操作系统的文件缓存。反过来，read很高也不一定是坏事，如果是一次性的大批量顺序扫描，操作系统层面的预读机制效率很高，反而是正常的。真正需要警惕的是重复出现的随机读，也就是同样的数据块被反复从磁盘读取，说明shared_buffers或者查询的work_mem不够用。观察shared_blks_read与calls的比值，如果每次调用平均读取的块数很多，而且这些块在后续查询中又被再次读取，那就需要调整内存参数或者优化查询逻辑。

日常运维中的持续监控

慢SQL统计不是一次性的排查动作，而是要嵌入日常运维流程。建议搭建一套轻量的监控体系，核心指标包括：每小时总耗时Top10的SQL变化、平均耗时突增超过50%的SQL、新增的从未出现过的查询模式、以及shared_blks_read陡升的语句。这些指标可以通过pg_stat_statements的快照差值自动计算，配合告警规则，在性能问题影响到用户之前就发出预警。很多团队等到CPU跑满或者连接池耗尽才发现问题，这时候日志已经被大量慢查询淹没，排查起来事倍功半。日常的统计和趋势分析，能让你在问题萌芽阶段就介入，定位成本低得多。

另外，PostgreSQL 15和16版本对pg_stat_statements做了不少增强，比如增加了toast_blk相关字段、支持查询内部子语句的独立统计等。如果你的环境允许升级，新版本在统计粒度和准确性上都有明显提升。但对于大多数还在用13、14版本的用户来说，上述方法已经完全够用，关键在于把采集、存储、分析这套流程跑起来，而不是追求工具本身的花哨功能。

服务指南

帮助中心