Postgresql高效的最新记录查询

Question

我需要执行一个大查询，但我只想得到最新的记录。

对于单个条目，我可能会做类似这样的事情：

SELECT * FROM table WHERE id = ? ORDER BY date DESC LIMIT 1;

但是我需要获取成千上万条记录中最新的记录，以下是我的代码，但它并不是非常高效。我想知道是否有更好的方法。

SELECT * FROM table a WHERE ID IN $LIST AND date = (SELECT max(date) FROM table b WHERE b.id = a.id);

- Sheldon Ross

---

那么，我的SELECT DISTINCT查询对你有帮助吗？它应该比相关子查询更快，但我不确定快多少。 - intgr

请使用此链接：https://dev59.com/AnI95IYBdhLWcg3w3yJU#2111420 - broderix

如果每个ID有多条记录具有相同的最大日期，则使用MAX会产生重复结果。这可能不是您想要的。 - cliffordheath

Answer 1

如果您不想更改数据模型，您可以使用DISTINCT ON从表"b"中获取每个"a"条目的最新记录：

SELECT DISTINCT ON (a.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY a.id, b.date DESC

如果你想避免查询中的"sort"，添加这样一个索引可能会有所帮助，但我不确定：

CREATE INDEX b_id_date ON b (id, date DESC)

SELECT DISTINCT ON (b.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY b.id, b.date DESC

或者，如果你想以某种方式对表"a"的记录进行排序：

SELECT DISTINCT ON (sort_column, a.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY sort_column, a.id, b.date DESC

替代方案

然而，以上所有的查询仍需要从表 "b" 中读取所有引用的行，所以如果你有大量数据，它可能仍然会太慢。

您可以创建一个新表，仅保存每个a.id的最新"b"记录--甚至将这些列移动到"a"表本身中。

- intgr

---

如果你正在寻找更高效的解决方案，请尝试下面Manji的答案。它的基准测试速度比这里介绍的DISTINCT ON解决方案快了约3倍。 - newUserNameHere

这个人在这方面非常努力：https://dev59.com/Wm865IYBdhLWcg3wduaH#7630564 - ricka

Answer 2

这种方法可能更高效。不同之处在于，查询表b的查询仅被执行一次，而你的相关子查询会为每一行都执行一次：

SELECT * 
FROM table a 
JOIN (SELECT ID, max(date) maxDate
        FROM table
      GROUP BY ID) b
ON a.ID = b.ID AND a.date = b.maxDate
WHERE ID IN $LIST 

- manji

---

看起来很有前途，但连接非常低效。 - Sheldon Ross

你为什么认为连接操作是低效的，特别是它只需要连接一行数据？ - Dmitry

经过测试两种方法后，对我来说max(date)的速度大约比DISTINCT ON快3倍。 - newUserNameHere

真的是个很棒的解决方案！非常感谢！我的查询时间从470毫秒降到了95毫秒。我使用max(id)作为最后一行标识符，所以比日期时间比较更有效率。 - Panoptik

如果我理解正确的话，这样做将不允许从b中提取另一个字段。例如，我想显示任务上的最新评论。 - nafg

Answer 3

你对此有何看法？

select * from (
   SELECT a.*, row_number() over (partition by a.id order by date desc) r 
   FROM table a where ID IN $LIST 
)
WHERE r=1

我过去经常使用它

- unknown

Answer 4

关于方法 - 创建一个小的派生表，其中包含表a上最近的更新/插入时间 - 将这个表称为a_latest。a_latest表需要足够的细粒度以满足您特定的查询要求。在您的情况下，使用以下内容应该是足够的：

CREATE TABLE 
a_latest 
( id INTEGER NOT NULL, 
  date TSTAMP NOT NULL, 
  PRIMARY KEY (id, max_time) );

然后使用类似najmeddine建议的查询：

SELECT a.* 
FROM TABLE a, TABLE a_latest 
USING ( id, date );

关键是保持 a_latest 的最新状态。可以通过插入和更新的触发器来实现。用 plppgsql 编写的触发器相对容易编写。如果您需要，我很乐意提供一个示例。

这里的重点是在更新时处理最新更新时间的计算。这将更多的负载从查询中移走。

- youngthing

Answer 5

如果您的每个ID有很多行，则肯定需要使用相关子查询。这将使每个ID进行1次索引查找，但这比整个表格排序要快。

类似于：

SELECT a.id,
(SELECT max(t.date) FROM table t WHERE t.id = a.id) AS lastdate
FROM table2;

你将使用的“table2”不是你在上面查询中提到的那个表，因为这里需要一个独特id列表以获得更好的性能。由于你的ids可能是另一张表中的FK，请使用这个。

- bobflux

Answer 6

你可以使用一个 NOT EXISTS 子查询来回答这个问题。本质上，你是在说“选择记录... 如果不存在（选择更新的记录）”：

SELECT t.id FROM table t
WHERE NOT EXISTS
    (SELECT * FROM table n WHERE t.id = n.id AND n.date > t.date)

- cliffordheath