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.