Content-Type: multipart/alternative;
 boundary="------------2rAnYRD29yT8hvgP830F9dHC"
Message-ID: <e464d721-5e34-4d6b-b396-43ddc1e0bf2b@gmail.com>
Date: Thu, 27 Jun 2024 17:51:42 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: A way to optimize sql about the last temporary-related row
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <451083be-83e8-413d-bc3a-ed7f3a6d99a9@gmail.com>
 <CAKFQuwb=EDKdxozCvkb1HqEcnBAzcCyNnOp=2Ywxyf2HdXV8ZA@mail.gmail.com>
Content-Language: it
From: "agharta82@gmail.com" <agharta82@gmail.com>
Disposition-Notification-To: "agharta82@gmail.com" <agharta82@gmail.com>
In-Reply-To: <CAKFQuwb=EDKdxozCvkb1HqEcnBAzcCyNnOp=2Ywxyf2HdXV8ZA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/e464d721-5e34-4d6b-b396-43ddc1e0bf2b%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------2rAnYRD29yT8hvgP830F9dHC
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hi,

Thanks for you reply.

About syntax you're right, but I couldn't think of anything better :(((  
I'm here for that too, to solve the problem in a fancy way, with your 
great support.

In practice, I need to get back a dataset with the last association (the 
most datatime recent record) for all the distinct entries of 
integer_field_2 based on filter:  integer_field_1 = 1

As said in another reply, the query needs to be performant even if data 
is not in cache (systemctl stop postgresql-16 && sync && echo 3 > 
/proc/sys/vm/drop_caches  && systemctl start postgresql-16).

Many thanks for your support.

Agharta


Il 27/06/24 5:33 PM, David G. Johnston ha scritto:
> 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 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;
>
>
>     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’m going by the general query form and the “last row” aspect of the 
> question.  I haven’t gone and confirmed your specific query can 
> benefit from this approach. The window expression does give me pause.
>
> David J.
>
--------------2rAnYRD29yT8hvgP830F9dHC
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi,</p>
    <p>Thanks for you reply.</p>
    <p>About syntax you're right, but I couldn't think of anything
      better :(((  I'm here for that too, to solve the problem in a
      fancy way, with your great support.<br>
    </p>
    <p>In practice, I need to get back a dataset with the last
      association (the most datatime recent record) for all the distinct
      entries of integer_field_2 based on filter:  integer_field_1 = 1<br>
    </p>
    <p>As said in another reply, the query needs to be performant even
      if data is not in cache (systemctl stop postgresql-16 &amp;&amp;
      sync &amp;&amp; echo 3 &gt; /proc/sys/vm/drop_caches  &amp;&amp; 
      systemctl start postgresql-16).</p>
    <p>Many thanks for your support.</p>
    <p>Agharta<br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
    <div class="moz-cite-prefix">Il 27/06/24 5:33 PM, David G. Johnston
      ha scritto:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAKFQuwb=EDKdxozCvkb1HqEcnBAzcCyNnOp=2Ywxyf2HdXV8ZA@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      On Thursday, June 27, 2024, <a href="mailto:agharta82@gmail.com"
        moz-do-not-send="true" class="moz-txt-link-freetext">agharta82@gmail.com</a>
      &lt;<a href="mailto:agharta82@gmail.com" moz-do-not-send="true"
        class="moz-txt-link-freetext">agharta82@gmail.com</a>&gt; wrote:<br>
      <blockquote class="gmail_quote"
style="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>
          select xx from (<br>
          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>
          from test_table<br>
          where integer_field_1 = 1<br>
          and datetime_field_1 &lt;= CURRENT_TIMESTAMP<br>
          ) ww group by ww.xx<br>
        <br>
        ),<br>
        last_row_per_ids as (<br>
          select tt.* from last_table_ids lt<br>
          inner join test_table tt on (tt.pk_id = 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’m going by the general query form and the “last row” aspect
        of the question.  I haven’t gone and confirmed your specific
        query can benefit from this approach. The window expression does
        give me pause.</div>
      <div><br>
      </div>
      <div>David J.</div>
      <div><br>
      </div>
    </blockquote>
  </body>
</html>

--------------2rAnYRD29yT8hvgP830F9dHC--