使用 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 发布，翻译遵循 CC BY-SA 4.0 许可协议

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

原文由 intgr 发布，翻译遵循 CC BY-SA 2.5 许可协议

Answer 2

On 方法 - 创建一个包含表 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 发布，翻译遵循 CC BY-SA 2.5 许可协议