Re: Last event per user

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