按查询优化组以检索每个用户的最新行

Question

按查询优化组以检索每个用户的最新行

下面是Postgres 9.2中用户消息的日志表(简化形式)：

CREATE TABLE log (
    log_date DATE,
    user_id  INTEGER,
    payload  INTEGER);

它包含每个用户和每天最多一条记录。每天大约有500 K记录，持续300天。每个用户的有效载荷都在不断增加(如果这很重要的话)。

我希望在特定日期之前有效地检索每个用户的最新记录。我的问题是：

SELECT user_id, max(log_date), max(payload) FROM log 
WHERE log_date <= :mydate 
GROUP BY user_id

非常慢。我也尝试过：

SELECT DISTINCT ON(user_id), log_date, payloadFROM logWHERE log_date <= :mydateORDER BY user_id, log_date DESC;

它有着相同的计划，而且同样缓慢。

到目前为止，我有一个单一的索引log(log_date)但没有多大帮助。

我有一个users包含所有用户的表。我还想为一些用户检索结果(payload > :value).

还有其他的索引，我应该使用它来加快速度，或任何其他的方式来实现我想要的吗？

Answer 1

也许一个不同的指数会有所帮助。试试这个：log(user_id, log_date)..我不确定Postgres会在distinct on.所以，我会坚持这个索引，并尝试这个版本：select&nbsp;*from&nbsp;log&nbsp;lwhere&nbsp;not&nbsp;exists&nbsp;(select&nbsp;1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;log&nbsp;l2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;where&nbsp;l2.user_id&nbsp;=&nbsp;l.user_id&nbsp;and &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;l2.log_date&nbsp;<=&nbsp;:mydate&nbsp;and &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;l2.log_date&nbsp;>&nbsp;l.log_date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;);这应该将排序/分组替换为索引查找。可能会更快。