一条慢查询把我卡了一个下午一条明明走了索引的SQL在数据量涨到千万级之后突然从几十毫秒变成几秒。当时第一反应是统计信息过期了跑完ANALYZE发现没用索引损坏重建一遍还是没用。最后用EXPLAIN (ANALYZE, BUFFERS) 一看执行计划发现优化器选了一个完全出乎意料的Join顺序。这事之后我下定决心把PostgreSQL的执行过程彻底啃了一遍才发现网上大多数讲执行过程的文章都停在解析-分析-重写-优化-执行这个流程图上到了优化器内部就含糊带过。这篇是执行过程系列的第二篇我打算把优化器到底怎么选执行计划、执行器每个节点实际在做什么、以及EXPLAIN里那些字段到底代表什么一五一十地拆开讲清楚。先说清楚这篇要覆盖的范围。上一篇把SQL从文本变成执行计划的整体流水线过了一遍解析、查询分析、查询重写这些阶段属于前期加工不涉及真正的数据访问。这篇聚焦两个最关键的环节一个是优化器基于统计信息计算代价并选择执行计划的过程另一个是执行器按照计划树逐节点读取和加工数据的细节。理解了这两块你才算真正看懂了EXPLAIN输出遇到慢SQL也才谈得上有系统性的排查思路。1. 优化器的决策黑箱从语法树到执行计划其实是一道数学题很多开发者对优化器的印象是它很智能能自动选最快的方式但它的本质不是什么人工智能而是一个基于统计信息和代价模型的打分系统。PostgreSQL的查询优化器Planner做决策的核心逻辑只有一句话枚举尽可能多的可行计划算出每个计划的预计代价选代价最小的那个。1.1 代价模型里的四个数字代表什么EXPLAIN输出里每个节点都会附带costxxx..yyy这个数字不是时间单位而是PostgreSQL内部定义的抽象代价单位。它由四个参数加权计算得出seq_page_cost顺序页读取代价默认1.0表示读取一个数据页的平均代价random_page_cost随机页读取代价默认4.0表示通过索引等随机方式读取一个页的代价cpu_tuple_cost处理一行记录的CPU代价默认0.01cpu_operator_cost执行一个操作符或函数的CPU代价默认0.0025算术上很简单如果估算要扫描1000个数据页、处理10000行记录那最终代价大致就是 1000 * 1.0 10000 * 0.01 1100。实际计算比这个复杂会逐层累加每个节点的IO和CPU开销但核心思路就是读页要花钱算行也要花钱。这个模型最值得注意的地方在于random_page_cost和seq_page_cost的比值。默认4比1意味着优化器默认认为随机读比顺序读慢4倍。这个假设在机械硬盘时代基本成立但在SSD上随机读和顺序读的差距已经缩小到几乎没有。如果你的数据库跑在SSD上却不调整这个参数优化器会倾向于选择顺序扫描而避开索引扫描这是很多明明有索引却不走问题的根源之一。1.2 统计信息是优化器唯一的眼睛优化器的所有估算都建立在pg_statistic和pg_class里的统计信息上。pg_statistic存的是每一列的分布数据最常见值MCVMost Common Values、直方图边界、空值比例、平均宽度等pg_class.reltuples和relpages则记录表的总行数和总页数。这些统计信息是ANALYZE命令采集的它通过随机采样默认default_statistics_target为100即采样30000行左右来推断全表分布。这里有个很容易踩的坑分析采样是随机的不是全表扫描所以统计信息永远只是近似值。如果某列数据分布严重倾斜比如一个值占了90%采样可能恰好没采到或采到过多导致优化器对选择率的估算严重失真。我建议高倾斜的列把statistics_target调高比如ALTER TABLE t ALTER COLUMN status SET STATISTICS 1000;让采样的直方图桶数更多分布刻画更细致。代价是ANALYZE耗时增加但对几千万行的表来说这个代价完全值得。1.3 选择率估算优化器怎么猜WHERE条件能过滤多少行执行计划中每个节点的rows字段就是优化器对这个节点要输出多少行的预测。这个预测决定了下游节点比如Join、Sort的代价所以它的准确性直接决定计划的优劣。PostgreSQL对WHERE条件的行数估算主要依赖三种手段等值条件直接查pg_statistic里的MCV列表看该值是否在列表里。如果在直接用该值的频率估算如果不在用查询频率最高的几个值之后剩余概率除以总行数减去MCV覆盖行数再用直方图区间估算范围条件、、BETWEEN用直方图边界histogram_bounds来估算位于某个区间的行数比例无统计信息可用时按一个经验假设——默认选择率为0.5%或0.33%这通常非常不准但也只能先这么兜底实际排查慢查询时如果发现EXPLAIN里估算rows和实际actual rows偏差超过一个数量级基本就能认定统计信息失准或采样没覆盖到关键值。修正手段有两个重新ANALYZE或者调高统计目标。2. 执行计划节点解剖扫描节点不是只有全表扫和索引扫两种执行计划是一棵从下往上执行的树最底层的节点是扫描节点负责从表或索引中取数。PostgreSQL的扫描节点种类远比初学者以为的多每种扫描适合的场景也完全不同。2.1 Seq Scan顺序扫描为什么不是洪水猛兽Seq Scan是全表顺序扫描从数据文件的第一页一直读到最后一页。它的特点是IO完全顺序预读友好无论表多大每次读取的页都是相邻的。在下面这几种场景里Seq Scan其实是最优解表很小比如几百页以内全表扫描的成本低到可以忽略查询需要返回表中大部分行比如超过5%到10%这时用索引反复随机读反而更贵统计信息显示列分布很均匀优化器估算过滤后仍然有大量行需要返回对于顺序扫描来说enable_seqscan这个参数的网络资料经常让人误以为关掉它就能强制走索引。这个参数确实可以让优化器调低顺序扫描的优先级但它是按代价权重起作用的不是一票否决。更重要的是你关掉它只是治标不治本——真正的问题要么是统计信息不准要么是索引选择不当要么是SQL写法导致无法用索引。真靠关闭参数来优化数据量再涨上去计划还是会崩。2.2 Index Scan 与 Index Only Scan回表与不回表的本质区别Index Scan先查索引拿到行的物理位置TID即页号和行号再根据TID去数据文件里读取那一行。这个过程叫回表。回表一次就要一次随机IOrandom_page_cost就是为这个动作准备的代价。Index Only Scan则是索引覆盖的场景查询需要的所有列都包含在索引列里那么只需要扫描索引页就能返回结果完全不用回表。判断计划里是否真的没回表要关注Heap Fetches堆页抓取次数这个字段——如果这个值很高说明VACUUM清理不及时可见性映射Visibility Map没有标记对应页为全可见优化器担心元组可见性无法确认只能回表验证Index Only Scan就名不副实了。这里有个实战要点如果你建了一个(a, b)复合索引业务查询经常只要a和b两个列那你只要建立这个索引就能在大部分情况下走Index Only Scan。但如果你在WHERE里用了aSELECT出来还需要c列那就必须回表。要想彻底不回表可以把c做成索引列尤其是PostgreSQL 11支持了INCLUDE语法不影响索引本身的选择性CREATE INDEX idx_t_a_b ON t (a) INCLUDE (b);INCLUDE列只存储值不参与排序和搜索既享受了覆盖索引的好处又不会因为多列参与索引查找而膨胀索引树。2.3 Bitmap Index Scan多条索引的合并考量当查询条件同时涉及两个索引列并且各自的选择率都不算太低时单独走任何一个索引都要回表大量行PostgreSQL会改用Bitmap Index Scan。它的工作流程分两步扫描索引把所有满足条件的行的TID放进一个位图Bitmap按物理顺序从位图中取出TID去表里读对应的行这个流程的关键优化在于位图本质上是把随机IO变成了按物理存储顺序的准顺序IO。虽然还是要回表但回表的顺序经过重排减少了磁盘磁头反复跳动的开销。多个Bitmap还可以做交集AND或并集OR后再回表SELECT * FROM t WHERE a 1 AND b 2;如果a和b上分别有索引PostgreSQL可以扫描两个索引生成两个位图做位图交集再回表。这比优化器只挑一个索引回表多了很多性能优势。位图扫描有两个前提条件work_mem足够存放位图结构否则会退化成Bitmap Heap Scan上的循环重复扫描每个索引页都去多次以及表足够大小表本身就不值得用位图方案。3. 表连接策略的取舍逻辑嵌套循环、哈希连接、归并连接何时胜出多表连接是SQL最复杂的部分也是优化器决策空间最大的地方。不少人以为连接方式只跟表大小有关实际上它是数据量索引情况内存设置数据分布综合博弈的产物。3.1 Nested Loop别因为它最笨就小看它嵌套循环是最简单的连接方式外层表Outer取一行就去内层表Inner找匹配行。时间复杂度是O(N*M)如果内层表有索引则退化为O(N * logM)。它适合的场景很明确外层表行数很少内层表有索引。比如一张用户表users只有100行订单表orders有1000万行查某个用户的订单走Nested Loop Join外层扫user内层对每个user在orders上走索引查总共大概执行100次索引查找非常快。换成哈希连接或归并连接光是构建哈希表或排序的开销就远超这个量。3.2 Hash Join无索引场景下的降维打击哈希连接的原理是把内层表的连接列全部读出来在内存里建一个哈希表然后外层表每来一行直接去哈希表里查找匹配项。它的时间复杂度是O(NM)建哈希表和查哈希表各一遍无论有没有索引都一样。哈希表的构建需要内存这部分内存来自work_mem。如果哈希表超过work_memPostgreSQL会把多余的批次落盘也就是把join分成多个批次batch每个批次分别构建哈希表、然后匹配。注意这个落盘的过程会显著增加IO导致性能断崖式下跌。EXPLAIN里可以看到Hash Join节点下的Buckets和Batches如果Batches大于1说明哈希表已经溢出到磁盘了需要调大work_mem。很多人有一个误解加了enable_hashjoinoff会让SQL变快。真实情况是如果配置合理整体哈希连接通常远快于嵌套循环在几千万行大表上的表现关闭它往往只是把问题转移改成归并或嵌套循环后依然慢甚至更慢。3.3 Merge Join排序好的数据是它的主场归并连接的前提是两个表的连接列都已经排好序。然后两边各用一个指针从头往后移动像拉链一样一一配对。如果两边数据没排序优化器会在节点上加Sort操作这时候总代价包括排序的开销。归并连接的适用场景是连接列已经有序比如连接列就是索引列索引天然有序数据量很大哈希表放不进内存查询结果本身需要排序恰好可以利用排序结果而且归并连接有它的独特优势连接结果默认就是有序的。如果你的查询里有ORDER BY并且排序键和连接键一致那优化器可以省掉一个显式的排序节点。3.4 连接顺序为什么优化器也会选错多表连接的顺序组合是阶乘级的。PostgreSQL为此限制了枚举数量当连接表数量较少时用动态规划穷举所有连接顺序超过geqo_threshold默认12后改用遗传算法搜索次优解。所以实际查询不要写太多表关联连接超过12个表优化器的搜索质量会肉眼可见地下降。这也是为什么那种一条SQL关联15张表的报表查询几乎不可能性能好。与其把所有逻辑堆在一条SQL里不如拆成多条SQL让应用层聚合。或者用PostgreSQL的WITH子句配合物化AS MATERIALIZED逐步缩小数据量但前提是每步都要有索引支撑。4. 执行器工作过程深入从EXPLAIN输出看真实执行轨迹执行计划生成之后执行器开始驱动整棵树。执行器用的是标准的火山模型Volcano Model每个节点对外暴露一个next()函数上层节点每调用一次下层节点就返回一行元组。这样一层一层地拉取和加工直到输出最后的结果集。4.1 一次EXPLAIN实际执行的字段解读如果你只是在SQL前面加上EXPLAIN看到的是优化器的估算值没有真实执行信息。要看到执行器真正的运行情况需要在EXPLAIN后面跟上ANALYZEEXPLAIN (ANALYZE, BUFFERS) SELECT u.name, o.amount FROM users u JOIN orders o ON u.id o.user_id WHERE o.created_at 2024-01-01;输出会分两列第一列是优化器的估算值第二列actual time是每行真实的耗时单位也是毫秒。actual ... loopsN表示这个节点被循环执行了几次。一个常见的误读是actual rows和actual time——actual time显示的是该节点被调用的总耗时不是单行耗时。如果loops非常大actual time的累计值会很大这是正常现象不代表每行都慢。BUFFERS选项会把每个节点的缓存读写情况打印出来shared hit命中了共享缓冲区shared buffers没有产生IOshared read从操作系统缓存或磁盘读入共享缓冲区这里有IO开销如果一个节点shared read非常高说明表或索引的数据不在内存里物理读代价很大。这时候应该考虑增大shared_buffers或者用pg_prewarm预热关键表。4.2 过滤条件下推先过滤还是先取数完全不一样执行计划里经常看到Filter和Index Cond两个不同的字段这两个容易混淆但含义差别很大。Index Cond表示的是索引搜索时用于定位的条件比如Index Cond: (created_at 2024-01-01::timestamp)这意味着优化器利用索引的B树结构直接跳到第一个满足条件的叶子节点只扫描满足区间的部分。Filter则意味着索引帮不了你必须取出行之后逐行过滤。比如Filter: (status active)出现在索引扫描之后说明被索引定位到的行很多但能通过status过滤的只有一部分。如果Filter过滤掉的比例非常高比如一半以上说明这个索引本身没有覆盖到status列需要考虑建立复合索引让过滤条件直接变成查询条件的一部分。4.3 物化与CTE执行器如何处理WITH子句PostgreSQL 12之前WITH子句默认被当作物化边界子查询先执行完结果集落盘到临时表然后再被外层查询引用。PostgreSQL 12之后优化器默认可以内联CTE但它的决策依赖对子查询大小的估算。如果优化器估算CTE只会被执行一次就会内联如果它认为子查询可能被多次引用反而选择物化一次。从执行器角度看物化节点Materialize是把下层节点的输出缓存下来而不是每次重新计算。这在Nested Loop中特别重要因为内层表可能被子层循环多次扫描如果内层是计算量很大的子查询物化一次就能省掉重复计算。这里有个常见的优化手段对只需要执行一次的WITH子查询显式加AS MATERIALIZED防止内联导致的重复计算对只需要跑一次但内联反而更快的子查询加AS NOT MATERIALIZED让优化器内联。这两个小关键字用好了能避开很多优化器判断不准的坑。5. 实战案例一次误用JOIN导致的全表扫描排查全程理论部分讲完用一个真实场景把上面的知识串起来。有一次生产库突然CPU飙高慢日志里出现了一条之前一直正常的SQLSELECT o.order_no, c.customer_name FROM orders o LEFT JOIN customers c ON o.customer_id c.id WHERE o.paid_at 2024-03-01 AND o.paid_at 2024-04-01 ORDER BY o.paid_at DESC LIMIT 100;orders表5000万行customers表200万行两个表的关联键都有主键索引。按道理orders表上应该有paid_at的索引优化器应该用索引定位三月份的数据再逐条回表连customers。但EXPLAIN显示优化器选择了Seq Scan扫orders全表然后做Hash Join连接customers最后排序取100行。5.1 排查链路先看统计信息再看代价参数第一步用EXPLAIN (ANALYZE, BUFFERS) 跑了完整计划后发现估算的三月订单行数是15万行实际只有8万行估算偏差在一个数量级以内不算太离谱。那优化器为什么还选全表扫第二步检查磁盘类型。这台库跑在SSD上但random_page_cost还是默认的4.0。我把订单表按paid_at索引查了一遍数据分布发现三月份订单占总体的5%左右按默认代价算索引扫描需要回表15万次随机读代价被高估所以优化器选了全表顺序扫。第三步把random_page_cost从4.0降到1.1重新EXPLAIN优化器立刻改选了Index Scan Nested Loop Join的计划总代价下降近一半。5.2 修复效果与后续优化调整完random_page_cost之后SQL执行时间从2.8秒降到120毫秒CPU占用也恢复平稳。但这类调整要谨慎random_page_cost影响的是整个实例的所有查询如果你的库里既有SSD又有机械盘表空间就不能简单地全局调整可以在表级别用ALTER TABLE ... SET (random_page_cost 1.1)做局部覆盖或者对特定查询用SET LOCAL在事务内改参数这个案例给到的一个核心教训是当优化器的选择和你的直觉不符时先别急着改SQL先检查统计信息的准确性和代价参数是否匹配硬件特性。这两个点是最容易出问题也最容易被忽略的。5.3 从执行计划回推性能瓶颈的三步判断法根据长期看执行计划的经验我总结了一套快速定位瓶颈的方法第一先看actual rows和rows的偏差。偏差超过10倍问题大概率出在统计信息或参数配置上而不是SQL本身。改正方法就是重新ANALYZE或调参而不是盲目加索引。第二看节点里耗时最多的环节。用EXPLAIN (ANALYZE, BUFFERS)跑完后actual time最大的节点就是瓶颈所在。如果瓶颈是Sort排序考虑是否真的需要排序或者能不能用索引排序替代如果是Seq Scan考虑索引是否合适。第三看Buffers的值确认瓶颈在内存还是IO。如果shared hit占比高而shared read很少说明数据已经在内存里瓶颈在CPU计算比如复杂的表达式如果shared read占比高说明IO是瓶颈需要优化IO层次的问题比如提高缓存命中率、优化索引减少访问的页数。这套方法适合90%以上的慢查询排查场景。剩下那10%往往是查询逻辑本身设计不合理——比如必须跨表关联做聚合、子查询嵌套过深、或者一个查询里包含了太多计算逻辑。这类情况已经不是执行计划调整能解决的需要从业务逻辑和SQL重构层面下手。6. 常见误区和优化边界为什么加了索引还是很慢索引不是万能的执行计划也不是越复杂越好。最后把实践中最常见的几个认知误区拿出来挨个拆一遍。6.1 函数包裹索引列索引必然失效很多人写SQL时不注意条件列的表达式形式比如WHERE DATE(create_time) 2024-01-01或者WHERE amount * 0.9 100。这类条件对优化器来说索引列的原始值被函数或运算改变了B树索引的排序键不再直接可用。PostgreSQL有两种解法使用WHERE create_time 2024-01-01 AND create_time 2024-01-02这种区间等效写法如果必须用函数查询可以建表达式索引Functional IndexCREATE INDEX idx_create_date ON t ((DATE(create_time)));6.2 复合索引列顺序的坑最左前缀原则索引(a, b, c)可以支持WHERE a 1、WHERE a 1 AND b 2、以及WHERE a 1 AND b 2 AND c 3但不能支持WHERE b 2或WHERE c 3单独使用。因为B树首先按第一列排序第一列不定后面列的有序性就无法利用。但这里有个反直觉的情况PostgreSQL支持SKIP SCAN也就是松散索引扫描可以在某些条件下跳过不匹配的键值直接搜索后面的列。不过它只在特定场景能发挥作用不要指望随时能用。6.3 并行查询的边界什么时候该开什么时候不该开PostgreSQL 9.6开始支持并行查询并行度由max_parallel_workers_per_gather控制。并行计划的运行逻辑是Gather节点把任务分发给多个并行worker每个worker独立执行一部分扫描或计算最后汇总结果。并行查询不是没有代价的。每个worker都有启动成本如果表很小或者结果集很小并行带来的调度开销可能超过收益。而且并行查询通常需要work_mem按并行度分配比如work_mem64MB加4个并行worker每个worker可能分到64MB总内存消耗是256MB而不是64MB。这一点极易被忽视一旦并发查询多起来内存很容易打满。实际调优的经验是小查询不要并行大表的聚合、扫描、连接才开并行。具体阈值可以由parallel_setup_cost和parallel_tuple_cost来控制它们决定了优化器认为多少行才值得启动并行worker。6.4 超大分页查询LIMIT偏移量的隐藏陷阱LIMIT 100 OFFSET 1000000这种写法会让执行器老老实实扫描并丢到前一百万行再返回之后的100行。数据量大时这个操作会越来越慢。合理的替代方案有两种键集分页Keyset Pagination记录上一页最后一条数据的排序键下一页查询条件追加WHERE id 上一页最后id ORDER BY id LIMIT 100延迟关联子查询里先用索引找出需要的ID再关联回原表取完整行数据这个优化思路非常管用但要注意排序键必须唯一且有索引否则翻页会漏数据或重复。7. 写在最后理解执行过程后我的排障思路完全变了把PostgreSQL执行过程的这些细节真正吃透之后我的SQL调优习惯有了明显转变。以前遇到慢查询第一反应是猜加索引改SQL实在不行就加pg_hint_plan强制走某个扫描方式。现在第一反应是打开EXPLAIN (ANALYZE, BUFFERS)看统计信息准不准看代价参数对不对先让优化器的估算贴近现实再考虑改SQL和索引。绝大多数慢查询优化器选错计划的原因归根到底就两个——统计信息失真或者代价模型和硬件不匹配把这两件事做对至少能解决七成问题。还有一个体会是不要试图用enable_seqscanoff这类开关来哄骗优化器。优化器做决策依据的是代价模型你关掉一个方案它就换另一个。真正健康和可持续的优化路径永远是——保证统计信息准确让代价参数匹配物理环境然后设计合理的索引和查询语句让优化器有更多好计划可选。这样即使数据量再涨几倍执行计划依然大概率是可靠的。剩下的那些零星的优化器犯傻时刻用手动参数或pg_hint_plan去纠正都不迟但前提是你得先用EXPLAIN把问题彻底看懂。