PostgreSQL / 系统课时
使用表达式统计
使用表达式统计
当查询条件写成 lower(email)、日期截断或其他函数表达式时,普通列统计往往无法描述“表达式计算结果”的分布,规划器只能退回默认选择率。本节通过单表达式统计,让 PostgreSQL 直接采集表达式值分布并改善基数估算。
学习目标
- 判断函数谓词何时缺少可用的选择性统计
- 为单个常用表达式创建扩展统计对象
- 区分表达式统计与表达式索引解决的问题
函数谓词为什么容易估错
普通 ANALYZE 会为表列采集最常见值、直方图和不同值数量,但查询条件如果是 lower(email) = 'vip@example.com',规划器面对的是 lower(email) 的结果,而不是原始 email 列本身。
如果没有这个表达式的统计信息,等值谓词可能使用默认选择率。真实数据一旦高度偏斜,估算就会与实际行数相差一个数量级,进一步影响连接顺序、扫描方式和内存估算。
为常用表达式建立统计
下面创建 10000 个账户,其中 4000 行经过 lower(email) 后都等于 vip@example.com,其余值各不相同。先观察基线,再创建单表达式统计。
DROP TABLE IF EXISTS customer_accounts;
CREATE TABLE customer_accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL
);
INSERT INTO customer_accounts (email)
SELECT
CASE
WHEN i <= 4000 THEN 'Vip@Example.com'
ELSE 'user-' || i || '@example.com'
END
FROM generate_series(1, 10000) AS g(i);
ANALYZE customer_accounts;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT *
FROM customer_accounts
WHERE lower(email) = 'vip@example.com';
CREATE STATISTICS customer_email_lower_stats
ON (lower(email))
FROM customer_accounts;
ANALYZE customer_accounts;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT *
FROM customer_accounts
WHERE lower(email) = 'vip@example.com';预期输出
创建表达式统计前:
Seq Scan on customer_accounts ... rows=50 ...
(actual rows=4000 loops=1)
创建表达式统计后:
Seq Scan on customer_accounts ... rows=4000 ...
(actual rows=4000 loops=1)计划中的成本和行宽可能因环境不同而变化。关键现象是:创建统计前,表达式等值条件通常按默认选择率估成约 50 行;重新 ANALYZE 后,规划器能够利用 lower(email) 的真实分布,估算接近 4000 行。
验证表达式统计是否已采集
pg_stats_ext_exprs 会把扩展统计对象中的表达式统计以可读形式暴露出来。可以检查表达式文本、不同值估算和最常见值,确认 ANALYZE 已经真正生成数据。
SELECT
statistics_name,
expr,
n_distinct,
most_common_vals,
most_common_freqs
FROM pg_stats_ext_exprs
WHERE tablename = 'customer_accounts'
AND statistics_name = 'customer_email_lower_stats';预期输出
statistics_name | expr | n_distinct | most_common_vals | most_common_freqs
customer_email_lower_stats | lower(email) | <NDISTINCT> | {vip@example.com} | {0.4}n_distinct 的具体表示可能随采样结果变化,但最常见值应能反映 vip@example.com 的热点频率。若统计对象刚创建却查询不到统计数据,先确认已经对目标表执行过 ANALYZE。
表达式统计不是表达式索引
表达式统计解决的是估算:告诉规划器某个表达式结果如何分布。它不会创建新的访问路径,所以示例中即使估算已经准确,仍可能继续使用顺序扫描。
如果生产查询既估算错误又需要快速定位少量记录,可以分别评估表达式统计与表达式索引;前者改善基数,后者提供索引扫描能力。还要让统计定义与高频查询中的表达式保持一致,例如统计 lower(email) 并不能自动代表任意其他字符串规范化函数。对变化频繁的数据,同样要保证自动或手工 ANALYZE 能及时刷新表达式统计。