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