读懂 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_ido.idWHEREo.user_id10086ORDERBYo.created_atDESCLIMIT20;输出大致长这样数值为示例Limit (cost1.12..45.30 rows20 width52) (actual time0.05..0.09 rows20 loops1) - Nested Loop (cost1.12..22130.44 rows20 width52) (actual time0.04..0.09 rows20 loops1) - Index Scan using idx_orders_user on orders o (cost0.43..1200.00 rows1 width24) (actual time0.02..0.03 rows20 loops1) Index Cond: (user_id 10086) - Index Scan using idx_items_order on order_items oi (cost0.43..1040.00 rows1 width36) (actual time0.01..0.01 rows1 loops20) Index Cond: (order_id o.id)关键读法每个节点括号里前一组是估算cost、rows、width后一组是实际actual time、rows、loops。发现“估算 rows”和“实际 rows”差距很大时就该怀疑统计信息或代价模型。4. 把 EXPLAIN 输出拆成几个可操作的数字理解 EXPLAIN本质上就是理解几个数字的来源字段含义由谁决定出错时会怎样cost估算的启动代价…总代价单位是抽象“页读取”代价规划器 cost 模型只影响选图不影响真实耗时rows该节点估算输出的行数统计信息 选择率公式偏差会放大到上层节点width估算每行平均字节数列类型与统计信息影响排序/哈希的内存估算actual time该节点真实耗时含子节点执行器实测用来判断真实瓶颈actual rows该节点真实输出行数执行器实测与估算对比找偏差loops该节点被执行的次数执行器实测上层嵌套循环会放大它这里最容易误解的是 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外表每行去内表查一次外表小、内表有索引外表被低估时急剧劣化哈希连接 Hash Join对小表建哈希表扫大表探测两表都较大、无可用索引相对稳定内存不足时落盘归并连接 Merge Join两表按连接键排序后归并连接键已有序或可排序依赖排序代价用流程表示一次嵌套循环的执行顺序计划树简化 Nested Loop |-- 外表 Index Scan (orders, user_id10086) rows20 实际20 |-- 内表 Index Scan (order_items, order_id...) loops20 执行顺序 取外表第 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%1000THEN10086ELSE(g%5000)1END,(random()*1000)::numeric(10,2)FROMgenerate_series(1,200000)ASg;CREATEINDEXidx_orders_demo_userONorders_demo(user_id);-- 关键先不 ANALYZE让统计信息为空EXPLAINANALYZESELECT*FROMorders_demoWHEREuser_id10086;关键步骤与预期结果未 ANALYZE 时规划器对表几乎一无所知rows估算可能是个很小的默认值而实际返回约 2000 行。执行计划也许仍选索引扫描但如果这个查询改成关联上层偏差就会被放大。接着执行 ANALYZE再对比ANALYZEorders_demo;EXPLAINANALYZESELECT*FROMorders_demoWHEREuser_id10086;此时估算 rows 会接近真实值。观察actual rows与rows是否收敛。适用场景任何怀疑统计信息过期的表。容易改错的地方忘了ANALYZE是采样而非全量扫描极端倾斜下仍可能估歪也不要指望 ANALYZE 能自动发现多列相关性。8. 统计信息目标给关键列“加采样”如果 ANALYZE 之后估算依然不准下一步就是调统计信息目标。它控制的是“每列收集多少份样本、直方图分几个桶”默认值是 100。对普通列够用但对付高度倾斜或取值极多的列往往不够。可以按列单独设置-- 提升关键列的采样精度ALTERTABLEorders_demoALTERCOLUMNuser_idSETSTATISTICS1000;-- 针对多列相关性建立扩展统计信息CREATESTATISTICSstat_orders_user_amount(dependencies)ONuser_id,amountFROMorders_demo;-- 重新收集ANALYZEorders_demo;调整手段作用代价何时使用SET STATISTICS提高单列样本量桶更细ANALYZE 更慢、存储略增单列严重倾斜CREATE STATISTICS (dependencies)记录列间函数依赖额外统计开销多列高度相关CREATE STATISTICS (ndistinct)修正多列去重估算额外统计开销分组估算偏手动ANALYZE立即刷新统计瞬时 I/O大批量写入后注意边界统计信息目标不是越大越好。设成 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 ANALYZEimportjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.Statement;publicclassExplainDemo{publicstaticvoidmain(String[]args)throwsException{Stringurljdbc:postgresql://localhost:5432/shop;StringsqlEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders_demo WHERE user_id 10086;try(ConnectionconnDriverManager.getConnection(url,app,secret);Statementstmtconn.createStatement();ResultSetrsstmt.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_ido.idWHEREo.user_id10086;预期结果orders_demo 的估算 rows 明显小于实际规划器可能选 Nested Looploops值很大实际耗时远高于估算。修复动作ANALYZEorders_demo;EXPLAIN(ANALYZE,BUFFERS)SELECTo.id,i.priceFROMorders_demo oJOINitems_demo iONi.order_ido.idWHEREo.user_id10086;关键步骤与观察点ANALYZE 后估算 rows 上升规划器可能从 Nested Loop 切换为 Hash Join 或调整访问路径。对比两次输出的actual time顶部节点通常能看到显著差异。适用场景批量写入、数据迁移、大促前预热后必须验证计划。容易改错的地方不要只盯着单项指标必须同时对比顶部节点的执行时间有时计划切换了但总耗时没变说明瓶颈在别处例如缺索引或返回列过多。11. 代价模型里被忽视的参数除了统计信息代价模型的参数本身就是偏差来源。尤其是存储介质从机械盘换成 SSD 之后默认参数往往不再匹配现实。参数默认值现实含义SSD 下的建议seq_page_cost1.0顺序读一页的代价保持 1.0 作为基准random_page_cost4.0随机读一页的代价常见调整到 1.1~1.5effective_cache_size4GB规划器认为可用于缓存的内存设为内存的一半到四分之三work_mem4MB每个排序/哈希节点可用内存过高会放大并发内存这里最容易误解的是effective_cache_size。它不分配内存只是告诉规划器“有多少数据可能命中 OS 缓存”从而更愿意选索引扫描。把它调太小规划器会误以为索引扫描要频繁读盘反而选择顺序扫描。调整方式可以在会话级也可以按库-- 会话级用于验证假设SETrandom_page_cost1.1;EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMorders_demoWHEREuser_id10086;边界全局修改前必须在业务低峰验证。代价参数影响所有查询改错方向可能让一批原本正常的语句变慢。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. 面试与复盘问题这些问题适合自测是否真正理解本文内容EXPLAIN 和 EXPLAIN ANALYZE 的核心区别是什么各自适合什么场景为什么嵌套循环对行数估算偏差特别敏感换成哈希连接会怎样统计信息目标默认 100什么情况下需要调大调大的代价是什么cost 为什么不是时间哪些参数会影响它多列相关性为什么会让估算偏低扩展统计是如何修正的在 Spring Boot 项目里如何把接口慢和数据库执行计划关联起来16. 一页纸决策框架最后把分散的知识收进一张判断表。现象最可能原因优先动作估算 rows 远小于实际统计过期或倾斜ANALYZE必要时调 STATISTICS选了嵌套循环且 loops 极大外层行数被低估修正估算或改写 SQL 限制外层cost 很低但 actual time 很高代价模型与硬件不符评估 random_page_cost同 SQL 测试库快、生产慢数据分布或统计不同对比两边统计与执行计划多列过滤估算持续偏低列间相关性建扩展统计一句话总结决策逻辑先确认估算是否准确再确认代价模型是否匹配硬件最后才考虑改 SQL 或加索引。顺序颠倒就会一直在治标。17. 总结PostgreSQL 的优化器本质上是在用统计信息“算账”估算行数是所有决策的起点。EXPLAIN 让你看到它的想法EXPLAIN ANALYZE 让你看到它估算与事实的差距。行数估算一旦偏离嵌套循环会把这个误差成倍放大最终表现为接口从毫秒变成秒级。排查的正确路径是先拿到真实计划对比估算与实际行数找到偏差最大的节点再回头检查统计信息是否过期、是否需要调高统计信息目标或建立扩展统计最后评估代价参数是否匹配当前硬件。把这条链路走顺慢 SQL 就从“玄学”变成了可复现、可验证的工程问题。18. 参考资料PostgreSQL 官方文档Using EXPLAINhttps://www.postgresql.org/docs/current/using-explain.htmlPostgreSQL 官方文档Planner Cost Constantshttps://www.postgresql.org/docs/current/runtime-config-query.htmlPostgreSQL 官方文档Statistics Used by the Plannerhttps://www.postgresql.org/docs/current/planner-statistics.htmlPostgreSQL 官方文档Extended Statisticshttps://www.postgresql.org/docs/current/sql-createstatistics.htmlPostgreSQL 官方文档Row Estimation Exampleshttps://www.postgresql.org/docs/current/row-estimation-examples.htmlPostgreSQL 官方文档auto_explain 模块https://www.postgresql.org/docs/current/auto-explain.html