Re: Last event per user

On Mon, Aug 12, 2019 at 5:03 PM Luís Roberto Weck <
[email protected]> 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.