使用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);

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

如果您想避免“排序”， “在查询中，添加这样的索引可能可以帮助您，但我不确定：

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”表本身中。

Answer 2

这可能会更有效率。区别：表 b 的查询仅执行 1 次，相关子查询针对每一行执行：

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 

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

我过去经常使用它

Answer 4

在方法上 - 创建一个包含表 a 上最近更新/插入时间的小型衍生表 - 将此表称为 a_latest。表 a_latest 将需要足够的粒度来满足您的特定查询要求。 就足够了

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

在你的情况下，使用then 使用类似于 najmeddine 建议的查询

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

：然后的技巧是保持 a_latest 是最新的。使用插入和更新触发器来执行此操作。用plppgsql 编写的触发器相当容易编写。如果您愿意，我很乐意提供一个例子。

这里的要点是，最新更新时间的计算是在更新本身期间进行的。这将更多的负载从查询中转移出来。

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 的列表才能获得良好的性能。由于您的 ID 可能是另一表的外键，因此请使用此表。

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)