Last event per user

Luís Roberto Weck <[email protected]>
Newsgroups gmane.comp.db.postgresql.performance
Message-ID <[email protected]>
Hey guys,

So I have two tables: users and events. It is very common for my 
application to request the last user event.

Usually, what I'll do is get the user, and then SELECT * from events 
WHERE user_id = :user order by timestamp_inc desc LIMIT 1.

I have a big problem, however:

My app uses a ORM for SQL execution and generation and it cant create 
subselects at all. The Ideal solution for me would be a view which has 
all the users last events.

I tried:

creating a view (last_user_event_1) on "SELECT DISTINCT ON (user_id) * 
FROM events ORDER BY user_id, timestamp_inc DESC" and another one 
(last_user_event_2) which is a view on users with a lateral join on the 
last event.

Running the query with lateral join by itself is very fast, and exactly 
what I need. It usually runs < 1ms. The one with "distinct on (user_id)" 
takes around 20ms to complete which is just too slow for my needs.

My problem is that when I run a query JOINing users with 
last_user_event_2, it takes about 2 seconds:

This is the explain output from joining users with "last_user_event_2":

https://explain.depesz.com/s/oyEp

And this is with "last_user_event_1":

https://explain.depesz.com/s/hWwF

Any help would be greatly appreciated.
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.