问题的部分原因是某些功能使RDBMS无法推断查询的某些属性,然后搜索任何索引。(有关更多信息,请查看SARGABLE。)
这意味着RDBMS必须处理事件的每种组合,并检查WHERE量表以查看它们是否在5天内。每个组合等于529,000,000,000,000个组合。(5.29亿美元是一个很大的数目。)
如果将查询改写为“ WHERE b.thetimeanddate具有这些属性”,则可能会提高性能。如果您有一个覆盖[thetimeanddate]字段的索引,则会发生这种情况。例如…
SELECT
a.eventID,
b.eventID,
COUNT(*)
FROM
table a
INNER JOIN
table b
ON a.eventID <> b.eventID
WHERE
b.thetimeanddate >= date_trunc('day', a.thetimeanddate) - INTERVAL '5 days'
AND b.thetimeanddate < date_trunc('day', a.thetimeanddate) + INTERVAL '6 days'
GROUP BY
a.eventID,
b.eventID
ORDER BY
COUNT(*) DESC
LIMIT
1000
;
现在,RDBMS应该能够更轻松地利用覆盖[thetimeanddate]字段的表上的任何索引。现在,它只为您拥有的2,900万个事件中的每个事件计算出截短的日期,并检查索引以查看在“此日期”和“该日期”之间出现了多少个事件。可能比其他方法快几百万倍…
(我也很想将WHERE子句移到ON子句中,但仅出于样式目的。性能将是相同的。记住,RBDMS会编译这些查询,并选择算法和优化方法。如果两个查询可以在代数中操作相同的话,它们通常会产生相同的最终执行计划([假设操作所需的所有信息都存在于查询中,并且“在您的脑海中并不为人所知”))
我还注意到,您正在同时按a.eventID和b.eventID进行分组,然后进行计数。假设eventID在表中是唯一的,那么它将始终产生1的计数。
+5改为+ INTERVAL‘5天’