PostgreSQL / 系统课时

搭建可复现索引实验环境

搭建可复现索引实验环境

本课先把索引实验所需的表结构、数据分布、查询样例和统计信息固定下来。完成后,每次实验都能从同一数据集出发,比较索引收益,同时避免随机数据和脏统计干扰结论。

学习目标

  • 建立规模、分布和查询模式稳定的实验数据集
  • 固定优化器相关设置,减少不同执行环境带来的偏差
  • 用 ANALYZE 更新统计信息,并验证实验基线已经准备就绪

固定实验对象

实验只保留一张订单表,使用 200000 行数据模拟线上常见的“按客户查询订单”场景。customer_id 在 10000 个客户之间均匀分布,因此单个客户平均对应约 20 行记录,既能观察全表扫描,也便于后续验证索引选择。

数据生成使用 generate_series,不会依赖人工准备文件。脚本先清理旧对象,再按固定公式生成金额、客户和状态字段,最后执行 ANALYZE,确保优化器获得与数据一致的统计信息。

创建可重复数据集

下面的 SQL 在当前 PostgreSQL 数据库中即可执行。实验反复开始时重新执行整段脚本,就能恢复到同一状态;没有显式创建客户索引,方便下一课直接比较加索引前后的计划差异。

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id bigint PRIMARY KEY,
    customer_id integer NOT NULL,
    status text NOT NULL,
    amount numeric(12,2) NOT NULL,
    created_at timestamp NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount, created_at)
SELECT
    g,
    ((g - 1) % 10000) + 1,
    CASE g % 4
        WHEN 0 THEN 'paid'
        WHEN 1 THEN 'pending'
        WHEN 2 THEN 'shipped'
        ELSE 'cancelled'
    END,
    round((10 + (g % 99000) / 100.0)::numeric, 2),
    timestamp '2026-01-01 00:00:00' + (g * interval '1 minute')
FROM generate_series(1, 200000) AS s(g);

ANALYZE orders;

SELECT
    count(*) AS rows,
    count(DISTINCT customer_id) AS customers,
    min(customer_id) AS min_customer_id,
    max(customer_id) AS max_customer_id
FROM orders;

预期结果

text
  rows  | customers | min_customer_id | max_customer_id
--------+-----------+-----------------+----------------
 200000 |     10000 |               1 |          10000

固定实验参数

索引实验关注的是访问路径,而不是并行执行或 JIT 编译带来的额外变量。实验会话可以关闭并行查询和 JIT,使同一 SQL 在不同轮次中更容易比较。该设置只影响当前会话,不会改变数据库全局配置。

sql
SET max_parallel_workers_per_gather = 0;
SET jit = off;

EXPLAIN (COSTS OFF)
SELECT order_id, amount
FROM orders
WHERE customer_id = 42;

预期结果

text
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on orders
   Filter: (customer_id = 42)

检查数据与统计信息

实验开始前至少确认三件事:总行数正确、客户分布没有意外变化、统计信息已经更新。ANALYZE 不是“让索引生效”的命令,而是让优化器知道列值分布和表规模;后续是否选择索引,仍取决于成本估算。

可以把下面的查询作为每轮实验的起点检查。reltuples 是统计估计值,因此不会要求它与真实行数逐字相等,但在刚执行 ANALYZE 后应当接近 200000。

sql
SELECT
    c.reltuples::bigint AS estimated_rows,
    s.n_live_tup AS live_rows,
    s.n_dead_tup AS dead_rows,
    s.last_analyze IS NOT NULL AS analyzed
FROM pg_class AS c
JOIN pg_stat_user_tables AS s
  ON s.relid = c.oid
WHERE c.relname = 'orders';

预期结果

text
 estimated_rows | live_rows | dead_rows | analyzed
----------------+-----------+-----------+----------
         200000 |    200000 |         0 | t

完成环境搭建后,后续实验应始终从这份固定数据集开始:先记录无索引基线,再只改变一个因素,例如新增一个索引。这样得到的性能差异才更接近“索引带来的收益”,也更容易解释维护成本是否值得。

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

目录与阅读设置

记录一个想法

搭建可复现索引实验环境

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

登录哪都通