时间戳范围和user_id的自连接索引

Question

我在postgresql(10.2)数据库中有一个表，像这样…

create table (user_id text, event_time timestamp, ...);

我想在一个自连接中使用这个表，将记录与来自相同user_id和event_time的其他记录在接下来的5分钟内匹配。像这样…

select
*
from
test as a
inner join
test as b
on
a.user_id = b.user_id
and a.event_time < b.event_time
and a.event_time > b.event_time - interval '5 minutes';

这工作得很好，但我理想地想使它更快。我已经得到了连接使用user_id上的索引，但我想知道是否有可能使索引同时使用user_id和时间戳?

我试过在事件时间到事件时间加5分钟的范围内建立一个gist索引，但Postgres似乎只是在这种情况下使用user_id索引。我尝试在user_id和tsrange上创建多列索引，但似乎不支持。

最后，我尝试仅对时间戳创建索引。

这些似乎都没有帮助。

然而，时间戳覆盖了很长的时间段，我只对5分钟的窗口感兴趣，直观地感觉一个好的索引应该帮助。

这能做到吗?

Answer 1

user_id文本和event_time时间戳上的多列索引应该工作。范围上的gist索引也需要包含用户id，而且它的通用性较差，因为它只能在固定的5分钟间隔内工作。我不会使用它，除非你真的想在表上建立一个排除约束。