MIME-Version: 1.0
In-Reply-To: <451083be-83e8-413d-bc3a-ed7f3a6d99a9@gmail.com>
References: <451083be-83e8-413d-bc3a-ed7f3a6d99a9@gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Thu, 27 Jun 2024 08:33:18 -0700
Message-ID: <CAKFQuwb=EDKdxozCvkb1HqEcnBAzcCyNnOp=2Ywxyf2HdXV8ZA@mail.gmail.com>
Subject: Re: A way to optimize sql about the last temporary-related row
To: "agharta82@gmail.com" <agharta82@gmail.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a8dfce061be0d9d0"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwb%3DEDKdxozCvkb1HqEcnBAzcCyNnOp%3D2Ywxyf2HdXV8ZA%40mail.gmail.com>
Precedence: bulk

--000000000000a8dfce061be0d9d0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thursday, June 27, 2024, 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 FOLLOWIN=
G)
> 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_table_ids lt
>   inner join test_table tt on (tt.pk_id =3D lt.xx)
>
> )
>
> select * /* or count(*) */ from last_row_per_ids;
>
>
> Do you think there is a way to optimize the query?


Write a lateral subquery to pick the first row of a descending ordered
query? Using group to select ranked rows is both semantically wrong and
potentially optimization blocking.

I=E2=80=99m going by the general query form and the =E2=80=9Clast row=E2=80=
=9D aspect of the
question.  I haven=E2=80=99t gone and confirmed your specific query can ben=
efit
from this approach. The window expression does give me pause.

David J.

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

On Thursday, June 27, 2024, <a href=3D"mailto:agharta82@gmail.com">agharta8=
2@gmail.com</a> &lt;<a href=3D"mailto:agharta82@gmail.com">agharta82@gmail.=
com</a>&gt; wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0 0=
 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br>Now the query:<br>
explain (verbose, buffers, analyze)<br>
with last_table_ids as materialized(<br>
=C2=A0 select xx from (<br>
=C2=A0 select LAST_VALUE(pk_id) over (partition by integer_field_2 order by=
 datetime_field_1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING=
) xx<br>
=C2=A0 from test_table<br>
=C2=A0 where integer_field_1 =3D 1<br>
=C2=A0 and datetime_field_1 &lt;=3D CURRENT_TIMESTAMP<br>
=C2=A0 ) ww group by ww.xx<br>
<br>
),<br>
last_row_per_ids as (<br>
=C2=A0 select tt.* from last_table_ids lt<br>
=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>Do you think there is a way to optimize the query?</blockquote><div><br=
></div><div>Write a lateral subquery to pick the first row of a descending =
ordered query? Using group to select ranked rows is both semantically wrong=
 and potentially optimization blocking.</div><div><br></div><div>I=E2=80=99=
m going by the general query form and the =E2=80=9Clast row=E2=80=9D aspect=
 of the question.=C2=A0 I haven=E2=80=99t gone and confirmed your specific =
query can benefit from this approach. The window expression does give me pa=
use.</div><div><br></div><div>David J.</div><div><br></div>

--000000000000a8dfce061be0d9d0--