读懂 PostgreSQL 执行计划：从 EXPLAIN 输出定位 Spring Boot 慢 SQL 的代价估算偏差

读懂 PostgreSQL 执行计划：从 EXPLAIN 输出定位 Spring Boot 慢 SQL 的代价估算偏差

1. 先看一个真实场景：接口为什么突然慢了 20 倍

假设你维护一个电商后台的订单查询接口，技术栈是 Spring Boot + MyBatis + PostgreSQL。上线半年一直稳定，某个周一早上，运营反馈“按用户查订单”的页面从 300 毫秒变成了 6 秒。你没有改过这段 SQL，也没有发布新版本，数据库 CPU 却从 15% 涨到了 80%。

你打开日志，看到这条语句只是很普通的一个关联查询：按用户 ID 查该用户最近的订单，再带上订单明细的商品名称。看起来条件很明确、也建了索引，为什么会慢？更让人困惑的是，把同样的 SQL 拿到测试库执行，几十毫秒就返回了。

这时候大多数人会做两件事之一：加索引，或者加缓存。但这两步都可能治错病。真正需要先回答的问题是：PostgreSQL 到底打算怎么执行这条 SQL？它预估要处理多少行？预估和实际差了多少？

数据库不会“随机”变慢。绝大多数突发的计划劣化，都源自同一个根因：它基于统计信息做出的行数估算错了，于是选了一个在真实数据量下极差的执行方式。本文就沿着这个根因，把 EXPLAIN 输出读懂、用透。

2. 一句话模型与全局框架

先记住一个最小模型：PostgreSQL 的查询优化器是一个“按估算成本挑选方案”的决策器。它先读统计信息估算每一步要处理多少行，再按行数乘以单位代价算出总成本，最后从若干候选计划里挑成本最低的那个去执行。

也就是说，优化器并不真的执行 SQL 来试哪个快，它是在“纸上算账”。账算错了，选择就错了。

把整体拆成四部分，它们按下面的顺序串起来：

SQL 文本 | v [1] 解析与重写 --> 语法树 / 查询树 | v [2] 规划器 Planner 读取 pg_statistic 统计信息 估算每个节点的 rows 用 cost 模型累加代价 生成若干候选路径（顺序扫描 / 索引扫描 / 各类连接） | v [3] 选出成本最低的计划 | v [4] 执行器 Executor 按计划树逐节点拉取数据 | v 结果返回 Spring Boot / JDBC

这四部分里，第 2 步是本文的主战场。因为 EXPLAIN 展示的就是第 2 步的产物，而 EXPLAIN ANALYZE 展示的是第 2 步的估算与第 4 步的实际同时对照。

一次请求完整走一遍是这样：JDBC 把 SQL 发给 PostgreSQL，后端进程解析、重写，规划器产出计划树，执行器从计划树的根节点开始，自上而下“要数据”，最底层节点从堆表或索引里取行，逐层向上传递、连接、过滤、排序，最终把结果集流式返回给 JDBC。Spring Boot 侧看到的耗时，就是这条链路的总和。

3. EXPLAIN 与 EXPLAIN ANALYZE：一个看想法，一个看事实

很多人第一次用 EXPLAIN，只会看到一堆缩进文本，不知道从哪读起。先区分两个命令：

• EXPLAIN：只展示规划器“打算怎么做”，不执行 SQL。它给出的是估算值。
• EXPLAIN ANALYZE：真正执行 SQL，并给出每一步的实际耗时、实际行数、实际循环次数，同时保留估算值。

定位慢 SQL 时，优先使用 EXPLAIN ANALYZE，因为只有它能把“估算”和“实际”并排摆出来。差异最大的地方，往往就是问题所在。

-- 输入：一个按用户查订单的关联查询EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECTo.id,o.created_at,oi.product_nameFROMorders oJOINorder_items oiONoi.order_id=o.idWHEREo.user_id=10086ORDERBYo.created_atDESCLIMIT20;

输出大致长这样（数值为示例）：

Limit (cost=1.12..45.30 rows=20 width=52) (actual time=0.05..0.09 rows=20 loops=1) -> Nested Loop (cost=1.12..22130.44 rows=20 width=52) (actual time=0.04..0.09 rows=20 loops=1) -> Index Scan using idx_orders_user on orders o (cost=0.43..1200.00 rows=1 width=24) (actual time=0.02..0.03 rows=20 loops=1) Index Cond: (user_id = 10086) -> Index Scan using idx_items_order on order_items oi (cost=0.43..1040.00 rows=1 width=36) (actual time=0.01..0.01 rows=1 loops=20) Index Cond: (order_id = o.id)

关键读法：每个节点括号里前一组是估算（cost、rows、width），后一组是实际（actual time、rows、loops）。发现“估算 rows”和“实际 rows”差距很大时，就该怀疑统计信息或代价模型。

4. 把 EXPLAIN 输出拆成几个可操作的数字

理解 EXPLAIN，本质上就是理解几个数字的来源：

这里最容易误解的是 cost。它不是毫秒，而是规划器自定义的相对单位。一次顺序页读取的成本由seq_page_cost控制（默认 1.0），一次随机页读取由random_page_cost控制（默认 4.0），一次 CPU 处理有cpu_tuple_cost等参数。这些参数是可调的，也是“估算偏差”的一大来源，尤其在 SSD 上默认的 random_page_cost 往往偏高。

另一个常见误解是：Limit节点的 cost 很小，不代表整条语句就快。因为 Limit 只负责截断，它下面的节点如果代价巨大，真实耗时依然会爆。

5. 行数估算错误：慢 SQL 最常见的根因

现在回到开头那个订单查询。为什么估算会错？

PostgreSQL 不是逐行扫描才知道有多少行，它维护的是统计信息：每个表有多少行、每列有多少不同值、最常见的值各占多少比例、以及直方图分布。这些信息由 autovacuum 触发或手动ANALYZE更新，存在系统表里。规划器用它们估算“这个条件会命中多少行”。

问题在于估算是有前提的。三类典型偏差来源：

第一是数据倾斜。比如订单表里 90% 的订单都属于少数几个大客户。统计信息里的“最常见值”能覆盖一部分，但如果某用户的订单量远超平均数，而它没被采样进 MCV 列表，估算就会严重偏低。

第二是多列相关性。当 SQL 同时过滤city = '上海'和district = '浦东'时，规划器默认两个条件独立，把选择率相乘。但现实中这两个值高度相关，真实命中行数远小于相乘结果。

第三是统计信息过期。批量导入十万行数据后，如果没有及时 ANALYZE，规划器还按老数据估算，选择计划自然错。

一个直观的数字例子：真实命中 50000 行，估算只有 20 行。优化器以为只要在循环里跑 20 次索引查找，于是选了嵌套循环；执行时却跑了 50000 次，每次都是一次随机 I/O，耗时从毫秒级变成秒级。估算误差不是小问题，它会被嵌套循环按倍数放大。

6. 嵌套循环：估算偏差的放大器

要理解为什么偏差如此致命，必须先理解三种连接的适用条件。

嵌套循环（Nested Loop）的逻辑是：对外表的每一行，去内表查一次匹配。它适合“外表很小、内表有高效索引”的场景。只要外表行数估算成 20，规划器就会觉得“循环 20 次很便宜”。

如果行数估算错了，外表实际有 50000 行，循环就变成 50000 次。即使每次索引查找只要 0.1 毫秒，也要 5 秒。这就是执行计划劣化的典型病理。

对比一下三种连接方式：

用流程表示一次嵌套循环的执行顺序：

计划树（简化） Nested Loop |-- 外表 Index Scan (orders, user_id=10086) rows=20 实际=20 |-- 内表 Index Scan (order_items, order_id=...) loops=20 执行顺序： 取外表第 1 行 -> 用 order_id 查内表 -> 得到匹配行 取外表第 2 行 -> 用 order_id 查内表 -> 得到匹配行 ... 取外表第 N 行 -> 用 order_id 查内表 -> 得到匹配行 外层行数 N 直接决定内层被执行多少次

结论：嵌套循环的成本几乎与外层估算行数成正比。估算翻倍，代价翻倍；估算偏小 100 倍，实际就可能慢 100 倍。这也是为什么“行数估算错误”排在慢 SQL 根因的第一位。

7. 完整示例一：复现一次估算偏差

目标：在一个干净的表上，亲眼看到估算与实际行数的差距，以及它如何改变执行计划选择。

前置环境：PostgreSQL 14+，本地可执行 psql。

-- 简化代码：建表并构造倾斜数据DROPTABLEIFEXISTSorders_demo;CREATETABLEorders_demo(id bigserialPRIMARYKEY,user_idbigintNOTNULL,amountnumeric(10,2)NOTNULL,created_at timestamptzNOTNULLDEFAULTnow());-- 插入 20 万行：99% 归属少数大客户，制造数据倾斜INSERTINTOorders_demo(user_id,amount)SELECTCASEWHENg%100=0THEN10086ELSE(g%5000)+1END,(random()*1000)::numeric(10,2)FROMgenerate_series(1,200000)ASg;CREATEINDEXidx_orders_demo_userONorders_demo(user_id);-- 关键：先不 ANALYZE，让统计信息为空EXPLAINANALYZESELECT*FROMorders_demoWHEREuser_id=10086;

关键步骤与预期结果：未 ANALYZE 时，规划器对表几乎一无所知，rows估算可能是个很小的默认值，而实际返回约 2000 行。执行计划也许仍选索引扫描，但如果这个查询改成关联上层，偏差就会被放大。

接着执行 ANALYZE，再对比：

ANALYZEorders_demo;EXPLAINANALYZESELECT*FROMorders_demoWHEREuser_id=10086;

此时估算 rows 会接近真实值。观察actual rows与rows是否收敛。

适用场景：任何怀疑统计信息过期的表。容易改错的地方：忘了ANALYZE是采样而非全量扫描，极端倾斜下仍可能估歪；也不要指望 ANALYZE 能自动发现多列相关性。

8. 统计信息目标：给关键列“加采样”

如果 ANALYZE 之后估算依然不准，下一步就是调统计信息目标。它控制的是“每列收集多少份样本、直方图分几个桶”，默认值是 100。对普通列够用，但对付高度倾斜或取值极多的列，往往不够。

可以按列单独设置：

-- 提升关键列的采样精度ALTERTABLEorders_demoALTERCOLUMNuser_idSETSTATISTICS1000;-- 针对多列相关性，建立扩展统计信息CREATESTATISTICSstat_orders_user_amount(dependencies)ONuser_id,amountFROMorders_demo;-- 重新收集ANALYZEorders_demo;

注意边界：统计信息目标不是越大越好。设成 10000 会让 ANALYZE 变慢、pg_statistic膨胀，收益递减。通常 500 到 1000 已经能覆盖大多数倾斜场景。

9. 完整示例二：Spring Boot 侧的慢 SQL 观测

目标：在 Spring Boot 应用里打开执行计划观测，把数据库的估算偏差和应用日志关联起来。

前置环境：Spring Boot 3.x + PostgreSQL 驱动 + MyBatis，本地数据库可达。

第一步，确认数据源配置。PostgreSQL JDBC 支持通过选项把执行计划日志带到服务端：

# application.ymlspring:datasource:url:jdbc:postgresql://localhost:5432/shop?options=-c%20log_min_duration_statement%3D200username:apppassword:secrethikari:maximum-pool-size:10connection-timeout:3000

这行配置的含义是：让执行超过 200 毫秒的语句写入 PostgreSQL 日志。它不是打印计划，而是先帮你“抓住慢语句”，随后用 EXPLAIN ANALYZE 逐条分析。

第二步，写一个可运行的最小验证类，直接用 JDBC 执行 EXPLAIN ANALYZE：

importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.Statement;publicclassExplainDemo{publicstaticvoidmain(String[]args)throwsException{Stringurl="jdbc:postgresql://localhost:5432/shop";Stringsql="EXPLAIN (ANALYZE, BUFFERS) "+"SELECT * FROM orders_demo WHERE user_id = 10086";try(Connectionconn=DriverManager.getConnection(url,"app","secret");Statementstmt=conn.createStatement();ResultSetrs=stmt.executeQuery(sql)){while(rs.next()){System.out.println(rs.getString(1));}}}}

关键步骤：运行后控制台会逐行输出计划树，包括实际的 rows 和 loops。预期结果是你能在应用进程里直接拿到计划，不必登数据库。

适用场景：想快速验证某条 MyBatis 语句的真实计划。容易改错的地方：EXPLAIN ANALYZE会真正执行语句，对写操作要在事务里回滚，否则会产生副作用；生产环境避免对写语句使用它。

10. 完整示例三：从偏差到修复的完整闭环

目标：模拟一个“估算偏小导致嵌套循环灾难”的场景，并通过调整统计信息和 SQL 修复它。

前置环境：沿用示例一的 orders_demo，再建一张明细表。

DROPTABLEIFEXISTSitems_demo;CREATETABLEitems_demo(id bigserialPRIMARYKEY,order_idbigintNOTNULL,pricenumeric(10,2)NOTNULL);-- 每个订单约 3 条明细INSERTINTOitems_demo(order_id,price)SELECTo.id,(random()*100)::numeric(10,2)FROMorders_demo oCROSSJOINgenerate_series(1,3);CREATEINDEXidx_items_demo_orderONitems_demo(order_id);-- 故意制造偏差：先让 orders_demo 的统计信息停留在旧状态ANALYZEorders_demo;INSERTINTOorders_demo(user_id,amount)SELECT10086,1.00FROMgenerate_series(1,80000);-- 此时不 ANALYZE，制造估算滞后EXPLAIN(ANALYZE,BUFFERS)SELECTo.id,i.priceFROMorders_demo oJOINitems_demo iONi.order_id=o.idWHEREo.user_id=10086;

预期结果：orders_demo 的估算 rows 明显小于实际，规划器可能选 Nested Loop，loops值很大，实际耗时远高于估算。

修复动作：

ANALYZEorders_demo;EXPLAIN(ANALYZE,BUFFERS)SELECTo.id,i.priceFROMorders_demo oJOINitems_demo iONi.order_id=o.idWHEREo.user_id=10086;

关键步骤与观察点：ANALYZE 后估算 rows 上升，规划器可能从 Nested Loop 切换为 Hash Join 或调整访问路径。对比两次输出的actual time顶部节点，通常能看到显著差异。

适用场景：批量写入、数据迁移、大促前预热后必须验证计划。容易改错的地方：不要只盯着单项指标，必须同时对比顶部节点的执行时间；有时计划切换了但总耗时没变，说明瓶颈在别处，例如缺索引或返回列过多。

11. 代价模型里被忽视的参数

除了统计信息，代价模型的参数本身就是偏差来源。尤其是存储介质从机械盘换成 SSD 之后，默认参数往往不再匹配现实。

这里最容易误解的是effective_cache_size。它不分配内存，只是告诉规划器“有多少数据可能命中 OS 缓存”，从而更愿意选索引扫描。把它调太小，规划器会误以为索引扫描要频繁读盘，反而选择顺序扫描。

调整方式可以在会话级，也可以按库：

-- 会话级，用于验证假设SETrandom_page_cost=1.1;EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMorders_demoWHEREuser_id=10086;

边界：全局修改前必须在业务低峰验证。代价参数影响所有查询，改错方向可能让一批原本正常的语句变慢。

12. 常见误区

下面这些误解，几乎每个排查慢 SQL 的人都踩过。

误区一：EXPLAIN 里的 cost 就是毫秒。它不是，只是相对单位。要看真实耗时，必须用 EXPLAIN ANALYZE 的 actual time。

误区二：估算 rows 差一点没关系。恰恰相反，嵌套循环下误差会被外层行数放大，差 100 倍就可能慢 100 倍。

误区三：加了索引就一定快。索引扫描是否被选，取决于规划器对行数和代价的估算。小表全表扫描可能比走索引更便宜，这是合理选择，不是 bug。

误区四：ANALYZE 一次就永远准。数据持续变化，统计信息会过期。自动清理依赖 autovacuum 的触发阈值，大表往往需要显式调度。

误区五：所有问题都能靠调参数解决。如果 SQL 写法本身导致了无法走索引（例如在索引列上做函数运算），再调参数也没用。

13. 生产实践建议

把上面的机制落成可执行的习惯：

第一，把 EXPLAIN ANALYZE 纳入上线前的 SQL 评审。对涉及大表关联的语句，要求提交计划对比截图。

第二，为关键表配置统计信息目标和扩展统计，尤其是多列过滤和高倾斜列。不要等出了问题才处理。

第三，批量写入、数据迁移、大促预热之后，主动ANALYZE关键表，避免规划器在统计滞后的窗口里选错计划。

第四，在 PostgreSQL 侧开启auto_explain，把慢语句的自动执行计划记录进日志，结合 Spring Boot 侧的链路追踪定位接口。

第五，SSD 环境评估random_page_cost与effective_cache_size，但只在数据支撑下调整，不凭感觉。

14. 排障清单

遇到慢 SQL 时，按这个顺序走，避免乱试。

1. 拿到语句与参数 | 2. EXPLAIN (ANALYZE, BUFFERS) 执行 | 3. 对比每个节点 估算 rows vs 实际 rows |--- 差距小 --> 看 actual time 找真实热点（排序/落盘/返回列多） |--- 差距大 --> 进入统计信息排查 | 4. 检查统计信息是否过期：pg_stat_user_tables.last_analyze | 5. ANALYZE 关键表后重跑 EXPLAIN |--- 仍未收敛 --> 调 SET STATISTICS / 建扩展统计 | 6. 观察是否仍选嵌套循环且 loops 巨大 |--- 是 --> 检查 cost 参数是否匹配硬件 | 7. 验证修复：对比顶部节点 actual time

每一步都要记录“改前/改后”的对比，否则无法判断是否真的有效。

15. 面试与复盘问题

这些问题适合自测是否真正理解本文内容：

1. EXPLAIN 和 EXPLAIN ANALYZE 的核心区别是什么？各自适合什么场景？
2. 为什么嵌套循环对行数估算偏差特别敏感？换成哈希连接会怎样？
3. 统计信息目标默认 100，什么情况下需要调大？调大的代价是什么？
4. cost 为什么不是时间？哪些参数会影响它？
5. 多列相关性为什么会让估算偏低？扩展统计是如何修正的？
6. 在 Spring Boot 项目里，如何把接口慢和数据库执行计划关联起来？

16. 一页纸决策框架

最后把分散的知识收进一张判断表。

一句话总结决策逻辑：先确认估算是否准确，再确认代价模型是否匹配硬件，最后才考虑改 SQL 或加索引。顺序颠倒，就会一直在治标。

17. 总结

PostgreSQL 的优化器本质上是在用统计信息“算账”，估算行数是所有决策的起点。EXPLAIN 让你看到它的想法，EXPLAIN ANALYZE 让你看到它估算与事实的差距。行数估算一旦偏离，嵌套循环会把这个误差成倍放大，最终表现为接口从毫秒变成秒级。

排查的正确路径是：先拿到真实计划，对比估算与实际行数，找到偏差最大的节点，再回头检查统计信息是否过期、是否需要调高统计信息目标或建立扩展统计，最后评估代价参数是否匹配当前硬件。把这条链路走顺，慢 SQL 就从“玄学”变成了可复现、可验证的工程问题。

18. 参考资料

• PostgreSQL 官方文档：Using EXPLAIN，https://www.postgresql.org/docs/current/using-explain.html
• PostgreSQL 官方文档：Planner Cost Constants，https://www.postgresql.org/docs/current/runtime-config-query.html
• PostgreSQL 官方文档：Statistics Used by the Planner，https://www.postgresql.org/docs/current/planner-statistics.html
• PostgreSQL 官方文档：Extended Statistics，https://www.postgresql.org/docs/current/sql-createstatistics.html
• PostgreSQL 官方文档：Row Estimation Examples，https://www.postgresql.org/docs/current/row-estimation-examples.html
• PostgreSQL 官方文档：auto_explain 模块，https://www.postgresql.org/docs/current/auto-explain.html