From: Luís Roberto Weck <luisroberto@siscobra.com.br>
To: Michael Lewis <mlewis@entrata.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Last event per user
Date: Mon, 12 Aug 2019 19:28:33 -0300
Message-ID: <3fba6c11d158490b07afebc5a21c7aa9@siscobra.com.br> (raw)
In-Reply-To: <CAHOFxGqhh5T2Uidw6ha404kiX87ipgs4MCGKOoDOqsp=j4Z89g@mail.gmail.com>
References: <f2188f91-12ea-cd2b-40b9-eef4a1367252@siscobra.com.br>
<CAHOFxGqhh5T2Uidw6ha404kiX87ipgs4MCGKOoDOqsp=j4Z89g@mail.gmail.com>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: luisroberto@siscobra.com.br, mlewis@entrata.com, pgsql-performance@lists.postgresql.org
Subject: Re: Last event per user
In-Reply-To: <3fba6c11d158490b07afebc5a21c7aa9@siscobra.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox