Re: Last event per user

On Mon, Aug 12, 2019 at 5:03 PM Luís Roberto Weck <
luisroberto(at)siscobra(dot)com(dot)br> wrote:

> If you modify last_user_event_2 to select user and event info in the view,
> and just put there where clause directly on the view which is not joined to
> anything, instead of on the "extra copy" of the users table like you were
> showing previously, I would expect that the performance should be excellent.
>
> But I need user_id and user_group to be outside of the view definition.
> user_id and user_group are dynamic values, as in, I need to call this query
> multiple times for different user_ids and user_groups .
>

I don't follow. Perhaps there is something within the limitations of the
ORM layer that I am not expecting. If you have this view-

"last_user_event_2"

SELECT u.*, e.*

FROM users u

JOIN LATERAL (SELECT *

FROM events

WHERE user_id = u.user_id

AND user_group = u.user_group

ORDER BY timestamp_inc DESC

LIMIT 1 ) e ON TRUE

And you execute a query like this-
SELECT * FROM last_user_event_2 e WHERE user_id = 1272897 and user_group =
19117;

Then I would expect very good performance.