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

< h2>替代方法

但是，以上所有查询仍然需要从表 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屋！