PostgreSQL 10.2中如何为user_id+时间范围的自连接创建高效索引？

我在PostgreSQL 10.2数据库中有如下表结构：

CREATE TABLE test (user_id text, event_time timestamp, ...);

需要执行自连接查询，匹配同一user_id且event_time在未来5分钟内的记录，SQL语句如下：

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索引，我尝试过创建基于event_time到其加5分钟的tsrange类型GIST索引、user_id与tsrange的多列索引（不支持）、单独的event_time索引，但都没效果。因为时间戳跨度大，只需要匹配5分钟窗口，感觉应该有合适的索引能优化，请问可行吗？

1. 创建(user_id, event_time)复合B-tree索引 #

这是最适配当前场景的方案，PostgreSQL可以通过这个索引快速定位同一user_id下的所有记录，再在该分组内按event_time的5分钟窗口高效匹配。创建语句：

CREATE INDEX idx_test_userid_eventtime ON test (user_id, event_time);

复合索引会先按user_id分组，再在每个分组内按event_time排序。对于每条b表记录，数据库只需在同一user_id范围内，查找event_time处于[b.event_time - 5分钟, b.event_time)区间的a表记录，比单独的user_id索引减少大量无效扫描。

2. 用窗口函数替代自连接（大数据量场景） #

如果数据量极大，自连接的开销过高，可以尝试用窗口函数筛选符合时间窗口的记录，避免显式自连接：

SELECT a.*, b.*
FROM (
    SELECT 
        *,
        -- 聚合同一user_id下，当前记录前5分钟内的所有时间点
        ARRAY_AGG(event_time) OVER (
            PARTITION BY user_id 
            ORDER BY event_time 
            RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
        ) AS recent_events
    FROM test
) a
JOIN test b 
    ON a.user_id = b.user_id 
    AND b.event_time = ANY(a.recent_events)
    AND b.event_time != a.event_time;

这种方式利用窗口函数提前分组计算符合条件的时间范围，再进行匹配，在有序分组数据上的处理效率通常优于自连接。

3. 验证索引有效性 #

创建索引后，执行EXPLAIN ANALYZE查看原SQL的执行计划，确认数据库是否正确使用了复合索引。如果索引未被选用，可以尝试添加非空约束提示（避免数据库认为NULL值过多而放弃索引），或者临时强制使用索引（仅用于验证）：

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'
WHERE a.user_id IS NOT NULL
AND b.user_id IS NOT NULL
USE INDEX (idx_test_userid_eventtime);

关于之前尝试的索引无效原因 #

• tsrange的GIST索引更适合无分组的范围重叠查询，对于user_id分组+时间窗口的场景，复合B-tree索引的针对性更强。
• PostgreSQL 10中，user_id（text类型）与tsrange的多列GIST索引需要依赖gist__btree_ops操作符类，但这种组合的优化逻辑不如B-tree复合索引直接，所以提升效果不明显。

内容的提问来源于stack exchange，提问作者John Chrysostom