说个我经常遇到的情况线上接口突然变慢翻出慢查询日志一看一条 SQL 要跑十几秒。很多人第一反应是“加索引”但索引加完有时候还是慢这时候数据库会告诉你真相——它到底是怎么执行这条 SQL 的。这就是 PostgreSQL 的执行计划。写这篇内容是想把执行计划的读法和调优思路完整梳理一遍后端开发、DBA、数据岗位的同学应该都用得上新手可以从零开始看懂老手也可以顺手对照一下自己的排障套路。1. 执行计划是什么为什么它是调优的地图1.1 优化器是怎么“想”的PostgreSQL 拿到一条 SQL 之后并不是直接按写好的顺序去跑。它会把 SQL 解析成语法树再根据表结构、索引、数据分布这些信息生成多个候选执行方案最后用一套成本模型算出每个方案的大概开销挑一个它认为最低的去执行。这个被挑中的方案就是执行计划本质上是一棵“操作树”。你可以把优化器当成一个导航软件SQL 只是目的地导航会规划出好几条路线走高速、走国道、绕小路最终选一条它认为最快、最省油的。执行计划就是那条选中的路线里面的每个节点就是一次转向或一个路段。比如 Seq Scan 是“全路段慢速通行”Index Scan 是“走快速路”Hash Join 是“先在路口建个临时立交桥再对接”。理解这一点很重要因为调优的本质不是“优化 SQL 的写法”而是“让优化器选到更快的执行路线”。你写的 SQL 只是需求描述真正决定性能的是优化器基于统计信息做出来的那个选择。1.2 慢查询日志和监控只是先头部队我见过不少同事排查慢查询时只看慢日志里的耗时然后凭感觉加索引或者把 SQL 拆开多线程跑。慢日志确实能告诉你“哪条 SQL 慢”但它不知道“为什么慢”是扫描慢连接慢排序慢还是中间结果太大执行计划回答的恰恰是这个问题。它把一条 SQL 的执行过程拆成了一个个节点每个节点都有估算行数、实际耗时、扫描方式、连接方式一眼就能看出瓶颈卡在哪个环节。没有执行计划做调优就像修车只看仪表盘上的故障灯不看发动机舱永远只能猜。所以这篇内容的定位很明确教你用执行计划当排障工具从看懂输出到定位瓶颈再到给出一套可以直接抄的优化流程。2. 先把执行计划读明白EXPLAIN 的每一列都是线索2.1 先分清 EXPLAIN 和 EXPLAIN ANALYZE很多新手上来就跑EXPLAIN SELECT ...看到输出里一堆 cost 和 rows还是不知道慢在哪。这里有个关键区别EXPLAIN只生成执行计划不真正执行 SQL输出的是优化器根据统计信息估算出来的“预判”。EXPLAIN ANALYZE会真正执行这条 SQL并输出每个节点实际的耗时、实际行数、循环次数。两者的关系就像是“天气预报”和“实测天气”。预判不一定准尤其统计信息过期的时候EXPLAIN看着很完美一执行就翻车所以排查真实性能问题时一定要用EXPLAIN ANALYZE。顺手推荐一个组合输出信息更全比默认格式好看很多EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM orders WHERE user_id 123;BUFFERS会告诉你每个节点读了多少个内存块和磁盘块FORMAT JSON在计划复杂时更好定位层级关系。注意ANALYZE会真跑 SQL如果是 UPDATE、DELETE、INSERT 这类写操作最好包在一个事务里最后 ROLLBACK避免线上数据被改动BEGIN; EXPLAIN ANALYZE DELETE FROM orders WHERE created_at 2020-01-01; ROLLBACK;2.2 从里往外读执行顺序和节点类型执行计划的输出有一个固定的阅读规则缩进最深的节点先执行缩进相同的节点从上往下执行。它是一棵从下往上算的树每个上层节点都在等待下层节点产出数据。以这个计划为例Hash Join (cost1.14..4195.07 rows4518 width16) Hash Cond: (o.user_id u.id) - Seq Scan on orders o (cost0.00..3208.39 rows118239 width8) - Hash (cost1.10..1.10 rows110 width16) - Index Scan using users_pkey on users u (cost0.29..1.10 rows110 width16)阅读顺序是先看最底层的Index Scan再往上是Hash把扫描结果做成哈希表再看中间的Seq Scan最后是Hash Join。凡是看到这种嵌套结构都要先在脑海里把它还原成“先扫描 A - 再扫描 B - 然后连接 - 最终返回结果”的过程。常见的节点类型也不算多Seq Scan全表扫描顺序读整个表。Index Scan索引扫描通过索引定位到表里的行。Bitmap Index ScanBitmap Heap Scan先用索引把匹配行的位置收集成位图再回表读取适合返回行数较多的情况。Sort排序如果内存不够会溢写到磁盘出现Sort Method: external merge就要警惕。Hash/Hash Join把一侧数据建成哈希表然后另一侧逐行探测。Nested Loop嵌套循环外表每行去内表找匹配行。Aggregate/GroupAggregate/HashAggregate聚合计算。看计划不需要记住所有细节但至少要能分辨这个查询是扫描还是连接用的是哪种扫描和连接方式数据量估算和实际偏差大不大这三点决定了 80% 的调优方向。2.3 cost 里的数字到底怎么看计划里每个节点都带costxx..yy中间的rows是优化器估算的行数。很多人问这个 cost 单位是毫秒吗不是。PostgreSQL 的 cost 是一个“任意代价单位”它综合了磁盘 IO、CPU 处理、内存操作等多项加权值主要用途是让优化器在不同方案之间比较而不是直接换算成真实时间。所以在EXPLAIN里看到 cost 大小没有直接意义重要的是用它和其他方案横向对比。真正反映性能的是EXPLAIN ANALYZE里每个节点的actual time和actual rows。一个非常实用的检查动作把估算行数 rows 和实际行数 actual rows 对比。如果两者差了几十倍甚至上千倍基本可以确定统计信息有问题优化器在拿一张“错误的地图”导航后面选出来的计划自然不靠谱。这种偏差是很多“看起来很奇怪”的执行计划的根源。3. 识别性能杀手最常见的几种执行计划瓶颈3.1 全表扫描不一定是坏事索引扫描也不一定是好事这是新手最容易踩的误区。看到 Seq Scan 就紧张觉得一定要把它换成 Index Scan。但全表扫描在表很小或者查询要返回表里大部分数据的时候其实非常高效因为它顺序读磁盘的性能比随机读要好得多。比如一张只有几百行的配置表全表扫描几毫秒就完事加了索引反而多余。反过来索引扫描也不是免费的。它需要先从索引里找到行指针再回表随机读数据如果满足条件的行数非常多比如占了全表 30% 以上优化器宁愿全表扫也不走索引。但如果你在计划里看到一个明显异常的情况——大表、选择性很强的过滤条件却依然是 Seq Scan那大概率是下面几种原因索引没建或者建错了列。查询条件里对索引列做了函数处理比如WHERE DATE(created_at) 2024-01-01导致索引失效。字段类型不匹配比如 varchar 列和数字比较PostgreSQL 可能走隐式转换导致索引用不上。统计信息过期优化器低估了返回行数以为全表扫描更划算。遇到这种情况先查索引定义再查统计信息新鲜度最后看 SQL 写法一步一步排查。3.2 三种连接方式Nested Loop、Hash Join、Merge Join连接方式的选择是整个执行计划里最影响性能的部分。我接触过的慢查询一多半问题出在这。Nested Loop是最朴素的方式两层循环逐行匹配。它的优势是内存占用极小而且如果外表数据量小、内表连接列有索引效率非常高。比如 10 行外表去索引查 100 万行的表每次都走索引总共也就几十次随机读很快。但如果外表也很大比如几十万行每行都要去内表探测一次几十万次随机 IO 就能让你怀疑人生。Hash Join的思路是先把一侧表读出来在内存里建一个哈希表然后另一侧每行直接探测。它适合两个表都没有索引或者数据量都很大的场景。PostgreSQL 的优化器通常会选较小的表作为哈希表的驱动侧以节省内存。但要注意如果驱动表比它估算的大得多哈希表放不进work_mem就会溢写到磁盘性能断崖式下跌。Merge Join要求两侧输入已经按连接键排好序然后像拉链一样归并匹配。如果两侧数据都已经是有序的比如来自索引扫描或者查询本身需要排序Merge Join 就很合适它不支持索引探测但两边各扫一遍就好。三种方式的对比可以整理成一张表连接方式适用场景最大隐患Nested Loop外表小、内表连接列有索引外表估算不准导致循环次数爆炸Hash Join大表连接无索引或数据量平均驱动表被低估导致哈希表溢出磁盘Merge Join两侧输入有序或需要有序输出额外排序带来的内存和 CPU 开销优化器一般会根据统计信息选最优方式但统计信息不准时经常选错。我在调优时有一个很实用的技巧如果怀疑某种连接方式有问题可以临时关掉它试试效果比如SET enable_hashjoin off;再跑一遍同样的 SQL 看计划。这是诊断手段线上不要长期改这些参数后面会展开说。3.3 “rows1” 的灾难统计信息失准执行计划读得多了你会慢慢形成一种直觉优化器的所有决策都是基于统计信息做的统计信息一旦失真再好的 SQL 也白搭。我遇到过一次非常典型的案例。某个项目里有一个订单表和商品表关联查询时优化器估算“扫描商品表返回 1 行”但实际返回了 120 万行。因为估算错了优化器选了 Nested Loop用 120 万行的商品表去驱动查询内层的订单表被反复探测了一百多万次单个接口直接超时。造成统计信息失准的原因通常有三个一是表数据发生了大规模变化但ANALYZE还没跑二是列之间存在相关性单列统计信息无法描述组合条件三是表达式上的统计信息缺失比如对函数表达式建了索引却没有对应的统计信息。排查办法很简单先手动执行ANALYZE 表名;让统计信息更新再看估算行数是否恢复。如果还不行可以用CREATE STATISTICS建扩展统计信息专门解决多个列组合查询时估算不准的问题。这个功能很多人不知道但在处理多列联合过滤时非常有用。4. 把计划变成调优指令一套可落地的优化流程4.1 先拿基线在复制环境或低峰期 EXPLAIN ANALYZE调优的第一步永远是拿数据不是拿直觉。我习惯在测试环境或者线上低峰期先跑一遍EXPLAIN (ANALYZE, BUFFERS)把下面这些信息记录下来总耗时和耗时最高的节点。每个节点的实际行数和估算行数偏差。有没有Sort Method: external merge、Buffers: shared hit/read数量巨大之类的危险信号。为什么不直接在生产环境跑因为ANALYZE会占用真实资源高并发时段跑一条重型 SQL本身就是对数据库的一次冲击。稳妥的做法是先在备库或低峰期验证确认计划之后再去线上处理。拿到基线之后要做的是找出耗时占比最大的节点然后回答一个问题这个节点为什么会花这么多时间是扫描的数据量大还是连接次数多还是排序溢出到磁盘了确认了瓶颈所在再往下走就有针对性了。4.2 对症下药从瓶颈节点反推优化动作不同的瓶颈节点对应的调优手段完全不同千万别一套“加索引”走天下。如果瓶颈是Seq Scan检查过滤条件的列有没有索引索引列有没有被函数包裹统计信息是否需要更新。需要注意的是有些场景下全表扫描反而是最优解不要为了消除 Seq Scan 而去调优。如果瓶颈是Nested Loop看驱动的外表实际行数是不是被低估了。如果外表真实数据量很大优化器应该选 Hash Join这时基本是统计信息问题先ANALYZE。如果统计信息正常但连接仍然很慢检查内表连接列有没有索引没有索引的 Nested Loop 会退化成双重全表扫描。如果瓶颈是Sort看是否真的需要全量排序。常见的优化方式有利用索引天然有序来消除排序、ORDER BY和GROUP BY写同一列以复用排序结果、把排序推到子查询里减少参与排序的数据量。实在需要大排序时可以适当调大work_mem但要记住它每个连接都可能占用不能无脑调太大。如果瓶颈是Hash检查驱动表是不是优化器意料之外的大表哈希表溢出到磁盘会体现在Buffers: written和该节点耗时暴增上。这时候优化统计信息、改写 SQL 让更多过滤条件下推到连接之前比单纯加内存更有效。4.3 一个实战案例把 10 秒查询压到 200 毫秒光讲方法没有体感我拿一个真实的调优过程出来说说。某项目里有个数据查询接口需要从订单明细表和商品表关联后按天汇总金额原 SQL 大概是这样的SELECT d.dt, count(*), sum(d.amount) FROM order_detail d JOIN product p ON d.product_id p.id WHERE p.category_id 10 GROUP BY d.dt ORDER BY d.dt;线上跑了 10 秒出头前端超时。用EXPLAIN (ANALYZE, BUFFERS)跑出来的计划里最刺眼的是order_detail表走了Seq Scan扫描了 1.2 亿行实际命中却只有几万行。反过来product表由于category_id 10过滤后只剩 2000 多行但优化器没有先做这个过滤。第一步先更新统计信息手动执行ANALYZE order_detail; ANALYZE product;。再跑计划发现product表开始走Index Scan了但order_detail还是全表扫。原因是order_detail和product的关联列product_id上没有索引优化器觉得先扫全表再做 Hash Join 比随机扫描更划算。第二步在订单明细表上建索引CREATE INDEX idx_order_detail_product_id ON order_detail(product_id); CREATE INDEX idx_order_detail_product_dt ON order_detail(product_id, dt);第二个是覆盖部分场景的复合索引关联和分组都能用上。再跑计划order_detail从 Seq Scan 变成了Bitmap Index Scan经过过滤后的数据量只有几十万行Hash Join 的耗时大幅下降。第三步发现仍然有将近 1 秒耗在Sort上因为GROUP BY d.dt和ORDER BY d.dt都在做排序。这时候把order_detail的复合索引改成(product_id, dt)并不能覆盖ORDER BY d.dt的场景因为排序列不是索引最前列。这里我选择的是在 SQL 写法上做一个小改动先把过滤后的商品 ID 集合作成临时结果再用JOIN LATERAL或直接IN子句缩小扫描范围本质上是让优化器更清晰地知道数据量。优化完成后整个查询从 10 秒降到了 200 毫秒左右。最核心的转机不是某个单一操作而是三件事的组合更新统计信息让优化器看清数据分布、补上连接索引消除全表扫描、改写 SQL 让过滤操作提前。这也是我想强调的思路调优不是单点操作而是顺着执行计划一层一层把瓶颈剥掉。5. 常见问题排查与高性价比技巧5.1 建了索引为什么还是慢这是社区里被问烂的问题。索引建了EXPLAIN却显示没走或者走了索引还是慢排查方向建议按这个顺序来先看索引列和查询条件列是否匹配包括字段类型、长度、排序规则。看条件里有没有对列做函数或运算WHERE product_id * 2 10这种写法基本无法用索引。看索引列的选择性如果一列只有几个不同值优化器会觉得走索引回表还不如全表扫。看统计信息新不新表数据涨了之后多年不ANALYZE优化器拿着旧地图找路。看是不是索引膨胀了大量更新和删除之后死元组多扫描大量死数据导致性能劣化需要VACUUM或重建索引。另外多列索引不是建得越长越好。比如idx(a, b, c)它能帮助a等值 b范围 c排序的情况但对单独的c条件无能为力。根据实际查询模式组合索引比建一个巨大的索引更有效。5.2 enable_xxx 开关调试利器不是长期方案PostgreSQL 提供了一批enable_开头的开关比如enable_seqscan、enable_hashjoin、enable_nestloop、enable_sort可以用来临时禁用某种执行方式。我在调优时经常用它们做“假设验证”关掉 Seq Scan 看看优化器会不会选索引如果计划变好了说明统计信息或成本参数有问题关掉 Hash Join 看会不会选 Nested Loop可以确认之前的估算偏差。但这些东西不能当作长期调优手段。关闭某种执行方式等于把优化器的选项砍掉数据分布一变原本被禁用的方案可能才是最优的结果会适得其反。我见过有人把enable_seqscan off写进配置结果数据量大了之后大量查询反而更慢。正确的姿势是用这些开关做诊断改完之后恢复默认把真正的问题通过索引、统计信息、SQL 改写解决掉。5.3 几个低成本高收益的日常习惯调优不一定每次都是大动干戈日常维护里有几个习惯性价比极高给大表设置合理的自动ANALYZE频率。数据变更超过 10% 左右就应该跑一次确保统计信息跟得上事实。打开auto_explain插件自动记录慢 SQL 的执行计划。这样下次调优不用等故障复现直接看历史日志就行shared_preload_libraries auto_explain auto_explain.log_min_duration 1000 auto_explain.log_analyze on设置后超过 1 秒的查询会自动带上执行计划落到日志里相当于给慢查询装了行车记录仪。定期查看pg_stat_user_tables关注哪些表长期没有 ANALYZE、死元组比例大、全表扫描次数多。这些指标能提前暴露调优机会而不需要等用户来投诉。最后提醒一个关于work_mem的教训。我早年为了加速一个大排序把work_mem调到了 1GB单条查询确实快了但线上几十个并发同时跑内存直接爆了。work_mem是每个排序、哈希操作都可能分配的不是“一次查询一个”。调它之前先数清楚并发数再按可承受的内存上限反推。我个人现在调优 SQL 时已经形成了“先 EXPLAIN ANALYZE、再看统计信息、再补索引或改写法”的固定套路整个过程很少超过半小时。执行计划其实不神秘它只是把数据库优化器的思考过程摊开给你看读得越多越能理解数据库为什么这么选、你该怎么帮它做更好的选择。如果你刚接触这块不用强记所有节点和参数先从跑一次EXPLAIN ANALYZE开始把每一行输出当成线索去追问几次下来就会有自己的判断节奏。