MIME-Version: 1.0
References: <451083be-83e8-413d-bc3a-ed7f3a6d99a9@gmail.com>
In-Reply-To: <451083be-83e8-413d-bc3a-ed7f3a6d99a9@gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 27 Jun 2024 11:27:40 -0400
Message-ID: <CANzqJaA1omaViNqAdEQzfKng8D4X6=Fku_spJhJNEvDt9Nt99Q@mail.gmail.com>
Subject: Re: A way to optimize sql about the last temporary-related row
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002c27fb061be0c6ae"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaA1omaViNqAdEQzfKng8D4X6%3DFku_spJhJNEvDt9Nt99Q%40mail.gmail.com>
Precedence: bulk

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

On Thu, Jun 27, 2024 at 11:20=E2=80=AFAM agharta82@gmail.com <agharta82@gma=
il.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 =3D 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 =3D floor(random() * (6-1+1) + 1)::int,
> integer_field_2 =3D 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.

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Jun 27, 2024 at 11:20=E2=80=AFAM =
<a href=3D"mailto:agharta82@gmail.com">agharta82@gmail.com</a> &lt;<a href=
=3D"mailto:agharta82@gmail.com">agharta82@gmail.com</a>&gt; wrote:<br></div=
><div class=3D"gmail_quote"><div>[snip]=C2=A0</div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,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 <b=
r>
my production table)<br>
update test_table set<br>
datetime_field_1 =3D timestamp &#39;2000-01-01 00:00:00&#39; + random() * <=
br>
(timestamp &#39;2024-05-31 23:59:59&#39; - timestamp &#39;2000-01-01 00:00:=
00&#39;),<br>
integer_field_1 =3D floor(random() * (6-1+1) + 1)::int,<br>
integer_field_2 =3D 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 <b=
r>
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 vacu=
uming before creating indices.</div><div>=C2=A0<br></div></div></div>

--0000000000002c27fb061be0c6ae--