Betalens新手系列 · 08 PostgreSQL 查询优化：time_tolerance 与索引策略

导言

大多数时候，Betalens 的 Datafeed 查询足够快，你不需要关心 SQL 怎么写。但当数据量上来之后——比如多年全市场行情、多因子并行预查询——查询时间会从几秒跳到几十秒甚至几分钟。

这篇文章从两个角度讲优化：一个是 Betalens 内部的 time_tolerance 参数到底在控制什么；另一个是 PostgreSQL 层面的索引和分区策略。

time_tolerance 的真实含义

在 pre_query_characteristic_data 和 BacktestBase 里，都有一个 time_tolerance 参数。它的单位是小时，默认值不同：

pre_query_characteristic_data 中的 tolerance

1
2
3
4
5
6
7
8

data = pre_query_characteristic_data(
    days,
    "股息率(报告期)",
    table_name="fundamentals",
    date_ranges=date_ranges,
    code_ranges=code_ranges,
    time_tolerance=24 * 2 * 365,  # 默认：最多往前找2年
)

tolerance=175200 小时的含义：如果某只股票在调仓日没有股息率数据，框架最多往前找 2 年内的最近一条。这是为了处理财务数据”披露滞后”的问题——比如在 2021-04-01 调仓，2020 年的年报可能还没披露，框架会自动取 2019 年甚至更早的股息率。

设太大（24*10*365）：查询范围过大，速度变慢，数据”过期”。
设太小（24*30）：如果最近一期财报还没披露，就查不到任何数据，导致该股票在调仓日被排除。

调优建议：

1
2
3
4
5
6
7
8
9

# 财务因子（年报披露滞后3-4个月）：用2年 tolerance
data = pre_query_characteristic_data(..., time_tolerance=24*2*365)

# 日频指标（换手率等）：用1天 tolerance
data = pre_query_characteristic_data(
    ...,
    table_name="daily_market",
    time_tolerance=24,  # 最多往前找1天
)

BacktestBase 中的 tolerance

1
2
3
4
5
6
7

engine = BacktestBase(
    weight=weights,
    symbol="Dividend",
    amount=1_000_000,
    table_name="daily_market",
    time_tolerance=24,  # 默认：价格数据最多缺失1天
)

tolerance=24 的含义：如果某只股票在某天的收盘价缺失，最多往前找1天内的最近价格。这个容差用于处理极端情况（如数据源偶尔缺失某一天的价格）。

PostgreSQL 索引策略

为什么要关心索引

Betalens 的 Datafeed 底层是 SQL 查询。如果你在 market_daily_fact 上做全表扫描（没有索引），查询全市场 10 年日行情：

1
2

SELECT * FROM market_daily_fact
WHERE trade_date BETWEEN '2014-01-01' AND '2024-12-31';

在没有索引的情况下，PostgreSQL 会做一次全表扫描——observation_fact 或 market_daily_fact 如果有几千万行，这个查询会非常慢。

主键索引

market_daily_fact 和 observation_fact 都有复合主键：

1
2
3
4
5

-- market_daily_fact
PRIMARY KEY (entity_id, trade_date)

-- observation_fact
PRIMARY KEY (entity_id, metric_id, trade_date)

主键本身就是一个 B-tree 索引，对 entity_id + trade_date 的组合查询非常高效。这就是为什么 query_time_range 按 (code, date_range) 查询时很快。

BRIN 索引（推荐用于时序数据）

BRIN（Block Range Index）是一种轻量级索引，专门适合物理存储顺序和逻辑顺序一致的数据（比如按日期存储的行情表）。它比 B-tree 索引小很多，对大表的范围查询效果也很好。

1
2
3
4
5
6
7
8
9
10
11

-- 为 trade_date 列创建 BRIN 索引
CREATE INDEX idx_market_daily_trade_date
ON market_daily_fact USING BRIN (trade_date);

-- 为 observation_fact 的 trade_date 创建 BRIN 索引
CREATE INDEX idx_observation_trade_date
ON observation_fact USING BRIN (trade_date);

-- 为 observation_fact 的 value_end_date 创建 B-tree 索引（PIT 查询需要）
CREATE INDEX idx_observation_value_end_date
ON observation_fact USING BTREE (value_end_date);

betalens_db_manager 在 init 时不会自动创建这些额外索引。如果你确定要优化，需要手动执行上述语句（一次性的，DBA 操作）。

复合索引：PIT 查询优化

PIT 查询（query_nearest_before）有一个固定模式：

1
2
3
4
5
6

WHERE metric_id = :mid
  AND entity_id = :eid
  AND trade_date <= :query_date
  AND value_end_date <= :query_date
ORDER BY trade_date DESC
LIMIT 1

如果这个查询很慢，可以在 observation_fact 上建一个复合索引：

1
2

CREATE INDEX idx_observation_pit
ON observation_fact (metric_id, entity_id, trade_date DESC, value_end_date DESC);

注意索引列的顺序：等于条件（metric_id、entity_id）放前面，范围条件（trade_date DESC）放后面。

分区表（超大数据量场景）

如果你的数据量超过 1 亿行（比如分钟级 Tick 数据），单表查询会成为瓶颈。此时可以对 market_daily_fact 做范围分区（按年份或月份）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 创建主表（不带数据）
CREATE TABLE market_daily_fact_partitioned (
    entity_id    BIGINT NOT NULL,
    trade_date   DATE NOT NULL,
    开盘价(元)   DECIMAL,
    ...
) PARTITION BY RANGE (trade_date);

-- 创建 2024 年分区
CREATE TABLE market_daily_fact_2024
PARTITION OF market_daily_fact_partitioned
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 将现有数据迁移到分区表
INSERT INTO market_daily_fact_partitioned SELECT * FROM market_daily_fact;

分区后，查询某一年数据时 PostgreSQL 只扫描对应分区，不会全表扫描。

警告：分区是 DBA 操作，错误分区会导致数据丢失。在做分区之前，务必备份数据库。 Betalens 框架本身不依赖特定的表结构，分区后只要表名和字段不变，Datafeed 查询不受影响。

查询计划分析

在优化之前，先看查询计划（EXPLAIN）：

1
2
3
4
5
6
7
8
9

from betalens.datafeed import Datafeed

data = Datafeed("daily_market")
# Datafeed 不直接暴露 EXPLAIN，但可以通过 raw connection：
with data.conn.cursor() as cur:
    cur.execute("EXPLAIN ANALYZE SELECT * FROM market_daily_fact WHERE entity_id = 1 AND trade_date >= '2024-01-01'")
    for row in cur:
        print(row)
data.close()

关注几个关键指标：

• Seq Scan：全表扫描，如果出现在大表上，需要加索引。
• Index Scan：用到了索引，好的。
• Rows：预估行数，如果和实际差异很大（>10x），说明统计信息过时，需要 ANALYZE。

1
2
3

-- 更新统计信息（解决预估不准的问题）
ANALYZE market_daily_fact;
ANALYZE observation_fact;

开发者侧：Datafeed 内部的查询优化

Betalens Datafeed 在查询层做了一些内置优化：

1. 批量 IN 查询：多只股票查询时，内部会合并成一条 SQL 的 IN 条件，而不是逐只循环。
2. PIT 查询的 LIMIT 1 优化：每个 entity/metric 组合只取一条最新记录，SQL 层面用 LIMIT 1 加 ORDER BY 配合索引。
3. 连接复用：Datafeed 维护一个连接池，频繁查询时不需要每次重新建立连接。

如果你发现某个查询仍然很慢，先用 EXPLAIN ANALYZE 确定瓶颈（是 Datafeed 的 Python 端，还是 PostgreSQL 的 SQL 执行），再决定是加索引、改 tolerance、还是换分区方案。

常见错误

1. 索引建反了顺序

1
2
3
4
5

-- 错：把范围列放在前面
CREATE INDEX idx_wrong ON observation_fact (trade_date, entity_id, metric_id);

-- 对：等值列放前面，范围列放后面
CREATE INDEX idx_right ON observation_fact (metric_id, entity_id, trade_date);

2. time_tolerance 太小导致数据缺失

1
2
3
4

# 财务因子设了1个月 tolerance，4月调仓时年报还没披露，查不到数据
data = pre_query_characteristic_data(..., time_tolerance=24*30)  # 太短！
# 修复：用默认的 2 年 tolerance
data = pre_query_characteristic_data(..., time_tolerance=24*2*365)

3. 做了分区但查询条件没包含分区键

1
2
3
4
5
6

-- 如果查询不带 trade_date 条件，会扫描所有分区（性能灾难）
SELECT * FROM market_daily_fact_partitioned WHERE entity_id = 1;

-- 对：带上 trade_date，PostgreSQL 可以剪枝掉不需要的分区
SELECT * FROM market_daily_fact_partitioned
WHERE entity_id = 1 AND trade_date >= '2024-01-01';

延伸阅读

• Betalens 新手系列 · 05：数据库架构全图——fact/dim 架构。
• Betalens 新手系列 · 06：Datafeed API 实战——查询接口详解。
• Betalens 新手系列 · 07：数据治理工具 betalens_db_manager——数据导入。
• docs/guide/db-manager.rst——官方数据库管理文档。
• PostgreSQL 官方文档：BRIN Indexes、Partitioning。