Last event per user
От | Luís Roberto Weck |
---|---|
Тема | Last event per user |
Дата | |
Msg-id | f2188f91-12ea-cd2b-40b9-eef4a1367252@siscobra.com.br обсуждение исходный текст |
Ответы |
Re: Last event per user
|
Список | pgsql-performance |
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.
В списке pgsql-performance по дате отправления: