PostgreSQL EXPLAIN实战：从执行计划看懂慢SQL优化

如果你接手过一套并发不算低的业务系统大概率会遇见这种场景页面上一次查询用户等了五六秒还没出结果。开发打开慢SQL日志锁定了那条语句然后发到群里问这SQL是不是索引没建对啊其实这时候最应该做的是在这条SQL前面加一个EXPLAIN看看PostgreSQL到底打算怎么去执行它。EXPLAIN是PostgreSQL里最基础也最实用的性能分析工具它把优化器脑子里的执行计划摊开给你看顺序扫描、索引扫描、哈希连接还是嵌套循环每一步预计花费多少成本、返回多少行数据。这篇文章就是围绕EXPLAIN展开的实战记录适合刚接触执行计划、或者被慢SQL折磨过一段时间的同学我会从输出格式讲到性能调优再配合几个真实改过的案例把这套东西讲透。1. EXPLAIN是做什么的先看懂优化器在“选路”1.1 SQL只是告诉数据库“要什么”而不是“怎么做”很多人第一次接触执行计划时会有一个疑惑SQL明明写得很直白比如“从订单表查customer_id等于42的记录”数据库直接全表扫一遍不就行了为什么还要搞出一个执行计划来这里得先理解关系型数据库的设计哲学。SQL是声明式语言它只声明结果集合不声明获取结果的过程。同样一条查询PostgreSQL内部可以有几十种执行方式直接扫全表、走索引回表、索引覆盖扫描、先过滤再连接、先排序再去重……每种方式在不同数据分布下的表现可能差出几个数量级。PostgreSQL里负责“选路”的组件叫规划器它的工作流程大致是解析SQL → 生成所有可行执行路径 → 基于统计信息估算每条路径的代价 → 选择总代价最低的路径。EXPLAIN就是把规划器最终选中的那条路径展示出来。用导航软件来类比很贴切。你告诉导航“我要从A点到B点”导航不会直接给一条路而是根据实时路况、距离、红绿灯数量计算几条方案再挑一条“预期最快”的。执行计划就是PostgreSQL的导航路线EXPLAIN就是把这个导航路线打印出来给你看。既然是“预期最快”那就存在一个天生的缺陷如果数据库手上的地图统计信息是过期的或者它对路况的认知代价参数和实际环境不匹配那么选出来的路就可能不是真最快的。后面讲的调优本质上都是在纠正这两件事。1.2 一份最简单的EXPLAIN输出怎么读先造一个最基础的场景。假设有张订单表里面存了100万行数据customer_id字段有索引EXPLAIN SELECT * FROM orders WHERE customer_id 42;输出长这样QUERY PLAN ----------------------------------------------------------------------------------------------- Index Scan using idx_orders_customer_id on orders (cost0.29..401.54 rows100 width56) Index Cond: (customer_id 42) (2 rows)这是最简单也是最典型的执行计划。第一行是执行方式Index Scan using idx_orders_customer_id说明PostgreSQL决定用idx_orders_customer_id这个索引来查Index Cond是索引条件说明它会在索引B-tree里直接定位customer_id42的位置。括号里的三个数字是重点cost0.29..401.54是两段式代价rows100是预估返回行数width56是平均行宽。这三样东西我会在下一节展开讲。读执行计划有个基本顺序先看最底层、缩进最深的节点然后从下往上、从内往外看。因为执行计划是自底向上执行的内层节点先运行结果传给外层节点。我见过不少新手直接看第一行发现是Index Scan就欢呼“走索引了”这不对。你得看整棵树尤其是cost数值大的节点。如果SQL性能差通常能在执行计划里看到一个明显的“大头”比如某一次全表扫描或者某节点循环了几万次。1.3 从cost、rows、width三个指标找线索cost是优化器用来比较不同执行路径的“虚拟货币”单位不是秒也不是毫秒。在PostgreSQL里默认约定顺序读一个8KB数据页的开销为1.0所以你可以近似把cost理解为“预计要读多少个页面”的加权和。它分两段0.29..401.54前面的0.29叫startup cost表示在返回第一行之前需要付出的固定开销例如读取索引根页、建立哈希表、做初始排序后面的401.54叫total cost表示这个节点处理完所有需要处理的数据之后的总开销。真实执行时间无法直接从cost换算但cost的相对大小很有参考价值。如果SQL跑得很慢而EXPLAIN里某个节点的cost明显高于其他节点那么优化重点通常就在那里。rows是优化器根据统计信息预估的行数。这个数字极其关键因为PostgreSQL的后续决策都建立在它上面。比如优化器预估某个JOIN会产生1000行中间结果于是决定用Nested Loop但如果实际产生的是100万行代价就完全失控了。所以你看到execution plan时一定要拿rows跟实际执行时的真实行数对比。后面讲EXPLAIN ANALYZE时会细说。width是结果行的平均宽度单位是字节。它决定中间结果的传输和存储成本。如果你在查询里写了SELECT *而表里有一个很大的text字段width就会变得很大可能导致优化器倾向于选择不同的连接或排序方式。这也是为什么“能不用SELECT *就不用”在优化器层面也有依据。2. EXPLAIN的正确打开方式2.1 裸EXPLAIN和EXPLAIN ANALYZE各自什么时候用很多初学者不知道直接写EXPLAIN SELECT ...这个SQL是不会真正执行的只是让规划器做一次路径选择和代价估算然后把计划打印出来。好处是快、无副作用大库上随便跑都没压力坏处是它的一切结论都依赖于统计信息和代价参数如果统计信息不准它本身也会“预估得不准”。真正的执行情况要用EXPLAIN ANALYZE。它会实实在在把SQL跑一遍然后告诉你每个节点实际花了多久、实际处理了多少行、循环了多少次EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id 42; QUERY PLAN ----------------------------------------------------------------------------------------------- Index Scan using idx_orders_customer_id on orders (cost0.29..401.54 rows100 width56) Index Cond: (customer_id 42) Execution Time: 0.356 ms如果再加上BUFFERS选项还能看到每个节点访问了多少共享缓冲页、命中了多少、要从磁盘读多少。这个信息在定位“到底是慢在IO还是慢在计算”时特别有用。经验是日常确认一条SQL“有没有走索引”“大概会怎么跑”用裸EXPLAIN就够了定位慢SQL的真实瓶颈、验证优化效果用EXPLAIN (ANALYZE, BUFFERS)。裸EXPLAIN和ANALYZE之间还有一个折中方案EXPLAIN (VERBOSE)会输出更详细的列信息但不执行SQL适合看计划细节。2.2 ANALYZE、BUFFERS、VERBOSE选项组合PostgreSQL从9.0开始支持带选项的EXPLAIN语法用法比你想的灵活EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM orders WHERE customer_id 42;说说各自用途。ANALYZE是执行并显示真实执行时间BUFFERS显示每个节点对缓冲区的访问格式类似shared hit21 read3其中hit表示从共享缓冲区命中的页面read表示从操作系统或磁盘读的页面read数值大基本可以判断这个节点是IO密集型VERBOSE会打印完整字段名、Schema限定名等这对JOIN查询特别有用因为不加上它你只能看到表名看不到输出列明细。还有一个FORMAT JSON常用在自动化分析场景EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...JSON格式输出里每个节点都有Execution Time、Actual Rows、Shared Hit Blocks等字段写脚本抓取、生成报表都很方便。如果你想观察并行效果还可以加EXPLAIN (ANALYZE, VERBOSE)看Workers Planned和Workers Launched。这里有一个特别重要的生产环境提醒EXPLAIN ANALYZE是真的会执行SQL的。如果对INSERT、UPDATE、DELETE使用它会真的改数据。在生产库上排查DML语句时务必把语句包在事务里并且最终回滚BEGIN; EXPLAIN (ANALYZE, BUFFERS) DELETE FROM orders WHERE status expired; ROLLBACK;这样既能拿到真实的执行耗时又不会对线上数据产生任何影响。这个习惯能帮你少背锅绝对值。2.3 一套可复现的实验环境和注意事项学习EXPLAIN最好的方式是自己动手跑一遍。下面这套实验脚本能帮你构建一个百万行级别的订单表而且在你本机上完全可复现CREATE TABLE orders ( id bigserial PRIMARY KEY, customer_id integer NOT NULL, amount numeric(10,2) NOT NULL, status text NOT NULL DEFAULT pending, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX idx_orders_customer_id ON orders(customer_id); CREATE INDEX idx_orders_created_at ON orders(created_at); INSERT INTO orders (customer_id, amount, status, created_at) SELECT (random() * 10000)::int, (random() * 1000)::numeric(10,2), (ARRAY[pending,paid,shipped])[1 (random() * 2)::int], now() - (random() * 365)::int * interval 1 day FROM generate_series(1, 1000000); ANALYZE orders;注意最后一行ANALYZE orders。很多新手插完数据立刻跑EXPLAIN发现明明有索引却走了全表扫描其实不是PostgreSQL傻而是它根本不知道表里现在有100万行。没有统计信息规划器默认表是空的当然会选最稳妥也最简单的Seq Scan。这个坑在后续章节还会遇到这里先埋个伏笔。3. 常见节点逐个拆解看到名字就知道问题在哪3.1 扫描节点Seq Scan、Index Scan、Bitmap Scan、Index Only Scan数据库拿到数据的基本单位是页面默认8KB扫描一个表本质上是“读取表里的哪些页面”。PostgreSQL有几种常见扫描方式Seq Scan顺序扫描从表的第一个数据页读到最后一个数据页不管要不要都读。小表用这个无可厚非大表读全页的代价很高。判断一个Seq Scan是不是问题主要看两个条件一是表本身有多大二是过滤条件实际能过滤掉多少行。如果查询要返回表中20%的行Seq Scan反而比走索引更划算因为顺序读磁盘比随机读快得多。Index Scan索引扫描先在索引B-tree里找到匹配行的指针再回表读取真正的数据行。回表意味着每行都可能访问不同页面——这是随机IO成本不可小觑。默认参数假设随机读比顺序读贵4倍所以PostgreSQL对“返回比例比较高”的情况会很谨慎地选择索引。Bitmap Scan位图扫描如果你查的数据比例中等、但物理分布比较分散直接每条路径回表会产生大量随机IO。这时候PostgreSQL会先扫描索引把匹配行所在的数据页标记在内存位图里然后再按页面顺序逐个读取需要访问的页面。位图扫描通常分成Bitmap Index Scan和Bitmap Heap Scan两步你会在EXPLAIN里看到它们成对出现。Index Only Scan索引覆盖扫描如果查询要的所有列都包含在索引中就不需要回表直接读索引就够了。这是最快的一种扫描方式代价就是你要建多列复合索引时把查询需要的列都包含进去。在PostgreSQL里它依赖一个叫VMVisibility Map的东西来确认数据对当前事务可见如果表频繁更新导致可见性信息过期它可能退化成普通的Index Scan或额外取堆页。3.2 连接节点Nested Loop、Hash Join、Merge Join表连接是执行计划里最值得花时间理解的一部分。PostgreSQL标准的有三种连接方式Nested Loop嵌套循环对“外表”的每一行去“内表”里查找匹配记录。如果内表有索引每次查找都很快如果内表没有索引每次查找都要做全表扫描代价就是外层行数乘以内层扫描代价非常容易爆炸。它的优点是小表驱动小表时不需要一次性把所有数据都读进内存startup cost很低。Hash Join哈希连接先把内表读出来构建成哈希表再一行行读外表去哈希表里探测。它适合两个表都没有合适连接索引、或者连接行数比较大的场景代价是hash表的构建需要内存内存不够会溢写到临时文件。Merge Join合并连接要求两边的数据按连接键排好序如果本来没排序要先做Sort然后像合并两个有序数组一样一路推进。它适合等值连接且两边数据量都不小的情况但前提是排序本身要花代价。看懂这三种连接形态对你定位JOIN性能问题非常有帮助。比如看到Nested Loop里内表是一个Seq Scan你基本可以断定内表缺索引看到Hash Join时你则应留意work_mem是否够用哈希表溢写后性能会断崖式下跌。3.3 排序、分组与分页里的隐含成本除了扫描和连接执行计划里还藏着一批“隐性成本节点”。Sort排序节点出现在ORDER BY、GROUP BY、DISTINCT、MERGE JOIN等场景。排序不是免费操作PG会尽量利用索引来避免排序如果做不到排序节点会非常醒目。当work_mem不够时排序还会溢写磁盘执行计划里能看到类似Sort Method: external merge Disk的提示这代表排序已经变慢了一个数量级。HashAggregate / GroupAggregateGROUP BY聚合时PG通常用哈希聚合内存够用或分组聚合数据已排好序。哈希聚合通常更快除非内存不足。看到GroupAggregate却觉得慢时可以琢磨是否能把数据喂成有序的比如建立一个合适的复合索引让分组键有序。Limit节点很多人以为LIMIT 100只需要读100行就行其实不一定。如果排序无法走索引PG得先把所有候选行排好序再取出前面100行。尤其是深分页比如ORDER BY id LIMIT 100 OFFSET 100000就算有主键索引它也得扫描并丢弃前100000行。你会在计划里看到它先Sort再Limit这时候所谓“分页优化”往往是无解的。4. 三个真实调优案例从执行计划到SQL改写4.1 统计信息过期预估行数差了上百倍有一次我帮同事排查一条慢查询表不大只有30万行左右但查询要跑3秒多。SQL大概长这样SELECT * FROM orders WHERE status pending AND customer_id 500;我跑了一下EXPLAIN惊讶地发现计划里预估rows800而实际执行时EXPLAIN ANALYZE显示的actual rows180000。也就是说PostgreSQL认为这个查询只返回800行结果实际返回了18万行。为什么会出现这种偏差因为早上业务批量导入了一批历史数据大概80万行导入完成后没有及时触发统计信息更新autovacuum还没来得及跑。规划器拿着旧统计信息做选择自然会把过滤条件选择性估算得极其乐观。处理办法很简单ANALYZE orders;执行完再看预估行数就回到了18万的实际水平执行计划真正反映了SQL的真实成本。这个案例给我最大的教训是遇到任何执行计划和实际感受严重对不上号的SQL第一优先排查的永远是统计信息而不是急着加索引。加索引解决不了“优化器看不见数据”的问题。4.2 WHERE条件包了一层函数普通索引失效另一个经典场景是时间范围查询。客户反馈某报表很慢我发现SQL这么写SELECT * FROM orders WHERE date(created_at) 2024-06-01;EXPLAIN出来是Seq Scan而实际上created_at字段上有索引。原因很常见date(created_at)把时间字段包进了一个函数里普通B-tree索引存的是原始timestamptz值函数表达式算出一个新值再比较索引完全帮不上忙只能全表硬扫。解决办法有两个。第一个是改写SQL让查询变成原始值范围SELECT * FROM orders WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00;这种写法能直接利用idx_orders_created_at改造后EXPLAIN会变成Bitmap Heap Scan Bitmap Index Scan或者Index Scan。如果因为某些原因SQL无法改写比如查询条件完全由接口传入那就建表达式索引CREATE INDEX idx_orders_created_on ON orders (date(created_at));PostgreSQL支持表达式索引建立后WHERE date(created_at) ...也能走索引。代价是每次插入和更新都要维护这个索引所以能用改写解决的就不建议加额外索引。这个案例延伸出一个判断技巧看到EXPLAIN里Filter越长、越不属于Index Cond越要怀疑查询条件被函数包裹导致索引失效。4.3 大表关联出现Nested Loop用参数和索引找回最优解第三个案例来自一个典型BI报表。SQL逻辑不复杂就是客户表和订单表做关联但订单表有几百万行SELECT c.id, c.name, count(*) FROM customers c JOIN orders o ON o.customer_id c.id WHERE c.region east GROUP BY c.id, c.name;customers表有10万行orders表有500万行orders.customer_id上没有索引。最初执行计划走了Nested Loop外层扫描符合条件的客户比如5000行内层每次对orders做一次全表扫描。loops字段显示内层Seq Scan循环了5000次每次都要扫500万行这能不慢吗实际调整分两步。第一步给orders表补上关联索引CREATE INDEX idx_orders_customer_id_cid ON orders(customer_id);加上索引后Nested Loop的内层从全表扫变成Index Scan每次成本大幅下降这条SQL已经能跑得不错了。第二步为了验证索引收益我用一个更合理的参数调整来观察。默认work_mem是4MB如果JOIN的哈希表超过这个大小PostgreSQL不会考虑Hash Join。在会话级别调大work_mem后重新EXPLAINSET work_mem 64MB; EXPLAIN SELECT ...执行计划变成了Hash Join。这不是说Hash Join一定比Nested Loop好而是说在数据量大、关联行数多的情况下Hash Join往往更稳定。实际生产里我没有盲目把work_mem调大因为它是每个会话独立占用的内存太大并发会爆内存。所以最终方案是加索引让Nested Loop更高效同时控制过滤条件下推让外层循环的行数尽可能少。5. 执行计划“说谎”的根源代价参数与统计信息5.1 默认参数是为机械硬盘准备的执行计划的判断依据除了统计信息还有一组代价参数。PostgreSQL默认参数里seq_page_cost1.0random_page_cost4.0意思是“顺序读一个页面需要1个成本单位随机读一个页面需要4个成本单位”。这个比例其实是从机械硬盘时代继承下来的规则机械硬盘随机IO确实比顺序IO慢好几倍。但现在生产环境大量使用SSD和NVMe随机读和顺序读之间的差距已经大幅缩小很多SSD甚至随机读比顺序读还稳定。如果沿用默认的4.0优化器会过度高估随机IO的代价结果就是明明走索引的路径更快优化器算出来却觉得顺序全表扫更便宜。这种情况相当常见。如果确认线上存储是SSD可以考虑适当调低随机读代价ALTER SYSTEM SET random_page_cost 1.2;改完之后需要重启或pg_reload_conf()。不要照抄网上数值建议从1.5起步边观察边调。调整完之后很多“明明有索引却不走索引”的问题可能不治而愈。5.2 work_mem和effective_cache_size怎么影响计划work_mem控制排序和哈希操作能用的内存大小。它太小排序就会落盘Hash Join可能退化成Nested Loop它太大并发会话同时排序时内存可能直接爆。默认4MB对现代服务器来说偏保守但也不能随意改成64GB。我的建议是观察EXPLAIN ANALYZE里有没有external merge Disk或者哈希溢写提示有再对症调整。effective_cache_size其实是估算操作系统文件缓存用了多少内存。它的作用不是分配内存而是影响优化器对“索引扫描命中缓存比例”的判断。如果这个值设得特别小优化器会觉得索引扫描每次回表都要读物理磁盘从而低估缓存的帮助也会导致它偏保守。经验上把它设为服务器可用内存的一半左右比较合理比如机器16GB内存设成8GB。这里有张速查表整理了我常用的参数检查思路参数默认值作用常见调整方向seq_page_cost1.0顺序读一页的代价一般不动random_page_cost4.0随机读一页的代价SSD机器可以调到1.1~1.5effective_cache_size4GB操作系统缓存估算按机器内存一半设置work_mem4MB排序/哈希的内存上限按查询复杂度逐步调高但别全局拉满5.3 别把enable_seqscanoff当调优手段论坛上常有人问为什么PostgreSQL不走索引然后有人回答把enable_seqscan关掉试试SET enable_seqscan off;这个开关确实存在而且确实能在会话级别强制优化器不选顺序扫描但它不是调优工具而是一个调试开关。关闭某种执行方式等于把导航里的“走普通道路”选项关掉强制它上高速。如果高速拥堵索引选择性差结果可能更慢。这些enable_xxx开关的正确用途是“假设验证”怀疑某条SQL如果改走索引会更快可以会话内把enable_seqscanoff临时打开一下对比执行计划确认索引路径的估算代价更低然后去想为什么默认没选它查统计信息、调参数。一旦定位原因应该把这层开关恢复原状。如果直接用开关压制到生产环境下次统计信息变化时可能选出更差的计划这种坑我踩过不止一次。6. 我的标准排查流程与习惯6.1 拿到慢SQL之后的第一件事先说结论不要急着改SQL更不要急着加索引。先把这条SQL在测试环境或者事务回滚里跑一次EXPLAIN (ANALYZE, BUFFERS)拿到第一手执行计划。这一步的目标是回答三个问题优化器预估的rows和实际返回的行数差多少哪个节点的Execution Time占大头有没有节点的loops特别高只要这三个问题有答案慢SQL的原因基本就浮出水面了。比如预估行数偏离实际行数很大那就是统计信息的问题某一个节点时间占比达到80%以上那就针对那个节点下手loops达到上万甚至几十万多半是连接算法选错了。6.2 看执行计划时最值得盯的两个数字我看执行计划有个习惯第一眼先找预估rows和实际rows相差悬殊的地方然后找loops最高的节点。这两个数字往往能揭露执行计划的思维盲区。rows差值大说明优化器信任的统计信息不可靠后续推理全部建立在沙地上loops高说明某个节点被执行了很多次相乘后的总代价可能完全超出预期。举个例子某个Join节点看起来单次执行时间只有0.05ms但loops120000那这个节点就贡献了6秒。只看单次耗时很容易被蒙蔽必须盯住loops。这也是我坚持用EXPLAIN ANALYZE而不是裸EXPLAIN的原因——裸EXPLAIN没有loops和actual time你永远看不到循环次数爆炸。6.3 最后对比验证与回滚习惯优化动作做完之后不要直接就跑要拿优化前后的执行计划做对比。我的习惯是保留优化前那次EXPLAIN (ANALYZE, BUFFERS)的输出然后把修改后的SQL再跑一遍同样的格式对比三个维度总执行时间、最大节点的耗时占比、预估与实际行数的差距。这里还想强调一个容易忽略的操作纪律任何对数据库结构的变更比如加索引、改参数尽量先在非生产环境验证。尤其是work_mem这类实例级参数改完可能影响其他SQL的计划选择上线后反而引出新的慢查询。我在实际运维中吃过亏为了救一条报表SQL把work_mem调到256MB结果高峰期几十个并发会话同时排序内存直接吃紧。如果条件允许可以开一个单独的会话做参数调整测试加索引也应该控制并行度低峰期执行。生产环境里慢SQL优化是一场持久战不是一锤子买卖。长期来看定期ANALYZE、定期收集慢SQL日志、关注pg_stat_statements里的高频高耗时语句比一次两次手工调优重要得多。在我个人经验里EXPLAIN最迷人的地方不是它展示了优化器选了什么而是你能顺着它的估算逻辑反推数据库的“世界观”——它看到的数据分布是什么样的它认为的硬件代价是什么样的它手里的统计信息过期了没有。把这三件事对齐了绝大多数慢SQL都能找到症结。这套方法我用了很多年也推荐你把它固化成自己的排查模板遇到计划异常先别“手痒”改SQL多问一句是不是哪里信息错了