Эффективный последний запрос записи с помощью Postgresql

Мне нужно сделать большой запрос, но мне нужны только последние записи.

Для одной записи я, вероятно, сделаю что-то вроде

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

Ответы

Ответ 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" , поэтому, если у вас много данных, все равно может быть слишком медленным.

Вы можете создать новую таблицу, которая содержит только самую новую запись "b" для каждого a.id - или даже переместить эти столбцы в таблицу "a" .

Ответ 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

Ответ 3

В методе - создайте небольшую таблицу производных, содержащую самое последнее время обновления/вставки в таблице 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, довольно легко писать. Я рад представить пример, если вы пожелаете.

Дело здесь в том, что расчет самого последнего времени обновления выполняется во время самих обновлений. Это смещает большую часть нагрузки от запроса.

Ответ 4

что вы думаете об этом?

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

Я использовал его много в прошлом

Ответ 5

Если у вас много строк на идентификатор, вам определенно нужен коррелированный подзапрос. Он будет обрабатывать один индекс на один идентификатор, но это быстрее, чем сортировка всей таблицы.

Что-то вроде:

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

"Таблица2", которую вы будете использовать, - это не та таблица, которую вы упомянули в своем запросе выше, потому что здесь вам нужен список отдельных идентификаторов для хорошей производительности. Так как ваши идентификаторы, вероятно, FKs в другую таблицу, используйте этот.