PostgreSQL / 系统课时
搭建可复现索引实验环境
搭建可复现索引实验环境
本课先把索引实验所需的表结构、数据分布、查询样例和统计信息固定下来。完成后,每次实验都能从同一数据集出发,比较索引收益,同时避免随机数据和脏统计干扰结论。
学习目标
- 建立规模、分布和查询模式稳定的实验数据集
- 固定优化器相关设置,减少不同执行环境带来的偏差
- 用
ANALYZE更新统计信息,并验证实验基线已经准备就绪
固定实验对象
实验只保留一张订单表,使用 200000 行数据模拟线上常见的“按客户查询订单”场景。customer_id 在 10000 个客户之间均匀分布,因此单个客户平均对应约 20 行记录,既能观察全表扫描,也便于后续验证索引选择。
数据生成使用 generate_series,不会依赖人工准备文件。脚本先清理旧对象,再按固定公式生成金额、客户和状态字段,最后执行 ANALYZE,确保优化器获得与数据一致的统计信息。
创建可重复数据集
下面的 SQL 在当前 PostgreSQL 数据库中即可执行。实验反复开始时重新执行整段脚本,就能恢复到同一状态;没有显式创建客户索引,方便下一课直接比较加索引前后的计划差异。
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;预期结果
rows | customers | min_customer_id | max_customer_id
--------+-----------+-----------------+----------------
200000 | 10000 | 1 | 10000固定实验参数
索引实验关注的是访问路径,而不是并行执行或 JIT 编译带来的额外变量。实验会话可以关闭并行查询和 JIT,使同一 SQL 在不同轮次中更容易比较。该设置只影响当前会话,不会改变数据库全局配置。
SET max_parallel_workers_per_gather = 0;
SET jit = off;
EXPLAIN (COSTS OFF)
SELECT order_id, amount
FROM orders
WHERE customer_id = 42;预期结果
QUERY PLAN
-------------------------------------------------------------
Seq Scan on orders
Filter: (customer_id = 42)检查数据与统计信息
实验开始前至少确认三件事:总行数正确、客户分布没有意外变化、统计信息已经更新。ANALYZE 不是“让索引生效”的命令,而是让优化器知道列值分布和表规模;后续是否选择索引,仍取决于成本估算。
可以把下面的查询作为每轮实验的起点检查。reltuples 是统计估计值,因此不会要求它与真实行数逐字相等,但在刚执行 ANALYZE 后应当接近 200000。
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';预期结果
estimated_rows | live_rows | dead_rows | analyzed
----------------+-----------+-----------+----------
200000 | 200000 | 0 | t完成环境搭建后,后续实验应始终从这份固定数据集开始:先记录无索引基线,再只改变一个因素,例如新增一个索引。这样得到的性能差异才更接近“索引带来的收益”,也更容易解释维护成本是否值得。