4. 统计信息与优化器

PostgreSQL 的优化器是基于代价的优化器（Cost-Based Optimizer，CBO）。它并不真正执行 SQL，而是先对每个候选执行计划估算代价，再挑选代价最低的那个。估算代价的核心依据，就是统计信息。如果统计信息失真，优化器就会选错连接方式、选错索引、误判返回行数，最终表现为“SQL 没变、数据没变，但突然变慢了”。理解统计信息的收集机制与偏差来源，是性能优化的基本功。

一、统计信息从哪里来：ANALYZE 与自动收集

统计信息由 ANALYZE 命令生成。它并不扫描全表，而是对表进行随机采样，默认采样目标为 30000 行（由 default_statistics_target 控制，默认值 100，采样行数约为 300 × target）。采样后，PostgreSQL 会计算每列的：

• 空值比例（null_frac）：该列 NULL 值占比；
• 平均宽度（avg_width）：用于估算行宽与内存；
• n_distinct：不同值的个数，正数表示绝对值，负数表示占表行数的比例；
• MCV 列表（most_common_vals / most_common_freqs）：出现频率最高的若干值及其频率；
• 直方图（histogram_bounds）：把剩余数据按等频划分成若干桶，用于估算范围条件的选择率。

自动收集由 autovacuum 的 analyze 子进程完成。触发条件是：表中被修改的行数超过 autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × 表行数（默认 50 + 0.1 × 行数）。例如一张 100 万行的表，需要约 10 万行发生变化才会触发自动 ANALYZE。这意味着大批量导入或批量更新后，统计信息可能长时间不更新，这是最常见的偏差来源之一。

二、pg_statistic 与 pg_stats：存储与查看

统计信息物理上存放在系统目录 pg_statistic 中，但该表使用 anyarray 等类型存储，普通用户无法直接读取。因此 PostgreSQL 提供了视图 pg_stats，把每列统计信息展开成可读的行。常用查询如下：

-- 查看某张表所有列的统计信息
SELECT schemaname, tablename, attname,
       null_frac, n_distinct, avg_width,
       most_common_vals, most_common_freqs,
       histogram_bounds
FROM pg_stats
WHERE tablename = 'orders';

-- 查看某列的选择率估算
SELECT attname, n_distinct,
       array_length(most_common_vals, 1) AS mcv_cnt
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

其中 n_distinct 的符号很关键：正值表示不同值的绝对个数，负值表示不同值个数与表行数的比值。例如 n_distinct = -0.01 表示不同值约为表行数的 1%。当表持续增长时，负值形式能让估算自动适应行数变化，这也是 PostgreSQL 对高基数列常用的表示方式。

三、统计目标调整：default_statistics_target 与列级覆盖

统计目标决定 MCV 列表长度和直方图桶数。目标越大，统计越精细，但 ANALYZE 越慢、pg_statistic 越大。默认 100 对多数场景够用，但对数据分布倾斜严重或范围查询频繁的列，往往需要提高。调整方式有两种：

• 全局：修改 postgresql.conf 中的 default_statistics_target，影响所有列；
• 列级：ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500;，只影响指定列，更推荐。

-- 只提高 orders.status 与 orders.created_at 的统计精度
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ALTER TABLE orders ALTER COLUMN created_at SET STATISTICS 500;

-- 立即重新收集
ANALYZE orders (status, created_at);

注意：SET STATISTICS 只改变下次 ANALYZE 的采样量，必须重新执行 ANALYZE 才生效。此外，对多列相关性强的场景（如 city 与 postcode），单列统计无法表达联合分布，需要创建扩展统计信息：

CREATE STATISTICS orders_city_zip (dependencies, ndistinct)
ON city, postcode FROM orders;
ANALYZE orders;

四、常见估算偏差案例

下面这张图展示了统计信息从采集到影响执行计划的完整链路，以及四类典型偏差的触发点：

下面逐一说明这四类偏差的识别与处理。

案例一：批量导入后未 ANALYZE。假设 orders 表原本 10 万行，某次批量导入 200 万行后立即查询。此时统计信息仍按 10 万行估算，优化器认为表很小，可能选择 Nested Loop 而不是 Hash Join。用 EXPLAIN (ANALYZE, BUFFERS) 对比 rows= 估算值与 actual rows= 实际值，若相差一个数量级以上，基本可判定统计信息过期。处理方式：立即执行 ANALYZE orders;，并在批量导入脚本末尾固定加上 ANALYZE。

案例二：数据倾斜且 MCV 不足。例如 status 列中 95% 的行是 'DONE'，其余为其他状态。若统计目标过低，MCV 列表可能装不下 'DONE'，优化器就会按平均选择率估算，导致 WHERE status='DONE' 的返回行数被严重低估。处理方式：ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500; 后重新 ANALYZE，让热点值进入 MCV。

案例三：多列相关性缺失。当查询同时过滤 city='杭州' AND postcode='310000' 时，优化器默认两列独立，选择率相乘。但现实中杭州与 310000 高度相关，实际返回行数远小于估算值。此时应创建扩展统计信息：CREATE STATISTICS ... (dependencies, ndistinct) ON city, postcode FROM orders;，让优化器感知列间依赖。

案例四：表达式或函数条件。形如 WHERE date(created_at) = '2024-01-01' 的写法，统计信息针对的是 created_at 原始列，无法直接匹配函数结果，优化器只能套用默认选择率（通常 0.5%）。改写为 WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02' 即可让直方图发挥作用；若无法改写，可考虑建立表达式索引。

总结来看，统计信息是优化器判断的“眼睛”。日常运维中应养成三个习惯：大批量数据变更后主动 ANALYZE；对倾斜列和范围查询列设置合适的列级统计目标；对多列相关条件使用扩展统计信息。当执行计划出现异常时，先看 rows 与 actual rows 的偏差，再回到 pg_stats 定位问题列，往往比直接调整 SQL 更快找到根因。