PostgreSQL 故障排查：如何找出数据库中最耗时的 SQL 语句

在 PostgreSQL 生产环境中，性能下降、CPU 飙升、连接堆积等问题往往源于少数几条“毒瘤”SQL。这些语句可能因缺失索引、数据倾斜、统计信息过期或设计缺陷，导致执行时间从毫秒级恶化至分钟甚至小时级。若不能快速、准确地识别并定位这些最耗时的 SQL，故障恢复将无从谈起。

本文将系统性地阐述“找出最耗时 SQL” 的完整方法论，涵盖日志分析、扩展工具、实时监控、执行计划解读及自动化手段，提供一套可立即落地的排查 SOP（标准操作流程），适用于 DBA、运维工程师及后端开发者。

一、核心目标与排查原则

1. 明确“耗时”的定义

• 单次执行耗时长（如一条查询跑 5 分钟）；
• 累计总耗时高（如某简单查询每秒执行 1000 次，总耗时占 90% CPU）；
• 资源消耗大（高 I/O、高内存、高锁等待）。

因此，“最耗时”需从平均耗时、总耗时、资源开销三个维度综合判断。

2. 排查原则

• 优先使用内置机制（避免外部依赖）；
• 区分历史累计与当前活跃；
• 结合上下文（参数、数据量、执行频率）；
• 生产环境操作需安全（避免加重负载）。

二、方法一：启用并分析pg_stat_statements（首选方案）

pg_stat_statements是 PostgreSQL 官方提供的 SQL 统计扩展，能按SQL 模板（归一化）聚合执行指标，是定位历史慢 SQL 的黄金标准。

1. 安装与配置

（1）修改postgresql.conf

# 必须放在 shared_preload_libraries 中（需重启） shared_preload_libraries = 'pg_stat_statements' # 可选配置（动态生效） pg_stat_statements.max = 10000 # 最多跟踪 1 万个不同 SQL pg_stat_statements.track = all # 跟踪所有语句（top, all, none） pg_stat_statements.save = on # 重启后保留统计

修改shared_preload_libraries后必须重启 PostgreSQL。

（2）创建扩展

CREATEEXTENSION pg_stat_statements;

2. 核心视图字段说明

查询pg_stat_statements视图，关键字段如下：

注意：total_time在 PostgreSQL 13+ 已更名为total_exec_time。

3. 实用查询模板

（1）找出平均执行时间最长的 Top 20 SQL

SELECTquery,calls,mean_exec_time,total_exec_time,rows/NULLIF(calls,0)ASavg_rowsFROMpg_stat_statementsORDERBYmean_exec_timeDESCLIMIT20;

适用场景：识别“单次特别慢”的查询（如报表、批处理）。

（2）找出总耗时最高的 Top 20 SQL

SELECTquery,calls,total_exec_time,total_exec_time/NULLIF(calls,0)ASavg_time,rowsFROMpg_stat_statementsORDERBYtotal_exec_timeDESCLIMIT20;

适用场景：识别“高频低效”查询（如循环内未优化的 SELECT）。

（3）找出高物理 I/O 的 SQL（I/O 瓶颈）

SELECTquery,shared_blks_read,shared_blks_hit,ROUND(100.0*shared_blks_read/NULLIF(shared_blks_read+shared_blks_hit,0),2)ASmiss_pctFROMpg_stat_statementsWHEREshared_blks_read>0ORDERBYshared_blks_readDESCLIMIT20;

若miss_pct高，说明缓存不足或全表扫描严重。

（4）找出使用临时文件的 SQL（内存不足）

SELECTquery,temp_blks_read,temp_blks_written,total_exec_timeFROMpg_stat_statementsWHEREtemp_blks_read>0ORtemp_blks_written>0ORDERBYtemp_blks_writtenDESC;

临时文件通常由ORDER BY、GROUP BY、Hash Join的work_mem不足引起。

4. 注意事项

• 归一化限制：query字段中的常量被替换为$1，无法直接看到具体参数。需结合应用日志还原。
• 内存开销：跟踪大量 SQL 会占用共享内存，合理设置pg_stat_statements.max。
• 重置统计：执行SELECT pg_stat_statements_reset();可清空当前统计（用于对比优化前后）。

三、方法二：分析 PostgreSQL 日志（适用于未启用扩展的场景）

若未提前配置pg_stat_statements，可通过日志回溯慢 SQL。

1. 配置日志记录慢查询

在postgresql.conf中设置：

log_min_duration_statement = 1000 # 记录执行时间 ≥1000ms 的语句 log_statement = 'none' # 避免记录所有语句（性能开销大） log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' # 添加上下文

重载配置：

pg_ctl reload -D$PGDATA

2. 日志示例

2026-02-08 10:30:45.123 UTC [12345]: [5-1] user=app_user,db=prod_db,app=web,client=10.0.0.5 LOG: duration: 4523.678 ms statement: SELECT * FROM orders WHERE user_id = $1 AND status = $2

3. 日志分析技巧

• 使用 grep/awk 提取：

  # 提取耗时 >5s 的语句grep"duration:"postgresql.log|awk'$7 > 5000 {print $0}'

• 使用工具解析：

  • pgbadger：生成可视化 HTML 报告；
  • goaccess或自定义脚本聚合。

缺点：无法按 SQL 模板聚合，需手动去重；且仅能分析配置后的日志。

四、方法三：实时监控当前活跃慢查询

上述方法针对历史数据。若数据库正在卡顿，需立即查看当前执行的慢 SQL。

1. 查询pg_stat_activity

SELECTpid,now()-query_startASduration,usename,datname,client_addr,application_name,state,queryFROMpg_stat_activityWHEREstate='active'ANDnow()-query_start>INTERVAL'5 seconds'ORDERBYdurationDESC;

关键字段：

• query_start：查询开始时间；
• state：active表示正在执行；
• wait_event：若非空，表示在等待（如Lock、IO）。

2. 结合pg_blocking_pids()查锁阻塞

若查询长时间不动，可能是被锁阻塞：

-- 查看阻塞者SELECTblocked.pidASblocked_pid,blocked.queryASblocked_query,blocking.pidASblocking_pid,blocking.queryASblocking_queryFROMpg_stat_activity blockedJOINpg_stat_activity blockingONblocking.pid=ANY(pg_blocking_pids(blocked.pid))WHEREblocked.wait_eventISNOTNULL;

3. 终止问题会话（谨慎！）

-- 取消查询（发送 SIGINT）SELECTpg_cancel_backend(pid);-- 强制终止会话（发送 SIGTERM）SELECTpg_terminate_backend(pid);

仅在确认无业务影响时使用。

五、方法四：使用auto_explain自动记录执行计划

若需不仅知道“哪条 SQL 慢”，还要知道“为什么慢”，可启用auto_explain自动记录慢查询的执行计划。

1. 配置postgresql.conf

shared_preload_libraries = 'pg_stat_statements, auto_explain' # auto_explain 设置 auto_explain.log_min_duration = 1000 # ≥1s 的查询记录计划 auto_explain.log_analyze = true # 记录实际执行时间（非估算） auto_explain.log_buffers = true # 记录 I/O auto_explain.log_format = json # JSON 格式便于解析

重启后生效。

2. 日志示例

LOG: duration: 4523.678 ms plan: { "Plan": { "Node Type": "Seq Scan", "Relation Name": "orders", "Alias": "orders", "Startup Cost": 0.00, "Total Cost": 123456.78, "Plan Rows": 1000000, "Actual Rows": 987654, "Actual Total Time": 4520.123 } }

优势：直接关联慢 SQL 与执行计划，无需手动EXPLAIN。

六、高级技巧：结合系统视图深度分析

1. 关联用户与数据库名

pg_stat_statements中的userid和dbid是 OID，需关联pg_user和pg_database获取名称：

SELECTd.datname,u.usename,s.query,s.mean_exec_timeFROMpg_stat_statements sJOINpg_database dONs.dbid=d.oidJOINpg_user uONs.userid=u.usesysidORDERBYs.mean_exec_timeDESCLIMIT10;

2. 识别未使用索引的表

高耗时查询常伴随缺失索引：

SELECTschemaname,tablename,idx_scanASindex_scansFROMpg_stat_user_indexesWHEREidx_scan=0ORDERBYschemaname,tablename;

3. 检查表膨胀与死元组

高n_dead_tup可能导致查询变慢：

SELECTschemaname,tablename,n_live_tup,n_dead_tup,ROUND(n_dead_tup::float/(n_live_tup+1),2)ASdead_ratioFROMpg_stat_user_tablesWHEREn_dead_tup>10000ORDERBYdead_ratioDESC;

七、自动化与监控集成

1. Prometheus + Grafana 监控

• 使用postgres_exporter采集pg_stat_statements指标；
• 在 Grafana 中创建面板，实时展示 Top 慢 SQL。

2. 自定义告警脚本

定期运行 SQL 检查，若发现异常则告警：

# 伪代码ifmax_mean_time>5000:send_alert("发现平均耗时 >5s 的 SQL: "+query)

3. APM 工具联动

• New Relic、Datadog 等 APM 工具可自动捕获慢 SQL；
• 结合应用上下文（如 URL、用户 ID）定位根因。

八、排查流程总结（SOP）

1. 初步判断：

   • 数据库是否正在卡顿？→ 查pg_stat_activity
   • 是否有历史慢 SQL？→ 查pg_stat_statements

2. 若已配置pg_stat_statements：

   • 按mean_exec_time和total_exec_time排序；
   • 分析高 I/O、高临时文件的语句。

3. 若未配置：

   • 检查日志中duration:记录；
   • 启用auto_explain为后续排查做准备。

4. 实时问题：

   • 查pg_stat_activity找活跃长查询；
   • 检查wait_event判断是否锁阻塞。

5. 深度分析：

   • 对 Top 慢 SQL 执行EXPLAIN (ANALYZE, BUFFERS)；
   • 检查索引、统计信息、参数配置。

6. 长期预防：

   • 开启pg_stat_statements和auto_explain；
   • 设置慢查询告警；
   • 建立 SQL 上线审核机制。

九、常见误区与注意事项

• 误区 1：“只看总耗时，忽略平均耗时”
  → 高频简单查询可能掩盖真正危险的单次慢查询。

• 误区 2：“直接 kill 长查询而不分析”
  → 可能反复发生，治标不治本。

• 误区 3：“认为pg_stat_statements会显著影响性能”
  → 实测开销通常 < 3%，远低于其带来的诊断价值。

• 注意：生产环境执行EXPLAIN ANALYZE时，避免对 DML（UPDATE/DELETE）使用，以免误操作。

结语：找出最耗时的 SQL 是 PostgreSQL 性能故障排查的第一步，也是最关键的一步。通过pg_stat_statements为核心，辅以日志、实时监控和执行计划分析，可构建一套高效、可靠的诊断体系。

记住：没有监控的数据库，就像没有仪表盘的飞机。提前配置好pg_stat_statements和auto_explain，才能在故障发生时从容应对，将 MTTR（平均恢复时间）降至最低。