Re: Last event per user

[Luís Roberto Weck <luisroberto@xxxxxxxxxxxxxxx>]

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.