sql - 如何优化这个 "Time apart"SQL 查询？

Question

我希望有一种简单的方法来对我的时间序列数据运行如下查询:

“相隔七日内最典型的事件是什么”？

我可以通过使用 SQL 和 Java 程序来做到这一点，方法是查看每一行并运行一个查询，该查询会查找早于或晚于 7 天的所有事件，但这不是很优雅，而且性能会很糟糕。

我还得到了 JNK 和 Milen A. Radev 的帮助，编写了以下 SQL。我的问题是，当我在 2300 万行上测试它时，它运行了两个小时然后停止，因为我的 RamDisk(我运行 PostgreSQL 数据库的地方)已满。您知道我该如何优化这样的查询吗？

SELECT a.eventID, b.eventID, COUNT(*)
FROM table a
INNER JOIN table b
    ON a.eventID <> b.eventID
WHERE aBS(EXTRACT(EPOCH FROM (a.thetimeanddate - b.thetimeanddate))) < 5 
GROUP BY a.eventID, b.eventID 
ORDER BY COUNT(*) DESC
LIMIT 1000;

Answer 1

部分问题是某些函数阻止 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] 字段的表上的任何索引。它现在只是为您拥有的 2900 万个事件中的每一个计算出截断的日期，并检查一个索引以查看有多少出现在“这个日期”和“那个日期”之间。很可能比替代方法快几百万倍...

(我也很想将 WHERE 子句移动到 ON 子句中，但仅出于样式目的。性能将相同。请记住，RBDMS 编译这些查询，并选择算法和优化。如果两个查询可以代数操作相同，它们通常会产生相同的最终执行计划。[假设操作所需的所有信息都存在于查询中，而不是“只在你的脑海中知道”。])

编辑

我还注意到您同时按 a.eventID 和 b.eventID 进行分组，然后进行计数。假设 eventID 在表中是唯一的，这将始终产生计数 1...

编辑

将 +5 更改为 + INTERVAL '5 天'