MIME-Version: 1.0
References: <451083be-83e8-413d-bc3a-ed7f3a6d99a9@gmail.com>
In-Reply-To: <451083be-83e8-413d-bc3a-ed7f3a6d99a9@gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Fri, 28 Jun 2024 04:16:24 +1200
Message-ID: <CAApHDvpvnw2FKbPxrk58F6vo2-S9d_ooTkwgsRoWTVjmjkc2dg@mail.gmail.com>
Subject: Re: A way to optimize sql about the last temporary-related row
To: agharta82@gmail.com
Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000875f07061be1747d"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvpvnw2FKbPxrk58F6vo2-S9d_ooTkwgsRoWTVjmjkc2dg%40mail.gmail.com>
Precedence: bulk

--000000000000875f07061be1747d
Content-Type: text/plain; charset="UTF-8"

On Fri, 28 Jun 2024, 3:20 am agharta82@gmail.com, <agharta82@gmail.com>
wrote:

>
> Now the query:
> explain (verbose, buffers, analyze)
> 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 = 1
>    and datetime_field_1 <= CURRENT_TIMESTAMP
>    ) ww group by ww.xx
>
> ),
> last_row_per_ids as (
>    select tt.* from last_table_ids lt
>    inner join test_table tt on (tt.pk_id = lt.xx)
>
> )
>
> select * /* or count(*) */ from last_row_per_ids;
>
>
> 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 = 1 and datetime_field_1 <= 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

>

--000000000000875f07061be1747d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><div><br><br><div class=3D"gmail_quote"><div dir=3D"ltr" =
class=3D"gmail_attr">On Fri, 28 Jun 2024, 3:20 am <a href=3D"mailto:agharta=
82@gmail.com">agharta82@gmail.com</a>, &lt;<a href=3D"mailto:agharta82@gmai=
l.com">agharta82@gmail.com</a>&gt; wrote:</div></div></div><div dir=3D"auto=
"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">=C2=A0<br>
Now the query:<br>
explain (verbose, buffers, analyze)<br>
with last_table_ids as materialized(<br>
=C2=A0=C2=A0 select xx from (<br>
=C2=A0=C2=A0 select LAST_VALUE(pk_id) over (partition by integer_field_2 or=
der by <br>
datetime_field_1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED <br>
FOLLOWING) xx<br>
=C2=A0=C2=A0 from test_table<br>
=C2=A0=C2=A0 where integer_field_1 =3D 1<br>
=C2=A0=C2=A0 and datetime_field_1 &lt;=3D CURRENT_TIMESTAMP<br>
=C2=A0=C2=A0 ) ww group by ww.xx<br>
<br>
),<br>
last_row_per_ids as (<br>
=C2=A0=C2=A0 select tt.* from last_table_ids lt<br>
=C2=A0=C2=A0 inner join test_table tt on (tt.pk_id =3D lt.xx)<br>
<br>
)<br>
<br>
select * /* or count(*) */ from last_row_per_ids;<br>
<br>
<br>
This query, on my PC, takes 46 seconds!!!<br></blockquote></div></div><div =
dir=3D"auto"><br></div><div dir=3D"auto">(Away from laptop and using my pho=
ne)</div><div dir=3D"auto"><br></div><div dir=3D"auto">Something like:</div=
><div dir=3D"auto"><br></div><div dir=3D"auto">select distinct on (integer_=
field_2) * from test_table where integer_field_1 =3D 1 and datetime_field_1=
 &lt;=3D CURRENT_TIMESTAMP order by integer_field_2,datetime_field_1 desc;<=
br></div><div dir=3D"auto"><br></div><div dir=3D"auto">Might run a bit fast=
er.=C2=A0 However if it&#39;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.</div><div dir=3D"auto"><br></div><div dir=3D"auto">It&#39;s di=
fficult 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.</div><div dir=3D"auto=
"><br></div><div dir=3D"auto">If the updated query is still too slow on col=
d cache then faster disks might be needed.</div><div dir=3D"auto"><br></div=
><div dir=3D"auto">David</div><div dir=3D"auto"><div class=3D"gmail_quote">=
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
</blockquote></div></div></div>

--000000000000875f07061be1747d--