帮助优化Postgresql数据库的查询

Question

我正在尝试寻找一些优化查询的建议，我正在使用该查询来获取大量数据。

我正在编写的原始代码遍历了一大组用户，并为每个用户计算了一个日期范围。然后，它将获取该日期范围，并查询他们在该日期范围内回答了多少问题以及正确了多少问题。这些结果已经被统计出来了，我们需要的就是这些最终的统计结果。

为了加快速度(因为它需要几分钟)，我已经做了这样的事情:脚本现在不是单独查询每个用户，而是遍历每个用户，计算适用于他们的日期范围(查询的所有其他方面对于每个用户都是相同的)。这些数据被收集在一个3d数组startDateuserid中，并构建一个查询来对所有用户执行该操作。下面是一个获得输出的查询示例：

SELECT COUNT(uapl.id) AS numAnswered,
SUM(CASE WHEN (a.correct OR q.survey OR uapl.answersId IS NULL) THEN 1 ELSE 0 END) AS numCorrect
FROM usersAnswersProgramsLink uapl
JOIN questions q ON uapl.questionsId=q.id
LEFT JOIN answers a ON uapl.answersId=a.id
WHERE
programsId=123
AND
(
  (
    CAST(timestamp AS date) >= '2009-09-01'
    AND CAST(timestamp AS date) <= '2009-09-21'
    AND usercontextid in('123','234','345','465','567')
  )
  OR
  (
    CAST(timestamp AS date) >= '2009-09-10'
    AND CAST(timestamp AS date) <= '2009-09-21'
    AND usercontextid in('321','432','543')
  )
  OR
  (
    CAST(timestamp AS date) >= '2009-09-16'
    AND CAST(timestamp AS date) <= '2009-09-21'
    AND usercontextid in('987','876')
  )
) 

这在加速代码方面效果相对较好。对于我在这上面运行的大多数测试，它现在需要20%到10%的时间。但在我最坏的情况下，它只有50%，我想要改善这一点。

最坏的情况发生在我有大量的用户id要比较的时候(一万个)。现在的问题是，对于我从中提取这些查询的算法，没有更多的优化工作要做。现在它以毫秒为单位流逝。这个查询需要很长时间。

这就是我的难题。我想让它更快一些。任何建议都将受到欢迎。这里有几条相关的信息：

1)日期范围和用户之间是一对多的关系。这些用户id都不会出现在多个日期范围中。2)我们要寻找的最终结果就是这些计数，但是日期范围需要在每个用户的基础上计算，因此是每个日期范围的id数组。

我认为有一件事可能会让它更快，那就是创建一个临时表，其中包含日期范围的列和用户id的列，然后使用与该表的连接重写查询，而不是将这些数字放在查询本身中。有没有人知道这是否可行？

谢谢你的建议！

Answer 1

如前所述:请提供EXPLAIN ANALYZE <query>的结果以及表结构和创建的索引，否则将很难提供帮助

timestamp::date上的索引可能会有所帮助(由于强制转换，不会使用时间戳上的索引)

您还可以将explain analyze输出发布到http://explain.depesz.com/中，这将突出显示执行计划中有问题的位置

Answer 2

我认为有一件事可能会让它更快，那就是创建一个临时表，其中包含日期范围的列和用户id的列，然后使用与该表的连接重写查询，而不是将这些数字放在查询本身中。有没有人知道这是否可行？

这将是我将采取的方法。这也会使查询更清晰。您也可以将索引添加到临时表中，不过应该在填充数据之后执行此操作。但是，不要假设您需要索引- test。

哦-您可能希望存储时间戳而不是日期(这样可以节省转换)，还可以在answers表中的"timestamp“列上建立索引。

PS -通常认为最好不要使用与内置类型相同的名称命名列。即使数据库不会被混淆，人类读者也可以。

Answer 3

首先，我建议您添加一个粗略的过滤器，使用usercontextid和timestamp上的索引

SELECT  COUNT(uapl.id) AS numAnswered,
        SUM(CASE WHEN (a.correct OR q.survey OR uapl.answersId IS NULL) THEN 1 ELSE 0 END) AS numCorrect
FROM    questions q
JOIN    usersAnswersProgramsLink uapl
ON      uapl.questionsId = q.id
LEFT JOIN
        answers a
ON      a.id = uapl.answersId
WHERE   programsId=123
        AND timestamp >= '2009-09-01'
        AND timestamp < '2009-09-22'
        AND usercontextid IN (/* all possible values here */)
        AND 
(
  (
    CAST(timestamp AS date) >= '2009-09-01'
    AND CAST(timestamp AS date) <= '2009-09-21'
    AND usercontextid in('123','234','345','465','567')
  )
  OR
  (
    CAST(timestamp AS date) >= '2009-09-10'
    AND CAST(timestamp AS date) <= '2009-09-21'
    AND usercontextid in('321','432','543')
  )
  OR
  (
    CAST(timestamp AS date) >= '2009-09-16'
    AND CAST(timestamp AS date) <= '2009-09-21'
    AND usercontextid in('987','876')
  )
)

您还需要澄清所有这些字段属于哪些表。