模式设计、索引与 EXPLAIN

某 沪深 300 私募 的 风控 在 飞书 上 找你：『我 昨晚 在 笔记本 样本 上 跑 30 毫秒 出结果 的 按 标的 回撤 查询，今天 打到 生产 上 跑 了 12 分钟。同样 的 SQL，同样 的 方言，同样 的 bars_1m 表——到底 什么 变了？』SQL 没变。变 的 是 行数：笔记本 5 万 行，生产 14 亿 行。『样本 上 快、生产 上 慢』几乎 永远 是 三件事 之一 造成 的：WHERE 过滤 的 列 上 没 索引，规划器 统计 过期 导致 它 选 错 了 join 算法，或者 模式 形状 逼着 数据库 做 它 本不该 做 的 工作。本课 教 你 把 这 三 件 都 诊断 出来 并 修好。

纵向事实表是对的形状

量化仓库的标准模式是​​纵向事实表​​：每行 一条 (symbol, ts) 在某 频率 上的观测，每个频率 一张事实表。ticks(symbol, ts, price, size, side) 装原始 tick，bars_1m(symbol, ts, open, high, low, close, volume) 装 1 分钟 bar，bars_1d 装日线。错的模式（点名一次后丢弃）是​​把标的拉成列​​（AAPL_close、MSFT_close、600519_close ……）：那种 形状 产生 列数 爆炸（千 标的 宇宙 就是 千 列 表），每次 调整 标的 池 都要 schema 迁移，并且 让 L1 的 RANK() OVER (PARTITION BY ts ORDER BY ret) 横截面 查询 无法 写。维度表小、用代理键连接。研究仓库的星型模式：

-- fact table (tall; one row per observation)
CREATE TABLE bars_1m (
  symbol TEXT NOT NULL,
  ts     TIMESTAMPTZ NOT NULL,
  open   NUMERIC(18,6),
  high   NUMERIC(18,6),
  low    NUMERIC(18,6),
  close  NUMERIC(18,6),
  volume BIGINT,
  PRIMARY KEY (symbol, ts)
);

-- dimension: instrument metadata (skinny; one row per symbol)
CREATE TABLE instrument (
  id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  symbol         TEXT NOT NULL UNIQUE,
  exchange       TEXT NOT NULL,
  tick_size      NUMERIC,
  multiplier     NUMERIC,
  listing_date   DATE,
  delisting_date DATE
);

-- dimension: trading calendar
CREATE TABLE calendar (
  d              DATE PRIMARY KEY,
  is_trading_day BOOLEAN NOT NULL,
  session_open   TIME,
  session_close  TIME
);

-- dimension: corporate actions
CREATE TABLE corp_action (
  id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  symbol         TEXT NOT NULL,
  effective_date DATE NOT NULL,
  action_type    TEXT NOT NULL,
  ratio          NUMERIC
);

A 股 场景 下，instrument.exchange 的 取值 集合 是 {'SSE', 'SZSE', 'CFFEX'}；calendar 表 每年 从 上交所 / 深交所 公布 的 交易日 历 或 内部 vendor 抓 一遍。星型 纪律：事实表 连 维度表，维度表 之间 不 互连。完整 的 Kimball / snowflake / SCD 理论 留 给 Track 4。

主键：事实自然、维度代理

规则 一句 话：​​事实表 用 唯一 标识 观测 的 自然 复合 键；维度表 用 代理 身份 键，跨 标的 改名 也 稳定。​ 所以 bars_1m 用 PRIMARY KEY (symbol, ts)——这个 复合 是 『一个 标的 在 一分钟 上 一根 bar』 的 自然 表达，并 兼任 聚簇 访问 路径（Postgres 不会 在 INSERT 之后 自动 维护 聚簇 顺序，但 一次性 跑 CLUSTER bars_1m USING bars_1m_pkey 重排 堆，之后 范围扫描 命中 连续页）。instrument、calendar、corp_action 用 代理 BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY——经得起 标的 改名。instrument 同时 带 一个 UNIQUE (symbol) 给 事实表 外键 用。

索引：B 树、BRIN、局部、覆盖

B 树 是 默认 答案，约 80% 场景 都 正确——支持 等值 与 范围 查询，并且 顺带 提供 排序 输出，于是 WHERE symbol = '510050' ORDER BY ts 不 用 Sort 节点 就 能 出结果。​​BRIN​​（块范围 索引）按 数据块 存 一对 min/max 范围——磁盘 占用 极小，对 追加 写入 且 自然 按 索引 列 有序 的 表 极 快。100 GB 的 bar 表 上 一个 ts BRIN 只 几 MB，『取 两个 时间戳 之间 的 所有 行』 就是 一次 廉价 范围 走 读。​​局部​​索引 只 覆盖 表 的 一片：WHERE delisting_date IS NULL 让 热门 在册 标的 集合 始终 紧凑。​​覆盖​​索引 通过 INCLUDE (...) 携带 附加 payload 列，让 规划器 仅 用 索引 就 满足 查询、不 必 回 堆——『仅索引 扫描』 把 一条 12 分钟 查询 压 到 一秒。仓库 上 四 个 标准 索引，按 创建 顺序：

CREATE INDEX idx_bars_symbol_ts_inc ON bars_1m (symbol, ts) INCLUDE (close, volume);
CREATE INDEX idx_bars_ts_brin       ON bars_1m USING BRIN (ts) WITH (pages_per_range = 32);
CREATE INDEX idx_instr_active       ON instrument (symbol) WHERE delisting_date IS NULL;
CREATE INDEX idx_corp_symbol_date   ON corp_action (symbol, effective_date);

复合 索引 列序 不容 商量：​​等值列 在前，范围 / 排序列 在后​​。(symbol, ts) 对 『取 一个 标的 在 一段 时间 窗口 的 所有 bar』 这种 负载 正确——规划器 一次性 下 探 到 symbol = '510050'，然后 沿 ts 连续 范围 走。(ts, symbol) 会 让 每条 查询 都 变 成 部分 范围扫描 加 过滤。同 一条 查询 在 两种 列序 上 跑 一遍 看 计划，差异 一目了然；规则 是 经验 总结 的，但 每次 都 落 在 同 一边。

逐行读 EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN (ANALYZE, BUFFERS) <query>; 会 真的 跑 这条 查询，并 打印 一棵 计划 树，附带 实际 行数、每个 节点 耗时、缓冲 命中 / 读取 计数。每个 节点 是 一个 算子。每个 量化 开发者 都 必须 认 出 的 十个 算子名：Seq Scan（全表 扫描）、Index Scan（索引 查找 + 回堆 取行）、Index Only Scan（覆盖 索引 或 可见性 映射 让 查询 仅靠 索引 满足）、Bitmap Heap Scan（先 收集 匹配 行 到 位图、再 按 磁盘 顺序 取）、Nested Loop（外侧 循环、内侧 探测）、Hash Join（一侧 建 哈希、一侧 探测）、Merge Join（两 个 已排序 输入 并行 走）、Sort（显式 排序 节点）、Hash Aggregate（用 哈希表 实现 GROUP BY）、Group Aggregate（在 已排序 输入 上 实现 GROUP BY）。

加 索引 前后 都 要 在 工作 例子 上 跑 的 三 条 诊断 查询：

EXPLAIN (ANALYZE, BUFFERS) SELECT close FROM bars_1m
  WHERE symbol = '510050' AND ts BETWEEN '2026-04-13' AND '2026-04-17' ORDER BY ts;

EXPLAIN (ANALYZE, BUFFERS) SELECT b.symbol, b.ts, b.close, i.exchange
  FROM bars_1m b JOIN instrument i ON i.symbol = b.symbol
  WHERE b.ts BETWEEN '2026-04-13' AND '2026-04-17';

EXPLAIN (ANALYZE, BUFFERS) SELECT symbol,
  AVG(close) OVER (PARTITION BY symbol ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
  FROM bars_1m WHERE symbol = '510050';

三 种 病理 与 对症。​​病理 一​​：查询 (1) 加 索引 前 显示 Seq Scan on bars_1m，扫 整张 十亿 行 堆。处方：建 idx_bars_symbol_ts_inc；事后 计划 显示 Index Only Scan using idx_bars_symbol_ts_inc，缓冲 读取 数 下降 三 个 数量级。​​病理 二​​：查询 (2) 显示 Nested Loop，bars_1m 在 外侧，因 为 pg_class.reltuples 是 一年前 的 一 百万 行 估计——实际 14 亿。处方：跑 ANALYZE bars_1m;；规划器 拿到 真实 基数，切到 Hash Join。​​病理 三​​：查询 (1) 在 只 有 (symbol, ts)（无 INCLUDE）时 显示 Index Scan 加 Heap Fetch 取 close。处方：drop 后 重建 索引 加 INCLUDE (close, volume)；计划 升级 为 Index Only Scan，回堆 消失。

任何 节点 上 估计 行数 与 实际 行数 相差 10 倍，都 是 ANALYZE 统计 过期 的 普遍 信号——规划器 用 一年前 的 基数 选 出 的 join 顺序，是 在 给 上一份 数据 形状 选 的，不是 给 现在 的。

操作纪律：ANALYZE、VACUUM、pg_stat_statements、事务

统计 过期 出 烂 计划；UPDATE / DELETE 留 的 死 元组 拖 慢 扫描。每个 运维 都 在 跑 的 四 条 单 行 命令：

ANALYZE bars_1m;
VACUUM (ANALYZE) bars_1m;
BEGIN; CREATE INDEX ...; ANALYZE bars_1m; COMMIT;
SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

每 次 大批 加载 之后 ANALYZE bars_1m;——单 一 杠杆 最高 的 习惯；它 拦下 的 性能 回归 比 任何 其他 一条 习惯 都多。大 DELETE 后 跑 VACUUM (ANALYZE) bars_1m; 回收 空间。autovacuum 处理 稳态。BEGIN; ... COMMIT; 包裹 让 多 语句 迁移 原子：半 应用 的 CREATE INDEX + ANALYZE 在 出错 时 整 体 回滚，绝不 停 在 半 应用 态。pg_stat_statements 是 标准 慢 查询 日志——『仓库 慢』 突然 变 一句 话 时，第一 个 该 看 的 地方。事务 隔离 一段 话：Postgres 默认 READ COMMITTED，几乎 所有 量化 批 处理 与 研究 工作 都 正确；SERIALIZABLE 的 少数 用 场 是 跨表 对账 任务 需要 看 到 两 张 表 的 单 一 时间点 快照。一条 提醒：A 股 量化 团队 使用 的 PolarDB-O 兼容 PostgreSQL 的 EXPLAIN 语法，本课 索引 与 计划 知识 直接 复用。

纪律总结

事实 表 纵向 建、维度 表 横向 建。事实 用 自然 复合 主键，维度 用 代理 身份 主键。默认 B 树、追加 时序 用 BRIN、热 切片 用 局部、仅索引 扫描 用 覆盖。每次 大批 加载 之后 ANALYZE。多 语句 迁移 用 BEGIN; ... COMMIT; 包起来。在 信任 一条 查询 快 之前 先 读 EXPLAIN (ANALYZE, BUFFERS)。第 3 课 把 负载 再 推 一档：当 持续 tick 摄入 突破 10 万 行/秒，答案 是 专门 的 时序 数据库。

​本课构件清单​​。Fenced ```sql 代码块：星型 DDL（事实表 bars_1m 加 三 张 维度 instrument、calendar、corp_action）；四 条 CREATE INDEX；三 条 EXPLAIN (ANALYZE, BUFFERS) 诊断 查询；操作 卫生 单行（ANALYZE、VACUUM、BEGIN; ... COMMIT;、pg_stat_statements）。Inline-code：十 个 算子 名 量化 开发 必须 认识。一 个 Exercise。Two Hints。市场 数据 锚 在 A 股 沪深 300 标的 '510050'、'510500'、'510300' 在 上证 / 深证 / CFFEX 的 私募 / 量化 私募 体系，T+1 结算、涨跌停 制度、SSE / SZSE 流通 的 50ETF 与 300ETF。

练习