文章目录一、核心目标与排查原则1. 明确“耗时”的定义2. 排查原则二、方法一启用并分析 pg_stat_statements首选方案1. 安装与配置1修改 postgresql.conf2创建扩展2. 核心视图字段说明3. 实用查询模板1找出平均执行时间最长的 Top 20 SQL2找出总耗时最高的 Top 20 SQL3找出高物理 I/O 的 SQLI/O 瓶颈4找出使用临时文件的 SQL内存不足4. 注意事项三、方法二分析 PostgreSQL 日志适用于未启用扩展的场景1. 配置日志记录慢查询2. 日志示例3. 日志分析技巧四、方法三实时监控当前活跃慢查询1. 查询 pg_stat_activity2. 结合 pg_blocking_pids() 查锁阻塞3. 终止问题会话谨慎五、方法四使用 auto_explain 自动记录执行计划1. 配置 postgresql.conf2. 日志示例六、高级技巧结合系统视图深度分析1. 关联用户与数据库名2. 识别未使用索引的表3. 检查表膨胀与死元组七、自动化与监控集成1. Prometheus Grafana 监控2. 自定义告警脚本3. APM 工具联动八、排查流程总结SOP九、常见误区与注意事项在 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视图关键字段如下字段类型说明useridoid执行用户 OIDdbidoid数据库 OIDqueryidbigintSQL 模板唯一 ID相同结构不同参数视为同一 IDquerytext归一化后的 SQL参数替换为$1,$2callsbigint执行次数total_exec_timedouble precision总执行时间毫秒mean_exec_timedouble precision平均执行时间毫秒rowsbigint返回总行数shared_blks_hitbigintshared buffer 命中次数shared_blks_readbigint从磁盘读取的 shared buffer 块数shared_blks_dirtiedbigint被修改的块数shared_blks_writtenbigint写回磁盘的块数temp_blks_read/writtenbigint临时文件 I/O排序/哈希溢出注意total_time在 PostgreSQL 13 已更名为total_exec_time。3. 实用查询模板1找出平均执行时间最长的 Top 20 SQLSELECTquery,calls,mean_exec_time,total_exec_time,rows/NULLIF(calls,0)ASavg_rowsFROMpg_stat_statementsORDERBYmean_exec_timeDESCLIMIT20;适用场景识别“单次特别慢”的查询如报表、批处理。2找出总耗时最高的 Top 20 SQLSELECTquery,calls,total_exec_time,total_exec_time/NULLIF(calls,0)ASavg_time,rowsFROMpg_stat_statementsORDERBYtotal_exec_timeDESCLIMIT20;适用场景识别“高频低效”查询如循环内未优化的 SELECT。3找出高物理 I/O 的 SQLI/O 瓶颈SELECTquery,shared_blks_read,shared_blks_hit,ROUND(100.0*shared_blks_read/NULLIF(shared_blks_readshared_blks_hit,0),2)ASmiss_pctFROMpg_stat_statementsWHEREshared_blks_read0ORDERBYshared_blks_readDESCLIMIT20;若miss_pct高说明缓存不足或全表扫描严重。4找出使用临时文件的 SQL内存不足SELECTquery,temp_blks_read,temp_blks_written,total_exec_timeFROMpg_stat_statementsWHEREtemp_blks_read0ORtemp_blks_written0ORDERBYtemp_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$PGDATA2. 日志示例2026-02-08 10:30:45.123 UTC [12345]: [5-1] userapp_user,dbprod_db,appweb,client10.0.0.5 LOG: duration: 4523.678 ms statement: SELECT * FROM orders WHERE user_id $1 AND status $23. 日志分析技巧使用 grep/awk 提取# 提取耗时 5s 的语句grepduration:postgresql.log|awk$7 5000 {print $0}使用工具解析pgbadger生成可视化 HTML 报告goaccess或自定义脚本聚合。缺点无法按 SQL 模板聚合需手动去重且仅能分析配置后的日志。四、方法三实时监控当前活跃慢查询上述方法针对历史数据。若数据库正在卡顿需立即查看当前执行的慢 SQL。1. 查询pg_stat_activitySELECTpid,now()-query_startASduration,usename,datname,client_addr,application_name,state,queryFROMpg_stat_activityWHEREstateactiveANDnow()-query_startINTERVAL5 secondsORDERBYdurationDESC;关键字段query_start查询开始时间stateactive表示正在执行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.pidANY(pg_blocking_pids(blocked.pid))WHEREblocked.wait_eventISNOTNULL;3. 终止问题会话谨慎-- 取消查询发送 SIGINTSELECTpg_cancel_backend(pid);-- 强制终止会话发送 SIGTERMSELECTpg_terminate_backend(pid);仅在确认无业务影响时使用。五、方法四使用auto_explain自动记录执行计划若需不仅知道“哪条 SQL 慢”还要知道“为什么慢”可启用auto_explain自动记录慢查询的执行计划。1. 配置postgresql.confshared_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.dbidd.oidJOINpg_user uONs.useridu.usesysidORDERBYs.mean_exec_timeDESCLIMIT10;2. 识别未使用索引的表高耗时查询常伴随缺失索引SELECTschemaname,tablename,idx_scanASindex_scansFROMpg_stat_user_indexesWHEREidx_scan0ORDERBYschemaname,tablename;3. 检查表膨胀与死元组高n_dead_tup可能导致查询变慢SELECTschemaname,tablename,n_live_tup,n_dead_tup,ROUND(n_dead_tup::float/(n_live_tup1),2)ASdead_ratioFROMpg_stat_user_tablesWHEREn_dead_tup10000ORDERBYdead_ratioDESC;七、自动化与监控集成1. Prometheus Grafana 监控使用postgres_exporter采集pg_stat_statements指标在 Grafana 中创建面板实时展示 Top 慢 SQL。2. 自定义告警脚本定期运行 SQL 检查若发现异常则告警# 伪代码ifmax_mean_time5000:send_alert(发现平均耗时 5s 的 SQL: query)3. APM 工具联动New Relic、Datadog 等 APM 工具可自动捕获慢 SQL结合应用上下文如 URL、用户 ID定位根因。八、排查流程总结SOP初步判断数据库是否正在卡顿→ 查pg_stat_activity是否有历史慢 SQL→ 查pg_stat_statements若已配置pg_stat_statements按mean_exec_time和total_exec_time排序分析高 I/O、高临时文件的语句。若未配置检查日志中duration:记录启用auto_explain为后续排查做准备。实时问题查pg_stat_activity找活跃长查询检查wait_event判断是否锁阻塞。深度分析对 Top 慢 SQL 执行EXPLAIN (ANALYZE, BUFFERS)检查索引、统计信息、参数配置。长期预防开启pg_stat_statements和auto_explain设置慢查询告警建立 SQL 上线审核机制。九、常见误区与注意事项误区 1“只看总耗时忽略平均耗时”→ 高频简单查询可能掩盖真正危险的单次慢查询。误区 2“直接 kill 长查询而不分析”→ 可能反复发生治标不治本。误区 3“认为pg_stat_statements会显著影响性能”→ 实测开销通常 3%远低于其带来的诊断价值。注意生产环境执行EXPLAIN ANALYZE时避免对 DMLUPDATE/DELETE使用以免误操作。结语找出最耗时的 SQL 是 PostgreSQL 性能故障排查的第一步也是最关键的一步。通过pg_stat_statements为核心辅以日志、实时监控和执行计划分析可构建一套高效、可靠的诊断体系。记住没有监控的数据库就像没有仪表盘的飞机。提前配置好pg_stat_statements和auto_explain才能在故障发生时从容应对将 MTTR平均恢复时间降至最低。