From: Luís Roberto Weck <luisroberto@siscobra.com.br>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Last event per user
Date: Mon, 12 Aug 2019 17:57:46 -0300
Message-ID: <f2188f91-12ea-cd2b-40b9-eef4a1367252@siscobra.com.br> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: luisroberto@siscobra.com.br, pgsql-performance@lists.postgresql.org
Subject: Re: Last event per user
In-Reply-To: <f2188f91-12ea-cd2b-40b9-eef4a1367252@siscobra.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox