★ FEATURED ARTICLE SQL 跑得慢是每个用 PostgreSQL 的人迟早要面对的事。表不大时还好顶多多等几秒一旦数据量上来一条查询从毫秒级变成秒级再变成分钟级你基本就陷入了“不知道它到底在干什么”的焦虑里。这种时候最该做的第一件事不是猜也不是盲目加索引而是打开 EXPLAIN看看数据库优化器到底打算怎么执行这条 SQL。EXPLAIN 输出的就是执行计划——PostgreSQL 把 SQL 拆解成一步步具体操作的地图。这张图会告诉你先扫哪张表、走没走索引、有没有排序、有没有嵌套循环、每一步估算了多少行、实际花了多少时间。可以说所有关于查询性能的答案都藏在这份计划里。这篇文章就围绕怎么读、怎么用、怎么靠它定位慢查询展开适合刚接触执行计划的初学者也适合已经会用 EXPLAIN、但遇到复杂计划不知道从何下手的开发者。1. EXPLAIN 到底在干什么两种模式先分清先说基础。EXPLAIN 有两种最常用的用法不带 ANALYZE 的EXPLAIN和带 ANALYZE 的EXPLAIN ANALYZE。两者输出的内容完全不同用途也不同很多人一开始就把它们搞混了。1.1 不带 ANALYZE只看优化器的“计划”EXPLAIN SELECT ...只负责生成执行计划不会真的去执行这条 SQL。它输出的是 PostgreSQL 优化器根据统计信息估算出来的执行路径包括每个节点的估算成本cost、估算行数rows、输出列宽width等。这类输出的价值在于速度快再复杂的 SQL 也只是规划一下不会真去跑数据。你可以在不改动任何数据的情况下快速验证一个查询会走什么路径。比如你想知道“加上某个索引后这个查询会不会走索引扫描”直接EXPLAIN就能看出来不用真的把查询跑一遍。缺点是它只是估算不是实测估算和实际可能差得很远尤其是统计信息不准确的时候。顺带提一个实用小技巧EXPLAIN可以带选项比如EXPLAIN (COSTS OFF)会去掉 cost 相关的输出只保留执行路径的节点结构。在只关心“走没走索引”“用的什么连接方式”这类问题时COSTS OFF 的输出干净得多一眼就能扫完整个计划的结构。1.2 带 ANALYZE真跑一遍看实际发生了什么EXPLAIN ANALYZE SELECT ...会真正执行这条 SQL然后在每个节点上记录实际耗时、实际返回的行数。比如EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id 12345;输出里除了有估算的 cost 和 rows还会多出actual time、actual rows、loops这些实测数据。actual time 的单位是毫秒带上 BUFFERS 后还会显示每个节点产生的共享缓存命中和磁盘读取次数。实际执行这一步非常关键因为 PostgreSQL 优化器不是神它只能基于统计信息做估算。统计信息过期、数据分布特殊、参数设置不当都会让估算值严重偏离实际——这时候只有EXPLAIN ANALYZE能让你看到“真实世界”长什么样。注意EXPLAIN ANALYZE 会真实执行写操作。如果你对 INSERT、UPDATE、DELETE 这类带写操作的语句使用EXPLAIN ANALYZE数据会被真正修改。想安全查看写操作的执行计划应该用BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;包一层跑完再回滚既拿到实际执行数据又不影响线上数据。我个人的习惯是先跑一次不带 ANALYZE 的计划看结构确认优化器选了哪条路径再跑一次带 ANALYZE 的看实际耗时和实际行数。两个一对比问题基本就暴露出来了。2. 读懂执行计划的输出字段、节点和成本拿到一份执行计划满屏的 Seq Scan、Index Scan、cost... rows... 很容易劝退新手。其实拆开来看就几个核心概念。2.1 cost、rows、width 分别意味着什么先看一个典型的输出片段Seq Scan on orders (cost0.00..12.10, rows5, width16) Filter: (customer_id 12345)cost优化器对这个节点“从头开始到这个节点完成”的抽象代价估算。格式是启动成本..总成本单位不是秒也不是毫秒而是 PostgreSQL 内部的一个相对计量。第一个数字是启动成本返回第一行之前的花费第二个数字是总成本处理完所有行的花费。rows优化器估算这个节点会返回多少行。width估算每行输出的字节数。成本估算的意义在于比较——优化器会在多个可行计划里选择总成本最小的那个。所以你不需要理解成本单位是怎么算出来的只需要知道成本越小优化器越认为它高效。真正要盯的是 rows 和 actual rows 的差距。如果一个节点的 estimated rows 是 5但是 actual rows 跑了 50000这就是统计信息严重失真。这种失真往往是慢查询的根源后面我会专门讲。2.2 从下往上读执行计划的阅读顺序很多人第一次看执行计划会从第一行开始往下读这是个误区。PostgreSQL 的执行计划是树形结构执行顺序是从下往上、从右往左的。也就是说最底层、最右边的节点最先执行它们的结果逐步向上层节点传递。拿嵌套循环连接举例Nested Loop (cost0.00..50.00 rows10 width20) - Index Scan on customers (cost0.00..5.00 rows1 width16) - Index Scan on orders (cost0.00..45.00 rows10 width16)这里先执行Index Scan on customers拿到一行结果再去Index Scan on orders里找对应数据最后在 Nested Loop 层把匹配结果拼起来。读执行计划时从最内层的缩进节点开始看一层一层往外读才能理清数据的流动路径。2.3 常见节点类型速查Seq Scan、Index Scan、Bitmap、Nested Loop、Hash Join 等不同节点代表不同的数据访问和连接策略每个都对应一种执行场景节点类型含义常见触发场景Seq Scan顺序扫描整表逐行读取表数据量小、或者查询需要返回表里大部分数据、或者没有可用索引Index Scan索引扫描通过索引定位然后回表取数据查询条件命中索引列且返回行数占表比例不高Index Only Scan索引覆盖扫描直接从索引拿数据不用回表查询的列全部包含在索引中Bitmap Index Scan Bitmap Heap Scan位图扫描先用位图标记满足条件的页面再批量读取查询条件命中多个索引、或者返回行数较多时Nested Loop嵌套循环连接外表每行去内表找匹配内表小且有高效索引Hash Join哈希连接先把一张表哈希化再探测另一张表两张表数据量较大且连接列上没有合适的索引Merge Join归并连接基于排序后的连接列匹配连接列上已经有序或者排序成本不高Sort排序节点显式对结果排序ORDER BY、GROUP BY、merge join 需要排序时Limit取前 N 行有 LIMIT 子句时Aggregate聚合节点比如 SUM、COUNT、AVG有聚合函数时我刚接触执行计划时最喜欢犯的错是看到 Seq Scan 就紧张觉得没走索引就是慢查询。这个判断并不准确。如果一张表只有几百行顺序扫描往往比索引扫描更快——因为走索引要先读索引页再回表读数据页两次随机 I/O 对小块数据来说反而是浪费。优化器懂这个道理所以小表上你常常会看到 Seq Scan这是正常选择不必焦虑。真正要警惕的是大表上的 Seq Scan、执行次数上万次的 Nested Loop、以及估算 rows 和 actual rows 差距巨大。这些才是性能杀手。3. 实战从一个慢查询到定位根因理论说多了容易飘直接来一个实际操作过的案例。假设有这么一个业务表存储某系统里的订单数据表结构和查询场景比较典型。3.1 场景描述与初始 SQL表结构简化如下CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id BIGINT NOT NULL, order_amount NUMERIC(10,2), order_status SMALLINT DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT now() ); CREATE TABLE customers ( id BIGSERIAL PRIMARY KEY, name VARCHAR(50) );数据量orders 表大约 200 万行customers 表大约 5 万行。业务上有个查询需要统计某天的高价值客户订单情况SQL 长这样SELECT c.name, count(*), sum(o.order_amount) FROM orders o JOIN customers c ON c.id o.customer_id WHERE o.created_at 2024-01-01 AND o.created_at 2024-01-02 AND o.order_amount 1000 GROUP BY c.name;这条 SQL 线上运行从最初的几十毫秒慢慢退化到 20 多秒业务方天天催。我拿到后没有直接看代码先跑了一个 EXPLAIN ANALYZE。EXPLAIN (ANALYZE, BUFFERS) SELECT c.name, count(*), sum(o.order_amount) FROM orders o JOIN customers c ON c.id o.customer_id WHERE o.created_at 2024-01-01 AND o.created_at 2024-01-02 AND o.order_amount 1000 GROUP BY c.name;3.2 第一次执行计划发现问题输出简化版如下HashAggregate (cost35000.00..35005.00 rows400 width72) (actual time22000.00..22005.00 rows350 loops1) - Hash Join (cost25000.00..34000.00 rows30000 width72) (actual time18000.00..21000.00 rows8000 loops1) Hash Cond: (c.id o.customer_id) - Seq Scan on customers (cost0.00..1000.00 rows50000 width50) (actual time0.01..20.00 rows50000 loops1) - Hash (cost20000.00..20000.00 rows30000 width40) (actual time15000.00..15000.00 rows8000 loops1) - Seq Scan on orders (cost0.00..20000.00 rows30000 width40) (actual time0.01..12000.00 rows8000 loops1) Filter: ((order_amount 1000) AND (created_at 2024-01-01::timestamp) AND (created_at 2024-01-02::timestamp))几个关键信息很清楚orders 走的是 Seq Scan没走索引。全表顺序扫了一遍扫描耗时 12 秒。customers 也是 Seq Scan这个其实还好5 万行的表全扫代价不大。估算的 orders Filter 结果是 30000 行实际是 8000 行。估算偏差接近 4 倍统计信息大概率不新鲜。总耗时 22 秒大头全在 orders 的顺序扫描上。这就是典型的“大表全表扫描”。orders 表 200 万行虽然过滤后只返回 8000 行但因为找不到合适的索引优化器只能把整张表从磁盘读一遍再过滤。3.3 索引选择为什么单列索引不顶用看到这里第一反应当然是加索引。不过要注意一下查询条件created_at的范围条件 order_amount的等值/范围条件混合。先试试只给created_at加索引CREATE INDEX idx_orders_created_at ON orders(created_at);再跑一次 EXPLAINBitmap Heap Scan on orders ... (actual time8.00..150.00 rows8000 loops1) Recheck Cond: (created_at 2024-01-01::timestamp AND created_at 2024-01-02::timestamp) Filter: (order_amount 1000) - Bitmap Index Scan on idx_orders_created_at (actual time3.00..3.50 rows25000 loops1)走索引了但问题来了这一天的订单有 25000 行Bitmap Index Scan 扫描了 25000 行再回表通过 Recheck 过滤掉order_amount 1000的行最后剩 8000 行。整个过程虽然比全表扫描快得多但依然会从表里随机读取 25000 行对应的页面I/O 开销不小。如果只给order_amount加索引同样面临尴尬满足order_amount 1000的行可能非常多索引扫描完也要回表过滤大量数据。这个场景的最优解是建立复合索引把两个过滤条件合并到一棵索引树里CREATE INDEX idx_orders_created_amount ON orders(created_at, order_amount);执行计划立刻变成Bitmap Heap Scan on orders (actual time5.00..70.00 rows8000 loops1) Recheck Cond: (created_at 2024-01-01::timestamp AND created_at 2024-01-02::timestamp AND order_amount 1000) - Bitmap Index Scan on idx_orders_created_amount (actual time2.00..2.80 rows8000 loops1)Bitmap Index Scan 这次只扫出 8000 行回表只读取这 8000 行对应的页面整体耗时从 12 秒级别降到几十毫秒级别。这个案例能说明两个问题一是复合索引和查询条件要匹配单列索引无法同时覆盖两个过滤条件二是执行计划是验证索引效果的最直接手段你不需要猜跑一遍计划全清楚了。顺序有讲究。对created_at, order_amount这种组合来说created_at放前面通常没问题因为它是等值范围的主过滤条件。如果字段上有等值条件那个字段放复合索引前面更合适。复合索引的列顺序是个大话题但最简单的判断方式是先用 EXPLAIN 实测行数明显降下来就说明匹配对了。3.4 统计信息更新为什么 rows 估算会差那么多前文提到优化器估算 Filter 结果是 30000 行实际只有 8000 行。这个偏差源自 pg_statistic 里的统计信息过期。PostgreSQL 的统计信息由 ANALYZE 任务或者 autovacuum 后台进程周期性更新。如果数据插入、删除频繁但又没触发自动 ANALYZE统计信息里记录的表行数、列值分布就可能严重滞后。检查一下SELECT relname, reltuples, relpages FROM pg_class WHERE relname IN (orders, customers);如果reltuples和实际行数差距很大先手动跑一次ANALYZE orders; ANALYZE customers;然后重新 EXPLAIN很多“优化器选错执行计划”的诡异问题其实根源就是统计信息太旧。4. 从执行计划到优化动作一条完整的排查路径看执行计划的终极目标不是为了“看懂”而是为了“定位问题并解决”。这里整理一套我自己常用的排查路径按顺序走大多数慢 SQL 都能找到病根。4.1 排查路径从总耗时到细化到每个节点拿到一份 EXPLAIN ANALYZE 输出我通常按这样的顺序去排查看最上层节点的 actual time确认总耗时是否真的高。如果总耗时低别折腾。从下往上找到 actual time 最长的那个节点它就是性能瓶颈所在。判断瓶颈类型表扫描耗时高看是否缺少索引或是否统计信息过期连接节点耗时高看连接顺序是否有问题、内表有没有索引、驱动表选择是否正确排序节点耗时高看能否利用索引天然顺序避免显式排序。针对瓶颈做优化改完再跑一次 EXPLAIN ANALYZE对比 actual time 是否下降。反复迭代直到上层节点实际耗时降到可接受范围。这套流程我实际用了很多年基本没有失效过。关键是“一次只改一个变量”改完必须重新测量。很多人喜欢一次加三个索引执行计划变了一个样但根本说不清是哪个索引起了作用后面维护成本也不可控。4.2 几种典型的执行计划病根对照不同的执行计划特征通常对应不同的病因。我做了一张速查表排查时可以直接对照执行计划特征可能的病因常规解法大表 Seq Scan全表扫描耗时高缺少匹配的索引或统计信息过期导致优化器放弃索引检查统计信息是否新鲜按查询条件设计复合索引Index Scan 返回大量行回表耗时高索引选择性较差过滤后的数据量仍大考虑复合索引覆盖更多过滤条件或改用 Index Only ScanNested Loop 的 loops 数量极大内层索引被反复执行上万次驱动表过大或者外层选择过滤不足增加驱动表过滤条件或者换用 Hash JoinHash Join 的 Hash 节点耗时高哈希表构建成本高通常是把大表建成了哈希表检查连接条件尝试让优化器选择小表作为哈希构建端必要时调整连接顺序或加索引Sort 节点出现在 ORDER BY / GROUP BY 中且耗时高需要排序的数据量超过 work_mem使用临时文件调整查询写法利用已有索引顺序或者提高 work_mem估算 rows 与实际行数误差极大比如超过10倍统计信息过期或字段数据存在严重分布倾斜执行 ANALYZE必要时对字段做扩展统计CREATED STATISTICS实际工作中我遇到过大量“估算 100 行、实际 20 万行”的案例几乎都是因为统计信息太旧。所以查慢查询时第一步永远是检查统计信息而不是直接改 SQL。4.3 一个调优前后的对比实例再看一个我处理过的真实调整。某查询原本执行计划长这样Sort (actual time3000.00..3100.00 rows200000 loops1) Sort Key: o.created_at DESC - Hash Join (actual time800.00..2000.00 rows200000 loops1) - Seq Scan on orders (actual time5.00..400.00 rows200000 loops1)两个关键问题一是 orders 表全表扫了 20 万行耗时 400 毫秒根本没过滤掉足够的数据二是 Sort 节点对 20 万行做了显式排序耗时 3 秒多。优化动作是给created_at加上索引并且在查询里把 ORDER BY 的字段调整成复合索引支持的顺序。改完执行计划变成Index Scan using idx_orders_created_at on orders (actual time50.00..600.00 rows200000 loops1) Index Cond: (created_at ...)排序节点消失了因为索引本身已经按created_at排好序扫描结果天然有序直接省掉了 Sort 的过程。总耗时从 3 秒多降到 600 毫秒以下。这个案例值得记住的点在于排序未必是问题本身它可能是另一个更底层问题比如大量数据被扫描的伴随症状。只要把数据访问路径优化好了排序节点自然消失不用单独去调 work_mem 或者改排序策略。5. 常见问题与排查技巧实录最后分享一些实际工作中反复踩过的坑和排查心得每条都是真实遇到过的情况。5.1 启用了索引却没走先排查统计信息有段时间我处理过一个“加了索引但查询还是 Seq Scan”的案例。索引建好了查询字段完全匹配但 EXPLAIN 就是不走索引。排查一遍发现根因是 autovacuum 没有及时触发 ANALYZEpg_class 里记录的reltuples还停留在 100 行而实际表已经有 50 万行。优化器看到“表这么小全表扫更快”自然放弃索引。手动执行ANALYZE之后执行计划立刻改成 Index Scan。加了索引不等于优化器会用。PostgreSQL 优化器始终以估算成本为准如果统计信息让它觉得全表扫描更便宜它就不会碰索引。遇到“索引无效”的错觉第一反应应该是 ANALYZE 而不是删索引。5.2 work_mem 不足引发的“隐形”排序溢出排序、哈希连接都有可能在内存不足时把数据写到临时磁盘文件。EXPLAIN ANALYZE 的输出里如果出现Sort Method: external merge Disk: 1234kB说明排序过程用到了磁盘这通常比内存排序慢一个数量级。遇到这种情况优先考虑修改 SQL 让它能利用索引天然顺序从根上消除排序如果无法消除再考虑适当调大 work_mem。注意 work_mem 是每个排序/哈希操作独占的不是全局上限调得过大可能吃光内存要留余地。5.3 嵌套循环的 loops 告诉你连接效率有多差Nested Loop 节点执行时内层节点会被反复执行。输出里的loops就是内层执行的次数。如果 loops 是 10000说明内层索引被调用了 10000 次哪怕每次只有 0.01 毫秒累计也有 100 毫秒。想优化嵌套循环方向有两个一是减少驱动表的行数从源头上减少循环次数二是让内层扫描变得更快比如建立更精准的索引。如果优化器选择 Hash Join 会更好可以考虑调整参数或者改写连接条件来“引导”优化器。5.4 并联多个索引未用复合索引匹配规则有开发者习惯把所有常用的单列索引都建好指望优化器自动组合。PostgreSQL 确实支持 Bitmap And 组合多个索引但这通常不如一个精心设计的复合索引高效。执行计划里如果出现BitmapAnd虽然也会用到索引但它仍然要分别读取多个索引再求交集这部分 I/O 是不小的成本。更合理的做法是根据实际查询模式设计一两个覆盖核心查询的复合索引把单列索引作为补充。用 EXPLAIN 实测确认效果而不是凭感觉建一堆索引。5.5 用 BUFFERS 选项定位 I/O 瓶颈EXPLAIN (ANALYZE, BUFFERS)输出每个节点的缓存命中与磁盘读取次数。看shared hit和shared read的比值如果 shared read 很高说明大量数据从磁盘读取I/O 是瓶颈如果 shared hit 很高而 actual time 依然高问题可能不在磁盘 I/O而在 CPU 计算或者数据量本身。这个输出对于判断“是不是该换硬件”很有用。我曾经碰到过一个查询计划看起来没什么问题索引也走了但就是慢。BUFFERS 一看发现每个节点都在大量读取磁盘页说明数据冷启动严重后面调整了预扩展和缓存相关配置才解决。6. 实操经验总结与后续扩展执行计划不是看一次就完事的工具它应该贯穿 SQL 开发的全流程。写完一条 SQL跑一遍 EXPLAIN ANALYZE 再上线养成这个习惯能省大量返工时间。我个人的经验是真正难的不是读懂执行计划而是改完索引或 SQL 之后有没有重新验证。很多问题在改完的第一个瞬间确实变快了但跑几天数据量上来了又回头变慢。根本原因是统计信息又过期了或者新索引在另一个查询里产生副作用。所以调优后要在一个周期内持续观察执行计划变化确认它在数据量浮动时依然稳定。后续还想继续深挖的话有两个方向建议一是学习 PostgreSQL 的统计信息机制理解 ANALYZE 怎么采样、怎么估算这是优化器决策的地基二是掌握pg_stat_statements扩展从全局视角定位哪些 SQL 消耗了最多的资源再针对这些 SQL 逐个用 EXPLAIN 深挖。另外执行计划也可以配合EXPLAIN (FORMAT JSON)做程序化解析把它集成到 CI 流程里。比如每次代码合并前自动跑一遍关键 SQL 的 EXPLAIN检测到 Seq Scan 或者 cost 暴增就报警。这个自动化能力对团队项目特别有用能在问题进入生产环境之前就筛掉一部分性能隐患。最后说一个最基础的忠告执行计划不是拿来“证明”什么的高深工具它就是数据库的体检报告。拿到报告先看最痛的点改一点测一点比闷头加索引靠谱一万倍。
阅读完成 · 觉得有帮助？
