PostgreSQL / 系统课时

搭建计划分析实验库

搭建计划分析实验库

计划实验最怕数据分布和环境不断变化,导致同一 SQL 的估算与计划无法比较。本课会搭建一张可重复生成的实验表,明确均匀、倾斜和相关列三种分布,并记录影响规划器判断的基础环境参数。

学习目标

  • 创建包含均匀分布、热点倾斜和强相关列的数据集
  • 为常用过滤列建立索引并收集统计信息
  • 用固定校验查询确认数据与环境基线可复现

固定实验环境变量

实验数据即使完全相同,PostgreSQL 版本、成本参数和统计目标不同,也可能得到不同计划。开始前先记录版本与几项关键规划器参数;后续比较计划时,应尽量保持这些值不变。

sql
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;

预期结果

text
 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% 的行上相等,分别用于观察均匀估算、热点值估算和列相关性问题。

sql
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;

预期输出

text
CREATE SCHEMA
CREATE TABLE
INSERT 0 100000
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ANALYZE

这里故意不使用 random(),因为随机数据会让热点频率和相关程度在每次重建后产生漂移。索引在装载后统一创建,也避免逐行维护索引影响实验准备时间。

验证数据基线

建表后先验证分布,不要直接开始看执行计划。只要这四个数字不对,后续的基数估算实验就失去共同基准。

sql
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;

预期结果

text
 total_rows | uniform_ndv | skew_hot_rows | correlated_rows
------------+-------------+---------------+----------------
     100000 |        1000 |         70000 |           95000

三类列承担不同实验目的:均匀列适合建立“估算正常”的参照组;倾斜列用于观察最常见值与热点过滤条件;相关列则用于制造单列统计信息难以表达的联合条件。

保持实验可重复

ANALYZE 会抽样生成统计信息,因此即使底层数据固定,个别直方图边界也可能在重新采样后略有变化。需要做前后对比时,至少固定 PostgreSQL 主版本、关键规划器参数、表数据、索引和列级统计目标,并在每轮实验前重新执行 ANALYZE。

如果实验库需要反复重建,最稳妥的方式是把本课 SQL 保存为版本化脚本,并在独立数据库中从空 schema 执行。这样后续看到计划变化时,可以先确认“数据是否变了、参数是否变了、统计信息是否重采样”,再讨论规划器本身。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

搭建计划分析实验库

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通