Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hxV0p-00015F-Ne for pgsql-performance@arkaria.postgresql.org; Tue, 13 Aug 2019 11:30:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hxV0o-0004gJ-Ht for pgsql-performance@arkaria.postgresql.org; Tue, 13 Aug 2019 11:30:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hxV0o-0004gA-8h for pgsql-performance@lists.postgresql.org; Tue, 13 Aug 2019 11:30:46 +0000 Received: from mail4990.hm1479.locaweb.com.br ([201.76.49.90]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hxV0i-0006MQ-FB for pgsql-performance@lists.postgresql.org; Tue, 13 Aug 2019 11:30:44 +0000 Received: from mcbain0013.correio.biz (189.126.112.18) by mail4970.hm1479.locaweb.com.br id haafko16a4cn for ; Tue, 13 Aug 2019 08:30:13 -0300 (envelope-from ) Received: from proxy.email-ssl.com.br (bartf0031.email.locaweb.com.br [10.31.120.63]) by mcbain0013.correio.biz (Postfix) with ESMTP id 79D28D40117; Tue, 13 Aug 2019 08:30:26 -0300 (-03) x-locaweb-id: qyHzKK_R8aWhjwoIerpCUhsi5PfCUymhK53qXIAegdcRogeYX3-XTadcSpgGGxitWQf3rKGbFuqvLmn5XdJ_XeDnSC7FYZn7HbLXpGF-9IgCFmgQGgrxCDEo15F9LB44OnsLfXjEQNdayh8qp-s9AotEt2KUQ2RJBl9oRDY6_jQGONTy23UbTLyu956pUuVT8UlUgaIM6XARSVi84RGoZ3y_BYb-aN8PXpaI7MRvykI= NmM3NTY5NzM3MjZmNjI2NTcyNzQ2ZjQwNzM2OTczNjM2ZjYyNzI2MTJlNjM2ZjZkMmU2Mjcy x-locaweb-id: qyHzKK_R8aWhjwoIerpCUhsi5PfCUymhK53qXIAegdcRogeYX3-XTadcSpgGGxitWQf3rKGbFuqvLmn5XdJ_XeDnSC7FYZn7HbLXpGF-9IgCFmgQGgrxCDEo15F9LB44OnsLfXjEQNdayh8qp-s9AotEt2KUQ2RJBl9oRDY6_jQGONTy23UbTLyu956pUuVT8UlUgaIM6XARSVi84RGoZ3y_BYb-aN8PXpaI7MRvykI= NmM3NTY5NzM3MjZmNjI2NTcyNzQ2ZjQwNzM2OTczNjM2ZjYyNzI2MTJlNjM2ZjZkMmU2Mjcy X-LocaWeb-COR: locaweb_2009_x-mail X-AuthUser: luisroberto@siscobra.com.br Received: from [192.168.0.42] (unknown [189.45.198.123]) (Authenticated sender: luisroberto@siscobra.com.br) by proxy.email-ssl.com.br (Postfix) with ESMTPSA id 7B22AB00250; Tue, 13 Aug 2019 08:30:31 -0300 (-03) Cc: "pgsql-performance@lists.postgresql.org" Subject: Re: Last event per user To: Michael Lewis References: <3fba6c11d158490b07afebc5a21c7aa9@siscobra.com.br> <1ab9f8c65040b2e724e559ce9bc0b8cd@siscobra.com.br> From: =?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= Message-ID: Date: Tue, 13 Aug 2019 08:34:58 -0300 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:60.0) Gecko/20100101 Thunderbird/60.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------21DF11E942B694B151D9C6EE" Content-Language: pt-BR List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------21DF11E942B694B151D9C6EE Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit > On Mon, Aug 12, 2019 at 5:03 PM Luís Roberto Weck > > 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). --------------21DF11E942B694B151D9C6EE Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On Mon, Aug 12, 2019 at 5:03 PM Luís Roberto Weck <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).
--------------21DF11E942B694B151D9C6EE--