From: Luís Roberto Weck <luisroberto@siscobra.com.br>
To: Michael Lewis <mlewis@entrata.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Last event per user
Date: Tue, 13 Aug 2019 08:34:58 -0300
Message-ID: <e2e251f8-d3df-e4b1-97c0-6142a873b241@siscobra.com.br> (raw)
In-Reply-To: <CAHOFxGpiuT2Hy+5DgX+G67tMcecuNgZZ5KBT69qG0MPFJ-CUYw@mail.gmail.com>
References: <f2188f91-12ea-cd2b-40b9-eef4a1367252@siscobra.com.br>
<CAHOFxGqhh5T2Uidw6ha404kiX87ipgs4MCGKOoDOqsp=j4Z89g@mail.gmail.com>
<3fba6c11d158490b07afebc5a21c7aa9@siscobra.com.br>
<CAHOFxGpUwcjhs9trJwAkNNhY8doyi2shzCr=2ymLk+-3RzJhrA@mail.gmail.com>
<e3e3f5e059d212c962d8cdb128837549@siscobra.com.br>
<CAHOFxGqT2CQHmP8J_qE3nwLL-3xMSFjjr1cpmTY4Wv4ioEqRVg@mail.gmail.com>
<1ab9f8c65040b2e724e559ce9bc0b8cd@siscobra.com.br>
<CAHOFxGpiuT2Hy+5DgX+G67tMcecuNgZZ5KBT69qG0MPFJ-CUYw@mail.gmail.com>
> On Mon, Aug 12, 2019 at 5:03 PM Luís Roberto Weck
> <luisroberto@siscobra.com.br <mailto:luisroberto@siscobra.com.br>> wrote:
>
>> If you modify last_user_event_2 to select user and event info in
>> the view, and just put there where clause directly on the view
>> which is not joined to anything, instead of on the "extra copy"
>> of the users table like you were showing previously, I would
>> expect that the performance should be excellent.
>>
> But I need user_id and user_group to be outside of the view
> definition. user_id and user_group are dynamic values, as in, I
> need to call this query multiple times for different user_ids and
> user_groups .
>
>
> I don't follow. Perhaps there is something within the limitations of
> the ORM layer that I am not expecting. If you have this view-
>
> "last_user_event_2"
>
> SELECT u.*, 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
>
>
> And you execute a query like this-
>
> SELECT * FROM last_user_event_2 e WHERE user_id = 1272897 and
> user_group = 19117;
>
> Then I would expect very good performance.
You're right, thanks! I just had to do a little adjustment on the
lateral join. Since both users and events have user_id and user_group,
PostgreSQL complains that I can't have more than one column with the
same name. I fixed it by changing the LATERAL condition from "ON TRUE"
to "USING (user_id,user_group)" (which I didn't even knew I could do).
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: <e2e251f8-d3df-e4b1-97c0-6142a873b241@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