如何优化此“分离” SQL查询？

admin

如何优化此“分离” SQL查询？

sql

我想有一种简单的方法来对我的时间序列数据运行类似以下查询的查询：

“彼此相隔7天之内最典型的事件是什么？”

我可以利用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;

阅读 471

2021-07-01

共1个答案

admin

问题的部分原因是某些功能使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天’

2021-07-01