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

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 *from log lwhere not exists (select 1
                  from log l2                  where l2.user_id = l.user_id and
                        l2.log_date <= :mydate and
                        l2.log_date > l.log_date                 );

这应该将排序/分组替换为索引查找。可能会更快。