1. 从一条慢SQL说起为什么我们要搞懂执行优化器先讲个我记忆特别深的场景。某天下午业务方扔过来一条SQL说页面打开要十几秒用户已经骂了好几次了。我拿过来一看单表只有几十万行索引也不缺WHERE条件也不复杂。当时我的第一反应是“这不合理”然后习惯性地敲了EXPLAIN ANALYZE结果出来一个让我有点意外的执行计划——明明有索引优化器却选择了全表扫描还做了两层嵌套循环。那一刻我意识到调SQL如果只看语法、只看索引不看执行优化器是怎么想的很多时候就是在碰运气。搞懂PG数据库的优化器本质上就是搞懂数据库是怎么做“决策”的。它为什么要全表扫为什么不用我建的索引为什么两个表JOIN的顺序跟我想的不一样为什么预估行数和实际行数差了十万八千里这些问题全部指向同一个底层机制——执行优化器。这篇文章不打算写成一册官方文档的摘抄而是想以一个实际踩坑、实际排查过的视角把PG的执行优化器讲清楚。会先聊它到底是干什么的、内部有哪些核心环节再实际拆解一条SQL的执行计划最后把常见的坑和排查思路整理出来包括一些我自己的实测经验和判断方法。适合三类人看刚接触执行计划、面对EXPLAIN输出一脸懵的新手写过不少SQL但对“为什么走这个计划”没有底的同学以及被慢SQL折磨过、想系统补一补优化器知识的人。PG的优化器是典型的基于代价的优化器Cost-Based OptimizerCBO它不做“经验主义”的判断而是给每一条可能的执行路径算一笔账然后挑最便宜的那条路。但这个“账本”有时候算得准有时候算得离谱。离谱的时候就是我们需要介入的时候。理解优化器的价值不在于把每条SQL都调成最优而在于你能看懂数据库为什么这么做以及什么情况下它的“判断”会失效。2. 执行优化器内部在做什么一个决策流水线2.1 从SQL文本到执行计划三个核心阶段一条SQL从客户端发到PG到真正执行中间要经过一连串的加工。优化器并不是一上来就在“想怎么执行”它得先把SQL变成自己能理解的东西。整个过程大致分三段解析、重写、规划。解析阶段Parser做的活儿比较简单粗暴就是把SQL字符串拆成一个个语法单元然后根据PG的语法规则生成一棵解析树Parse Tree。这个阶段基本不对SQL做任何“思想性”的判断纯粹是在检查语法对不对。比如你写了SELEC * FROM t在这里就会被判定为语法错误。解析树只是把SQL的骨架还原出来它跟你脑子里想的那条SQL是一一对应的还没有任何执行层面的信息。重写阶段Rewrite会基于规则Rule对解析树做变换。PG这里有一套自带的规则系统最典型的是视图展开。假如你查询一个视图优化器不会真的以“视图”为单位去执行而是会把视图的定义SQL替换进来变成对底层表的查询。这个阶段也是PG比较有特色的地方——它的规则系统非常强大甚至可以让你对一张表的INSERT做改写。但在日常优化中重写阶段我们关注得不多因为大部分问题发生在更后面的规划阶段。规划阶段Planner才是真正意义上的“优化器”。它拿到重写后的查询树开始生成多个候选的执行计划给每个计划估算代价最后选一个代价最小的作为最终执行计划。这一阶段有两个独立的子问题连接顺序怎么排、每个表怎么访问。PG会把这些组合起来生成一棵完整的计划树。理解这个流水线你就知道了一个关键点你写的SQL只是优化器的输入之一不是执行方式的最终决定者。优化器有可能把你的子查询改成JOIN有可能把你的JOIN顺序调换有可能把你的IN改成EXISTS这些改写发生在规划过程中内部机制远比我们想的复杂。2.2 代价模型数据库是怎么“算账”的PG优化器的核心思想是“算账”。每条执行路径它都会给出一个总代价Total Cost然后选最低的。代价的单位不是毫秒也不是CPU周期而是PG自定义的一个抽象单位。我们可以简单把它理解为“数据库执行这条路径需要消耗多少标准资源”。PG的代价模型主要包含几个部分启动代价Startup Cost表示返回第一行之前需要付出的代价总代价Total Cost表示整个计划执行完需要付出的代价还有就是行数预估Rows和行宽度Width。其中行数和宽度会通过EXPLAIN显示出来这两个值直接影响后续操作的代价估算。在计算代价时PG会用到几个重要的权重参数顺序扫描页代价seq_page_cost、随机扫描页代价random_page_cost、CPU处理一条元组的代价cpu_tuple_cost、CPU处理一个索引条件的代价cpu_index_tuple_cost、CPU处理一个操作符的代价cpu_operator_cost。这些参数有默认值也可以调。我在实际项目中就遇到过因为random_page_cost设置过大导致优化器宁可全表扫也不用索引的情况。这个问题后面细说。代价估算的大致逻辑是扫描路径的代价由“读页的代价”加上“处理元组的代价”构成JOIN路径的代价则要额外加上连接操作的CPU代价排序操作还会有专门的排序代价。这套模型看起来很科学但它的准确性严重依赖于统计信息。如果统计信息过期、失真那算出来的账就是一本烂账。2.3 统计信息优化器的“视力表”如果说代价模型是优化器的大脑那统计信息就是它的眼睛。优化器看不到表的真实数据它只能根据pg_statistic里存的数据分布信息来做判断。PG通过ANALYZE命令来收集统计信息包括每个列的空值比例、平均宽度、最常见值MCV、直方图边界等。这些信息会被规划器用来估算选择率Selectivity——也就是满足WHERE条件的行占比。选择率乘以表行数就得出预估行数。统计信息有个非常经典的问题它是采样估计的不是精确的。默认情况下PG采集的样本量有限对于数据分布不均匀的表预估结果可能偏离真实值。比如一个列上90%的值都是同一个值那在等值查询时即使你建了索引优化器也会算出“返回大量行”的结果从而选择全表扫描。所以我把统计信息比作优化器的视力表。你长期不跑ANALYZE表的统计信息停留在半年以前数据却已经翻了几十倍那优化器就是在“近视”状态下做决策做出的计划自然不可靠。3. 核心机制拆解访问路径与连接策略3.1 单表访问为什么有索引也不走优化器在决定怎么访问一张表时主要候选路径就几种顺序扫描Seq Scan、索引扫描Index Scan、位图扫描Bitmap Scan、仅索引扫描Index Only Scan。这里最容易被误解的是顺序扫描。很多新手一看到Seq Scan就头大觉得数据库肯定“偷懒”了。但顺序扫描在两种情况下是完全合理的一是表很小读整张表比走索引还要快二是查询要返回大部分行走索引反而要来回随机读堆表代价更高。那优化器怎么判断“大部分”是多少呢这里有个关键概念叫选择率。比如一个表有100万行你查某列等于某个值数据分布是均匀的假设有100个不同值那选择率大约是1%预估返回1万行。1万行对于顺序扫描和索引扫描的决策会产生不同影响。位图扫描经常被忽略但它在处理“单索引选择率不够低、多条件组合”时特别有用。它的思路是先用索引定位到所有满足条件的堆页再按物理顺序批量读取这些页。这种方式能把随机I/O转换成相对有序的大块读取在机械硬盘时代效果显著。现在虽然很多环境已经是SSD但位图扫描依然有价值尤其是多索引合并的场景。而仅索引扫描是性能最好的一种——如果查询的列全部包含在索引中PG就可以只读索引不读堆表省掉回表环节。但这要求足够多的可见元组映射VM信息否则还要检查可见性。单表访问路径的选择本质上是代价的比较而这个比较结果我们完全可以通过EXPLAIN看到预估代价的数值。3.2 多表连接优化器最烧脑的决策多表连接比单表访问复杂一个量级。PG需要决定表之间的连接顺序是什么每个连接用什么算法是否需要对输入先做排序或物化连接算法主要有三种嵌套循环连接Nested Loop Join、哈希连接Hash Join、归并连接Merge Join。嵌套循环是最基础的算法——对于外层表的每一行去内层表找匹配行。如果内层表有索引就是“索引嵌套循环”在没有索引或内层表特别小的情况下代价可能很大。它适合外层表小、内层表连接列有索引的场景。哈希连接的做法是对外层表建一个哈希表然后遍历内层表去哈希表里探测。它适合两表都比较大的等值连接场景。PG默认对等值连接一般会优先考虑哈希连接因为它不需要内层有索引。归并连接要求两个输入都已经按连接列排好序然后像合并两个有序数组一样做匹配。它适合连接列上有索引或已有排序结果的场景也适合非等值连接。这里有个很重要的认知优化器并不一定选择执行最快的那种算法它选的是估算代价最低的那种。如果统计信息不准估算出来的代价就很离谱。比如内表实际只有100行统计信息却显示有100万行优化器算出的嵌套循环代价会高得吓人于是选了哈希连接。结果哈希连接建表开销不小本来嵌套循环几十毫秒就能跑完最后跑了1秒。连接顺序的问题也同样关键。N个表连接可能的连接顺序数量是阶乘级的PG不会穷举所有可能而是通过动态规划和遗传算法来搜索。当表数量较少时一般少于12个使用动态规划可以找到最优顺序表特别多时PG会切到遗传算法来降低搜索空间但这样找到的可能不是全局最优解。这也是为什么我常在多表JOIN场景下推荐手工用JOIN_ORDER提示干预的原因。3.3 排序与去重被低估的代价来源排序操作在SQL里太常见了ORDER BY、DISTINCT、GROUP BY、Merge Join的前置操作都可能需要排序。PG提供专门的排序节点Sort当排序数据量超过work_mem时会临时落到磁盘文件里性能暴跌。很多人调SQL只看扫描类型和JOIN类型不关注计划里隐藏的Sort节点。其实排序的代价往往被低估。比如一个百万行的结果集做排序内存排序也许只要几百毫秒一旦落到磁盘就是几秒钟的差距。优化器在这里还有一个优化手段如果发现排序键刚好是索引列并且索引扫描本身就是按这个顺序返回的那么就可以省掉排序。这在计划里会显示为Index Scan ... Using ...且上层没有Sort节点。这也是为什么复合索引设计要考虑查询的排序需求——不是为了匹配WHERE条件而是为了“顺便消掉排序”。4. 实操手把手解析一条SQL的执行计划4.1 准备环境与示例数据理论讲完了总得实际操作一遍。我用一个模拟业务场景来演示。假设我们有两张表orders订单表和users用户表数据量分别约200万和50万。我们需要查询“最近30天内下单超过3次的活跃用户”。SELECT u.user_id, u.nickname, COUNT(*) AS order_cnt FROM users u JOIN orders o ON u.user_id o.user_id WHERE o.created_at NOW() - INTERVAL 30 days GROUP BY u.user_id, u.nickname HAVING COUNT(*) 3;先不建任何新索引直接跑EXPLAIN ANALYZE看优化器给的默认计划。EXPLAIN (ANALYZE, BUFFERS) SELECT u.user_id, u.nickname, COUNT(*) AS order_cnt FROM users u JOIN orders o ON u.user_id o.user_id WHERE o.created_at NOW() - INTERVAL 30 days GROUP BY u.user_id, u.nickname HAVING COUNT(*) 3;4.2 逐步拆解每个节点到底在干什么假设我得到如下简化版的执行计划HashAggregate (cost45234.11..45256.21 rows2210 width48) Filter: (count(*) 3) - Hash Join (cost43821.10..45230.11 rows10234 width48) Hash Cond: (o.user_id u.user_id) - Seq Scan on orders o (cost0.00..42123.45 rows10234 width8) Filter: (created_at now() - 30 days::interval) - Hash (cost1725.12..1725.12 rows50112 width44) - Seq Scan on users u (cost0.00..1725.12 rows50112 width44)在没有索引的情况下这个计划其实非常典型。优化器选择了对orders做顺序扫描过滤条件之后预估剩下1万行users直接全表扫建了一个哈希表然后用Hash Join把两个集合连接起来最后通过HashAggregate做分组聚合再过滤掉计数小于等于3的组。从执行时间上看这个计划在本地可能跑1.5秒左右。对200万行的订单表来说不算离谱但我们有更好的选择——对orders(user_id, created_at)建一个联合索引。CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);再跑一次计划会变成这样HashAggregate (cost12033.22..12055.32 rows2210 width48) Filter: (count(*) 3) - Nested Loop (cost0.56..11941.10 rows10234 width48) - Seq Scan on users u (cost0.00..1725.12 rows50112 width44) - Index Scan using idx_orders_user_created on orders o (cost0.56..8.11 rows1 width8) Index Cond: (user_id u.user_id) Filter: (created_at now() - 30 days::interval)执行时间可能降到400毫秒左右。4.3 结合EXPLAIN输出逐项解读很多人在看执行计划时只瞟一眼最上面那个节点其实应该从最底层往上看。每个缩进层级代表一个执行节点缩进越深的先执行。cost那一列有两个数字第一个是启动代价第二个是总代价。注意父节点的启动代价是从所有子节点启动那一刻算起的不是单看它自己。rows是预估行数actual rows是实际行数。如果你看到两者差距巨大比如预估1万行实际10万行这就说明统计信息或者选择率估算出了问题。BUFFERS选项会给出每个节点实际读了多少共享缓冲页这个信息对判断I/O开销极其有用。Execution Time是整个语句的总执行时间。但要注意这不包括网络传输时间和客户端接收结果的时间。4.4 验证统计信息的作用上面的例子中如果我在插入大量数据后没有跑ANALYZE优化器可能仍然认为orders只有20万行那么它算出来的Seq Scan代价就会偏低从而坚持走全表扫描。这个现象非常常见。解决方式很简单ANALYZE orders; ANALYZE users;分析完后再跑EXPLAIN预估行数会明显贴近真实数据执行计划可能自动切换为索引扫描。这也是排查执行计划异常时第一步要做的事——别急着改SQL先更新统计信息看看。5. 与代价相关的关键参数让优化器更懂你的硬件5.1 数据页与CPU代价参数PG的代价模型是参数驱动的。几个最核心的参数决定了优化器如何计算访问路径的代价seq_page_cost默认1.0。顺序读一页的代价。random_page_cost默认4.0。随机读一页的代价。cpu_tuple_cost默认0.01。处理一行元组的CPU代价。cpu_index_tuple_cost默认0.005。处理一条索引元组的CPU代价。cpu_operator_cost默认0.0025。执行一个操作符比如比较运算的CPU代价。effective_cache_size默认4GB。用于评估索引扫描“缓存命中”的概率。random_page_cost是我踩过最多坑的参数之一。默认4.0来源于机械硬盘时代——随机I/O比顺序I/O慢4倍。但在如今普遍使用SSD甚至NVMe的环境里随机读与顺序读的差距远小于4倍。如果保持默认4.0优化器会高估索引扫描的代价导致它倾向于全表扫描。我的经验是纯SSD环境可以设到1.5左右NVMe的话设到1.1都没问题。但这只是经验参考值具体还要看实际压测结果。有的项目我有意调成1.0让优化器把随机读和顺序读看作几乎等价效果很好但要注意监控有没有“计划抖动”——也就是同一条SQL的执计划频繁换。5.2 work_mem与排序、哈希的内存边界work_mem控制每个排序或哈希操作最多能使用多少内存。默认值是4MB。这个数值看起来不起眼但对大量排序和哈希JOIN的性能影响极大。当一个Hash Join想建的哈希表超过work_mem限制时PG会分批处理把溢出的部分写到磁盘临时文件。这个“溢出”操作非常昂贵。优化器在评估Hash Join时也会把“可能溢出”的代价算进去因此work_mem太小时优化器甚至可能避开哈希连接而选择更慢的嵌套循环。调work_mem不能贪大因为它不是全局共享一个4MB而是每次排序或哈希操作都可能独占这么多内存。如果一条SQL有多个Hash Join和Sort节点连接池并发一高内存可能直接被吃满。我的建议是先处理慢SQL观察单条语句的峰值内存在安全范围内从64MB开始逐步调。5.3 planner是否有全局的“偏好设置”还有一个容易被忽略的参数叫enable_*系列比如enable_seqscan、enable_hashjoin、enable_nestloop、enable_mergejoin。这些参数不是强制指定执行方式而是把对应路径的代价乘上一个很大的惩罚系数不是直接禁用。早期排查慢SQL时我经常临时用它们来确认问题出在哪种执行方式上。比如怀疑全表扫描不合理就执行SET enable_seqscan off;再重新看执行计划。如果用了索引还是慢说明问题不在选路。这个技巧只能用于排查不能作为上线配置——长期关闭全表扫描会导致优化器做出各种奇怪的选择。6. 实战排查优化器选错计划的两个真实场景6.1 统计信息滞后导致的全表扫描我先说的这个场景是我实际遇到过的。某张业务流水表平时一天新增几十万行但因为数据保留策略要定期删旧数据。某一天我收到慢SQL告警查询是查最近7天的数据结果走了全表扫描。问了一圈运维说数据更新脚本里没有执行ANALYZE的习惯。查看执行计划时优化器以为表只有80万行其实当前已经460万行了。它还认为“最近7天”的数据会占很大比例算了算觉得全表扫更划算。直到我手动执行了ANALYZE重新查看计划优化器才意识到最近7天只占4%的数据切换到索引扫描后查询从3秒降到30毫秒。这个场景能复现得非常稳定。所以我给大家一条非常实用的建议上线任何批量数据变更脚本大量INSERT、DELETE、UPDATE之后都主动跑一次ANALYZE。尤其是那种做DELETE后回收空间的统计信息滞后必然发生。6.2 参数设置与硬件不匹配导致的索引拒绝还有一次是在某个高并发读多写少的系统上明明查询条件能用到索引但优化器每次都是全表扫描。我当时判断大概率是random_page_cost设高了于是问了下运维磁盘类型。确认是SSD后我把random_page_cost从4.0改成1.5然后再看执行计划索引路径的代价就直接低于全表扫描了。这个案例的启发是优化器的“价值观”是参数喂出来的硬件变了参数也要变。数据库迁移到云端、或者从机械盘换成SSD之后除了关注性能基准测试也要重新审视random_page_cost和effective_cache_size这些参数。6.3 JOIN顺序问题与手工干预还有一类问题是明明可以用小表驱动大表优化器偏偏选择把大表作为外表。这种现象多发生在统计信息不准之后。即使统计信息准很多开发者也喜欢一口气JOIN七八张表表的连接顺序一旦复杂优化器的遗传算法结果并不是全局最优。遇到这类情况我的常规做法是使用pg_hint_plan扩展来手工指定连接顺序和连接方法。PG本身不像某些商业数据库那样原生支持HINT需要安装扩展。SET search_path TO public; LOAD pg_hint_plan;然后在SQL前面加提示注释/* Leading((u (o p))) HashJoin(o p) IndexScan(o) SeqScan(u) */ SELECT ...这种方法适合作为“最后手段”。原因在于一旦手工指定SQL就脱离了自动优化能力以后表数据分布变化、统计信息更新这个固定计划可能反而变差。所以我的建议是手工提HINT之前先确保统计信息是最新的参数设置是合理的然后再考虑“人工接管”。7. 常见问题速查执行的迷雾与出口表现最可能原因排查方向建议处理有索引但走全表扫描random_page_cost过高、统计信息过期、选择率高查看EXPLAIN中预估行数与实际行数更新统计信息、调整random_page_cost预估值与实际值差距极大统计信息陈旧、数据倾斜严重对比rows和actual rows执行ANALYZE、考虑扩展统计信息多个表JOIN后计划奇怪连接顺序搜索不充分、统计信息不准用EXPLAIN逐层查看起估行数适时手工指定JOIN顺序排序操作变成性能瓶颈work_mem过小导致磁盘排序观察计划中的Sort节点是否带external sort适当调大work_memHash Join频繁落盘work_mem过小观察计划中Hash节点的内存使用调大work_mem、优化连接顺序同一条SQL时快时慢计划抖动对比多时段执行计划固定参数、考虑HINT某个WHERE条件列有索引但始终不用数据分布倾斜、MCV统计缺失或错误查看pg_stats中该列的直方图重新ANALYZE、必要时分桶清洗这个表格是我长期排查问题的结果汇总。可以说90%的优化器选择异常都能归到这七个方向里。排查的顺序很重要先看统计信息新不新再看参数是否匹配硬件最后才是手工干预。8. 延伸从执行优化器到SQL优化思维学习执行优化器的最终目的不是背下来代价公式而是建立起一种“站在数据库角度想问题”的思维模式。很多时候开发者习惯于从SQL文本去推测执行方式“我写了JOIN那应该就是嵌套循环吧”“我建了索引那必须走索引”。但优化器的真实逻辑完全不是这样。它只认三样东西统计信息、代价参数、路径枚举结果。所以我的SQL优化流程已经固化成一套动作第一步看执行计划前先确认统计信息是否新鲜。数据量波动大的表优先跑ANALYZE。第二步用EXPLAIN (ANALYZE, BUFFERS)看实际执行。比较预估行数和实际行数如果差异超过10倍那说明选择率估算出了问题。第三步检查参数。重点是random_page_cost和work_mem。这两个参数几乎是最常被忽视的。第四步试着简化SQL。把复杂的JOIN拆开逐步加上去观察执行计划的变化找到导致代价爆炸的“元凶”。第五步抗不住了再上HINT。手工固定计划可以但一定要注释清楚“为什么固定”避免后人接手时一头雾水。这套流程用完绝大多数慢SQL都能找到明确的优化方向。剩下的少数顽固分子往往需要从业务逻辑入手调整SQL写法那已经超出优化器的范畴了。9. 写在最后的一些个人体会PG的执行优化器是一个设计精巧又相当复杂的组件。刚接触时会觉得它就是个黑盒子——输入一条SQL输出一个执行计划好坏全靠运气。但实际用下来会发现它其实有非常清晰的决策逻辑统计信息是它看世界的眼睛代价模型是它的价值观路径枚举是它做选择的工具箱。我踩过不少坑其中印象最深的教训是千万不要用自己的直觉去替代优化器的判断。比如你觉得“这个表必须走索引”于是拼命加索引实际执行计划却完全不看。这时候应该停下来想想为什么优化器认为全表扫描更便宜是因为统计信息不准还是因为代价参数没有适配硬件想清楚这两个问题比盲目加索引要有效一百倍。另外学习优化器不能只停留在“看懂EXPLAIN输出”这个层面。强烈建议大家自己建两张测试表插入几万到几百万行数据多试试不同的WHERE条件、JOIN顺序、索引组合对比执行计划的差异。只有亲手造过几个极端数据分布的场景才能真正理解优化器在面临这些情况时的取舍逻辑。PG的优化器不是万能的但它非常讲道理。你给它准确的统计信息配好合理的代价参数它一般能给你不错的计划。它出错的时候通常是因为外部条件变了而我们还拿着旧地图在找新大陆。最后分享一个小技巧每次优化完一条慢SQL我都会把优化前后的执行计划、参数调整过程、最终结果记录成一篇短文档。时间久了这比任何理论书都有价值——因为它是真实数据、真实场景下的决策记录而那些“为什么这样选”的思考过程恰恰是最难从文档里学到的部分。