On Mon, Aug 12, 2019 at 5:03 PM Luís Roberto Weck
<luisrobe...@siscobra.com.br <mailto:luisrobe...@siscobra.com.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).
