PSQL -在两个独立字段上按间隔过滤查询的性能

Question

我有一个PostgreSQL表，涵盖时间间隔。

这是我的表的简化结构

CREATE TABLE intervals (
name        varchar(40),
time_from   timestamp,
time_to     timestamp
);

表包含数百万条记录，但是，如果您在过去的特定时间点应用过滤器，

所对应的记录数量

time_from <= [requested time] <= time_to

的数量总是非常有限的(不超过3k个结果)。因此，像这样的查询

SELECT *
FROM intervals
WHERE time_from <= '2020-01-01T10:00:00' and time_to >= '2020-01-01T10:00:00'

应该返回相对较少的结果，并且，从理论上讲，如果我使用正确的索引，它应该非常快。但它一点也不快

我尝试在time_from和time_to上添加一个组合索引，但是引擎没有选择它。

Seq Scan on intervals  (cost=0.00..156152.46 rows=428312 width=32) (actual time=13.223..3599.840 rows=4981 loops=1)
Filter: ((time_from <= '2020-01-01T10:00:00') AND (time_to >= '2020-01-01T10:00:00'))
Rows Removed by Filter: 2089650
Planning Time: 0.159 ms
Execution Time: 3600.618 ms

我应该添加什么类型的索引，以加快这个查询?

Answer 1

b树索引在这里不是很有效。它可以快速抛出time_from>'2020-01-01T10:00:00'，但这可能不是表格的全部内容(至少，如果你的表格是多年前的)。一旦索引的第一列以这种方式被使用，下一列就不能非常有效地使用了。它只能跳转到time_from关联中的time_to值的特定部分，这不是很有用，因为可能没有那么多关联。(至少，它不能在规划查询时证明自己)。

你需要的是一个主旨索引，它专门用于这种多维事物:

create extension btree_gist ;
create index on intervals using gist (time_from,time_to);

这个索引将支持您的查询。另一种可能性是对时间范围进行索引，而不是将开始点和结束点分开。

-- this one does not need btree_gist.
create index on intervals using gist (tsrange(time_from,time_to));

但是这个索引强制您以不同的方式编写查询:

SELECT * FROM intervals
WHERE tsrange(time_from,time_to) @> '2020-01-01T10:00:00'::timestamp