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.