1. 一条慢查询背后为什么问题偏偏出在统计信息上1.1 复盘起点一条执行计划跑偏的报表SQL上个月我处理了一桩很典型的慢查询。某张订单表大约 3500 万行之前一直跑得好好的报表统计SQL某天突然从 2 秒多变成 3 分半。第一反应当然是查索引、看锁、看CPU结果都没异常。把 EXPLAIN ANALYZE 拉出来一看症状非常明显优化器估算返回 800 行左右实际却扫出来 480 万行执行计划选择了嵌套循环驱动表那边有 50 多万行硬生生做了几十万次索引探测。我把三天的排查记录交给了常用的 AI 辅助工具做归纳它给出来的核心线索只有一条去查统计信息是不是过期了。顺着这个方向检查果然出问题的那张订单表最近一次 ANALYZE 是 20 天前跑的而中间正好有两天在做状态字段的批量更新。说白了优化器手里拿的路况数据还是旧版的它规划的路线当然不是最优的。这篇文章就是围绕这条主线展开的PostgreSQL统计信息为什么能影响查询性能、失真以后会造成什么后果、我们又该怎么检查和修复。适合后端开发、自己维护数据库的团队以及刚接触执行计划的同学阅读。1.2 统计信息在查询优化链路里的位置一条 SQL 从客户端发出到返回结果PG 内部经历的链路大致是词法语法解析、查询重写、生成计划、执行。真正决定怎么跑的是生成计划这一步也就是优化器planner。优化器不是靠猜的它会针对每一种可能的执行方式全表扫描、索引扫描、哈希连接、嵌套循环、排序归并等估算一个总成本然后挑成本最低的方案。成本估算的依据主要有三类表级行数、页数这些物理信息每列的分布情况比如高频值、NULL比例、平均宽度、相关性还有各类成本参数像顺序扫描成本、随机扫描成本、CPU处理成本。其中前两类都属于统计信息的范畴。你可以这样理解优化器相当于一个地图App统计信息就是路况和里程数据。路况数据越新、越细致App 给出的路线就越靠谱数据过期或者粒度太粗导航就会自信地把你带进堵死的老路。2. 统计信息到底存在哪长的又是什么样2.1 pg_class 管表、pg_statistic 管列先分清楚PostgreSQL 里跟统计信息相关的系统表不止一张但最常见的就俩。pg_class 里存的是表级基础信息其中 relpages 表示表占用数据页数的估算值reltuples 表示行数的估算值。注意这俩都是估算值不是精确数由 VACUUM 和 ANALYZE 负责更新。优化器估算全表扫描行数时主要就靠 reltuples估算扫描顺序和IO开销时会用到 relpages。列级的统计信息则放在 pg_statistic 表里。这张表的设计比较特殊一行代表一个表的一列但具体数据被编码在 stavalues1、stanumbers1 这些数组字段里普通查询直接查的话体验很差。所以 PG 提供了 pg_stats 这个视图把编码后的内容解析成可读字段。我们日常巡检统计信息基本就是查 pg_stats。2.2 一张表看懂 pg_stats 里那些关键字段pg_stats 视图字段很多但真正影响性能判断的核心也就下面这几个。字段名含义对查询的影响null_frac该列中 NULL 值所占比例决定 IS NULL 条件估算的准确性也影响等值查询对非空值的概率修正avg_width该列的平均字节宽度估算扫描和处理行的内存、CPU 开销n_distinct该列去重值的估算数量负数表示去重值数/总行数的比率影响 GROUP BY、DISTINCT、JOIN 后去重行数的估算most_common_vals最常见值的列表即 MCV等值匹配和 IN 条件首选查这里高频值命中时准确度极高most_common_freqs与 MCV 对应的出现频率和 MCV 搭配表示每个高频值的占比histogram_bounds直方图桶边界用于描述非高频值的分布范围查询, , BETWEEN主要靠它估算选择率correlation物理存储顺序与逻辑值顺序的相关系数越接近 1 或 -1说明数据顺序性好索引扫描成本估算越乐观MCV 和直方图是一种互补关系。PG 会把出现次数最多的若干值单独存进 MCV 列表精确记录它们的频率剩下那些没那么常见的值则被分桶放进直方图里。等值查询命中 MCV 时估算通常非常准范围查询在直方图上插值也能得到还不错的选择率。但这两者都有一个共同问题如果数据分布发生了剧烈变化而 ANALYZE 没有及时重跑桶和 MCV 就全不准了。2.3 ANALYZE 的采样逻辑以及它为什么偶尔会看不清ANALYZE 不是把整张表逐行读一遍再做精确统计PG 采用的是随机块采样先随机抽一批数据页再从这些页里抽样行。采样规模受 default_statistics_target 控制默认值是 100意思是 MCV 最多存 100 个值直方图也最多有 100 个桶整体采样量会随这个参数增大而增大。这样设计的原因很实际一张 10 亿行的表如果每次都全量扫描来分析ANALYZE 本身就会变成慢查询谁也受不了。但采样策略也带来一个副作用对超大表来说如果某段时间插入或更新的数据占比很小抽样很可能根本抽不到新数据生成的统计信息反映的还是旧分布。我碰到过一张 5000 万行的日志表某一天突然有业务把某列的状态从 0 改成了 2改动了 200 万行相对总量只有 4%。自动分析触发之前如果连续有查询进来优化器就会用旧统计估算表现就是执行计划明显偏离正常。理解采样逻辑之后你就能明白为什么定期手动 ANALYZE 关键表这点这么重要完全指望自动任务是有窗口期的。3. 统计失真以后优化器是怎么把查询一步步带偏的3.1 等值选择率偏差该走索引的查询变成全表扫描等值条件是最常见的情况优化器处理 WHERE status done 这类过滤时大致顺序是如果 status 出现在 MCV 列表里直接用对应频率没出现在 MCV 里就在直方图上取一个桶的比例作为近似频率。因为 MCV 只记录 top 值如果一个值原来是低频值后来变成了绝对主流但统计没跟上优化器就会以为过滤后只有几千行于是高高兴兴走索引扫描。我遇到过一条典型的 SQL订单状态从 A 批量改成 B 之后SELECT 很多个字段WHERE status B。旧统计里 B 出现频率只有 0.2%优化器评估后觉得走 B 字段索引最划算。但实际 B 已经占了 85%索引扫描加回表要做数百万次随机IO比全表扫描慢得多。如果当时优化器知道 B 是高频值它压根不会选这条路线。所以别小看 MCV 列表和频率它们直接影响索引可用性。3.2 Join 基数误判嵌套循环的灾难现场统计信息失真最夸张的后果集中在 JOIN 计划上。嵌套循环适合外表小、内表探测快的场景哈希连接适合两边都大、一次性建哈希表的场景归并连接则适合两边都已排序或能省掉排序的情况。优化器选哪一种核心依据就是 JOIN 后预计的行数基数。用个具体例子A 表 100 万行B 表 200 万行SQL 是 A JOIN B ON B.aid A.id WHERE B.status done。如果 B.status 的 MCV 显示 done 只占 5%优化器会认为内表经过过滤后只剩 10 万行哪怕外表有 100 万行它也敢选嵌套循环因为觉得每次探测内表都很快。可实际 done 占 45%内表匹配行数膨胀到 90 万嵌套循环要对 B 的索引做上百万次探测单次再快也扛不住总量。这类案例从 EXPLAIN 输出里看特别明显rows 那栏写着几十万actual rows 却有上千万差距直接暴露统计失真。3.3 NULL值和大范围数据直方图覆盖不到的暗角还有一个常被忽略的失真点NULL 与直方图的关系。pg_stats 里 null_frac 单独记录 NULL 比例直方图只描述非 NULL 且非 MCV 的值。如果某列大量为 NULLIS NULL 查询还能通过 null_frac 估算得比较准但一旦和别的条件组合比如多个 IS NOT NULL 条件叠加选择率就会被连续相乘误差可能被指数级放大。另外直方图本身也有短板。它是一种均匀化近似遇到高度倾斜但又不算高频的值时单个桶覆盖的选择率可能和桶内真实分布差异很大。举个例子某个值分布在全表的 1% 桶里但桶内总共有 99% 的数据优化器会按桶均值估算和真实情况差很多倍。这种问题光调 default_statistics_target 不一定能彻底解决还得靠 MCV 或扩展统计帮忙。3.4 多列条件之间的关联性单列统计的铁索连船PostgreSQL 默认假设各列是相互独立的。从概率上讲WHERE country 某国 AND city 某城 的选择率等于两个单列选择率的乘积。听起来合理但如果 country 和 city 存在明显的函数依赖关系比如某国的城市组合只在特定范围内出现这个乘法的结果就会远低于真实选择率导致估算行数被严重低估。联合条件造成低估以后优化器可能低估了数据的规模选择小号的哈希表或者更激进的并行计划最终反而放大执行成本。更常见的现象是 GROUP BY 多列时PG 用各列去重数的乘积来估组数实际因为有依赖组数远小于乘积估算值虚高内存参数和聚合策略跟着走样。这个多列相关性问题单靠传统的单列统计是无解的这也是为什么后续版本推出了扩展统计对象。4. 把统计信息拉回正轨手动校准和自动维护双轨跑4.1 ANALYZE 命令的常规用法与新手容易踩的坑手动校准最直接的方式就是跑 ANALYZE。只分析一张表写 ANALYZE table_name分析整个库直接 ANALYZE想顺带清理死元组就 VACUUM ANALYZE table_name。需要注意ANALYZE 拿的是 SHARE UPDATE EXCLUSIVE 锁日常 DML 不受影响但 DDL 类操作会被阻塞所以核心业务高峰期还是要谨慎。我见过不少新手上来就对全库执行 ANALYZE结果大表多的时候这个命令可能跑上好几分钟期间还占资源。更合理的做法是只针对统计信息可疑的表做或者至少避开业务峰值。另外有个细节容易忽略大批量 DELETE 或 UPDATE 之后哪怕表里行的总量变化不大列的分布也可能已经面目全非这时候不要犹豫直接 VACUUM ANALYZE 一张表比等 autovacuum 自动兜底要靠谱得多。4.2 autovacuum 和 autoanalyze让数据库自己干活生产环境不可能全靠人手去刷统计PG 自带的 autovacuum 会自动触发 vacuum 和 analyze。相关参数有四个autovacuum_vacuum_threshold 默认 50autovacuum_analyze_threshold 默认 50autovacuum_vacuum_scale_factor 默认 0.2autovacuum_analyze_scale_factor 默认 0.1。大致触发逻辑是当表上被修改的行数超过阈值 比例 × 当前行数时后台进程就会启动对应操作。这个机制平时很好用但有两个窗口期要注意。第一修改是持续累积的如果业务流量不够大修改量还没过阈值统计就一直停留在旧状态某些低频更新表非常容易中招第二大批量任务经常在极短时间内完成修改等 autovacuum 真正跑起来可能已经是几分钟甚至更久以后中间进来的查询就会吃到过期统计。所以 autovacuum 是兜底不是万能。4.3 default_statistics_target 调到多少才合适前面说过default_statistics_target 默认是 100。对大多数表来说这个值够用了。但碰到高基数、分布极度倾斜、或者经常用于等值过滤的列100 个 MCV 和 100 个桶可能不够细。我习惯把报表库中某些核心维度列的统计目标单独调大比如 ALTER TABLE ... ALTER COLUMN status SET STATISTICS 500然后再 ANALYZE。调大之后最直接的变化是 ANALYZE 变慢、pg_statistic 占用空间变大。我实测过一张 2000 万行的表统计目标从 100 提到 1000ANALYZE 耗时从 6 秒涨到 23 秒磁盘占用从十几兆涨到几十兆但某些范围的估算误差从 20 倍降到 3 倍以内。这个性价比要看具体场景不要全库无脑调大只针对查询热点列做精细化调整收益会好很多。4.4 校准效果怎么验证EXPLAIN 里的两个数字校准有没有效果别靠感觉用 EXPLAIN 看两个数字估算行数rows和实际行数actual rows。EXPLAIN ANALYZE 会真正执行 SQL所以生产环境要谨慎可以先在从库或者把查询条件换成 10 的变体来试。怎么看偏差估算行数和实际行数相差一个数量级以内通常问题不大相差三个数量级以上基本可以断定统计严重失真。一个小技巧EXPLAIN (ANALYZE, BUFFERS) 还能看到实际读了多少块缓存这有助于区分估算行数不准和成本参数配置不合理。比如实际返回的行数和估算差不多但优化器死活走全表那是成本参数的问题而不是统计信息的锅。这个区分很关键否则你会白调半天统计。5. 常规统计覆盖不到的区域交给扩展统计5.1 多列依赖统计dependencies 修正组合条件的低估多列相关性这一块PostgreSQL 提供了 CREATE STATISTICS 扩展统计对象。最常见的类型是 dependencies用来发现列与列之间的函数依赖关系。用法很简单CREATE STATISTICS st_users_city_dep ON country, city FROM users; 然后 ANALYZE users。之后优化器在遇到同表的多个列过滤条件时会参考依赖程度不再傻傻地把单列选择率相乘。这套机制对国家-城市这类天然有依赖的字段格外有效。建完依赖统计后我之前那个组合条件的行数估算可以从低估 200 倍缩小到基本贴合实际。但要记住扩展统计对象也依赖 ANALYZE 来写入数据所以新建统计对象之后必须手动 ANALYZE 一次否则它不会生效。5.2 ndistinct多列 GROUP BY 的唯一值估算救星第二种扩展统计类型是 ndistinct它专门估算多列组合的去重值数量。比如 GROUP BY a, b如果 a 和 b 完全不相关去重数大约是 a 的去重数乘 b 的去重数如果相关性强实际去重数会小很多甚至接近单列去重数。默认情况下 PG 按乘积估算GROUP BY 之后如果实际组数远小于估算组数内存预分配和排序策略都会被误导。William? 不回到正题。CREATE STATISTICS st_ex_nd ON a, b FROM t 配合 ANALYZE就可以让优化器直接用组合去重数。我在一个商品属性表上验证过效果单看每一列去重数都是几千组合去重数实际只有一万出头但默认估算会把它们乘到几百万优化器差点选了一个非常夸张的聚合计划。建完 ndistinct 统计之后计划才回到合理范围。5.3 表达式统计函数包装列不再是统计盲区另一种常见失真场景是 WHERE lower(email) xx 这类函数表达式。优化器无法直接使用 email 列上的普通统计来估算 lower(email) 的选择率只得按默认的固定比例猜结果通常很糟糕。PostgreSQL 14 开始支持为表达式建扩展统计直接写 CREATE STATISTICS st_expr ON (lower(email)) FROM users再 ANALYZE 一次就可以为这个表达式本身收集 MCV、直方图等信息。如果你的版本还没到 14或者表达式很复杂一个绕开办法是建生成列把 lower(email) 物化成一列再在这列上建索引和统计。不过生成列会占用额外的存储空间每次写入都有计算成本是否值得要结合写入频率判断。表达式统计现在最大的限制是查询里的表达式必须和统计定义里的表达式写法一致稍有不同就匹配不上比如大小写、多余括号都不行。5.4 扩展统计的边界不要看见什么列就想建扩展统计不是越多越好。每建一个统计对象ANALYZE 时都要额外采样计算pg_statistic_ext 表也会增长同理不是所有列组合都值得建我只建议给两类列建一是高频出现在 WHERE/GROUP BY 的组合维度二是已知有条件依赖关系或者组合去重数严重偏离乘积的字段。另外扩展统计对象并不能替代单列统计。它的定位是补充层优化器对单列条件的估算仍然由 pg_statistic 负责。碰上组合爆炸的场景比如一次查询带了 8 个条件你不可能穷举所有组合建扩展统计。真到那一步更该思考是不是查询模式本身可以精简而不是继续堆统计信息。6. 一次完整的慢查询排查链路照着做就行6.1 用 pg_stat_statements 快速揪出可疑SQL排查慢查询我一般先开 pg_stat_statements 扩展它默认不算常见插件但一旦启用就能持续记录每条 SQL 的总执行时间、调用次数、平均耗时、缓冲区命中情况。定位问题快的 SQL一条语句搞定SELECT calls, mean_exec_time, max_exec_time, rows, round(100 * mean_exec_time / sum(mean_exec_time) OVER (), 2) AS time_pct FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;拿到可疑 SQL 之后照着原文去分析它的 WHERE、JOIN 条件涉及哪些表决定是不是要进入下一步。这一步主要是缩小范围省得漫无目的地翻日志。6.2 EXPLAIN ANALYZE 里对比估的和实的第二步是给可疑 SQL 套一层 EXPLAIN ANALYZE重点看每个节点上的两行数字。下面是我处理过的模拟输出截取其中一个 Scan 节点Seq Scan on orders (cost0.00..312458.20 rows4800000 width42) (actual time0.523..4520.188 rows4803955 loops1) Filter: (status done::text) Rows Removed by Filter: 27000这里估算 480 万实际也是 480 万反而说明当前这个节点是准的。真正要警惕的是中间层节点上估算 800、实际 480 万这种割裂。一旦发现某个节点估算和实际差了两三个数量级就去定位它扫描的是哪张表、过滤条件是什么字段然后直奔 pg_stats。6.3 翻 pg_stats 定位失真类型第三步不是瞎猜直接查 pg_stats 里对应表、列的那一行SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE schemaname public AND tablename orders AND attname status;如果 most_common_vals 里根本没有 done而 most_common_freqs 中最高的那个频率也只是 0.05同时你确认线上 done 已经占了 85%那就是典型的 MCV 过期。如果 n_distinct 显示是 -1表示全部唯一但你实际知道这列只有三个枚举值那显然也是统计信息和现实脱节。定位到这一步问题类型基本清晰了。6.4 修正、复测、回滚一套动作一气呵成修正动作按优先级来轻量级是直接 ANALYZE 对应表如果 ANALYZE 之后还是不准再考虑调大列的 STATISTICS 目标要是多列组合条件就建扩展统计。每一次修正后跑一次 EXPLAIN ANALYZE对比同一节点的估算行数、实际行数和总耗时。阶段估算行数实际行数执行时间修复前80048000003m40sANALYZE后4200000480000028s调目标扩展统计后4600000480000019s还要留意一个问题如果 SQL 是通过 PreparedStatement 或服务端预处理跑高频查询PG 在第一次执行时生成的计划可能被缓存复用。也就是说哪怕你修好了统计旧计划还会顶一阵子。这时候要么在测试环境重连会话要么执行 PREPARE 重建计划别误以为统计修正无效。6.5 容易忽略的边界场景记录有几个边角场景我想单独列出来因为踩过不止一次。第一个是并行查询EXPLAIN 里估算行数看着准确但并行 worker 数量选择错误导致并行度不足或过度这种问题根源往往也在统计信息里的行数和页数关系。第二个是数据分区的场景分区表的统计需要分别分析每个分区父表的统计信息是汇总层面的如果只 ANALYZE 父表子分区的统计可能依然陈旧。第三个是模拟环境验证的坑。很多团队喜欢在测试库复现生产慢查询但如果测试库的数据量和分布跟生产完全不一样统计信息自然不同复现出来的执行计划也可能完全不同。我在测试库上见过一条 SQL 好好的一上生产就慢最后发现原因就是测试库的某张表压根没有 ANALYZE 过。所以做这类验证之前先确认源头表的数据规模和分布别让错误复现把结论带偏。这段时间处理下来我最大的体会是PostgreSQL 的统计信息看着是个小东西平时不声不响但它一旦失真能让一条不错的 SQL 跑得比全表扫描还难看。排查慢查询的时候别先把锅扣在代码和索引头上养成先看 EXPLAIN 估算、再翻 pg_stats 的习惯很多问题五分钟就能找到方向。我自己现在做数据库巡检会把近期是否发生过大批量数据变更、对应表是否及时 ANALYZE写进固定检查项这比等慢查询报警出来再救火要省事得多。