PostgreSQL / 系统课时
搭建计划分析实验库
搭建计划分析实验库
计划实验最怕数据分布和环境不断变化,导致同一 SQL 的估算与计划无法比较。本课会搭建一张可重复生成的实验表,明确均匀、倾斜和相关列三种分布,并记录影响规划器判断的基础环境参数。
学习目标
- 创建包含均匀分布、热点倾斜和强相关列的数据集
- 为常用过滤列建立索引并收集统计信息
- 用固定校验查询确认数据与环境基线可复现
固定实验环境变量
实验数据即使完全相同,PostgreSQL 版本、成本参数和统计目标不同,也可能得到不同计划。开始前先记录版本与几项关键规划器参数;后续比较计划时,应尽量保持这些值不变。
SELECT
current_setting('server_version') AS server_version,
current_setting('default_statistics_target') AS default_statistics_target,
current_setting('random_page_cost') AS random_page_cost,
current_setting('effective_cache_size') AS effective_cache_size;预期结果
server_version | default_statistics_target | random_page_cost | effective_cache_size
----------------+---------------------------+------------------+---------------------
<VERSION> | <VALUE> | <VALUE> | <VALUE><VERSION> 和 <VALUE> 取决于当前实例。保存这组值的目的不是追求某个“标准答案”,而是避免把环境变化误判成统计信息变化。
创建三类数据分布
下面的主示例使用 generate_series 生成 100000 行确定性数据。uniform_key 有 1000 个近似等频值,skew_key=1 固定占 70%,corr_a 与 corr_b 在 95% 的行上相等,分别用于观察均匀估算、热点值估算和列相关性问题。
CREATE SCHEMA planner_lab;
CREATE TABLE planner_lab.sample_orders (
id integer PRIMARY KEY,
uniform_key integer NOT NULL,
skew_key integer NOT NULL,
corr_a integer NOT NULL,
corr_b integer NOT NULL,
payload text NOT NULL
);
INSERT INTO planner_lab.sample_orders (
id, uniform_key, skew_key, corr_a, corr_b, payload
)
SELECT
i,
((i - 1) % 1000) + 1,
CASE
WHEN i <= 70000 THEN 1
ELSE 2 + ((i - 70001) % 99)
END,
((i - 1) % 100) + 1,
CASE
WHEN i % 20 = 0 THEN ((i + 17) % 100) + 1
ELSE ((i - 1) % 100) + 1
END,
repeat('x', 64)
FROM generate_series(1, 100000) AS g(i);
CREATE INDEX sample_orders_uniform_idx
ON planner_lab.sample_orders (uniform_key);
CREATE INDEX sample_orders_skew_idx
ON planner_lab.sample_orders (skew_key);
CREATE INDEX sample_orders_corr_a_idx
ON planner_lab.sample_orders (corr_a);
CREATE INDEX sample_orders_corr_b_idx
ON planner_lab.sample_orders (corr_b);
ANALYZE planner_lab.sample_orders;预期输出
CREATE SCHEMA
CREATE TABLE
INSERT 0 100000
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ANALYZE这里故意不使用 random(),因为随机数据会让热点频率和相关程度在每次重建后产生漂移。索引在装载后统一创建,也避免逐行维护索引影响实验准备时间。
验证数据基线
建表后先验证分布,不要直接开始看执行计划。只要这四个数字不对,后续的基数估算实验就失去共同基准。
SELECT
count(*) AS total_rows,
count(DISTINCT uniform_key) AS uniform_ndv,
count(*) FILTER (WHERE skew_key = 1) AS skew_hot_rows,
count(*) FILTER (WHERE corr_a = corr_b) AS correlated_rows
FROM planner_lab.sample_orders;预期结果
total_rows | uniform_ndv | skew_hot_rows | correlated_rows
------------+-------------+---------------+----------------
100000 | 1000 | 70000 | 95000三类列承担不同实验目的:均匀列适合建立“估算正常”的参照组;倾斜列用于观察最常见值与热点过滤条件;相关列则用于制造单列统计信息难以表达的联合条件。
保持实验可重复
ANALYZE 会抽样生成统计信息,因此即使底层数据固定,个别直方图边界也可能在重新采样后略有变化。需要做前后对比时,至少固定 PostgreSQL 主版本、关键规划器参数、表数据、索引和列级统计目标,并在每轮实验前重新执行 ANALYZE。
如果实验库需要反复重建,最稳妥的方式是把本课 SQL 保存为版本化脚本,并在独立数据库中从空 schema 执行。这样后续看到计划变化时,可以先确认“数据是否变了、参数是否变了、统计信息是否重采样”,再讨论规划器本身。