The obfuscation makes it difficult to guess at the query you are writing and the schema you are using. Can you provide any additional information without revealing sensitive info?
1) Do you have an index on ( user_id ASC, timestamp_inc DESC ) ?
2) Sub-queries can't be re-written inline by the optimizer when there is an aggregate inside the subquery, and I think DISTINCT ON would behave the same. So, that might explain the significant change in behavior when the lateral is used. I am guessing at how you wrote the two versions of the view though.
Obviously not best design, but you could insert events as "is_latest" and update any prior events for that user via trigger as is_latest = false.
Thanks for the reply!
the schema is basically this (simplified):
table users (user_id,user_group,user_name)
table events (user_id,user_group,event_id,timestamp_inc,event_description)
Views:
"last_user_event_2"
SELECT 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
"last_user_event_1"
SELECT DISTINCT ON (user_id)
*
FROM events
ORDER BY user_id, timestamp_inc DESC
The query itself is:
SELECT *
FROM users u
JOIN last_user_event_(1|2) e USING (user_id,user_group)
This explain plan: https://explain.depesz.com/s/oyEp is what Postgres uses with "last_user_event_2" and https://explain.depesz.com/s/hWwF, "last_user_event_1"
I do have a btree index on user_id,user_group,timestamp_inc DESC.