From: | Ron Johnson <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: CLUSTER vs. VACUUM FULL |
Date: | 2024-04-22 02:50:46 |
Message-ID: | CANzqJaDRsY7POw=-90bGzkTjJRa6N-_wQRgEPrNM34phiUjVLQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Apr 21, 2024 at 9:35 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> On Mon, 22 Apr 2024 at 12:16, Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:
> >
> > On Sun, Apr 21, 2024 at 6:45 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>
> >> Ron Johnson <ronljohnsonjr(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Abhishek Chanda | 2024-04-22 03:18:53 | Missing PG_MODULE_MAGIC error |
Previous Message | David Rowley | 2024-04-22 01:34:58 | Re: CLUSTER vs. VACUUM FULL |