PHP小码农

Appearance

慢查询优化流程 ​

前三页给了你三件工具：慢查询日志（现场取证）、auto_explain（案发计划）、pg_stat_statements（累计档案）。但工具不会自动变成结果——很多团队的困境不是「没有数据」，而是拿到数据后不知道按什么顺序做什么：是先加索引还是先改 SQL？要不要调参数？改了怎么证明有效？没有一套固定的流程，慢查询治理就会退化成「凭感觉加索引」的经验主义。

本页给出一套完整的慢查询优化方法论：发现 → 复现 → 分析 → 假设 → 验证 → 回归监控六步闭环。每一步都标注「用什么工具、输出什么、常见的坑」，并把本站已学过的知识页（EXPLAIN、扫描方式、连接算法、SQL 改写、GIN 索引等）按步骤挂接到流程里。同时提供常见根因清单、SQL 检查清单与案例走查模板，让这套流程能直接写进团队手册。

使用本页的正确姿势：第一次通读建立整体地图；之后遇到实际案件时，直接跳到对应步骤的「标准动作」与「失败模式」小节按图索骥；团队采纳时，把「语法与用法」小节的六步动作与「案例走查模板」合并成你自己的 SOP 文档。流程页的价值在反复使用中被兑现。

前置知识

建议先阅读：pg_stat_statements 慢查询分析

基础概念 ​

六步闭环总览 ​

text
┌────────┐   ┌────────┐   ┌────────┐   ┌────────┐   ┌────────┐   ┌──────────┐
│ ① 发现  │──►│ ② 复现  │──►│ ③ 分析  │──►│ ④ 假设  │──►│ ⑤ 验证  │──►│ ⑥ 回归监控 │
│ Discover│   │Reproduce│   │ Analyze │   │Hypothesize│ │ Verify │   │  Monitor  │
└────────┘   └────────┘   └────────┘   └────────┘   └────────┘   └────┬─────┘
     ▲                                                                │
     └──────────────────────── 回到 ① 持续循环 ◄───────────────────────┘
步骤核心问题主要工具产出
① 发现哪条 SQL 值得优化？pg_stat_statements、慢查询日志、告警优先级排序的目标清单
② 复现它在受控环境怎么表现？EXPLAIN (ANALYZE, BUFFERS)可重复的执行计划
③ 分析时间花在哪个节点？计划解读、估算偏差、行数核查瓶颈节点定位
④ 假设哪种手段能改善？索引 / SQL 改写 / 参数 / 结构候选方案与预期收益
⑤ 验证改了确实变好了吗？对比计划、回看统计层前后对比证据
⑥ 回归监控以后还会劣化吗？监控基线与告警长效防护

六步中最常被跳过的是 ② 和 ⑥：跳过复现直接凭 SQL 文本加索引（赌错了白费功夫，赌对了也可能只是巧合），跳过回归监控则三个月后劣化复发时无人知晓。本页把这两步的分量特意加重。

与工具页的对应关系 ​

本站已学过的知识在流程中的挂接位置：

流程步骤对应知识页
② 复现EXPLAIN ANALYZE
③ 分析EXPLAIN 基础、EXPLAIN 输出解读、扫描方式、连接算法
④ 假设SQL 改写、GIN 索引总览
①⑥ 发现与监控前三页（日志 / auto_explain / pg_stat_statements）

一张图记住工具链：监控体系负责发现与回归（①⑥），优化知识负责分析与修复（②③④⑤）。本节四页解决前者，后续知识页支撑后者。

什么样的语句值得优化：ROI 判断 ​

不是所有慢语句都值得立案。用两个量做 ROI 估算：

text
优化收益 ≈ (当前耗时 - 预期耗时) × 未来调用频次 × 业务权重
优化成本 ≈ 分析工时 + 手段实施成本 + 回归验证成本

三种典型的高 ROI 形态：

形态统计指纹ROI
单条极慢 + 频次不低mean 巨大、calls 每日数百以上极高，通常一次修复终身受益
单条不慢 + 频次爆炸mean 正常、calls 环比暴涨高，且往往是业务事故前兆
占比集中Top N 占全库总耗时 > 50%高，集中度高意味着收益集中

反之，「一年跑一次的年报查询优化 10 分钟」这类案件，除非它是业务核心（如月结流程阻塞），否则应降级为技术债清单。流程的纪律之一是敢于对低 ROI 的目标说不——优化工作的价值按全库指标衡量，不按个案数量衡量。

语法与用法 ​

这里「语法与用法」的对象是流程本身：每一步的标准动作与命令。

第一步：发现——把「感觉慢」变成「指标清单」 ​

日常输入有三类：告警（监控曲线越限）、工单（业务投诉）、巡检（周期性 Top 榜）。标准动作是把它们全部归一到 pg_stat_statements 的 Top 榜，再按「总耗时占比 × 业务重要度」排优先级。

发现阶段的纪律：一次只立案一个目标。榜单上 20 条语句同时动手是流程失控的开始；按优先级串行处理，每条走完全流程再开下一案。三类输入各有其位：告警是中断信号（立即响应），工单是业务视角的补充（提供「哪条链路在痛」的上下文），巡检是主动的日常（防患于未然）。把三者混在一起处理，是发现环节混乱的根源。

sql
-- 巡检输入：按总耗时占比排序的目标清单
SELECT queryid,
       calls,
       round(total_exec_time::numeric / 1000, 1)  AS total_s,
       round(mean_exec_time::numeric, 1)          AS mean_ms,
       round(100 * total_exec_time::numeric
             / sum(total_exec_time::numeric) OVER (), 1) AS pct,
       left(regexp_replace(query, E'[\\n\\r\\t ]+', ' ', 'g'), 100) AS query_head
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
sql
-- 事件输入：从慢查询日志确认最近 24h 的异常语句
-- （jsonlog + jq 一行流，阈值 1s）
jq -r 'select(.message | startswith("duration:"))
       | (.message | split(" ")[1]) + " " + (.statement // "" | .[0:90])' \
  postgresql-2026-10-07_*.json | sort -rn | head -20

发现阶段的纪律：一次只立案一个目标。榜单上 20 条语句同时动手是流程失控的开始；按优先级串行处理，每条走完全流程再开下一案。

第二步：复现——EXPLAIN (ANALYZE, BUFFERS) ​

sql
-- 保守版复现：只看计划，不真实执行（写语句必须用它）
EXPLAIN (COSTS, VERBOSE)
SELECT ...;

-- 完整版复现：真实执行并计时（只读语句首选）
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT ...;

复现的三条安全纪律：

  1. 写语句（INSERT/UPDATE/DELETE）绝不能直接 ANALYZE——它会真实执行一遍。用事务包裹后回滚可以安全执行：BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;（注意序列等副作用不回滚）。
  2. 优先在与生产同版本的环境复现，参数（work_mem、random_page_cost 等）用 SET 对齐到生产值，否则计划不可比。
  3. 记下复现时间与数据量，验证与回归阶段要引用。

第三步：分析——定位瓶颈节点 ​

text
计划自顶向下读，找四类嫌疑节点：
① actual rows 与估算 rows 偏差 > 10 倍  → 统计问题或参数问题
② 顺序扫描大表（rows removed 巨大）      → 索引问题
③ 节点耗时占比集中（> 50%）             → 单点瓶颈
④ temp 块读写 / 溢出标记                 → work_mem 不足

分析的方法论细节（节点类型、估算偏差、连接算法的取舍）在《EXPLAIN 输出解读》《扫描方式》《连接算法》三页展开，本页只要求记住输出：一句话说清「时间花在哪个节点的哪个动作上」。这句话写不出来，就还不算分析完成，不许跳到第四步。

第四步：假设——候选手段清单 ​

按「成本从低到高」排序候选手段，并给出可证伪的预期：

text
假设模板：
「我认为 <语句X> 的瓶颈是 <节点Y>；
 若 <手段Z> 生效，预期 <指标M> 从 A 降到 B。」
text
示例（完整）：
我认为订单列表查询的瓶颈是对 orders(user_id, created_at) 的排序扫描；
若增加 (user_id, created_at DESC) 复合索引，预期 shared_blks_read 从 12 万块降到 2 千块以内，
mean_exec_time 从 1.8s 降到 50ms 以内。

没有「预期指标」的假设不是假设，是愿望。写不出预期说明第三步没分析透，退回上一步。

第五步：验证——前后对比 ​

sql
-- ① 计划对比：改动后重新 EXPLAIN，确认计划按预期变化
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- ② 统计层验收：等待一段时间后回看 pg_stat_statements
SELECT queryid,
       round(mean_exec_time::numeric, 1)  AS mean_ms,
       shared_blks_read,
       temp_blks_written
FROM pg_stat_statements
WHERE queryid = <目标模板的 queryid>;

注意验收窗口：改动刚上线时统计是新旧混合的，均值下降是渐进的；生产验证至少要覆盖一个完整业务周期（通常一天），并与第四步写的预期指标逐项对照。

第六步：回归监控——别让胜利溜走 ​

sql
-- 把目标模板纳入专项巡检：每周记录其统计快照
INSERT INTO slow_query_watch (checked_at, queryid, mean_exec_time, calls, shared_blks_read)
SELECT now(), queryid, mean_exec_time, calls, shared_blks_read
FROM pg_stat_statements
WHERE queryid IN (<已修复模板清单>);

配套动作：在监控平台为该模板的 mean_exec_time 设阈值告警；把修复方案与「预期劣化触发条件」写进文档（例如「orders 表行数超过 5000 万或 user_id 选择性变化时需复核该索引」）。

六步在告警值班场景的压缩版 ​

完整流程适合立案；值班时面对「业务在喊」的场景，可以先用压缩版止血，事后补全流程：

text
值班压缩版（15 分钟内）：
1. pg_stat_activity 看当下（有没有跑飞的长查询/长事务 → 能 kill 先止血）
2. pg_locks / pg_blocking_pids 看有没有锁链 → 有则处理阻塞者
3. 慢查询日志看最近 30 分钟的新面孔 → 指向明确的先冻结相关变更
4. 无法定位时升级：保留现场（活动会话快照、日志窗口）→ 转立案流程

压缩版与完整版的关系：前者是「急诊科」，目标是止血与保住证据；后者是「专科门诊」，目标是根治与回归防护。两版并存才完整——只学完整版会在值班时手忙脚乱，只用压缩版会永远在救火。

值班场景还有一个铁律：压缩版允许「先止损后归因」，但不允许「止损后不归因」。kill 长查询、重启连接池都只是恢复服务，立案与归档是值班的收尾义务——否则同一个雷会在下一个夜班再炸一次。

详细说明 ​

流程图逐节点讲透 ​

① 发现的输入优先级：告警 > 工单 > 巡检。告警说明业务已经受损，工单说明感知已经扩散，巡检是日常增量。发现阶段最容易犯的错误是「只看平均」——均值掩盖尾部，P99 突刺要靠日志与监控分位数发现（前者给现场，后者给频率）。

② 复现的本质是获得一份受控且可重复的计划。生产环境直接抓 auto_explain 的案发计划固然真实，但它往往缺少 BUFFERS 细节与可调参的对照环境；所以标准姿势是「案发计划定方向、受控复现做实验」。

③ 分析读计划的总原则：自顶向下、先看行数再看耗时。估算偏差（estimated vs actual rows）是 PostgreSQL 排障的第一信号——偏差大意味着优化器基于错误前提做决策，此时先修统计（ANALYZE）比任何索引都优先。

④ 假设要按成本排序探索，而不是一上来就上大杀器：

text
成本阶梯（从低到高）：
SQL 改写 → 新增/调整索引 → 统计与参数调整 → 结构调整（分区/物化视图）→ 硬件扩容

⑤ 验证的对照物有两层：计划（立即见效的证据）与统计层（业务实效的证据）。两层都绿才算通过；只看计划不看统计，容易漏掉「计划变好但频率暴涨导致总耗时反而上升」的陷阱（Jevons 悖论的数据库版）。

⑥ 回归监控是流程的「存档点」。每个已立案优化的语句都要有归属：谁负责、何时复核、劣化触发条件是什么。没有这一步，优化成果会随数据增长悄悄蒸发。

常见根因清单 ​

分析阶段卡壳时，按这张清单逐一排查（按生产出现频率大致排序）：

#根因典型指纹快速验证
1统计信息过期估算行数偏差巨大；last_analyze 很旧pg_stat_user_tables 的 n_mod_since_analyze
2缺索引或索引失效大表 Seq Scan、Rows Removed by Filter 巨大计划节点 + \d 看索引定义
3随机参数计划劣化max_exec_time / min_exec_time 巨大pg_stat_statements 的 spread
4work_mem 不足temp_blks_written 高、Sort/Hash 节点溢出计划 temp 块统计
5锁等待duration 远大于执行计划耗时pg_locks / pg_blocking_pids()
6表/索引膨胀行数没涨查询变慢；死元组多n_dead_tup、膨胀巡检 SQL
7连接池排队DB 侧单条很快、业务侧慢业务耗时 - DB 耗时的差值
8硬件/存储瓶颈全库性变慢、无单一语句突出系统指标（IO util、CPU steal）
9写放大 / WAL 堆积wal_bytes 巨大、复制延迟WAL 统计 + 复制视图
10查询本身逻辑冗余重复扫描同一表、N+1计划同一关系出现多次

清单的意义不是「背下来」，而是给假设阶段提供搜索空间——每一条都对应第四步的一种候选假设。

两个隐形根因：连接池排队与硬件瓶颈 ​

清单里最容易漏掉的是第 7、8 条，因为它们的证据都在数据库之外：

连接池排队的形态是「数据库侧单条语句飞快，业务侧 P99 很差」。验证方法是把两端耗时做差：

text
业务接口耗时 800ms - 数据库执行耗时 15ms = 785ms 消耗在链路其他环节
（连接池排队 / 网络往返 / 应用内处理 / 序列化）

差值巨大时，数据库侧的一切优化都是无效功——正确的动作是检查连接池的 max_clients/pool_size 配比、PgBouncer 的池化模式、以及连接的获取/归还日志。这类「假慢查询」在监控曲线上的指纹：DB 侧 QPS 与耗时平稳，但连接池等待队列增长。

硬件瓶颈的形态是「全库性、无差别变慢」。三条快速证据：CPU 高位但 pg_stat_statements 榜单无显著变化（时间花在系统层）；IO util 持续 100%（iostat 的 %util 与 await 拉长）；虚拟化环境的 CPU steal 为非零。这类问题的处置在系统层与容量层，语句级流程只能「排除嫌疑」——但排除嫌疑本身也是价值，它防止团队在数据库里空转。

索引假设的专项检查 ​

索引是最常用的手段，也最容易做错。提出索引假设前过一遍检查清单：

text
□ 索引列顺序匹配查询条件与排序？（等值列在前、范围/排序列在后）
□ 表达式与类型匹配？（函数包裹列、隐式类型转换会让 B-tree 失效）
□ 是否被更便宜的手段替代？（改写 SQL 消除排序/过滤，比加索引更治本）
□ 写放大预算？（每加一个索引，该表所有 INSERT/UPDATE 多一次索引维护）
□ 是否已有语义等价索引可复用？（(a) 与 (a,b) 的关系）
□ 特殊场景选对类型？（全文/数组/JSONB → GIN，见 GIN 索引总览页）

第七项值得强调：JSONB 与数组字段的查询瓶颈，B-tree 无能为力，正确工具是 GIN——本站 GIN 索引总览 一页有完整的选型依据。

改写假设的专项检查 ​

SQL 改写常被低估，但它零成本、零风险（相对 DDL），应当先于索引尝试。常见改写方向（详细手法见《SQL 改写》一页）：

text
□ SELECT * 改按需取列（减少 I/O 与网络，可能触发覆盖索引扫描）
□ 分页用 keyset（WHERE id > $last）替代大 OFFSET
□ OR / IN 大列表改 UNION 或临时表 join
□ 相关子查询改 JOIN（或反向，视选择性而定）
□ 递归/窗口函数替代应用侧循环（消除 N+1 的根本手段）
□ 大事务拆批（减少锁持有时间与 WAL 峰值）

改写的验证同样走第五步：计划对比 + 统计层验收，特别是改写前后的 rows 与 calls 语义必须一致——性能优化不允许改变结果语义，这也是回归验证要带上功能测试的原因。

参数假设的边界 ​

调参数（work_mem、random_page_cost、effective_cache_size、max_parallel_workers_per_gather 等）能救急，但要清楚它的边界：

  • 参数改变的是成本模型的判断，不改变数据访问的物理事实。缺索引的表调 random_page_cost 只能让优化器「更愿意试索引」，不能让顺序扫描凭空变快。
  • 会话级 / 角色级设置优于全局设置（影响面可控），ALTER ROLE ... SET + EXPLAIN (SETTINGS) 可对调参做精确的 A/B。
  • 每次只动一个参数，动完即验——多参数同调的「改善」无法归因，等于放弃回归分析。

结构与分区假设的专项检查 ​

当改写与索引都到顶时，剩下的手段是结构调整（分区、物化视图、冗余表）。它们的共同特点是引入新的长期维护义务，假设阶段必须评估：

text
□ 分区：查询能否裁剪到少数分区？（分区键必须出现在绝大多数查询条件中）
□ 分区：现有主键/唯一约束是否兼容分区键？（全表唯一约束在分区下受严格限制）
□ 物化视图：可接受的刷新延迟是多少？增量刷新（REFRESH MATERIALIZED VIEW CONCURRENTLY）成本？
□ 冗余表：写侧同步逻辑由谁维护？不一致的止损方案？
□ 每种结构都要回答：一年后的维护者能否看懂并安全操作它？

结构手段的正确打开方式是「数据规模到了硬门槛才上」，而不是「先上结构再优化查询」——前者是工程判断，后者是把复杂度当玩具。

每步的常见失败模式 ​

流程六步各自有自己的典型死法，对照自检：

步骤失败模式症状解药
① 发现只盯单条语句修完 A 发现大头是 B 的调用量先看全局占比再立案
② 复现环境失真预发计划与生产完全不同参数对齐 + 数据量对齐
③ 分析只看耗时不算行数把估算错误误判为索引问题先核估算偏差
④ 假设凭经验先射箭「加个索引试试」循环预期格式强制
⑤ 验证语义漂移性能好了结果错了功能测试进验收
⑥ 回归案例无归档半年后同类问题从零开始立案单制度

②的环境失真值得单独说明：random_page_cost、effective_cache_size、work_mem、max_parallel_workers_per_gather 任何一项不同都可能让计划分叉，所以 EXPLAIN (SETTINGS) 的输出要逐项与生产核对——这正是它在验证清单里的价值。

与 EXPLAIN 知识的衔接 ​

复现与分析两步的手感，完全取决于对 EXPLAIN 输出的熟练度。为方便回查，把本站相关页面的分工再强调一次：看不懂某行输出去《EXPLAIN 基础》与《EXPLAIN 输出解读》；分不清节点类型为什么被选中去《扫描方式》与《连接算法》；想改写 SQL 而不是加索引去《SQL 改写》。流程给出的是「走到哪一步该翻哪本书」，书的内页知识不在此重复。

案例走查模板 ​

团队手册里的标准模板，任何一个慢查询优化案都按此归档：

text
═══════════════════════════════════════════════
慢查询优化立案单
═══════════════════════════════════════════════
编号：SQO-2026-101
【发现】来源：巡检 Top 榜（2026-10-07）    优先级：P1
  证据：total_exec_time 占比 38%，calls=523/day，mean=35.2s
【复现】环境：预发（2026-10-07，数据量对齐生产 95%）
  计划要点：orders 顺序扫描，Rows Removed=980万，估算偏差 40 倍
【分析】瓶颈：缺少 (user_id, created_at) 匹配的索引，过滤在扫描后
【假设】新增复合索引 idx_orders_user_created 后，
  预期 mean_exec_time 35.2s → <500ms，shared_blks_read 下降 95%+
【验证】上线：2026-10-09（CREATE INDEX CONCURRENTLY，耗时 22min）
  计划：Index Scan 确认          统计：mean 降至 210ms（10-10 复核）
  达成预期：是
【回归】巡检项：SQO-WATCH-007；复核条件：表行数 > 5000 万时重估
归档：计划截图、前后统计、DDL 变更单号 CHG-3391
═══════════════════════════════════════════════

模板的价值不在格式而在强制性：每一步都有证据槽位，跳步肉眼可见。

时间预算：一次完整的优化案要多久 ​

给流程各步骤一个参考时长（以中等复杂度案件为例），用于判断自己是否在某一步上钻了牛角尖：

text
① 发现与立案          0.5h（数据现成时）
② 复现                0.5h～2h（数据对齐是主要成本）
③ 分析                0.5h～2h（简单索引缺失很快，估算纠偏较慢）
④ 假设                0.5h
⑤ 验证                0.5h 即时 + 1 个业务周期的统计复核（等待为主）
⑥ 回归归档            0.5h
合计：一线投入约 3～7 小时，日历周期 3～5 天（受验证等待与变更窗口影响）

两个节奏要点：验证的「等待业务周期」不应阻塞下一个案件的立案（并行流水线，但上线动作串行）；超过预算两倍仍无头绪的案件，果断升级会诊或降级为长期课题——流程是加速器，不是牢笼。

多语句并存的优先级矩阵 ​

当 Top 榜上有多条目标时，用两个维度排矩阵：

text
                 优化成本低
                ┌────────────────────────┐
     业务重要度高│  ② 先做（性价比最高）    │
                ├────────────────────────┤
     业务重要度低│  ④ 攒到技术债清单        │
                └────────────────────────┘
                 优化成本高 → ③ 做长期方案

高价值低成本（②象限）立刻做；高价值高成本（③）立项排期；低价值低成本（④）顺手做；低价值高成本明确说不做。流程纪律的第二条：每个象限的动作都要落到看板，而不是只做②象限、遗忘③④。

什么时候该停下来升级 ​

流程不是万能的，三类信号出现时应当升级处置而不是继续单兵作战：

  • 反常信号：系统指标正常、语句统计正常、但业务持续变慢——问题可能在应用层、网络层或中间件，需要跨团队会诊。
  • 反复复发：同一语句三个月内立案两次以上——根因判断错了或者触发条件没找对，需要重新走一遍而不是第三次复制上次的「修复」。
  • 超出能力边界：需要修改内核行为（自定义操作符类、hook）或重构数据模型时，这是架构议题，不是优化议题。

识别「停」与「继续」同样重要：优化流程能解决的是「优化器与数据形态不匹配」这一大类问题，超出这个范围的问题要交给更合适的流程。

实战示例 ​

示例一：完整走查——报表查询从 35 秒到 200ms ​

sql
-- ① 发现：巡检 Top 1，占总耗时 38%
SELECT queryid, calls, mean_exec_time FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 1;
text
      queryid       | calls | mean_exec_time
--------------------+-------+----------------
 382749113627984598 |   523 |      35186.000
sql
-- ②③ 复现与分析
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.*, u.region
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.user_id = 4212
  AND o.created_at >= now() - interval '90 days'
ORDER BY o.created_at DESC;
text
Sort (actual rows=41322.00 loops=1)
  Sort Key: o.created_at DESC
  Sort Method: external merge  Disk: 128kB
  ->  Seq Scan on orders o (actual rows=41322.00 loops=1)
        Filter: ((user_id = 4212) AND (created_at >= ...))
        Rows Removed by Filter: 9876544
  ...
sql
-- ④ 假设：复合索引消除扫描与排序
CREATE INDEX CONCURRENTLY idx_orders_user_created
  ON orders (user_id, created_at DESC);

-- ⑤ 验证
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;   -- Index Scan，buffers 大幅下降
text
Index Scan using idx_orders_user_created on orders o
  (actual rows=41322.00 loops=1)
  Index Cond: ((user_id = 4212) AND (created_at >= ...))
sql
-- ⑥ 回归：纳入巡检与告警
INSERT INTO slow_query_watch (queryid, note)
VALUES (382749113627984598, 'SQO-2026-101 已修复，周复核');

从发现到归档 3 天，全部六步有据可查。

示例二：完整走查——N+1 从应用侧修复 ​

sql
-- ① 发现：avg_rows=1、calls=48 万的点查高居调用榜
SELECT queryid, calls, rows/calls AS avg_rows, mean_exec_time
FROM pg_stat_statements ORDER BY calls DESC LIMIT 3;
text
     queryid       | calls  | avg_rows | mean_exec_time
-------------------+--------+----------+----------------
 712398045629381120| 482319 |    1     |           2.31
sql
-- ②③④：语句本身无法再优化（已是主键点查），
-- 根因在调用方式：应用在循环里逐单查商品。
-- 假设：改为批量查询 id = ANY($1)，单次返回 50 行、频率降 50 倍。
sql
-- ⑤ 验证（应用上线后）
SELECT queryid, calls, rows/calls AS avg_rows, mean_exec_time
FROM pg_stat_statements
WHERE queryid = 712398045629381120;
text
     queryid       | calls | avg_rows | mean_exec_time
-------------------+-------+----------+----------------
 712398045629381120|  9634 |      50  |           3.10

calls 降 50 倍、单次语义变为批量——总耗时从日均 1114s 降到约 30s。这个案例的意义：有些慢查询的答案不在数据库里，流程的「假设」步骤应当允许跨出 SQL 边界。

示例三：完整走查——统计过期导致的集体劣化 ​

sql
-- ① 发现：某天上午大批语句 mean 环比上涨 5~20 倍，无发布记录
-- ②③ 分析：抽查计划发现估算行数全部偏小 3 个数量级
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'order_items', 'payments');
text
   relname   |   last_analyze    | n_mod_since_analyze
-------------+-------------------+---------------------
 orders      | 2026-09-01 03:00  |            42123987
 order_items | 2026-09-01 03:00  |            88422110
 payments    | 2026-08-30 03:00  |            12045118

autovacuum 的 analyze 因长事务阻塞欠账近一个月。假设：ANALYZE 上述表后计划回归。验证：ANALYZE VERBOSE orders; 后立即 EXPLAIN 对比，估算偏差消失、全部走回索引扫描；统计层次日复核 mean 回落。回归动作：排查阻塞 autovacuum 的长事务来源，并把「估算偏差抽查」加入每日巡检。这个案例演示了根因清单第 1 条的标准处置，也说明多语句同时劣化时优先怀疑「环境变化」而非逐条分析。

示例四：锁等待伪装成的慢查询 ​

sql
-- ① 发现：订单更新语句 mean 从 5ms 涨到 2.8s，但仅此一条语句异常
SELECT queryid, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements WHERE queryid = 6639910345184701970;
sql
-- ②③ 复现：单独执行只要 4ms！计划毫无异常。
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'shipped' WHERE id = 99123;
-- Update ... (actual ... time=4.1..4.2 rows=1 loops=1)

「计划 4ms、统计层 mean 2.8s」的矛盾指向根因清单第 5 条：锁等待（duration 包含等待，计划不含）。验证：

sql
-- 确认阻塞链：谁在持有该行锁？
SELECT blocked.pid      AS blocked_pid,
       blocked.query    AS blocked_query,
       blocking.pid     AS blocking_pid,
       blocking.query   AS blocking_query,
       blocking.state   AS blocking_state,
       now() - blocking.xact_start AS blocking_xact_age
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';
text
 blocked_pid |      blocked_query       | blocking_pid |     blocking_query      | blocking_state | blocking_xact_age
-------------+--------------------------+--------------+-------------------------+----------------+-------------------
       43211 | UPDATE orders SET ...    |        42897 | SELECT * FROM orders ...| idle in transaction | 00:41:22

真凶是一个 idle in transaction 41 分钟的会话（应用 bug：开事务后忘记提交）。修复在应用层：事务边界收敛 + 连接池侧设置 idle_in_transaction_session_timeout。本案的启示写进流程：duration 与计划耗时的差值本身就是一条诊断线索，差值大必查锁与等待，别在索引上浪费时间。

注意事项 ​

不要跳过复现直接改。 看着 SQL 文本加索引是流程里最昂贵的赌博：索引创建在高写入大表上并不免费（写放大 + CONCURRENTLY 的长时间维护），赌错意味着回滚 DDL 与资源双输。先拿到计划，再谈手段。

生产复现写语句必须包事务回滚。 BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK; 是安全底线，同时注意序列与外部副作用不参与回滚。更保守的选择是用 auto_explain 的案发计划替代手工 ANALYZE。

优化不允许改变结果语义。 改写后的 SQL 必须通过功能测试与结果比对（行数、聚合值抽查）。历史上「为了快去掉一个 JOIN」造成数据错误的案例，比慢查询本身的事故更难收拾。

警惕「计划变好但系统变慢」的反直觉结局。 单条语句优化后调用频率暴涨（缓存失效更频繁、业务放量、触发重试风暴），总耗时可能不降反升。所以验收必须看统计层的 total_exec_time 而非只看 mean_exec_time。

索引不是越多越好。 每个索引都是写放大与空间成本；为低频语句建的索引如果拖慢了高频写入，可能是负收益。新建索引前评估该表写入 QPS，上线后观察写入侧统计。

锁问题伪装成慢查询。 duration 包含等待时间，根因清单第 5 条（锁等待）的处置路径完全不同（找阻塞者、调整事务粒度），加索引无用。拿到「计划很快但 duration 很慢」的矛盾证据时，第一嫌疑就是锁。

别把参数调整当成长期方案。 work_mem 全局调大十倍救了一次报表，却可能让并发场景内存溢出。参数假设必须评估影响面（全局/库/角色/会话），并写明回滚条件。

一次只动一个变量。 同时加索引 + 改写 SQL + 调参数，即使变快了也不知道谁的功劳、哪个副作用埋了雷。多变量同调是回归分析的天敌。

流程要与变更管理打通。 CREATE INDEX CONCURRENTLY 是 DDL，要走变更窗口与审批；立案单里的「上线」槽位对应的就是变更单号。游离于变更流程之外的优化无法审计也无法回滚。

文档与案例库是最容易被忽略的产出。 每个案件的计划截图、根因、预期触发条件都要归档——三个月后同类问题复发时，翻案例的十分钟胜过重新分析的十小时。

并发优化之间可能互相抵消。 两个「都已验证有效」的优化同时上线，效果可能不如单上其一：两个新索引会互相干扰成本估算、并行度参数与索引共同影响计划选择。重大优化要错峰上线并分别复核。

面向未来的假设要包含数据增长。 「今天 500 万行够快」不等于「明年 5000 万行还够快」；预期格式里补一行「数据量翻 10 倍后该方案的预期表现」，能提前识别大量返工。

最佳实践 ​

  1. 把六步流程写成团队 SOP 并强制立案单。 好流程的价值在一致性：任何人接手任何一个慢查询案件，走的都是同一套动作、产出同一套证据。
  2. 发现阶段固定输出「Top 榜 + 占比」巡检报告。 每周一次，用 pg_stat_statements 的总耗时占比口径，让优化优先级始终有数据锚点。
  3. 复现一律 EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 起步。 SETTINGS 自带参数上下文，BUFFERS 是 I/O 归因的最低要求；写语句用事务回滚或改用 auto_explain 案发计划。
  4. 先修统计再看计划。 估算偏差超过一个数量级时，ANALYZE 是第一动作——它便宜（秒级）且可能直接治愈。pg_stat_user_tables 的 n_mod_since_analyze 与 last_analyze 是判断依据。
  5. 假设必须写成「可证伪的预期」格式。 瓶颈节点 + 手段 + 预期指标三要素缺一不可；写不出预期的假设退回分析步骤。
  6. 手段按成本阶梯排序尝试：改写 → 索引 → 参数 → 结构 → 硬件。 每一级都有对应的专题页知识（改写、GIN 选型、扫描方式、连接算法），流程负责串接，知识页负责纵深。
  7. 验证分两层：计划对比（即时）+ 统计层验收（覆盖完整业务周期）。 验收指标回看 mean_exec_time、total_exec_time、shared_blks_read、temp_blks_written 是否达成预期。
  8. 每个已修复语句建立回归档案与复核触发条件。 条件示例：表行数阈值、数据分布变化、相关 DDL。触发即重开案件，而不是等业务投诉。
  9. 多语句同时劣化优先排查环境因素。 统计过期、参数变更、版本升级、硬件水位——先找共同原因，再逐条立案。共同原因排查一遍往往能救一批语句。
  10. 把 N+1 与失控查询的巡检 SQL 固化。 rows/calls、calls 环比、temp_blks_written 三条巡检每周跑一次，输出到看板（本节第三页的示例可直接复用）。
  11. 优化成果要「回到监控」。 修复动作上线的同时，把该模板纳入监控告警清单（阈值 = 修复后均值的 3~5 倍），让回归在业务感知之前被发现。
  12. 用立案单沉淀团队案例库。 本页模板落库归档，按根因类型索引；新人培养读 20 个历史案件，胜过零散的口传心授。

下一节

继续学习：pgAdmin 管理与监控

参考链接 ​