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 1hxIo5-0004Ej-Ix for pgsql-performance@arkaria.postgresql.org; Mon, 12 Aug 2019 22:28:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hxIo4-0007sk-DZ for pgsql-performance@arkaria.postgresql.org; Mon, 12 Aug 2019 22:28:48 +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 1hxIo4-0007sb-54 for pgsql-performance@lists.postgresql.org; Mon, 12 Aug 2019 22:28:48 +0000 Received: from hm1481-5.locaweb.com.br ([201.76.49.127]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hxInz-0006ev-BW for pgsql-performance@lists.postgresql.org; Mon, 12 Aug 2019 22:28:47 +0000 Received: from mcbain0011.correio.biz (189.126.112.14) by hm1481.locaweb.com.br id ha7jvq169v0r for ; Mon, 12 Aug 2019 19:28:06 -0300 (envelope-from ) Received: from moe0166.email.locaweb.com.br (unknown [10.31.120.111]) by mcbain0011.correio.biz (Postfix) with ESMTP id 10AA3EC0398; Mon, 12 Aug 2019 19:28:55 -0300 (-03) x-locaweb-id: qyHzKK_R8aWhjwoIerpCUhsi5PfCUymhK53qXIAegdcRogeYX3-XTadcSpgGGxitWQf3rKGbFuqvLmn5XdJ_XeDnSC7FYZn7HbLXpGF-9Ijnyhi0HYcMFDJYmHUvxBBkNpCEMoE-bP6ezYctSSAGAiUQTdcn44n38CMMTur8AtK2CBQxYrL8BXB-mYlvBizJAEF_8856gTBS-um1KjH5QQ== NmM3NTY5NzM3MjZmNjI2NTcyNzQ2ZjQwNzM2OTczNjM2ZjYyNzI2MTJlNjM2ZjZkMmU2Mjcy x-locaweb-id: qyHzKK_R8aWhjwoIerpCUhsi5PfCUymhK53qXIAegdcRogeYX3-XTadcSpgGGxitWQf3rKGbFuqvLmn5XdJ_XeDnSC7FYZn7HbLXpGF-9Ijnyhi0HYcMFDJYmHUvxBBkNpCEMoE-bP6ezYctSSAGAiUQTdcn44n38CMMTur8AtK2CBQxYrL8BXB-mYlvBizJAEF_8856gTBS-um1KjH5QQ== NmM3NTY5NzM3MjZmNjI2NTcyNzQ2ZjQwNzM2OTczNjM2ZjYyNzI2MTJlNjM2ZjZkMmU2Mjcy X-AuthUser: luisroberto@siscobra.com.br Received: from moe0166.webmail-seguro.com.br (localhost [IPv6:::1]) (Authenticated sender: luisroberto@siscobra.com.br) by moe0166.email.locaweb.com.br (Postfix) with ESMTPA id 131C2500062; Mon, 12 Aug 2019 19:28:33 -0300 (-03) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_f22b7b27e1fa484cdfb513069496c311" Date: Mon, 12 Aug 2019 19:28:33 -0300 From: =?UTF-8?Q?Lu=C3=ADs_Roberto_Weck?= To: Michael Lewis Cc: pgsql-performance@lists.postgresql.org Subject: Re: Last event per user Organization: Siscobra Sistemas In-Reply-To: References: Message-ID: <3fba6c11d158490b07afebc5a21c7aa9@siscobra.com.br> X-Sender: luisroberto@siscobra.com.br User-Agent: Roundcube Webmail/1.3.8 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=_f22b7b27e1fa484cdfb513069496c311 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII > 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. --=_f22b7b27e1fa484cdfb513069496c311 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8

The obfuscation makes it difficult to guess at the query you are wr= iting and the schema you are using. Can you provide any additional informat= ion 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 t= he 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 vi= ew 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 =3D= 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_descriptio= n)

Views:

"last_user_event_2"

SELECT e= =2E*

  &= nbsp;FROM users u

  &= nbsp;     JOIN LATERAL (SELECT *

  &= nbsp;                    = FROM events

  &= nbsp;                    = WHERE user_id =3D u.user_id

  &= nbsp;                    =  AND user_group =3D u.user_group 

  &= nbsp;                    = ORDER BY timestamp_inc DESC

  &= nbsp;                    = LIMIT 1 ) e ON TRUE


"last_user_event_1"


SELECT D= ISTINCT ON (user_id)

  &= nbsp;    *

  F= ROM events

 OR= DER BY user_id, timestamp_inc DESC

 =

The query its= elf  is:


SELECT *=

  F= ROM users u

  &= nbsp;    JOIN last_user_event_(1|2) e USING (user_id,user_gr= oup)



This explain = plan: https://explain.de= pesz.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 b= tree index on user_id,user_group,timestamp_inc DESC.




--=_f22b7b27e1fa484cdfb513069496c311--