时间戳

首页 > 解决方案 > 范围包含不使用元素列索引的元素过滤器

postgresql - 范围包含不使用元素列索引的元素过滤器

问题描述

我有一个带有时间戳列的表，该列具有 btree 索引。

CREATE TABLE tstest AS
SELECT '2019-11-10'::timestamp + random() * '10 day'::interval ts
FROM generate_series(1,10000) s;

CREATE INDEX ON tstest(ts);

我想找到一个时间范围之间的所有行。范围的两端可以是“无限”/null，范围的开始必须是独占的，结束是包含的。所以这形成了一个查询：

SELECT * FROM tstest WHERE ts <@ tsrange($1, $2, '(]');

结果是正确的，但没有使用 ts 列的索引，而是进行了 seq 扫描。

要正确使用索引，我必须执行如下查询：

SELECT * FROM tstest WHERE  ($1 IS null OR $1 < ts) AND ($2 IS null OR ts <= $2);

我更喜欢 <@ 语法。它更容易理解并且更短。
有什么我可以做不同的事情来利用索引并使查询更快吗？也许是不同类型的索引？

我还尝试使用btree_gist模块为 ts 列添加一个 gist 索引，但这并没有改变这种情况。

我已经用 PostgreSQL 9.6 和 12.0 对此进行了测试。

标签： postgresql

解决方案

btree 索引（您正在使用）不支持<@运算符，GiST 索引也无济于事，因为时间戳列不是范围类型。

但是您仍然可以通过使用 coalesce 使 btree 索引可用，infinity从而消除了对OR条件的使用：

SELECT * 
FROM tstest 
WHERE ts > coalesce($1, '-infinity') 
  and ts <= coalesce($2, 'infinity');

推荐阅读