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 <mailto: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.

You're right, thanks! I just had to do a little adjustment on the
lateral join. Since both users and events have user_id and user_group,
PostgreSQL complains that I can't have more than one column with the
same name. I fixed it by changing the LATERAL condition from "ON TRUE"
to "USING (user_id,user_group)" (which I didn't even knew I could do).