Select newest entry for each user without using group by (postgres)

Question

I have a table myTable with four columns:

id        UUID,
user_id   UUID ,
text      VARCHAR ,
date      TIMESTAMP

(id is the primary key and user_id is not unique in this table)

I want to retrieve the user_ids ordered by their newest entry, which i am currently doing with this query:

SELECT user_id FROM myTable GROUP BY user_id ORDER BY MAX(date) DESC

The problem is that GROUP BY takes a long time. Is there a faster way to accomplish this? I tried using a window function with PARTITION BY as described here Retrieving the last record in each group - MySQL, but it didn't really speed things up. I've also made sure that user_id is indexed.

My postgres version is 10.4

Edit: The query above that I'm currently using is functionally correct, the problem is that it's slow.

What do you mean with "ordered by their newest entry"? Do you want to see **only** the newest entry (what your title suggests) or **all** of them, but with some kind of special sort order? — , Jul 29 '20 at 14:23
I want **all** the user_ids, sorted by each user_id's newest date. The query above is correct, it's just slow. I'm not looking for complete entries, only the user_ids. — guest856, Jul 29 '20 at 14:27
Then why can't you simply use `select user_id from the_table order by user_id, "date" desc`? — , Jul 29 '20 at 14:38
Perhaps i'm explaining this poorly, I want all the user_ids, but each user_id exactly one time. — guest856, Jul 29 '20 at 15:15

GMB · Answer 1 · 2020-07-29T14:34:10.880

3

Your query seems like a relevant approach for your requirement:

select user_id 
from mytable 
group by user_id 
order by max(date) desc

I would recommend an index on (user, date desc) to speed things up. It needs to be a single index on both colums.

You could also give a try to distinct on, which might, or might not, give you better performance:

select user_id
from (
    select distinct on(user_id) user_id, date
    from mytable
    order by user_id, date desc
) t
order by date desc

edited Jul 29 '20 at 14:34

answered Jul 29 '20 at 14:27

GMB

216,147
25
84
135

index should be on `(user_id, date DESC)` – Vesa Karjalainen Jul 29 '20 at 14:30
@VesaKarjalainen: ah yes, obviously... Thanks. – GMB Jul 29 '20 at 14:34
1

I end up liking this PostgreSQL SQL extension. It's very handy. – The Impaler Jul 29 '20 at 14:42
@GMB And now, of course, I would love to be able to keep more than one row per group. Maybe somethign like `distinct (5) on (user_id)`? I know, I know. I'm asking for too much, now :D – The Impaler Jul 29 '20 at 14:48
@TheImpaler: nope, `distinct on` can't do that... In that case, you need to resort the good-old `row_number()` approach. – GMB Jul 29 '20 at 14:58
I want to accept your answer as well, but alas stack overflow won't let me do that :( – guest856 Jul 29 '20 at 15:05

score 1 · Accepted Answer · answered Jul 29 '20 at 14:26

Start with an index on user_id, date desc. That might help.

You can also try filtering -- once you have such an index:

select t.user_id
from myTable t
where t.date = (select max(t2.date)
                from myTable t2
                where t2.user_id = t.user_id
               )
order by t.date desc

However, you might find that the order by ends up taking almost as much time as the group by.

This version will definitely use the index for the subquery:

select user_id
from (select distinct on (user_id) user_id, date
      from myTable t
      order by user_id, date desc
     ) t
order by date desc;

Adding the index sped things up :). In addition, both your suggestions turned out to be faster than my current query, although the first approach gives some duplicates, presumably because some user_ids have multiple entries with the same newest date. — guest856, Jul 29 '20 at 15:03

Select newest entry for each user without using group by (postgres)

2 Answers2