Content-Type: multipart/alternative;
 boundary="------------O8cVNOXxNnH1DZL4zHaN45l1"
Message-ID: <84196842-d6bc-4813-8740-48acc227a1dc@gmail.com>
Date: Fri, 28 Jun 2024 09:20:26 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: A way to optimize sql about the last temporary-related row
To: David Rowley <dgrowleyml@gmail.com>
Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
References: <451083be-83e8-413d-bc3a-ed7f3a6d99a9@gmail.com>
 <CAApHDvpvnw2FKbPxrk58F6vo2-S9d_ooTkwgsRoWTVjmjkc2dg@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: <CAApHDvpvnw2FKbPxrk58F6vo2-S9d_ooTkwgsRoWTVjmjkc2dg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/84196842-d6bc-4813-8740-48acc227a1dc%40gmail.com>
Precedence: bulk

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

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


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, 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
>
--------------O8cVNOXxNnH1DZL4zHaN45l1
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>HOO-HA! This is HUGE!</p>
    <p>Only 2.2 seconds on my data!!!! Amazing!</p>
    <p>distinct on (field) <b>followed by "*" </b>is a hidden gem!<br>
    </p>
    <p>Thank you so much and thanks to everyone who helped me!  Thank
      you very much!!</p>
    <p>Cheers,</p>
    <p>Agharta<br>
    </p>
    <p> <br>
    </p>
    <p><br>
    </p>
    <div class="moz-cite-prefix">Il 27/06/24 6:16 PM, David Rowley ha
      scritto:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAApHDvpvnw2FKbPxrk58F6vo2-S9d_ooTkwgsRoWTVjmjkc2dg@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="auto">
        <div><br>
          <br>
          <div class="gmail_quote">
            <div dir="ltr" class="gmail_attr">On Fri, 28 Jun 2024, 3:20
              am <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:</div>
          </div>
        </div>
        <div dir="auto">
          <div class="gmail_quote">
            <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 <br>
              datetime_field_1 RANGE BETWEEN UNBOUNDED PRECEDING AND
              UNBOUNDED <br>
              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>
              This query, on my PC, takes 46 seconds!!!<br>
            </blockquote>
          </div>
        </div>
        <div dir="auto"><br>
        </div>
        <div dir="auto">(Away from laptop and using my phone)</div>
        <div dir="auto"><br>
        </div>
        <div dir="auto">Something like:</div>
        <div dir="auto"><br>
        </div>
        <div dir="auto">select distinct on (integer_field_2) * from
          test_table where integer_field_1 = 1 and datetime_field_1
          &lt;= CURRENT_TIMESTAMP order by
          integer_field_2,datetime_field_1 desc;<br>
        </div>
        <div dir="auto"><br>
        </div>
        <div dir="auto">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.</div>
        <div dir="auto"><br>
        </div>
        <div dir="auto">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.</div>
        <div dir="auto"><br>
        </div>
        <div dir="auto">If the updated query is still too slow on cold
          cache then faster disks might be needed.</div>
        <div dir="auto"><br>
        </div>
        <div dir="auto">David</div>
        <div dir="auto">
          <div class="gmail_quote">
            <blockquote class="gmail_quote"
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
            </blockquote>
          </div>
        </div>
      </div>
    </blockquote>
  </body>
</html>

--------------O8cVNOXxNnH1DZL4zHaN45l1--