PostgreSQL时间戳范围查询复合索引未生效问题排查

架构老林

2026-8-30

问题背景

现有存储事件记录的event表，共200万行数据，表结构如下：

create table event
(
    id         serial
        constraint event_pk
            primary key,
    type       text      not null,
    start_date timestamp not null,
    end_date   timestamp not null,
    val        text
);

业务需要查询和指定时间窗口存在重叠的所有事件，SQL逻辑如下：

EXPLAIN (analyse, buffers, format text)
SELECT *
from event
WHERE end_date >= '2010-01-12T18:00:00'::timestamp -- 事件结束时间晚于窗口起点
  AND start_date <= '2010-01-13T00:00:00'::timestamp; -- 事件开始时间早于窗口终点

此前尝试创建复合B树索引优化该查询：

create index my_index
    on event (end_date, start_date desc);

但该索引未被使用，执行计划显示走全表顺序扫描，耗时201ms：

Seq Scan on event  (cost=0.00..53040.01 rows=1967249 width=57) (actual time=0.142..149.163 rows=1971694 loops=1)
  Filter: ((end_date >= '2010-01-12 18:00:00'::timestamp without time zone) AND (start_date <= '2010-01-13 00:00:00'::timestamp without time zone))
  Rows Removed by Filter: 28307
  Buffers: shared hit=15762 read=7278
Planning:
  Buffers: shared hit=4
Planning Time: 0.127 ms
Execution Time: 201.610 ms

作为对比，创建另一组复合B树索引、执行不同逻辑的时间范围查询时，索引可以正常命中，仅耗时9ms：

-- 对比用索引
create index simple
    on event (start_date, end_date DESC);

-- 对比用查询：事件开始时间晚于窗口起点，且结束时间早于窗口终点
SELECT *
from event
WHERE event.start_date >= '2010-01-12T18:00:00'::timestamp
  AND event.end_date <= '2011-01-13T00:00:00'::timestamp;

对应命中索引的执行计划：

Bitmap Heap Scan on event  (cost=466.91..23418.68 rows=18035 width=57) (actual time=1.954..8.551 rows=15944 loops=1)
  Recheck Cond: ((start_date >= '2010-01-12 00:00:00'::timestamp without time zone) AND (end_date <= '2011-01-13 00:00:00'::timestamp without time zone))
  Heap Blocks: exact=7694
  Buffers: shared hit=7734 read=26
  ->  Bitmap Index Scan on simple  (cost=0.00..462.40 rows=18035 width=0) (actual time=1.314..1.314 rows=15944 loops=1)
        Index Cond: ((start_date >= '2010-01-12 00:00:00'::timestamp without time zone) AND (end_date <= '2011-01-13 00:00:00'::timestamp without time zone))
        Buffers: shared hit=55 read=11
Planning:
  Buffers: shared hit=8
Planning Time: 0.133 ms
Execution Time: 9.025 ms

需要明确两个核心问题：

为什么对比场景下复合索引可以正常命中，而针对目标查询建的索引没有生效？
目标时间重叠查询应该怎么建索引才能获得稳定的高效率？

问题1：索引命中差异的核心原因

两个场景的索引表现差异，本质是两个因素共同作用的结果：

B树复合索引的生效规则
PostgreSQL优化器的成本计算逻辑

B树复合索引的过滤逻辑

B树复合索引是按索引列的顺序逐层排序的：先按第一列排序，第一列取值相同的条目再按第二列排序，以此类推。做范围查询时，只有第一列的范围可以被索引直接定位为连续的切片；在第一列的范围切片内，第二列是有序的，可以继续做范围过滤，但如果第一列的范围跨度很大，第二列的过滤需要遍历整个第一列的切片范围。

两个场景创建的复合索引本身都符合B树匹配规则，都是可以被数据库调用的，真正导致执行计划选择差异的是结果集占比：

对比场景的查询最终返回15944行，仅占全表200万行的0.8%，这种情况下通过索引定位条目再回表取数据的随机IO成本，远低于全表顺序扫描的成本，优化器自然选择走索引。
目标查询的最终返回1971694行，占全表数据的98.5%，仅过滤掉了2.8万行不符合条件的数据。这种情况下如果走B树索引，需要对近200万条索引条目做定位、再逐一回表取整行数据，随机IO的成本会远高于直接顺序扫描全表的成本，优化器选择全表扫描是完全正确的成本决策，不是索引建错了，也不是索引失效。

可以做个简单验证：把目标查询的时间窗口缩小到1~5分钟，让符合条件的行数降到几万甚至几千行，再看执行计划，之前建的(end_date, start_date desc)索引会正常命中。

问题2：时间重叠查询的最优索引方案

这种「判断事件和时间窗口是否重叠」的查询，是典型的范围相交查询，普通B树索引无法在所有查询窗口大小下都保持高效率，最优方案是使用GiST索引配合PostgreSQL内置的范围类型：

（推荐，PG14及以上版本支持）添加存储时间范围的生成列，不需要修改业务写入逻辑：

ALTER TABLE event 
ADD COLUMN event_range tsrange 
GENERATED ALWAYS AS (tsrange(start_date, end_date, '[]')) STORED;

如果是PG14以下版本，可以跳过加列步骤，直接建表达式索引。

创建GiST索引：

-- 有加生成列的情况
CREATE INDEX idx_event_range_gist ON event USING GIST (event_range);

-- 不加列、直接建表达式索引的情况
CREATE INDEX idx_event_range_gist ON event USING GIST (tsrange(start_date, end_date, '[]'));

查询时用范围重叠操作符&&做匹配，逻辑和原SQL完全等价：

-- 用生成列的写法
SELECT * FROM event
WHERE event_range && tsrange('2010-01-12T18:00:00'::timestamp, '2010-01-13T00:00:00'::timestamp, '[]');

-- 用表达式索引的写法
SELECT * FROM event
WHERE tsrange(start_date, end_date, '[]') && tsrange('2010-01-12T18:00:00'::timestamp, '2010-01-13T00:00:00'::timestamp, '[]');

这种方案的优势是，不管查询的时间窗口多大、符合条件的结果集占比多少，GiST索引都可以高效过滤出符合重叠条件的记录，不会出现B树索引因为结果集占比高被优化器放弃的问题，性能表现稳定。

如果因为特殊原因无法使用GiST索引，只能用B树的话，不需要强行调整索引结构：当查询窗口小、结果集占比低时，之前建的(end_date, start_date desc)索引会自动生效；当查询窗口大、结果集占比高时，全表扫描本身就是效率更高的选择，不需要强制走索引。

内容的提问来源于stack exchange，提问作者Vladislav Kiper

PostgreSQL时间戳范围查询复合索引未生效问题排查

B树复合索引的过滤逻辑 #

B树复合索引的过滤逻辑