文章目录环境文档用途详细信息环境系统平台Linux x86-64 Red Hat Enterprise Linux 7版本9.0,6.0,4.5文档用途分析同一条 SQL 执行时间差异大的常见原因并提供排查思路、解决方案。详细信息一、问题概述同一条 SQL 在相同参数下执行时间出现明显波动从毫秒级到秒级甚至更长是生产环境中最常见的疑难问题之一。根因大致可归为以下几类类别典型现象执行计划变化某次查询突然走了全表扫描缓冲区缓存未命中冷启动 / 大表查询首次明显慢等待事件锁等待、IO 等待导致排队统计信息过期autovacuum 滞后基数估算偏差大参数嗅探Parameter Sniffing预编译语句与实际参数分布不匹配二、逐类分析与排查2.1 执行计划变化原因统计信息更新导致优化器切换计划如从 Index Scan → Seq Scanenable_* 参数被会话级修改表数据量增长越过优化器阈值自定义 plan_cache_mode 设置排查方法-- 查看实际执行计划含缓冲区信息EXPLAIN(ANALYZE,BUFFERS,FORMATTEXT)SELECT*FROMordersWHEREuser_id12345;重点关注Rows Removed by Filter 过大 → 说明基数估算偏差Seq Scan 替代 Index Scan → 可能是统计信息问题或 random_page_cost 设置偏高actual rows 与 estimated rows 差距超过 10 倍 → 需要手动 ANALYZE-- 强制刷新统计信息ANALYZEorders;-- 查看统计信息最后更新时间SELECTschemaname,relname,last_analyze,last_autoanalyze,last_vacuum,last_autovacuumFROMpg_stat_user_tablesWHERErelnameorders;固定执行计划应急手段-- 强制使用索引扫描会话级SETenable_seqscanoff;-- 或使用 pg_hint_plan 扩展需提前安装/* IndexScan(orders idx_orders_user_id) */SELECT*FROMordersWHEREuser_id12345;2.2 缓冲区缓存Shared Buffers未命中原因数据库重启 / failover 后缓存为空冷缓存大批量写入或 VACUUM 操作将热数据页从 shared_buffers 中驱逐查询的数据集超出 shared_buffers依赖 OS page cache排查方法-- 查看缓冲区命中率越接近 1 越好SELECTsum(heap_blks_hit)/NULLIF(sum(heap_blks_hit)sum(heap_blks_read),0)AScache_hit_ratioFROMpg_statio_user_tables;-- 查看单表的缓冲区使用情况需要 pg_buffercache 扩展CREATEEXTENSIONIFNOTEXISTSpg_buffercache;SELECTc.relname,count(*)ASbuffersFROMpg_buffercache bJOINpg_class cONb.relfilenodepg_relation_filenode(c.oid)WHEREc.relnameordersGROUPBYc.relname;-- EXPLAIN BUFFERS 中的关键指标解读-- Buffers: shared hit1000 read500-- hit → 从 shared_buffers 读取快-- read → 从磁盘/OS cache 读取慢EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREcreated_atnow()-interval1 day;建议热数据表考虑使用 pg_prewarm 预热缓存评估 shared_buffers 是否需要调整建议为总内存的 25%-- 预热指定表SELECTpg_prewarm(orders);2.3 等待事件Wait Events这是生产环境中最常见的突然变慢根因之一。常见等待事件分类等待类型典型事件说明Lockrelation, tuple, transactionid行锁、表锁冲突LWLockBufferContent, WALWrite内部轻量锁竞争IODataFileRead, WALWrite磁盘 IO 瓶颈ClientClientRead网络延迟或客户端处理慢IPCBgWorkerShutdown后台进程协调等待排查方法-- 实时查看当前等待事件SELECTpid,usename,application_name,wait_event_type,wait_event,state,query_start,now()-query_startASelapsed,LEFT(query,80)ASquery_snippetFROMpg_stat_activityWHEREstate!idleANDwait_eventISNOTNULLORDERBYelapsedDESCNULLSLAST;-- 查找阻塞链谁在堵谁SELECTblocked.pidASblocked_pid,blocked.queryASblocked_query,blocked.stateASblocked_state,blocking.pidASblocking_pid,blocking.queryASblocking_query,blocking.stateASblocking_state,blocking.usenameASblocking_userFROMpg_stat_activityASblockedJOINpg_stat_activityASblockingONblocking.pidANY(pg_blocking_pids(blocked.pid))WHEREcardinality(pg_blocking_pids(blocked.pid))0;-- 统计等待事件分布适合周期采样后分析SELECTwait_event_type,wait_event,count(*)FROMpg_stat_activityWHEREstate!idleGROUPBY1,2ORDERBY3DESC;2.4 统计信息过期 / autovacuum 滞后原因大批量 INSERT/UPDATE/DELETE 后 autovacuum 还未触发autovacuum 被长事务阻塞xmin 过老表的 autovacuum_analyze_scale_factor 阈值设置过高排查方法-- 查看表的死元组数量与统计信息新鲜度SELECTrelname,n_live_tup,n_dead_tup,ROUND(n_dead_tup::numeric/NULLIF(n_live_tupn_dead_tup,0)*100,2)ASdead_ratio_pct,last_vacuum,last_autovacuum,last_analyze,last_autoanalyzeFROMpg_stat_user_tablesWHERErelnameorders;-- 查看阻塞 autovacuum 的老事务SELECTpid,usename,xact_start,now()-xact_startASage,state,queryFROMpg_stat_activityWHEREbackend_xminISNOTNULLORDERBYxact_startASC;-- 手动触发 VACUUM ANALYZE不锁表VACUUMANALYZEorders;2.5 参数嗅探Parameter Sniffing原因数据库使用 generic plan通用计划缓存 prepared statement。当实际参数的数据分布与通用计划假设不符时会产生严重的计划偏差。通过 plan_cache_mode 参数控制值含义auto默认前 5 次用 custom plan之后对比开销决定force_generic_plan强制通用计划参数不敏感场景性能好force_custom_plan每次根据实际参数重新规划计划一定准但有规划开销排查方法-- 使用 PREPARE 模拟观察通用计划PREPAREtest_plan(bigint)ASSELECT*FROMordersWHEREuser_id$1;-- 执行 6 次后触发通用计划EXECUTEtest_plan(12345);-- ... 重复 5 次-- 查看通用计划EXPLAINEXECUTEtest_plan(12345);-- 清理DEALLOCATEtest_plan;-- 会话级强制每次用 custom plan排查专用SETplan_cache_modeforce_custom_plan;三、系统化排查流程SQL 执行时间波动 │ ├─ 1. 先看 pg_stat_activity → 是否有等待事件、锁等待 │ └─ 有锁 → 找阻塞链 │ └─ IO 等待 → 检查磁盘压力 │ ├─ 2. EXPLAIN (ANALYZE, BUFFERS) → 执行计划是否改变 │ └─ 计划改变 → 检查统计信息考虑固定计划 │ └─ Buffers read 高 → 缓存冷考虑预热 │ ├─ 3. pg_stat_user_tables → 统计信息是否过期 │ └─ 死元组多、last_analyze 很旧 → VACUUM ANALYZE │ ├─ 4. prepared statement └─ 是 → 检查 plan_cache_mode考虑 force_custom_plan四、参数调优参考参数默认值优化建议影响shared_buffers128MB总内存 25%缓冲区命中率effective_cache_size4GB总内存 75%优化器索引决策random_page_cost4.0SSD 改为 1.1~1.5影响索引扫描选择work_mem4MB复杂查询 16~64MB排序/hash 性能plan_cache_modeauto参数偏斜场景用 force_custom_plan计划稳定性autovacuum_analyze_scale_factor0.2大表改为 0.01~0.05统计信息新鲜度log_min_duration_statement-1生产建议 500~1000ms慢查询日志六、总结优先级检查项工具高锁等待 / 阻塞链pg_stat_activity, pg_blocking_pids()高执行计划突变EXPLAIN (ANALYZE, BUFFERS)中统计信息过期pg_stat_user_tables VACUUM ANALYZE中缓冲区冷缓存pg_buffercache, pg_prewarm中参数嗅探plan_cache_mode低系统资源瓶颈pg_stat_bgwriter, OS 层监控建议遇到突发慢查询先看等待事件再看执行计划最后看资源。80% 的波动问题出在前两项。
同一 SQL 执行时间差异大的原因分析与排查
文章目录环境文档用途详细信息环境系统平台Linux x86-64 Red Hat Enterprise Linux 7版本9.0,6.0,4.5文档用途分析同一条 SQL 执行时间差异大的常见原因并提供排查思路、解决方案。详细信息一、问题概述同一条 SQL 在相同参数下执行时间出现明显波动从毫秒级到秒级甚至更长是生产环境中最常见的疑难问题之一。根因大致可归为以下几类类别典型现象执行计划变化某次查询突然走了全表扫描缓冲区缓存未命中冷启动 / 大表查询首次明显慢等待事件锁等待、IO 等待导致排队统计信息过期autovacuum 滞后基数估算偏差大参数嗅探Parameter Sniffing预编译语句与实际参数分布不匹配二、逐类分析与排查2.1 执行计划变化原因统计信息更新导致优化器切换计划如从 Index Scan → Seq Scanenable_* 参数被会话级修改表数据量增长越过优化器阈值自定义 plan_cache_mode 设置排查方法-- 查看实际执行计划含缓冲区信息EXPLAIN(ANALYZE,BUFFERS,FORMATTEXT)SELECT*FROMordersWHEREuser_id12345;重点关注Rows Removed by Filter 过大 → 说明基数估算偏差Seq Scan 替代 Index Scan → 可能是统计信息问题或 random_page_cost 设置偏高actual rows 与 estimated rows 差距超过 10 倍 → 需要手动 ANALYZE-- 强制刷新统计信息ANALYZEorders;-- 查看统计信息最后更新时间SELECTschemaname,relname,last_analyze,last_autoanalyze,last_vacuum,last_autovacuumFROMpg_stat_user_tablesWHERErelnameorders;固定执行计划应急手段-- 强制使用索引扫描会话级SETenable_seqscanoff;-- 或使用 pg_hint_plan 扩展需提前安装/* IndexScan(orders idx_orders_user_id) */SELECT*FROMordersWHEREuser_id12345;2.2 缓冲区缓存Shared Buffers未命中原因数据库重启 / failover 后缓存为空冷缓存大批量写入或 VACUUM 操作将热数据页从 shared_buffers 中驱逐查询的数据集超出 shared_buffers依赖 OS page cache排查方法-- 查看缓冲区命中率越接近 1 越好SELECTsum(heap_blks_hit)/NULLIF(sum(heap_blks_hit)sum(heap_blks_read),0)AScache_hit_ratioFROMpg_statio_user_tables;-- 查看单表的缓冲区使用情况需要 pg_buffercache 扩展CREATEEXTENSIONIFNOTEXISTSpg_buffercache;SELECTc.relname,count(*)ASbuffersFROMpg_buffercache bJOINpg_class cONb.relfilenodepg_relation_filenode(c.oid)WHEREc.relnameordersGROUPBYc.relname;-- EXPLAIN BUFFERS 中的关键指标解读-- Buffers: shared hit1000 read500-- hit → 从 shared_buffers 读取快-- read → 从磁盘/OS cache 读取慢EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREcreated_atnow()-interval1 day;建议热数据表考虑使用 pg_prewarm 预热缓存评估 shared_buffers 是否需要调整建议为总内存的 25%-- 预热指定表SELECTpg_prewarm(orders);2.3 等待事件Wait Events这是生产环境中最常见的突然变慢根因之一。常见等待事件分类等待类型典型事件说明Lockrelation, tuple, transactionid行锁、表锁冲突LWLockBufferContent, WALWrite内部轻量锁竞争IODataFileRead, WALWrite磁盘 IO 瓶颈ClientClientRead网络延迟或客户端处理慢IPCBgWorkerShutdown后台进程协调等待排查方法-- 实时查看当前等待事件SELECTpid,usename,application_name,wait_event_type,wait_event,state,query_start,now()-query_startASelapsed,LEFT(query,80)ASquery_snippetFROMpg_stat_activityWHEREstate!idleANDwait_eventISNOTNULLORDERBYelapsedDESCNULLSLAST;-- 查找阻塞链谁在堵谁SELECTblocked.pidASblocked_pid,blocked.queryASblocked_query,blocked.stateASblocked_state,blocking.pidASblocking_pid,blocking.queryASblocking_query,blocking.stateASblocking_state,blocking.usenameASblocking_userFROMpg_stat_activityASblockedJOINpg_stat_activityASblockingONblocking.pidANY(pg_blocking_pids(blocked.pid))WHEREcardinality(pg_blocking_pids(blocked.pid))0;-- 统计等待事件分布适合周期采样后分析SELECTwait_event_type,wait_event,count(*)FROMpg_stat_activityWHEREstate!idleGROUPBY1,2ORDERBY3DESC;2.4 统计信息过期 / autovacuum 滞后原因大批量 INSERT/UPDATE/DELETE 后 autovacuum 还未触发autovacuum 被长事务阻塞xmin 过老表的 autovacuum_analyze_scale_factor 阈值设置过高排查方法-- 查看表的死元组数量与统计信息新鲜度SELECTrelname,n_live_tup,n_dead_tup,ROUND(n_dead_tup::numeric/NULLIF(n_live_tupn_dead_tup,0)*100,2)ASdead_ratio_pct,last_vacuum,last_autovacuum,last_analyze,last_autoanalyzeFROMpg_stat_user_tablesWHERErelnameorders;-- 查看阻塞 autovacuum 的老事务SELECTpid,usename,xact_start,now()-xact_startASage,state,queryFROMpg_stat_activityWHEREbackend_xminISNOTNULLORDERBYxact_startASC;-- 手动触发 VACUUM ANALYZE不锁表VACUUMANALYZEorders;2.5 参数嗅探Parameter Sniffing原因数据库使用 generic plan通用计划缓存 prepared statement。当实际参数的数据分布与通用计划假设不符时会产生严重的计划偏差。通过 plan_cache_mode 参数控制值含义auto默认前 5 次用 custom plan之后对比开销决定force_generic_plan强制通用计划参数不敏感场景性能好force_custom_plan每次根据实际参数重新规划计划一定准但有规划开销排查方法-- 使用 PREPARE 模拟观察通用计划PREPAREtest_plan(bigint)ASSELECT*FROMordersWHEREuser_id$1;-- 执行 6 次后触发通用计划EXECUTEtest_plan(12345);-- ... 重复 5 次-- 查看通用计划EXPLAINEXECUTEtest_plan(12345);-- 清理DEALLOCATEtest_plan;-- 会话级强制每次用 custom plan排查专用SETplan_cache_modeforce_custom_plan;三、系统化排查流程SQL 执行时间波动 │ ├─ 1. 先看 pg_stat_activity → 是否有等待事件、锁等待 │ └─ 有锁 → 找阻塞链 │ └─ IO 等待 → 检查磁盘压力 │ ├─ 2. EXPLAIN (ANALYZE, BUFFERS) → 执行计划是否改变 │ └─ 计划改变 → 检查统计信息考虑固定计划 │ └─ Buffers read 高 → 缓存冷考虑预热 │ ├─ 3. pg_stat_user_tables → 统计信息是否过期 │ └─ 死元组多、last_analyze 很旧 → VACUUM ANALYZE │ ├─ 4. prepared statement └─ 是 → 检查 plan_cache_mode考虑 force_custom_plan四、参数调优参考参数默认值优化建议影响shared_buffers128MB总内存 25%缓冲区命中率effective_cache_size4GB总内存 75%优化器索引决策random_page_cost4.0SSD 改为 1.1~1.5影响索引扫描选择work_mem4MB复杂查询 16~64MB排序/hash 性能plan_cache_modeauto参数偏斜场景用 force_custom_plan计划稳定性autovacuum_analyze_scale_factor0.2大表改为 0.01~0.05统计信息新鲜度log_min_duration_statement-1生产建议 500~1000ms慢查询日志六、总结优先级检查项工具高锁等待 / 阻塞链pg_stat_activity, pg_blocking_pids()高执行计划突变EXPLAIN (ANALYZE, BUFFERS)中统计信息过期pg_stat_user_tables VACUUM ANALYZE中缓冲区冷缓存pg_buffercache, pg_prewarm中参数嗅探plan_cache_mode低系统资源瓶颈pg_stat_bgwriter, OS 层监控建议遇到突发慢查询先看等待事件再看执行计划最后看资源。80% 的波动问题出在前两项。
