MIME-Version: 1.0
References: <CANzqJaD8o0UuK42F==BXZA=L380n=aXt+VJ93PN3waFbNhZHBg@mail.gmail.com>
 <2870091.1713739514@sss.pgh.pa.us> <CANzqJaBvcGKdhqaZz+b0jNc8WLK74UCDW+zk=Ja78duOjWUBRA@mail.gmail.com>
 <CAApHDvraS7EkYZsgZnAWc-L4_jbie5UkPAtedKSqN6s1j+PJiQ@mail.gmail.com>
In-Reply-To: <CAApHDvraS7EkYZsgZnAWc-L4_jbie5UkPAtedKSqN6s1j+PJiQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sun, 21 Apr 2024 22:50:46 -0400
Message-ID: <CANzqJaDRsY7POw=-90bGzkTjJRa6N-_wQRgEPrNM34phiUjVLQ@mail.gmail.com>
Subject: Re: CLUSTER vs. VACUUM FULL
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c16f9f0616a681e3"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDRsY7POw%3D-90bGzkTjJRa6N-_wQRgEPrNM34phiUjVLQ%40mail.gmail.com>
Precedence: bulk

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

On Sun, Apr 21, 2024 at 9:35=E2=80=AFPM David Rowley <dgrowleyml@gmail.com>=
 wrote:

> On Mon, 22 Apr 2024 at 12:16, Ron Johnson <ronljohnsonjr@gmail.com> wrote=
:
> >
> > On Sun, Apr 21, 2024 at 6:45=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wr=
ote:
> >>
> >> Ron Johnson <ronljohnsonjr@gmail.com> writes:
> >> > Why is VACUUM FULL recommended for compressing a table, when CLUSTER
> does
> >> > the same thing (similarly doubling disk space), and apparently runs
> just as
> >> > fast?
> >>
> >> CLUSTER makes the additional effort to sort the data per the ordering
> >> of the specified index.  I'm surprised that's not noticeable in your
> >> test case.
> >
> > Clustering on a completely different index  was also 44 seconds.
>
> Both VACUUM FULL and CLUSTER go through a very similar code path. Both
> use cluster_rel().  VACUUM FULL just won't make use of an existing
> index to provide presorted input or perform a sort, whereas CLUSTER
> will attempt to choose the cheapest out of these two to get sorted
> results.
>
> If the timing for each is similar, it just means that using an index
> scan or sorting isn't very expensive compared to the other work that's
> being done.  Both CLUSTER and VACUUM FULL require reading every heap
> page and writing out new pages into a new heap and maintaining  all
> indexes on the new heap. That's quite an effort.
>

My original CLUSTER command didn't have to change the order of the data
very much, thus, the sort didn't have to do much work.

CLUSTER on a different index was indeed much slower than VACUUM FULL.

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

<div dir=3D"ltr"><div dir=3D"ltr">On Sun, Apr 21, 2024 at 9:35=E2=80=AFPM D=
avid Rowley &lt;<a href=3D"mailto:dgrowleyml@gmail.com">dgrowleyml@gmail.co=
m</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"g=
mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204=
,204,204);padding-left:1ex">On Mon, 22 Apr 2024 at 12:16, Ron Johnson &lt;<=
a href=3D"mailto:ronljohnsonjr@gmail.com" target=3D"_blank">ronljohnsonjr@g=
mail.com</a>&gt; wrote:<br>
&gt;<br>
&gt; On Sun, Apr 21, 2024 at 6:45=E2=80=AFPM Tom Lane &lt;<a href=3D"mailto=
:tgl@sss.pgh.pa.us" target=3D"_blank">tgl@sss.pgh.pa.us</a>&gt; wrote:<br>
&gt;&gt;<br>
&gt;&gt; Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com" target=
=3D"_blank">ronljohnsonjr@gmail.com</a>&gt; writes:<br>
&gt;&gt; &gt; Why is VACUUM FULL recommended for compressing a table, when =
CLUSTER does<br>
&gt;&gt; &gt; the same thing (similarly doubling disk space), and apparentl=
y runs just as<br>
&gt;&gt; &gt; fast?<br>
&gt;&gt;<br>
&gt;&gt; CLUSTER makes the additional effort to sort the data per the order=
ing<br>
&gt;&gt; of the specified index.=C2=A0 I&#39;m surprised that&#39;s not not=
iceable in your<br>
&gt;&gt; test case.<br>
&gt;<br>
&gt; Clustering on a completely different index=C2=A0 was also 44 seconds.<=
br>
<br>
Both VACUUM FULL and CLUSTER go through a very similar code path. Both<br>
use cluster_rel().=C2=A0 VACUUM FULL just won&#39;t make use of an existing=
<br>
index to provide presorted input or perform a sort, whereas CLUSTER<br>
will attempt to choose the cheapest out of these two to get sorted<br>
results.<br>
<br>
If the timing for each is similar, it just means that using an index<br>
scan or sorting isn&#39;t very expensive compared to the other work that=
9;s<br>
being done.=C2=A0 Both CLUSTER and VACUUM FULL require reading every heap<b=
r>
page and writing out new pages into a new heap and maintaining=C2=A0 all<br=
>
indexes on the new heap. That&#39;s quite an effort.<br></blockquote><div><=
br></div><div>My original CLUSTER command didn&#39;t have to change the ord=
er of the data very=C2=A0much, thus, the=C2=A0sort didn&#39;t have to do mu=
ch work.</div><div><br></div><div>CLUSTER on a different index was indeed m=
uch slower than VACUUM FULL.</div><div><br></div></div></div>

--000000000000c16f9f0616a681e3--