PostgreSQL / 系统课时

构建基准数据集

构建基准数据集

本节构建一套可重复生成的 PostgreSQL 18 基准数据:订单具有客户热点,明细具有商品热点,同时保留连续时间字段和四表关联。完成后,可用同一数据集稳定复现实验并比较执行计划变化。

学习目标

  • 创建包含维度表、事实表和明细表的统一测试模型。
  • 用确定性规则制造客户与商品数据倾斜,同时保留时间序列特征。
  • 通过统计查询验证行数、热点分布和多表关联结果,为后续优化建立固定基线。

设计可复现的数据模型

数据集使用 customer、product、orders、order_item 四张表。订单指向客户,订单明细同时指向订单与商品;热点客户承载多数订单,热点商品承载多数明细。生成规则只依赖序号运算,不依赖 random(),因此重复构建时分布保持一致。

统一基准数据集的表关系、时间轴与热点分布

这套结构既能测试时间过滤、聚合,也能测试多表 JOIN 与基数估计。除主键和 order_item 的复合主键外,暂不创建额外业务索引,避免在数据生成阶段提前替后续索引实验做出优化决策。

一次性生成完整数据

下面脚本会重建 bench schema,生成 1 万客户、5000 商品、30 万订单和 75 万订单明细,并在完成后执行 ANALYZE。固定时间锚点让相同脚本在不同日期运行时仍得到同一时间分布。

sql
DROP SCHEMA IF EXISTS bench CASCADE;
CREATE SCHEMA bench;

CREATE TABLE bench.customer (
  customer_id bigint PRIMARY KEY,
  segment text NOT NULL,
  region text NOT NULL
);

CREATE TABLE bench.product (
  product_id bigint PRIMARY KEY,
  category text NOT NULL,
  price numeric(10,2) NOT NULL CHECK (price > 0)
);

CREATE TABLE bench.orders (
  order_id bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES bench.customer(customer_id),
  created_at timestamptz NOT NULL,
  status text NOT NULL
);

CREATE TABLE bench.order_item (
  order_id bigint NOT NULL REFERENCES bench.orders(order_id),
  line_no smallint NOT NULL,
  product_id bigint NOT NULL REFERENCES bench.product(product_id),
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(10,2) NOT NULL CHECK (unit_price > 0),
  PRIMARY KEY (order_id, line_no)
);

INSERT INTO bench.customer (customer_id, segment, region)
SELECT
  g,
  CASE g % 5
    WHEN 0 THEN 'enterprise'
    WHEN 1 THEN 'growth'
    ELSE 'standard'
  END,
  'region-' || (1 + g % 8)
FROM generate_series(1, 10000) AS s(g);

INSERT INTO bench.product (product_id, category, price)
SELECT
  g,
  'category-' || (1 + g % 20),
  round(5 + (g % 5000)::numeric / 10, 2)
FROM generate_series(1, 5000) AS s(g);

INSERT INTO bench.orders (order_id, customer_id, created_at, status)
SELECT
  g,
  CASE
    WHEN g % 10 < 6
      THEN 1 + ((g * 37 + (g / 10) * 13) % 100)
    ELSE 101 + (((g / 10) * 97 + (g % 10) * 53) % 9900)
  END,
  timestamptz '2026-03-01 00:00:00+00'
    + make_interval(days => (g % 180))
    + make_interval(secs => (g * 37 % 86400)),
  CASE
    WHEN g % 20 < 16 THEN 'completed'
    WHEN g % 20 < 19 THEN 'pending'
    ELSE 'cancelled'
  END
FROM generate_series(1, 300000) AS s(g);

INSERT INTO bench.order_item (
  order_id,
  line_no,
  product_id,
  quantity,
  unit_price
)
SELECT
  o.order_id,
  s.line_no::smallint,
  x.product_id,
  1 + ((o.order_id + s.line_no) % 3)::integer,
  p.price
FROM bench.orders AS o
CROSS JOIN LATERAL
  generate_series(1, 1 + (o.order_id % 4)::integer) AS s(line_no)
CROSS JOIN LATERAL (
  SELECT CASE
    WHEN (o.order_id + s.line_no) % 10 < 7
      THEN 1 + ((o.order_id * 31 + s.line_no * 17) % 50)
    ELSE 51 + ((o.order_id * 31 + s.line_no * 17) % 4950)
  END AS product_id
) AS x
JOIN bench.product AS p
  ON p.product_id = x.product_id;

ANALYZE bench.customer;
ANALYZE bench.product;
ANALYZE bench.orders;
ANALYZE bench.order_item;

预期结果

text
DROP SCHEMA
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 10000
INSERT 0 5000
INSERT 0 300000
INSERT 0 750000
ANALYZE
ANALYZE
ANALYZE
ANALYZE

首次执行时若 bench 不存在,DROP SCHEMA IF EXISTS 还会产生一条 NOTICE;它不影响结果。实际耗时取决于磁盘、WAL 配置和硬件。

验证规模与数据倾斜

先确认每张表的行数,再检查热点客户。生成规则让 60% 的订单落入前 100 个客户,前 100 个热点客户各有 1800 笔订单;其余订单分散到 9900 个长尾客户。

sql
SELECT 'customer' AS relation, count(*) AS rows FROM bench.customer
UNION ALL
SELECT 'product', count(*) FROM bench.product
UNION ALL
SELECT 'orders', count(*) FROM bench.orders
UNION ALL
SELECT 'order_item', count(*) FROM bench.order_item;

SELECT customer_id, count(*) AS order_count
FROM bench.orders
GROUP BY customer_id
ORDER BY order_count DESC, customer_id
LIMIT 5;

预期结果

text
  relation  |  rows
------------+--------
 customer   |  10000
 product    |   5000
 orders     | 300000
 order_item | 750000

 customer_id | order_count
-------------+------------
           1 |       1800
           2 |       1800
           3 |       1800
           4 |       1800
           5 |       1800

第二个结果证明分布不是均匀随机数据。这样的热点会直接影响连接基数估计、缓存命中和不同索引策略的收益,因此比“每个键出现次数几乎相同”的测试数据更适合高级优化实验。

验证时间序列与复杂关联

最后执行一个固定时间窗口的四表关联。它同时过滤订单时间和状态,再按客户分群、商品分类聚合收入,可作为后续 EXPLAIN (ANALYZE, BUFFERS)、索引、扩展统计和 JOIN 策略实验的统一查询样本。

sql
SELECT
  c.segment,
  p.category,
  count(*) AS line_count,
  round(sum(oi.quantity * oi.unit_price), 2) AS revenue
FROM bench.orders AS o
JOIN bench.customer AS c
  ON c.customer_id = o.customer_id
JOIN bench.order_item AS oi
  ON oi.order_id = o.order_id
JOIN bench.product AS p
  ON p.product_id = oi.product_id
WHERE o.created_at >= timestamptz '2026-07-01 00:00:00+00'
  AND o.status = 'completed'
GROUP BY c.segment, p.category
ORDER BY revenue DESC
LIMIT 5;

预期结果

text
 segment  |  category   | line_count |  revenue
----------+-------------+------------+-----------
 standard | category-17 |       8392 | 1657117.40
 standard | category-1  |       8029 | 1632541.00
 standard | category-13 |       4151 | 1611930.00
 standard | category-11 |       6966 | 1610724.00
 standard | category-15 |       5059 | 1607939.20

只要脚本未改动,这些行数与聚合结果就是稳定基线。后续实验可以改变索引、统计对象或查询写法,但应保持数据生成脚本与验证查询不变;否则执行时间变化可能来自数据分布变化,而不是优化本身。

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

目录与阅读设置

阅读设置

17

记录一个想法

构建基准数据集

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

登录哪都通