Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sNFkz-007a4i-S5 for pgsql-general@arkaria.postgresql.org; Fri, 28 Jun 2024 17:51:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sNFkw-0065JZ-Fu for pgsql-general@arkaria.postgresql.org; Fri, 28 Jun 2024 17:51:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sNFkv-0065JQ-Kt for pgsql-general@lists.postgresql.org; Fri, 28 Jun 2024 17:51:30 +0000 Received: from sender4-op-o12.zoho.com ([136.143.188.12]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sNFkr-003aW2-1Y for pgsql-general@lists.postgresql.org; Fri, 28 Jun 2024 17:51:28 +0000 ARC-Seal: i=1; a=rsa-sha256; t=1719597079; cv=none; d=zohomail.com; s=zohoarc; b=O7ErkF98g3biiDu/9ZKlvy7u1XNmzFSszas6VtOU4qygz+fG4Lj5VhLuUbkDMfY+c4/Q9kvzu5YbSFORl49lCNMNvv+9nM6v2H7b5fkFQy0EJ6/fY3b3Kp+M6OFGwdpsdyWTAziuylWuW6Y9JaJfdmNQFimQzwQqLYJcaCXmZNc= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1719597079; h=Content-Type:Cc:Cc:Date:Date:From:From:In-Reply-To:MIME-Version:Message-ID:References:Subject:Subject:To:To:Message-Id:Reply-To; bh=jPdLE6asSeZV8Andp7iUCW22oVHJLki4GYuJ0uKsEDs=; b=KXWI9S7k/2FVCjtnTGvJDNl/p5HFE19//DwNL1yRQdOMIHAto7lBFZSSXcvxiboHPd53fH3/Fau1AlJm99JaAfY4gsgIbUUFi2WKwzQoPSj8Ipqbnycgp1SeP//FgEjm526QEY5QtZkj6G3yPiRJFl8NbcbDJlefyhviDJ2OaqI= ARC-Authentication-Results: i=1; mx.zohomail.com; dkim=pass header.i=salesium.com; spf=pass smtp.mailfrom=rwelty@salesium.com; dmarc=pass header.from= DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; t=1719597079; s=zoho; d=salesium.com; i=rwelty@salesium.com; h=Date:Date:From:From:To:To:Cc:Cc:Message-Id:Message-Id:In-Reply-To:References:Subject:Subject:MIME-Version:Content-Type:Reply-To; bh=jPdLE6asSeZV8Andp7iUCW22oVHJLki4GYuJ0uKsEDs=; b=KLn5fKhHh4g2lJ2XAuu/L1o45lbhaBEzuRrk7wr2jEsvor9sDbwku1/iyN7KRhJw utLDMl48sP8UYb1Z/9N0y3+0KIqZfTujP12nYB8Ni46aKP7uA+gf3cA4fErjDKqF/az aiX2g4dnLIYq4zFg/eH3P0BDhqBQXE/yj8yjNR7A= Received: from mail.zoho.com by mx.zohomail.com with SMTP id 1719597078074187.6994296367178; Fri, 28 Jun 2024 10:51:18 -0700 (PDT) Date: Fri, 28 Jun 2024 13:51:17 -0400 From: Richard Welty To: "agharta82" Cc: "David Rowley" , "PostgreSQL General" Message-Id: <1905ff915c3.f065a5491661249.6426145716909430027@salesium.com> In-Reply-To: <84196842-d6bc-4813-8740-48acc227a1dc@gmail.com> References: <451083be-83e8-413d-bc3a-ed7f3a6d99a9@gmail.com> <84196842-d6bc-4813-8740-48acc227a1dc@gmail.com> Subject: Re: A way to optimize sql about the last temporary-related row MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5462865_2038468308.1719597077955" Importance: Medium User-Agent: Zoho Mail X-Mailer: Zoho Mail List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_5462865_2038468308.1719597077955 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable not really in direct response to this conversation, but is there any reason on the face of the planet why read receipts need to be sent to every single recipient of the mailing list? just saying, =C2=A0 richard ---- On Fri, 28 Jun 2024 03:20:26 -0400 wrote --- HOO-HA! This is HUGE! Only 2.2 seconds on my data!!!! Amazing! distinct on (field) followed by "*" is a hidden gem! Thank you so much and thanks to everyone who helped me!=C2=A0 Thank you very much!! Cheers, Agharta =C2=A0 Il 27/06/24 6:16 PM, David Rowley ha scritto: On Fri, 28 Jun 2024, 3:20 am mailto:agharta82@gmail.com, wrote: =C2=A0 Now the query: explain (verbose, buffers, analyze) with last_table_ids as materialized( =C2=A0=C2=A0 select xx from ( =C2=A0=C2=A0 select LAST_VALUE(pk_id) over (partition by integer_field_2 order by=20 datetime_field_1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED=20 FOLLOWING) xx =C2=A0=C2=A0 from test_table =C2=A0=C2=A0 where integer_field_1 =3D 1 =C2=A0=C2=A0 and datetime_field_1 <=3D CURRENT_TIMESTAMP =C2=A0=C2=A0 ) ww group by ww.xx =20 ), last_row_per_ids as ( =C2=A0=C2=A0 select tt.* from last_table_ids lt =C2=A0=C2=A0 inner join test_table tt on (tt.pk_id =3D lt.xx) =20 ) =20 select * /* or count(*) */ from last_row_per_ids; =20 =20 This query, on my PC, takes 46 seconds!!! (Away from laptop and using my phone) Something like: select distinct on (integer_field_2) * from test_table where integer_field_1 =3D 1 and datetime_field_1 <=3D CURRENT_TIMESTAMP order by integer_field_2,datetime_field_1 desc; Might run a bit faster.=C2=A0 However if it's slow due to I/O then maybe not much faster.=C2=A0 Your version took about 5 seconds on my phone and my version ran in 1.5 seconds. It's difficult for me to check the results match with each query from my phone. A quick scan of the first 10 or so records looked good. If the updated query is still too slow on cold cache then faster disks might be needed. David ------=_Part_5462865_2038468308.1719597077955 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =
not really in direct response to this conversation= , but is there any reason
on the face of the planet why read = receipts need to be sent to every single
recipient of the mai= ling list?

just saying,
  r= ichard



---- On= Fri, 28 Jun 2024 03:20:26 -0400 <agharta82@gmail.com> wrote = ---

HOO-HA! This is HUGE!

Only 2.2 seconds on my = data!!!! Amazing!

distinct on (field) followed by "*" is a= hidden gem!

Thank you so much and thanks to everyone who helped = me!  Thank you very much!!

Cheers,

Agharta

 <= br>


Il 27/06/24 6:= 16 PM, David Rowley ha scritto:



On Fri, 28 Jun 2024, 3:20 am agharta82@gmail.com, <agharta82@gmail.com> wrote:
 
Now the query:
explain (verbose, buffers, analyz= e)
with last_table_ids as materialized(
   select xx from= (
   select LAST_VALUE(pk_id) over (partition by integer_field_2 order by
datetime_field_1 RANGE BETWEEN = UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING) xx
   from test_table=
   where integer_field_1 =3D 1
   and datetime= _field_1 <=3D CURRENT_TIMESTAMP
   ) ww group by ww.xx
=
),
last_row_per_ids as (
   select tt.* from last_t= able_ids lt
   inner join test_table tt on (tt.pk_id =3D lt.x= x)

)

select * /* or count(*) */ from last_row_per_ids;

This query, on my PC, takes 46 seconds!!!

(Away from laptop and = using my phone)

Some= thing like:

select d= istinct on (integer_field_2) * from test_table where integer_field_1 =3D 1 and datetime_field_1 <=3D CURRENT_TIMESTAMP order by integer_field_2,datetime_field_1 desc;

Might run a bit faster.  However if it's = slow due to I/O then maybe not much faster.  Your version took about 5 seconds on my phone and my version ran in 1.5 seconds.

It's difficult for me = to check the results match with each query from my phone. A quick scan of the first 10 or so records looked good.

If the updated query is still too slow on cold cache then faster disks might be needed.

David



------=_Part_5462865_2038468308.1719597077955--