先说个真实场景。有一回我值班凌晨一点多被群里一条消息炸醒某报表页面加载超时。登上数据库一看一条三表关联的SQL跑了将近五十秒其中最大的一张表也才三百万行。这个量级在PostgreSQL里真的不算大问题基本都出在查询计划上。从那次以后我就意识到查询优化这事儿真不是DBA的专属技能只要你是写SQL的人就应该把它当成基本功。这篇是 PostgreSQL 查询优化系列的第一篇定位是概述。我会把整个优化工作的核心脉络讲清楚一条SQL从写出来到真正跑完中间到底发生了什么优化器是怎么做选择的我们在什么环节介入最有效以及这个系列后面会沿着什么路线深入。内容上我会默认你写过基本的SQL、知道索引大概是什么但没系统学过执行计划。哪怕你只是刚接触PostgreSQL这篇也能帮你建立一张完整的地图以后再遇到慢查询至少知道该往哪个方向查而不是靠猜。1. 查询优化到底在优化什么1.1 一条慢查询背后通常藏着三个瓶颈很多人一遇到查询慢第一反应就是“加索引”。但我观察到的真实情况是慢查询的根因往往分三个层面索引只是其中一个解药。第一个层面是SQL写法本身。同样一个业务需求写法不同执行效率可能差一个数量级。比如在WHERE条件里对列套了函数比如WHERE date(created_at) 2024-01-01这种写法会让索引失效又比如在子查询里做了不必要的全表扫描或者明明可以join却用了逐条查库的写法。这些是代码层面的问题改SQL就能解决。第二个层面是数据结构与索引设计。表大了之后缺索引、复合索引列顺序不对、统计信息长期不更新都会让优化器做出糟糕的选择。这个层面的问题靠加索引、调整索引列顺序、维护统计信息来解决。第三个层面是数据库实例配置。比如work_mem设置太小导致排序和哈希连接只能落到磁盘比如random_page_cost还是机械硬盘时代的默认值4在SSD上会让优化器过度害怕随机读从而回避明明更快的索引扫描。我的建议是拿到一条慢查询先别动手改任何东西先判断它属于哪个层面。大多数慢查询是“代码索引配置”三者的混合体只处理其中一个往往不够。层面典型症状常用手段SQL写法列套函数、关联顺序不合理、子查询滥用改写SQL数据结构缺索引、复合索引顺序不对、数据倾斜建索引、调整表结构实例配置work_mem过小、随机读成本参数失真参数调整、更新统计信息1.2 优化器是你的队友不是万能的神PostgreSQL的优化器是典型的基于成本的优化器CBO。它会根据表行数、页数、列值分布等统计信息为一条SQL生成多个可能执行计划然后估算每个计划的代价选它认为最便宜的那个执行。这里有个关键认知优化器不是万能的。它掌握的统计信息如果过期或者失真就会算错账它看不到数据的业务含义比如某列90%都是同一个值它只看到直方图里的分布它也不会知道你将来要加什么索引只知道当前存在什么索引。所以我们的工作不是替它写死一条路而是想方设法让它在“信息充分”的情况下自然地选到好的计划。PostgreSQL不像某些数据库那样允许在SQL里直接写“强制走某个索引”的提示想要干预执行计划标准做法是调整统计信息、改写SQL、优化索引让优化器自己做判断。明白了这一点你思考的姿势就对了与其研究怎么“命令”优化器不如研究怎么“供给”它更好的决策依据。这也是为什么后面我会花大量篇幅讲执行计划和统计信息而不是教你背一堆“优化口诀”。2. PostgreSQL执行一条SQL的完整链路2.1 从SQL文本到结果的五个阶段一条SQL交到PostgreSQL手里要经过五个阶段才算真正跑完了解这个过程对你理解查询优化至关重要。第一阶段是解析。SQL说白了就是一段文本得先变成语法树这一层检查的是语法对不对。比如关键字拼错了、括号没闭合都是在这一步报错。第二阶段是分析。解析树还得继续加工检查表是否存在、列是否存在、类型是否匹配生成一棵带类型信息的分析树。如果表名写错、列不存在报错发生在这里。第三阶段是重写。这一步会按照规则系统处理视图和规则比如你查的是一个视图这里就会把视图定义展开成底层表的查询再往下走。第四阶段是规划这是整个优化链条的核心。规划器负责生成所有可能执行计划并估计算代价最后选出它认为最好的那一个。我前文说的所有优化手段本质都是影响这棵计划树的形状和代价估算。第五阶段是执行也就是把计划树真正跑起来逐节点产生结果。你通过EXPLAIN看到的就是第四阶段和第五阶段的信息而查询优化要琢磨的东西几乎都集中在这两个阶段。2.2 成本模型优化器在算一笔什么账优化器用的“钱”不是秒也不是毫秒而是一个抽象的成本单位。每种操作都有对应的成本系数这些系数在PostgreSQL里对应一组参数最关键的是下面这几种参数默认值含义seq_page_cost1.0顺序读一个数据页的成本random_page_cost4.0随机读一个数据页的成本cpu_tuple_cost0.01处理一行的CPU成本cpu_index_tuple_cost0.005扫描索引时处理一个索引行的CPU成本cpu_operator_cost0.0025执行一次操作符函数比如比较运算的成本这些值是相对比例不是绝对时间。默认的random_page_cost4.0是在机械硬盘时代定的当时的随机读确实比顺序读慢好几倍。但现在很多服务器用的是SSD随机读和顺序读的差距远没有4倍那么大这个默认值就明显失真了。我自己在SSD环境上一般会把它调到1.1左右实测效果经常立竿见影。举个例子说明成本估算的过程。假设一张表有100万行、大约15000个数据页顺序扫描的成本大致是15000页 × seq_page_cost(1.0) 1000000行 × cpu_tuple_cost(0.01) 15000 10000 25000如果走索引扫描情况就不一样了。假设WHERE条件能筛出5万行那么代价大约是B-tree树根的几次随机读、叶子页的随机读、每条命中数据对应的堆页面随机读再加上CPU成本。粗略算下来按照默认random_page_cost4光是5万次堆页面随机读就是20万成本比顺序扫描的2.5万高得多。这就解释了一个让很多人困惑的现象明明有索引优化器却选择了全表扫描——因为按它的估算全表扫描确实更便宜。2.3 扫描方式和连接方式速览PostgreSQL的扫描方式主要有三种理解它们的适用场景是读执行计划的敲门砖。顺序扫描Seq Scan从第一页开始把整张表读完。表比较小、或者需要返回很大比例的行时这是最快的方式因为顺序读的 I/O 效率很高。索引扫描Index Scan先走B-tree索引找到匹配的行的位置再回表读取实际数据。适用于筛选后数据量占比很小的场景。如果查询所需的列全部包含在索引里还能走索引覆盖扫描Index Only Scan连回表都省了。位图堆扫描Bitmap Index Scan先用索引找出所有符合条件的页面汇总成一个位图再按页面顺序回表读数据。这种方案适合筛选比例中等、回表行数不少但又没到全表扫描地步的场景它把“随机回表”变成了“部分顺序回表”减少了I/O。连接方式也有三种各有各的合适场景嵌套循环连接Nested Loop外层表每匹配一行就去内层表查一次。适合外层数据量小、内层有索引能快速定位的场景两边都大时基本会慢到没法看。哈希连接Hash Join先给较小的表建哈希表再遍历较大的表逐行探测。适合等值连接、两边数据量都比较大且能装进work_mem的场景这也是OLTP环境里最常见的连接方式。归并连接Merge Join要求两边都按连接键排好序然后像归并排序一样并行推进。适合数据已经有序、或者连接键本身有合适索引的场景。它不太占内存但排序本身有成本。这三个连接方式没有绝对的好坏关键看数据量和是否有索引辅助。读执行计划时看到连接类型的瞬间你心里就应该有一条线为什么优化器选它而不选另一个。3. 读懂执行计划优化绕不开的基本功3.1 EXPLAIN和EXPLAIN ANALYZE到底用哪个EXPLAIN只会打印优化器估算的执行计划不真正执行SQL。它很快、很安全但有一个致命短板显示的“行数”、“代价”都是估算值跟真实情况可能差距很大。EXPLAIN ANALYZE会真正把SQL跑一遍然后给你带actual time、rows等真实数据的执行计划。这是排查慢查询最重要的工具因为它能告诉你“优化器以为会发生什么”和“实际发生了什么”之间的距离。差距越大说明可优化的空间越大。我还习惯加上BUFFERS选项也就是EXPLAIN (ANALYZE, BUFFERS) SELECT ...加上之后每个节点会额外显示读了多少个共享缓冲区页、命中多少个、写入多少个。它能帮你判断数据是否在内存里很多“明明索引很快但实际很慢”的问题一开BUFFERS就现原形了。这里必须提醒一个坑EXPLAIN ANALYZE会真实执行你的SQL。如果是SELECT被查询的数据不会被改动问题不大但如果SQL是INSERT、UPDATE、DELETE或者调用了有副作用的函数直接执行会改动真实数据。排查这种语句时我通常在外面包一层事务再回滚BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status PAID WHERE id 42; ROLLBACK;这样既能拿到真实执行数据又不会把改动落盘。3.2 执行计划里每行在说什么执行计划的结构是一棵树很多人第一次看很懵其实只需要抓住几个关键字段。costcost0.00..25000.00这样一段。点号前面的是这个节点的启动成本比如找第一条行要花多大代价点号后面是完成整个节点所有输出的总成本。注意这不是毫秒是抽象成本单位看绝对值没有意义用它对比同一查询的替代方案才有意义。rows优化器预估这个节点会返回的行数。它来自统计信息是判断估算是否准确的关键。actual time真实执行的时间单位是毫秒。格式也是startup..total即“产生第一行耗时..完成整个节点耗时”。如果total远大于startup说明这个节点在产生大量输出如果差距很小说明大部分时间花在准备阶段。loops一个节点被循环执行了多少次。比如嵌套循环的内层节点loops可能是个很大的数。真实的单次执行时间需要把 nodes 的 actual time 除以 loops 再去看光看总耗时容易被误导。rows removed by filter被过滤条件剔除的行数。这个数值如果特别大往往意味着我们做了很多“用不上的工作”是优化的突破口。读计划的原则是从上往下读结构从下往上追数据流。下一层节点的输出正好是上一层节点的输入最底层的扫描节点是整个查询的“源头”如果它的实际行数和预估差太远优化器后面所有判断都建立在错误的地基上。3.3 实操逐行解读一个慢查询计划我用一个电商场景的SQL来演示。orders 表存订单一百万行customers 表存客户五万行。现在要查今年已付款订单的客户信息EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, c.customer_name, o.order_no FROM orders o JOIN customers c ON c.id o.customer_id WHERE o.created_at 2024-01-01 AND o.status PAID;假设执行计划长这样Hash Join (cost1285.47..8934.20 rows18432 width120) (actual time215.382..2861.093 rows17321 loops1) Hash Cond: (o.customer_id c.id) - Seq Scan on orders o (cost0.00..7211.80 rows18432 width40) (actual time13.205..2534.177 rows17321 loops1) Filter: ((created_at 2024-01-01::date) AND (status PAID)) Rows Removed by Filter: 982679 - Hash (cost1053.47..1053.47 rows42347 width80) (actual time202.091..202.092 rows50000 loops1) Buckets: 65536 Batches: 1 Memory Usage: 4821kB - Seq Scan on customers c (cost0.00..1053.47 rows42347 width80) (actual time0.105..166.883 rows50000 loops1)逐行看。整个查询选择了 Hash Join说明优化器觉得用customers建哈希、再去探测orders这条路径最便宜。问题出在下面这个顺序扫描orders表被完整扫了一遍Filter 筛掉了98万行只留下17321行节点总耗时高达2534毫秒。这就是瓶颈所在——为了找17321行我们翻了整个一百万的表。而customers那个分支其实很快5万行建哈希才花200毫秒左右内存占用4.8MB没落盘。所以优化的切入点很清楚想办法让orders表的扫描更快而不是去动哈希连接部分。针对这个计划最直接的优化就是给orders表建一个复合索引把status和created_at放一起。这里列的顺序有讲究等值条件status PAID放前面范围条件created_at 放后面。建完索引再跑一次计划大概率会变成 Bitmap Index Scan 或者更快的 Index Scan那17321行就不再需要扫100万行了。这个实例本质上就是“用读计划定位瓶颈再用索引消除无效扫描”的标准套路。4. 优化思路和工具箱4.1 一套可以反复使用的优化流程面对慢查询我习惯按固定流程走不凭感觉乱试。这个流程你可以直接照抄收集慢查询。优先看业务日志或者开pg_stat_statements插件按总耗时排序找出最该优化的SQL。对目标SQL执行EXPLAIN (ANALYZE, BUFFERS)拿到真实执行计划和成本数据。从执行计划里找出耗时最长的节点判断它是扫描慢、连接慢还是排序慢。对比该节点的预估行数和实际行数。差异大先怀疑统计信息差异小再检查索引和SQL写法。针对瓶颈做最小修改一次只改一处。改完重新执行EXPLAIN (ANALYZE, BUFFERS)对比前后响应时间和耗时节点的变化。最后做多组数据和多个场景的回归验证防止“这个查询变快了其他查询变慢了”。这套流程的价值在于把“优化”从艺术变成工程。尤其是第5步的“一次只改一处”看着简单实际操作中很多人做不到往往是索引、参数、SQL一起改最后出了问题都不知道是谁的锅。4.2 索引设计B-tree不是万能药索引是查询优化里最常用的手段但也是最容易被滥用的。B-tree索引能覆盖等值查询和范围查询应付大部分场景没问题但要注意几个要点。复合索引的列顺序非常关键。基本法则是把等值条件放前面范围条件放后面。比如WHERE status PAID AND created_at 2024-01-01建(status, created_at)的索引就比(created_at, status)更合适。原因在于B-tree先按第一列走等值定位再在第一列相同的区间内按第二列扫范围这个顺序能最大限度利用索引的有序性。如果某个查询只需要返回少量列可以考虑用INCLUDE把额外列塞进索引实现覆盖索引CREATE INDEX idx_orders_status_created ON orders (status, created_at) INCLUDE (order_no);这样order_no可以直接从索引里取连回表都省了速度又会快一截。对于只查某一类数据的场景部分索引很实用。比如业务里90%的查询都只关心status PAID的订单那就没必要索引全表CREATE INDEX idx_orders_paid ON orders (created_at) WHERE status PAID;索引小了很多写入和维护成本也降下来了。此外PostgreSQL还有 GIN 索引适合数组、JSONB、全文检索场景text_pattern_ops这类操作符类可以解决某些特殊LIKE查询无法走索引的问题。索引不是越多越好每一个索引都会拖累写入速度和膨胀维护成本设计时要拿真实查询说话而不是“觉得以后可能用得上”。4.3 统计信息别让优化器在黑暗里做决定优化器做决策的基本盘是统计信息。表的行数、页数、各列值的分布、NULL占比这些数据都在pg_statistic里。如果统计信息不更新优化器等于蒙着眼睛做选择题加再多索引也没用。PostgreSQL默认开着autovacuum会定期自动做ANALYZE。但对频繁大批量导入数据的表自动更新的频率往往跟不上变化。比如白天业务高峰期后做了大批量更新晚上就发现查询计划在乱走十有八九是统计信息过期。手动补一次就好ANALYZE orders;如果某张表数据量极大也可以只对查询涉及的热点列做部分采样分析缩短耗时。还有一种常见情况是多个条件列之间存在强关联比如“订单状态”和“订单创建时间”其实有很大相关性。优化器默认假设它们是独立的分开估算再相乘误差会成倍放大。这时可以建扩展统计CREATE STATISTICS stts_orders_status_created (dependencies) ON status, created_at FROM orders; ANALYZE orders;扩展统计能帮助优化器更准确地估算联合过滤条件的行数遇到“预估几行实际几十万行”这类怪问题时值得先检查这一项。4.4 值得关注的三个配置参数参数调整要非常克制不建议一上来就大动干戈。有三个参数我建议每个PostgreSQL使用者都认真看一遍。第一个是effective_cache_size。这个参数告诉优化器操作系统文件缓存大概有多大单位是8KB的页。它不真正分配内存只影响成本估算里“索引页面能不能命中共用缓存”的假设。设置得比实际偏小优化器就会低估索引扫描的收益总想走全表扫描。机器内存比较大的场景可以往高了设比如物理内存的一半甚至三分之二。第二个是random_page_cost。前文说过默认4.0是按机械硬盘设的SSD上建议调到1.1左右。这个参数直接影响索引扫描和顺序扫描之间的取舍很多“有索引不走路”的问题调完这个参数就好了。调节后记得重新ANALYZE要新计划不然有些场景不会立刻生效。第三个是work_mem。它控制排序、哈希连接、位图操作等最多能用多少内存默认4MB对于一个大型排序或哈希连接来说确实不够很容易落盘。但千万注意这个参数是按“一次操作”来算的如果一条SQL里同时有多个排序内存占用会翻倍并发连接一多总内存瞬间就爆了。我的做法是先抓出实际需要大量内存的慢查询通过执行计划看到Memory Usage和Batches落盘警告再针对性给该会话调大work_mem而不是盲目改全局值。5. 高频问题与避坑经验5.1 索引明明建了优化器却不用这是出现频率最高的问题。我排下来原因基本是以下几种。第一类是“写法破坏了可索引性”。最常见的是对索引列套函数。比如WHERE date(created_at) 2024-01-01这时候索引里的值是有精确时间的但你拿“日期函数处理后的值”去匹配B-tree没法直接用索引自然就废了。正确写法是改写成范围条件WHERE created_at 2024-01-01 AND created_at 2024-01-02第二类是隐式类型转换。比如列的类型是字符串你却用整数去匹配或者反过来PostgreSQL可能选择把列做隐式转换转换后的值跟索引里的原始值对不上索引又白搭。排查方法是用 查看执行计划里的Filter和Index Cond看看有没有类似::integer的转换痕迹。第三类是选择性太低。如果索引列的值分布很宽比如状态列里90%的行都是PAID优化器看到“要返回90万行”会觉得走索引还不如全表扫这时候即使有索引也会选择顺序扫描。解决方向是找到选择性更高的列或者改用部分索引只覆盖那10%非PAID的行。第四类是统计信息过期优化器把选择性高估或低估了。这种情况下先ANALYZE再去EXPLAIN计划很可能自己就变了。判断这几种情况我的经验是先看执行计划里有没有Index Cond。有才是真的用了索引只有Filter说明是在扫描结果里过滤索引没起到定位作用。5.2 预估行数和实际行数差距巨大怎么办执行计划里rows是优化器预估的数字actual ... rows是实际数字。两者差一个数量级是正常的差几个数量级就说明优化器在做“错误的地基决策”。最常见的原因是数据分布严重倾斜。比如某个客户下的订单占了全表的40%但优化器的直方图把它稀稀拉拉地摊开了就会严重低估这个值上的行数。解决办法是收集更细的统计信息或者对特定列提高统计目标ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000; ANALYZE orders;另一个原因是多条件列之间的相关性。status和created_at如果高度相关默认独立假设会算出偏差很大的预估行数这时用CREATE STATISTICS ... (dependencies)效果非常明显。还有一种容易被忽略的情况是LIMIT和OFFSET。优化器对LIMIT的处理有时过于乐观尤其配合排序时它可能预估只需扫描一小段有序数据就能拿到结果实际却要把整个排序列都算进去。遇到这种问题要看排序节点是不是变成了Sort Method: external merge如果是就该想想排序的数据量是不是能缩小或者能不能用索引直接提供有序输出。5.3 几个新手最容易掉进去的坑先说说“见慢就加索引”。这是最常见的第一反应但很多慢查询根本不是缺索引而是SQL写法或统计信息问题。加索引之前先花十分钟读执行计划确认瓶颈真的在扫描环节再动手建索引。我见过太多表上堆了七八个索引写入越来越慢查询却没快多少。第二个坑是“认为全表扫描一定是坏事”。不是的。如果一张表只有几万行全表扫描也许就是最快的方式一个扫描只要几个毫秒建了索引反而浪费写入和维护成本。优化指标不是“消除全表扫描”而是“降低整体响应时间”。第三个坑是用单个查询去调全局参数。比如一条特别吃内存的排序查询慢你就把work_mem全局调高到1GB结果几十个并发连接一来内存直接撑爆。调参必须结合并发情况和整体负载来判断不能因为一棵树放弃整片森林。第四个坑是优化完不验证。修改索引或SQL之后至少要在真实数据量、真实并发条件下重跑一遍记录前后基线。没有基线的优化就是玄学你不知道自己到底是变好了还是变坏了。第五个坑是忽略写入侧的代价。每加一个索引每次INSERT、UPDATE都要同步维护它开销是实实在在的。索引设计要围绕真实热点查询而不是给“可能用得上”的所有查询都铺路。6. 这个系列接下来怎么走6.1 后面的内容我会怎么安排作为概述这篇文章把查询优化的宏观框架搭了起来。后面的系列文章我打算按递进的深度逐篇展开第二篇讲执行计划深度拆解把EXPLAIN输出的各种节点类型、成本字段、真实硬件环境下的读法系统过一遍目标是让每个人都能在五分钟内定位最耗时的节点。第三篇讲索引的原理和实战重点覆盖B-tree的页面结构、复合索引命中规则、部分索引和覆盖索引的建模方法配合大量真实案例。第四篇聚焦统计信息与优化器行为讲清直方图、扩展统计、采样机制以及如何让优化器在复杂业务里做出正确选择。第五篇聊参数调优和监控体系包括pg_stat_statements、慢日志、锁等待和缓冲池命中率帮大家从“被动救火”变成“主动体检”。第六篇准备做真实案例复盘挑几个生产环境里的经典慢查询从问题表象一路分析到最终优化方案带大家完整走一遍排查流程。这个安排基本覆盖了日常运维中会遇到的大部分查询优化场景。如果你有特别想先看的话题也可以按这个框架对号入座。6.2 一个让我少踩很多坑的习惯文章最后分享一个我坚持了很久的习惯每次新功能上线之前我一定会把新增和改动的SQL统一抓出来跑一遍EXPLAIN (ANALYZE, BUFFERS)重点看三件事——有没有全表扫描、预估行数和实际行数差得大不大、有没有意外的排序或内存落盘。这个习惯帮我拦下过不少线上事故。印象最深的一次一个看起来人畜无害的单表查询上线前顺手一跑发现优化器预估只返回8行实际返回了60万行原因就是统计信息没跟上。如果在生产环境里让这个查询跑起来页面超时基本是板上钉钉的事。这些看似不起眼的检查动作成本极低回报却非常可观。希望你养成这个习惯之后能少经历几次我开头说的那种凌晨被叫醒的场景。