时间戳

首页 > 解决方案 > Postgres：如何使用时间间隔在 timestamptz 上设置索引？

sql - Postgres：如何使用时间间隔在 timestamptz 上设置索引？

问题描述

我有一个查询需要使用索引进行优化。有什么方法可以索引 timestamptz 类型的列assigned_at。我正在考虑最初使用assigned_at的GIST索引，有什么方法可以合并它，因为我必须使用now（）或传入可变且无法索引的当前日期时间。

select id from foo f where 
f.context_id = $1 
and (f.assigned_at <= (now() - interval '60 second') or f.assigned_at is null)

打开以获取为此查询设置索引的建议！提前致谢！！

标签： sqlpostgresqldatabase-indexes

解决方案

考虑到您发布的查询，使用复合索引 context_id并且assigned_at应该可以解决问题：

CREATE INDEX idx_foo ON foo (context_id,assigned_at);

根据您的数据，OR条件可能会稍微减慢您的查询速度。另一种方法是将此条件拆分为两个不同的查询：

SELECT * FROM foo f 
WHERE f.context_id = 1 AND f.assigned_at <= (now() - interval '60 second')
UNION ALL
SELECT * FROM foo f WHERE f.context_id = 1 AND f.assigned_at IS NULL;

与往常一样，同时使用查询并测试哪种方法最适合您的数据。

演示：db<>fiddle

推荐阅读