Content-Type: multipart/alternative;
 boundary="------------hKjdAcm0W03a3ez6wGObpgzi"
Message-ID: <fa4d471f-a13a-4153-9ed7-52be9c0774b8@gmail.com>
Date: Thu, 27 Jun 2024 17:33:13 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: A way to optimize sql about the last temporary-related row
To: pgsql-general@lists.postgresql.org
References: <451083be-83e8-413d-bc3a-ed7f3a6d99a9@gmail.com>
 <CANzqJaA1omaViNqAdEQzfKng8D4X6=Fku_spJhJNEvDt9Nt99Q@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: <CANzqJaA1omaViNqAdEQzfKng8D4X6=Fku_spJhJNEvDt9Nt99Q@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/fa4d471f-a13a-4153-9ed7-52be9c0774b8%40gmail.com>
Precedence: bulk

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

Hi,

You are right. Too quickly copy-paste on my part :-)

I take this opportunity to add a NOT insignificant detail.

Before executing the select query I clear the cache:

systemctl stop postgresql-16 && sync && echo 3 > 
/proc/sys/vm/drop_caches  &&  systemctl start postgresql-16

I need to get a performance result even if data is not in cache.


My best regards,

Agharta


Il 27/06/24 5:27 PM, Ron Johnson ha scritto:
> On Thu, Jun 27, 2024 at 11:20 AM agharta82@gmail.com 
> <agharta82@gmail.com> wrote:
> [snip]
>
>     -- insert 4M records
>     insert into test_table(pk_id) select generate_series(1,4000000,1);
>
>     -- now set some random data, distribuited between specific ranges
>     (as in
>     my production table)
>     update test_table set
>     datetime_field_1 = timestamp '2000-01-01 00:00:00' + random() *
>     (timestamp '2024-05-31 23:59:59' - timestamp '2000-01-01 00:00:00'),
>     integer_field_1 = floor(random() * (6-1+1) + 1)::int,
>     integer_field_2 = floor(random() * (200000-1+1) + 1)::int;
>
>
>     -- indexes
>     CREATE INDEX idx_test_table_integer_field_1 ON
>     test_table(integer_field_1);
>     CREATE INDEX xtest_table_datetime_field_1 ON
>     test_table(datetime_field_1
>     desc);
>     CREATE INDEX idx_test_table_integer_field_2 ON
>     test_table(integer_field_2);
>
>
> Off-topic: save some resources by vacuuming before creating indices.
>
--------------hKjdAcm0W03a3ez6wGObpgzi
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>You are right. Too quickly copy-paste on my part :-)</p>
    <p>I take this opportunity to add a NOT insignificant detail.</p>
    <p>Before executing the select query I clear the cache:<br>
    </p>
    <p>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>I need to get a performance result even if data is not in cache.<br>
    </p>
    <p><br>
    </p>
    <p>My best regards,</p>
    <p>Agharta<br>
    </p>
    <p><br>
    </p>
    <div class="moz-cite-prefix">Il 27/06/24 5:27 PM, Ron Johnson ha
      scritto:<br>
    </div>
    <blockquote type="cite"
cite="mid:CANzqJaA1omaViNqAdEQzfKng8D4X6=Fku_spJhJNEvDt9Nt99Q@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">On Thu, Jun 27, 2024 at 11: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:<br>
        </div>
        <div class="gmail_quote">
          <div>[snip] </div>
          <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
            -- insert 4M records<br>
            insert into test_table(pk_id) select
            generate_series(1,4000000,1);<br>
            <br>
            -- now set some random data, distribuited between specific
            ranges (as in <br>
            my production table)<br>
            update test_table set<br>
            datetime_field_1 = timestamp '2000-01-01 00:00:00' +
            random() * <br>
            (timestamp '2024-05-31 23:59:59' - timestamp '2000-01-01
            00:00:00'),<br>
            integer_field_1 = floor(random() * (6-1+1) + 1)::int,<br>
            integer_field_2 = floor(random() * (200000-1+1) + 1)::int;<br>
            <br>
            <br>
            -- indexes<br>
            CREATE INDEX idx_test_table_integer_field_1 ON
            test_table(integer_field_1);<br>
            CREATE INDEX xtest_table_datetime_field_1 ON
            test_table(datetime_field_1 <br>
            desc);<br>
            CREATE INDEX idx_test_table_integer_field_2 ON
            test_table(integer_field_2);<br>
            <br>
          </blockquote>
          <div><br>
          </div>
          <div>Off-topic: save some resources by vacuuming before
            creating indices.</div>
          <div> <br>
          </div>
        </div>
      </div>
    </blockquote>
  </body>
</html>

--------------hKjdAcm0W03a3ez6wGObpgzi--