使用 Postgresql 进行高效的最新记录查询 [英] Efficient latest record query with Postgresql

问题描述

我需要做一个大查询，但我只想要最新的记录.

I need to do a big query, but I only want the latest records.

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

For a single entry I would probably do something like

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

但我需要为大量(数千条条目)记录提取最新记录，但只提取最新条目.

But I need to pull the latest records for a large (thousands of entries) number of records, but only the latest entry.

这就是我所拥有的.这不是很有效.我想知道是否有更好的方法.

Here's what I have. It's not very efficient. I was wondering if there's a better way.

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

推荐答案

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

If you don't want to change your data model, you can use DISTINCT ON to fetch the newest record from table "b" for each entry in "a":

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

如果你想避免在查询中排序"，添加这样的索引可能对你有帮助，但我不确定:

If you want to avoid a "sort" in the query, adding an index like this might help you, but I am not sure:

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"中的记录进行排序:

Alternatively, if you want to sort records from table "a" some way:

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"中读取所有引用的行，所以如果您有大量数据，它可能仍然太慢了.

Alternative approaches

However, all of the above queries still need to read all referenced rows from table "b", so if you have lots of data, it might still just be too slow.

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

You could create a new table, which only holds the newest "b" record for each a.id -- or even move those columns into the "a" table itself.

这篇关于使用 Postgresql 进行高效的最新记录查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！