PHP小码农

Appearance

扩展统计信息（CREATE STATISTICS） ​

单列统计能精确描述「每一列自己的分布」，但它有一个根本性的前提假设：不同列之间是相互独立的。规划器估算 WHERE a = 1 AND b = 1 时，会把两个条件的选择性直接相乘。

当列与列之间存在相关性时（例如「城市」与「邮编」、「月份」与「日期」），这个乘法会产生极其离谱的估算。扩展统计信息（extended statistics）就是用来打破「独立假设」的工具：它让 ANALYZE 额外收集多列联合的分布信息，供规划器在做多条件估算时修正误差。

本节讲清三种扩展统计类型（dependencies、ndistinct、mcv）的原理、语法、适用场景与代价，并通过官方示例完整复现「建统计对象前 vs 建统计对象后」的估算变化。

前置知识

阅读本节前，建议先了解：直方图与相关性统计

基础概念 ​

问题从哪来：独立假设 ​

单列统计只描述「一列」的分布。当 WHERE 里出现两个及以上条件时，规划器没有关于它们「共同出现」的信息，只能假设：

text
P(A AND B) = P(A) × P(B)      ← 独立事件的概率乘法

只要 A、B 有相关性，这个等式就不成立。实际数据里相关性非常常见：

场景列 A列 B相关性来源
地址表cityzip邮编几乎决定城市
订单表statuspaid_at未支付订单 paid_at 必为 NULL
日志表levelmessageERROR 级别的消息集中在一小撮文本
时间序列date_trunc('month', t)date_trunc('day', t)前者完全由后者决定

独立假设导致的两种错误 ​

text
表 t：10000 行，a 与 b 完全相关（a = b = i % 100）

查询：SELECT * FROM t WHERE a = 1 AND b = 1

单列统计告诉我们：
  a = 1 的选择性 = 1/100 = 0.01    （100 个不同值均匀分布）
  b = 1 的选择性 = 1/100 = 0.01

规划器假设独立 → 相乘：
  0.01 × 0.01 = 0.0001
  估算行数 = 10000 × 0.0001 = 1 行    ← 严重低估（实际 100 行）

而另一个查询：
  SELECT * FROM t WHERE a = 1 AND b = 10

  同样估算 1 行，甚至可能估算成"接近 1 行"
  但实际是 0 行 —— 因为 (1, 10) 这个组合根本不存在
  单列统计无法知道这一点

低估行数会让规划器误以为结果集很小，从而错误地选择 Nested Loop、选择索引扫描；无法识别零结果组合则会浪费大量 I/O 去扫描一个必然为空的组合。

三种扩展统计类型 ​

PostgreSQL 提供三种多列统计，各自的思路与代价不同：

statistics_kind记录的内容解决的问题代价
dependencies列与列之间的函数依赖系数AND 条件相乘导致低估最低（只存几个系数）
ndistinct多列组合的去重值个数GROUP BY a, b 组数估算错误低（只存计数）
mcv多列最常用值组合的列表既能修低估，也能识别不兼容组合、支持范围条件最高（存实际值）

三者的精度与开销成正比：dependencies 最便宜但限制最多，mcv 最贵但能力最强。

单列表达式统计 ​

CREATE STATISTICS 还有另一种形式，专门给表达式建立单列统计。表达式（如 date_trunc('month', a)）本身没有列统计，规划器只能用默认估算值（等值条件 0.5%、范围条件 0.5%、n_distinct 假设与列一样）。

sql
-- 只有单个表达式时，不允许指定 statistics_kind
CREATE STATISTICS s_expr ON (date_trunc('month', a)) FROM t3;

这种统计的作用类似「表达式索引」，但不需要维护索引本身，代价小得多。

统计对象是独立的数据库对象 ​

一个常见的误解是「扩展统计是表的附属品」。实际上：

text
pg_statistic_ext          ← 定义（哪些列、哪种 kind），CREATE STATISTICS 时创建
      │  oid = stxoid
      ▼
pg_statistic_ext_data     ← 数据（ANALYZE 时填充）

统计对象的所有权与表相互独立：创建统计对象需要是表的 owner，但创建之后，谁拥有统计对象、谁拥有表可以不同。

术语表 ​

术语含义
扩展统计 / 统计对象（statistics object）CREATE STATISTICS 创建的目录对象
函数依赖（functional dependency）由 A 的值可以唯一确定 B 的值
多变量统计（multivariate statistics）跨多个列的统计，即扩展统计
单变量表达式统计针对单个表达式的统计，类似无索引开销的表达式索引
基础频率（base frequency）按单列频率相乘算出的「假如独立」的频率

语法与用法 ​

创建扩展统计：两种形式 ​

sql
-- 形式一：单表达式统计（不能指定 statistics_kind）
CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]
    ON ( expression )
    FROM table_name

-- 形式二：多列 / 多表达式统计（可选指定 statistics_kind）
CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]
    [ ( statistics_kind [, ... ] ) ]
    ON { column_name | ( expression ) }, { column_name | ( expression ) } [, ...]
    FROM table_name

形式一用于给单个表达式收集统计，好处类似表达式索引但无需维护索引。形式二用于多列（每列可以是列名，也可以是括号包起来的表达式），并且会自动为列表中的每个表达式额外收集单变量统计。

statistics_kind 参数 ​

取值作用
ndistinct收集多列组合的去重值个数
dependencies收集函数依赖统计
mcv收集多列最常用值列表

省略 statistics_kind 时，全部三种都会被包含。如果只关心某一种，显式写出可以显著降低 ANALYZE 与规划开销。

ON 列表的规则 ​

sql
-- 合法：两个列名
CREATE STATISTICS stts (dependencies) ON city, state FROM zipcodes;

-- 合法：列名与表达式混用
CREATE STATISTICS s3 (ndistinct)
    ON date_trunc('month', a), date_trunc('day', a) FROM t3;

-- 非法：多变量形式至少需要两个条目
CREATE STATISTICS bad ON a FROM t;   -- 错误！多变量统计至少要两个列/表达式

多变量形式下至少要有两个列名或表达式，并且顺序不重要（ON a, b 与 ON b, a 等价）。

IF NOT EXISTS ​

sql
CREATE STATISTICS IF NOT EXISTS stts (dependencies) ON a, b FROM t;

指定 IF NOT EXISTS 时必须给出 statistics_name。注意它只比对名字、不比对定义细节——如果同名对象已存在但定义不同，命令不会报错也不会修改它，只发一条 notice。

修改与删除 ​

sql
-- 重命名
ALTER STATISTICS stts RENAME TO stts_zip;

-- 改所有者
ALTER STATISTICS stts OWNER TO app_owner;

-- 移到别的 schema（需要新 schema 的 CREATE 权限）
ALTER STATISTICS stts SET SCHEMA reporting;

-- 调整统计目标（范围 0 ~ 10000，DEFAULT 表示恢复系统默认值）
ALTER STATISTICS stts SET STATISTICS 500;

-- 删除
DROP STATISTICS stts;

-- 删除（不存在时不报错）
DROP STATISTICS IF EXISTS stts;

ALTER STATISTICS ... SET STATISTICS 影响的是该统计对象后续 ANALYZE 的采样目标，范围 0 到 10000。设为 DEFAULT 恢复系统默认值（即 default_statistics_target）。

查看扩展统计 ​

sql
-- 查看某个统计对象的依赖系数（需要 join 两个目录）
SELECT stxname, stxkeys, stxddependencies
FROM pg_statistic_ext
JOIN pg_statistic_ext_data ON (oid = stxoid)
WHERE stxname = 'stts';

-- 查看 n-distinct 组合计数
SELECT stxkeys AS k, stxdndistinct AS nd
FROM pg_statistic_ext
JOIN pg_statistic_ext_data ON (oid = stxoid)
WHERE stxname = 'stts2';

-- 查看 MCV 列表（必须用 set-returning 函数展开）
SELECT m.*
FROM pg_statistic_ext
JOIN pg_statistic_ext_data ON (oid = stxoid),
     pg_mcv_list_items(stxdmcv) m
WHERE stxname = 'stts3';

pg_stats_ext 视图把这些目录整合成了更易读的形式，且只显示当前用户拥有的表上的统计对象，因此可以安全地开放给普通用户读。

通过 pg_stats_ext 查看 ​

sql
SELECT statistics_name,
       attnames,
       exprs,
       kinds,
       n_distinct,
       dependencies
FROM pg_stats_ext
WHERE tablename = 'zipcodes';

pg_stats_ext 的列包括 schemaname、tablename、statistics_schemaname、statistics_name、statistics_owner、attnames、exprs、kinds、inherited、n_distinct、dependencies、most_common_vals、most_common_val_nulls、most_common_freqs、most_common_base_freqs。

生效时机 ​

text
CREATE STATISTICS   →  只创建目录条目（表达"我想收集这些统计"）
                         此时没有任何实际数据
ANALYZE             →  才真正采样、计算并写入 pg_statistic_ext_data

建完统计对象必须重新 ANALYZE（或等 autovacuum 自动分析触发）才会生效。改了 SET STATISTICS 目标值之后同样需要重新 ANALYZE。

详细说明 ​

函数依赖：系数的含义 ​

函数依赖统计存下来的是一组「方向性系数」，用 column number 表示：

sql
CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes;
ANALYZE zipcodes;

SELECT stxname, stxkeys, stxddependencies
FROM pg_statistic_ext
JOIN pg_statistic_ext_data ON (oid = stxoid)
WHERE stxname = 'stts';
text
 stxname | stxkeys |              stxddependencies
---------+---------+---------------------------------------------
 stts    | 1 5     | {"1 => 5": 1.000000, "5 => 1": 0.423130}
(1 row)

读法：

  • stxkeys = 1 5 表示统计对象覆盖表的第 1 列与第 5 列。
  • "1 => 5": 1.000000 表示「第 1 列完全决定第 5 列」——知道邮编就知道城市，系数 1.0。
  • "5 => 1": 0.423130 表示「第 5 列只能决定第 1 列的约 42%」——一个城市可能对应多个邮编，约 58% 的城市不止一个邮编。

系数越高，两个条件越"冗余"。当条件冗余时，规划器就不会再把选择性相乘，而是只保留其中最"紧"的那一个。

函数依赖的修正过程 ​

text
查询：SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';

没有依赖统计：
  选择性(city) × 选择性(zip)   → 严重低估

有依赖统计（zip 决定 city，系数 1.0）：
  规划器识别出 city 条件不改变选择性 → 忽略它，只用 zip 的选择性
  → 估算恢复正确

函数依赖的四项限制 ​

函数依赖是三种统计里最便宜的一种，但限制也最多。它只适用于：

  1. 简单的等值条件：列 = 常量，例如 city = 'X'。
  2. IN 子句：且 IN 列表里必须是常量。

它不适用于：

  1. 两列相互比较：WHERE a = b。
  2. 列与表达式比较：WHERE a = f(b)。
  3. 范围条件：WHERE a < 10 AND b > 5。
  4. LIKE 及其他条件类型。

超出这些范围时，dependencies 统计不会改善估算。

函数依赖的"兼容假设"陷阱 ​

用函数依赖估算时，规划器默认条件之间是兼容的（即冗余的）。如果条件实际不兼容，正确结果应是 0 行，但规划器不会考虑这种可能：

sql
-- 这个查询里 city 和 zip 是兼容的，忽略 city 条件是正确的
SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';

-- 但这个查询实际返回 0 行，规划器依然会忽略 city 条件
SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';

因为函数依赖统计只记录列级别的依赖关系，不记录具体值之间的组合，所以它没有能力判断「San Francisco 与 90210」这个组合是否存在。

实践含义：如果应用层（例如一个联动的下拉框）永远只会产生兼容的组合，这个假设总是成立，dependencies 就很好用；反之，如果用户能自由组合任意城市与邮编，dependencies 就可能在「零结果查询」上给出过于乐观的估算——此时应改用 mcv。

多变量 n-distinct：组数估算 ​

单列统计只存单列的去重值个数。GROUP BY a, b 的组数估算在只有单列统计时经常离谱：

sql
-- 表 t：10000 行，a 与 b 都是 i % 100
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
text
 HashAggregate  (cost=220.00..230.00 rows=1000 width=16) (actual rows=100.00 loops=1)
   Group Key: a, b
   ->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=8) (actual rows=10000.00 loops=1)

估算 1000 组，实际 100 组，差了一个数量级。原因：规划器假设 a、b 独立，于是用容斥公式把两个 100 组合成约 1000。

加上 ndistinct 统计后就准确了：

sql
DROP STATISTICS stts;
CREATE STATISTICS stts (dependencies, ndistinct) ON a, b FROM t;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
text
 HashAggregate  (cost=220.00..221.00 rows=100 width=16) (actual rows=100.00 loops=1)
   Group Key: a, b
   ->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=8) (actual rows=10000.00 loops=1)

ndistinct 会为列集合中所有 "两列及以上" 的组合都收集数据。看一个真实例子：

sql
CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes;
ANALYZE zipcodes;

SELECT stxkeys AS k, stxdndistinct AS nd
FROM pg_statistic_ext
JOIN pg_statistic_ext_data ON (oid = stxoid)
WHERE stxname = 'stts2';
text
-[ RECORD 1 ]------------------------------------------------------
k  | 1 2 5
nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178}

读法："1, 2": 33178 表示「第 1 列与第 2 列的组合有 33178 个不同值」。因为邮编本身唯一，「邮编 + 州」「邮编 + 城市」「邮编 + 城市 + 州」都是 33178；而「城市 + 州」只有 27435 个不同值（同名城市或跨州城市导致重复）。

多变量 MCV：最灵活也最贵 ​

MCV 列表记录多列最常见的值组合。它是单列 MCV 的自然延伸，但代价明显更高：ANALYZE 时构建更慢、占存储更多、规划时也更耗时。

sql
CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes;
ANALYZE zipcodes;

SELECT m.*
FROM pg_statistic_ext
JOIN pg_statistic_ext_data ON (oid = stxoid),
     pg_mcv_list_items(stxdmcv) m
WHERE stxname = 'stts3';
text
 index |       values        | nulls | frequency | base_frequency
-------+---------------------+-------+-----------+----------------
     0 | {Washington, DC}    | {f,f} |  0.003467 |        2.7e-05
     1 | {Apo, AE}           | {f,f} |  0.003067 |        1.9e-05
     2 | {Houston, TX}       | {f,f} |  0.002167 |       0.000133
     3 | {El Paso, TX}       | {f,f} |     0.002 |       0.000113
     4 | {New York, NY}      | {f,f} |  0.001967 |       0.000114
     5 | {Atlanta, GA}       | {f,f} |  0.001633 |        3.3e-05
     6 | {Sacramento, CA}    | {f,f} |  0.001433 |        7.8e-05
     7 | {Miami, FL}         | {f,f} |     0.0014 |         6e-05
     8 | {Dallas, TX}        | {f,f} |  0.001367 |        8.8e-05
     9 | {Chicago, IL}       | {f,f} |  0.001333 |        5.1e-05
 ...
(99 rows)

四个关键列：

列含义
values这个组合的具体值
nulls各列在该组合里是否为 NULL（{f,f} 表示都不为 NULL）
frequency这个组合在采样中实际出现的频率
base_frequency假若两列独立、由单列频率相乘得到的频率

注意第 0 行：实际频率 0.003467（约 0.35%），而基础频率只有 2.7e-05——独立假设低估了两个数量级。这正是 MCV 要修正的偏差。

估算时，规划器把条件应用到 MCV 列表的每一个条目上，然后把匹配条目的频率相加。

MCV 相对函数依赖的两大优势 ​

优势一：能识别不兼容的组合。因为存的是具体值，规划器可以判断某个组合不存在：

sql
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 10;
text
 Seq Scan on t  (cost=0.00..195.00 rows=1 width=8) (actual rows=0.00 loops=1)
   Filter: ((a = 1) AND (b = 10))
   Rows Removed by Filter: 10000

虽然估算仍是 1 行（不是 0），但它给出了组合不存在的上界，比函数依赖完全无视这种可能要好。

优势二：支持更丰富的条件类型，不限于等值：

sql
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a <= 49 AND b > 49;
text
 Seq Scan on t  (cost=0.00..195.00 rows=1 width=8) (actual rows=0.00 loops=1)
   Filter: ((a <= 49) AND (b > 49))
   Rows Removed by Filter: 10000

范围条件也能被 MCV 列表正确处理。

用 dependencies 还是 mcv？ ​

text
列相关性极强（几乎一一对应）、条件全是等值
        ↓
    用 dependencies      ← 便宜、够用

相关性存在例外、需要识别"不存在的组合"、有范围条件
        ↓
    用 mcv               ← 贵，但精确

两者都要 → 同时写 (dependencies, mcv)

如果只是想让 GROUP BY 的组数估算准确，那是 ndistinct 的活，前两者都帮不上忙。

表达式与 ndistinct 的联合：官方 t3 案例 ​

官方用一种很典型的情况演示表达式统计 + ndistinct 的配合：一个 timestamp 列，查询用它按天、按月截断后的值。

sql
CREATE TABLE t3 (a timestamp);

INSERT INTO t3
SELECT i FROM generate_series(
    '2020-01-01'::timestamp,
    '2020-12-31'::timestamp,
    '1 minute'::interval) s(i);

ANALYZE t3;

在没有表达式统计时：

  • 规划器知道 a 的分布，但完全不知道 date_trunc(...) 结果的分布，只能用默认值（等值 0.5%、范围 0.5%）。
  • 而对于 date_trunc('month', a)，规划器会把「去重值个数」按底层列的 n_distinct 来推测，也就是假设它与原列一样是唯一的（因为每行的 a 都不同）——但实际只有 12 个月。
  • 更糟的是它不知道两个表达式有函数依赖关系（月完全由日决定），于是在 GROUP BY 1, 2 时把两者选择性相乘，严重高估组数。
sql
-- 为这一对表达式建立 ndistinct 统计
-- （列表里每个表达式会自动获得单变量统计）
CREATE STATISTICS s3 (ndistinct)
    ON date_trunc('month', a), date_trunc('day', a) FROM t3;

ANALYZE t3;

之后：

  • 等值条件 date_trunc('month', a) = '2020-01-01' 有了真实的表达式统计可用；
  • 范围条件 date_trunc('day', a) BETWEEN ... 也准了；
  • GROUP BY 1, 2 通过 ndistinct 识别出两个表达式相关，组数估算大幅改善。

继承与分区下的 inherited ​

ANALYZE 在统计继承表（含分区表）时会同时记录两套数据：一套包含子表（inherited = t），一套只含父表本身（inherited = f）。pg_stats_ext 的 inherited 列同理。规划器会按查询的实际扫描范围选择对应的一套统计。

现状限制：不用于连接估算 ​

扩展统计目前不参与表连接的 selectivity 估算。 也就是说，WHERE a.x = 1 AND b.y = 2 这种跨表条件，或者在 JOIN 的 ON 条件上，扩展统计不会起作用。它作用于同一张表上的多个条件。

sql
-- 扩展统计能改善这类查询（同一张表的多条件）
SELECT * FROM orders WHERE city = 'X' AND zip = 'Y';

-- 扩展统计暂时帮不上这类查询（跨表连接）
SELECT * FROM orders o JOIN customers c ON o.city = c.city AND o.zip = c.zip;

官方说明这是一个未来版本可能移除的临时限制。

实战示例 ​

场景一：官方 t1 —— 函数依赖修复低估 ​

sql
-- 建表：a 与 b 存在函数依赖（a 决定 b）
CREATE TABLE t1 (a int, b int);

INSERT INTO t1
SELECT i/100, i/500
FROM generate_series(1,1000000) s(i);

ANALYZE t1;

建统计对象之前：

sql
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);

规划器假设两个条件独立，把选择性相乘，得到远小于实际的行数估算。因为 a = 1 与 b = 0 其实强烈相关（a = 1 时 b 只能是 0），实际匹配行数远多于相乘的结果。

sql
-- 建立函数依赖统计
CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;

ANALYZE t1;   -- 必须重新分析，统计才生效

EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
text
-- 建统计对象后：规划器识别出两个条件是冗余的，不再相乘
-- rows 估算显著向实际值靠拢

关键动作：CREATE STATISTICS 之后必须 ANALYZE，否则目录里只是空壳。

场景二：官方 t2 —— MCV 列表处理相关列与不合组合 ​

sql
-- 建表：a 与 b 完全相关（内容完全相同的两列）
CREATE TABLE t2 (a int, b int);

INSERT INTO t2
SELECT mod(i,100), mod(i,100)
FROM generate_series(1,1000000) s(i);

CREATE STATISTICS s2 (mcv) ON a, b FROM t2;

ANALYZE t2;

有效组合（存在于 MCV 中）：

sql
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);
text
-- 组合 (1, 1) 在 MCV 列表里，规划器能拿到它的真实频率
-- 估算接近实际行数

无效组合（不在 MCV 中）：

sql
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);
text
-- (1, 2) 不存在，MCV 列表让规划器知道该组合的频率上界很低
-- 避免了对"必然为空"的查询给出高估算

对比含义：如果改用 dependencies，第二种查询无法被正确处理——函数依赖只看到「a、b 完全相关」就假设条件冗余，从而给出乐观估算。mcv 因为存了具体值，能区分「兼容」与「不兼容」。

场景三：一次完整的排障流程 ​

假设线上有一个慢查询，估算与实际差几十倍：

sql
-- 步骤 1：观察估算偏差
EXPLAIN (ANALYZE, BUFFERS) SELECT *
FROM orders
WHERE customer_city = 'Hangzhou' AND customer_zip = '310000';
text
 Seq Scan on orders  (cost=0.00..98210.00 rows=12 width=210)
                     (actual time=0.031..842.117 rows=18420 loops=1)
   Filter: ((customer_city = 'Hangzhou'::text) AND (customer_zip = '310000'::text))
   Rows Removed by Filter: 311580
 Planning Time: 0.412 ms
 Execution Time: 845.203 ms

rows=12（估算）对 rows=18420（实际），低估 1500 倍，典型的多列独立假设问题。

sql
-- 步骤 2：确认单列统计正常
SELECT attname, n_distinct,
       array_length(most_common_vals, 1) AS mcv_cnt
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders'
  AND attname IN ('customer_city', 'customer_zip');

单列统计都正常，说明问题就在「两列的联合分布」上。

sql
-- 步骤 3：建立扩展统计（这里两个条件都是等值，dependencies 就够）
CREATE STATISTICS orders_city_zip (dependencies, mcv)
    ON customer_city, customer_zip FROM orders;

ANALYZE orders;
sql
-- 步骤 4：确认统计已生成
SELECT statistics_name, attnames, kinds, dependencies
FROM pg_stats_ext
WHERE tablename = 'orders';
text
 statistics_name  |         attnames          |        kinds        |         dependencies
------------------+---------------------------+---------------------+------------------------------
 orders_city_zip  | {customer_city,customer_zip} | {d,m}             | {"1 => 2": 0.991000, "2 => 1": 1.000000}
(1 row)

"2 => 1": 1.000000 表示邮编完全决定城市，也就是说城市条件几乎完全冗余。

sql
-- 步骤 5：复测估算
EXPLAIN (ANALYZE, BUFFERS) SELECT *
FROM orders
WHERE customer_city = 'Hangzhou' AND customer_zip = '310000';
text
 Seq Scan on orders  (cost=0.00..98210.00 rows=18100 width=210)
                     (actual time=0.028..806.551 rows=18420 loops=1)
   Filter: ((customer_city = 'Hangzhou'::text) AND (customer_zip = '310000'::text))
   Rows Removed by Filter: 311580
 Planning Time: 0.508 ms
 Execution Time: 809.116 ms

估算从 12 变为 18100，与实际的 18420 只差 1.7%。规划器现在有正确基数依据，才能做出真正正确的计划选择（例如是否走索引、是否用并行）。

场景四：批量巡检哪些表需要扩展统计 ​

sql
-- 找出"同一张表上多个条件经常一起出现"的候选列组
-- 这里用 pg_stat_statements 的查询文本做粗筛（需已启用该扩展）
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
WHERE query ILIKE '% AND %'
  AND query ILIKE '% WHERE %'
ORDER BY total_exec_time DESC
LIMIT 20;

拿到慢查询文本后，识别其中「同一张表上成对出现的等值/范围条件列」，再针对性建统计对象——不要盲目给所有列组合都建。

注意事项 ​

  • CREATE STATISTICS 之后必须重新 ANALYZE：命令本身只创建目录条目，不采集任何数据。此时查看 pg_statistic_ext_data 是空的，估算不会有任何改善。

    sql
    CREATE STATISTICS s (dependencies) ON a, b FROM t;
-- 错误！此时统计尚未生效，必须再执行一次
ANALYZE t;

  • 不要给弱相关或无关的列建统计：官方明确建议只为强相关的列组建 dependencies、只为确实用于分组的组合建 ndistinct、只为确实一起用于条件的组合建 mcv。否则只是白白浪费 ANALYZE 周期与规划时间。

    sql
    -- 不推荐：user_id 与 created_at 基本无关，建了也白建
CREATE STATISTICS useless (dependencies, ndistinct, mcv) ON user_id, created_at FROM logs;

  • dependencies 不能识别不兼容条件：它假设条件总是兼容的。对「零结果查询」会给出过于乐观的估算。需要处理这种情况时改用 mcv。

    sql
    -- dependencies 无法知道这个查询实际返回 0 行
SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';

  • dependencies 只支持等值和 IN：两列比较、列与表达式比较、范围条件、LIKE 都不适用。

    sql
    -- 不推荐依赖 dependencies 来优化这类条件
SELECT * FROM t WHERE a > 100 AND b < 200;   -- 范围条件，dependencies 无效

  • mcv 的存储与规划开销最大：因为要保存实际的组合值。列宽很大（如长文本）时尤其明显。不要在全库无差别地建 mcv。

  • IF NOT EXISTS 只比对名字：同名但定义不同时，命令既不报错也不更新，只发一条 notice。容易造成「以为改了其实没改」。

    sql
    -- 若 stts 已存在且只含 dependencies，下面这条不会把它改成 mcv
CREATE STATISTICS IF NOT EXISTS stts (mcv) ON a, b FROM t;

  • 多变量形式至少需要两个列/表达式：单个列应该用普通列统计（ALTER TABLE ... ALTER COLUMN ... SET STATISTICS），单表达式用 ON (expr) 形式。

  • ALTER STATISTICS ... SET STATISTICS 0 会关闭该统计对象的收集：目标设为 0 后，后续 ANALYZE 不再为它计算数据。

  • 表达式统计与表达式索引不是一回事：表达式统计让估算更准，但不能让查询走索引。要使 WHERE date_trunc('month', a) = ... 走索引，仍然需要建表达式索引。

    sql
    -- 统计只改善估算，这条仍会全表扫描，除非另有表达式索引
CREATE STATISTICS s ON (date_trunc('month', a)) FROM t3;

  • 扩展统计不用于表连接估算：跨表的多条件估算不会用到扩展统计，只对单表多条件有效。

  • 统计对象的所有权与表独立：建完之后可以把统计对象转给别人，或挪到别的 schema，不影响它继续服务于原表。但创建统计对象时必须拥有该表。

  • 修改统计定义后需要重新 ANALYZE：ALTER STATISTICS ... SET STATISTICS、改动列列表（需先 DROP 再 CREATE）之后，都要重新分析才生效。

最佳实践 ​

  • 先确认问题真的是"多列相关"再动手：用 EXPLAIN (ANALYZE) 对比估算与实际。只有同一张表上多个条件的估算严重偏离时，扩展统计才是解药；单列估算偏差要靠 SET STATISTICS，连接估算偏差要靠其他手段。

  • 条件全是等值、相关性极强 → 优先 dependencies：最便宜，往往就够用。

    sql
    CREATE STATISTICS orders_city_zip (dependencies)
    ON customer_city, customer_zip FROM orders;

  • 需要识别"不存在的组合"或有范围条件 → 用 mcv：

    sql
    CREATE STATISTICS orders_city_zip (mcv)
    ON customer_city, customer_zip FROM orders;

  • GROUP BY 组数估算错误 → 用 ndistinct：

    sql
    CREATE STATISTICS sales_group (ndistinct)
    ON region, product_category, sale_month FROM sales;

  • 不确定就用全量，但只在确认有价值的列组上：省略 statistics_kind 会包含三种。对关键列组可以这么写，但不要撒网式滥用。

    sql
    CREATE STATISTICS orders_city_zip ON customer_city, customer_zip FROM orders;

  • 建完后立刻 ANALYZE 并复测：把「建统计 → 分析 → 复测 EXPLAIN」做成固定三步，验证估算确实改善了，而不是凭感觉。

    sql
    CREATE STATISTICS s (dependencies) ON a, b FROM t;
ANALYZE t;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM t WHERE a = 1 AND b = 1;

  • 给统计对象起可读的名字：名字应能反映涉及的列或用途，便于后期审计与清理。

    sql
    -- 推荐
CREATE STATISTICS orders_city_zip (dependencies) ON customer_city, customer_zip FROM orders;

-- 不推荐
CREATE STATISTICS s1 (dependencies) ON customer_city, customer_zip FROM orders;

  • 把统计对象定义纳入版本化迁移脚本：与表结构、索引一样，统计对象也属于 schema 的一部分，应该用迁移工具管理，避免手工操作导致环境不一致。

  • 表达式列优先用表达式统计而不是表达式索引（当目的只是估算时）：省掉索引维护成本。

    sql
    CREATE STATISTICS t3_month (ndistinct)
    ON date_trunc('month', a), date_trunc('day', a) FROM t3;

  • 定期清理无用统计对象：业务变化后，原来的列组可能不再一起出现。用 pg_statistic_ext 盘点，删掉长期无用的对象，减轻 ANALYZE 负担。

    sql
    SELECT stxname, stxrelid::regclass AS tbl, stxkeys, stxkind
FROM pg_statistic_ext
ORDER BY stxrelid, stxname;

  • mcv 列组要控制列宽与列数：MCV 存实际值，列越多、值越长，开销越大。优先用在窄列、两到三列的组合上。

  • 监控 autovacuum 的分析是否覆盖到统计对象：扩展统计由 ANALYZE（手工或自动）计算，若表的 autovacuum 配置关闭了 analyze，扩展统计会逐渐过期。

    sql
    SELECT relname, reloptions
FROM pg_class
WHERE relname = 'orders';

  • 不要用扩展统计替代正确的 schema 设计：如果两列真的完全冗余（如既存 city 又存 zip），长期方案是规范化，扩展统计只是让「不得不反规范化」的场景不至于估算崩塌。

下一节

继续学习：统计信息对执行计划的影响

参考链接 ​