PostgreSQL CREATE STATISTICS 实战：纠正城市与邮编相关性导致的基数估算偏差

问题场景

PostgreSQL 优化器在缺少多列统计信息时，默认假设不同列的过滤条件相互独立。对于：

WHERE city = 'city_a' AND zip = '10000'

它会用 P(city='city_a') * P(zip='10000') 估算联合选择率。若邮编与城市强相关（例如某个邮编只属于某个城市），真实联合选择率应接近 P(zip='10000')，而不是两者相乘。估算行数偏小，可能让优化器误以为只需扫描很少行，从而选择索引扫描或嵌套循环连接；实际行数远大于估算时，执行效率可能下降。

构造示例数据

下面用 10 万行数据构造强相关：city_a 共 1 万行，邮编全部为 10000；city_b 共 9 万行，邮编分布在 20000 到 20008。这样 zip='10000' 一定属于 city_a，两列存在函数依赖。

DROP TABLE IF EXISTS addr_demo;

CREATE TABLE addr_demo (
    id   bigint PRIMARY KEY,
    city text   NOT NULL,
    zip  text   NOT NULL
);

INSERT INTO addr_demo (id, city, zip)
SELECT i,
       CASE WHEN i <= 10000 THEN 'city_a' ELSE 'city_b' END,
       CASE WHEN i <= 10000 THEN '10000'
            ELSE '2000' || (i % 9)::text
       END
FROM generate_series(1, 100000) AS i;

CREATE INDEX addr_demo_zip_idx  ON addr_demo (zip);
CREATE INDEX addr_demo_city_idx ON addr_demo (city);

ANALYZE addr_demo;

统计前：观察估算偏差

执行：

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM addr_demo
WHERE city = 'city_a'
  AND zip  = '10000';

在输出中找到 rows= 和 actual rows=。按独立假设，city_a 占 10%，zip='10000' 占 10%，估算行数约为 100000 * 0.1 * 0.1 = 1000；实际满足条件的行数是 10000。也就是说，估算值约为实际值的十分之一。计划节点可能选择位图索引扫描或索引扫描；若这个表参与连接，偏小的行数还可能影响连接顺序和连接算法选择。

创建多列统计信息

为 city 与 zip 创建扩展统计，重点使用 dependencies 类型。它用于描述列之间的函数依赖，适合“邮编决定城市”这类关系；ndistinct 可同时改善多列 distinct 估算，本例保留作为补充。

CREATE STATISTICS addr_demo_city_zip_stats (dependencies, ndistinct)
ON city, zip
FROM addr_demo;

ANALYZE addr_demo;

注意：创建统计信息后必须重新 ANALYZE，否则优化器还看不到新统计。可以查看统计对象：

SELECT stxname, stxkind
FROM pg_statistic_ext
WHERE stxname = 'addr_demo_city_zip_stats';

再次执行同一个 EXPLAIN：

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM addr_demo
WHERE city = 'city_a'
  AND zip  = '10000';

此时 rows= 应明显接近 10000，而不是 1000。优化器通过依赖关系知道 zip='10000' 已经蕴含 city='city_a'，不再把两个条件的选择率简单相乘。估算修正后，是否走索引、是否改选连接顺序，会更贴近真实数据分布。

运行方法

1. 准备一个支持 CREATE STATISTICS 的 PostgreSQL 数据库（PostgreSQL 10 及以上支持 dependencies 与 ndistinct；mcv 类型需 12 及以上）。
2. 将上面 SQL 保存为 demo.sql。
3. 执行：psql -h <host> -U <user> -d <dbname> -f demo.sql，按提示输入密码。
4. 分别运行统计前和统计后的 EXPLAIN (ANALYZE, BUFFERS)，对比 rows 与 actual rows。

边界与限制

• 扩展统计只针对单表列间相关性，不能直接修正跨表连接的相关性；连接估算仍依赖各表统计与连接条件。
• dependencies 主要对等值条件有效。列被表达式、函数或隐式类型转换包裹后，优化器可能无法匹配统计对象。
• 统计基于采样，极端偏斜、样本过小或数据快速变化时仍可能偏差；需要合理设置统计目标并定期 ANALYZE。
• 多列统计会增加 ANALYZE 与查询规划的开销，不建议对所有列组合盲目创建。
• 估算改善不等于执行一定变快；它只是让优化器更有机会选对访问路径和连接顺序，仍需用 EXPLAIN ANALYZE 验证真实执行情况。

小结

城市与邮编这类强相关列，是优化器独立性假设容易失效的典型场景。先用 EXPLAIN ANALYZE 找到估算行数与实际行数的差距，再针对相关列创建 CREATE STATISTICS ... (dependencies)，并重新 ANALYZE，通常能把联合选择率拉回合理范围。它不能替代索引设计和执行验证，但能有效减少因基数估算偏差导致的错误计划选择。