Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 5:56 PM Adrian Klaver wrote: > On 4/22/24 14:35, Ron Johnson wrote: > > > > > > > On Stack Exchange, I've got a question on how to determine when > > to run > > > CLUSTER. It ties in strongly with this thread.. > > > > > > > And the link is? >

Re: CLUSTER vs. VACUUM FULL

On 4/22/24 14:35, Ron Johnson wrote: > > On Stack Exchange, I've got a question on how to determine when to run > CLUSTER.  It ties in strongly with this thread.. > And the link is? It should have been the initial question of this thread and it explains what you

Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 5:03 PM Adrian Klaver wrote: > On 4/22/24 13:59, Ron Johnson wrote: > > On Mon, Apr 22, 2024 at 4:21 PM Adrian Klaver > > wrote: > > [snip] > > > > Which gets us back to your comment upstream: > > > > "What the VACUUM docs

Re: CLUSTER vs. VACUUM FULL

On 4/22/24 13:59, Ron Johnson wrote: On Mon, Apr 22, 2024 at 4:21 PM Adrian Klaver > wrote: [snip] Which gets us back to your comment upstream: "What the VACUUM docs "should" do, it seems, is suggest CLUSTER on the PK, if the PK is a sequence

Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 4:21 PM Adrian Klaver wrote: [snip] > Which gets us back to your comment upstream: > > "What the VACUUM docs "should" do, it seems, is suggest CLUSTER on the > PK, if the PK is a sequence (whether that be an actual sequence, or a > timestamp or something else that grows

Re: CLUSTER vs. VACUUM FULL

On 4/22/24 12:51, Ron Johnson wrote: On Mon, Apr 22, 2024 at 3:14 PM Adrian Klaver > wrote: 1) If they are already in enough of a PK order that the CLUSTER time vs VACUUM FULL time would not be material as there is not much or any sorting to

Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 3:14 PM Adrian Klaver wrote: > > > On 4/22/24 11:45 AM, Ron Johnson wrote: > > On Mon, Apr 22, 2024 at 12:29 PM David G. Johnston > > mailto:david.g.johns...@gmail.com>> wrote: > > > > > > > > On Mon, Apr 22, 2024, 08:37 Ron Johnson >

Re: CLUSTER vs. VACUUM FULL

On 4/22/24 11:45 AM, Ron Johnson wrote: On Mon, Apr 22, 2024 at 12:29 PM David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Mon, Apr 22, 2024, 08:37 Ron Johnson mailto:ronljohnso...@gmail.com>> wrote: On Mon, Apr 22, 2024 at 10:25 AM Tom Lane

Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 12:29 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > > > On Mon, Apr 22, 2024, 08:37 Ron Johnson wrote: > >> On Mon, Apr 22, 2024 at 10:25 AM Tom Lane wrote: >> >>> Marcos Pegoraro writes: >>> > But wouldn't it be good that VACUUM FULL uses that index

Re: CLUSTER vs. VACUUM FULL

Em seg., 22 de abr. de 2024 às 11:25, Tom Lane escreveu: > No ... what would be the difference then Well, I think if a cluster index was defined sometime in a table, it should be respected for next commands, including VACUUM FULL. If I want to come back to PK or any other index I would use

Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024, 08:37 Ron Johnson wrote: > On Mon, Apr 22, 2024 at 10:25 AM Tom Lane wrote: > >> Marcos Pegoraro writes: >> > But wouldn't it be good that VACUUM FULL uses that index defined by >> > Cluster, if it exists ? >> >> No ... what would be the difference then? >> > > What the

Re: CLUSTER vs. VACUUM FULL

On 4/22/24 08:37, Ron Johnson wrote: On Mon, Apr 22, 2024 at 10:25 AM Tom Lane > wrote: Marcos Pegoraro mailto:mar...@f10.com.br>> writes: > But wouldn't it be good that VACUUM FULL uses that index defined by > Cluster, if it exists ? No ... what

Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 10:25 AM Tom Lane wrote: > Marcos Pegoraro writes: > > But wouldn't it be good that VACUUM FULL uses that index defined by > > Cluster, if it exists ? > > No ... what would be the difference then? > What the VACUUM docs "should" do, it seems, is suggest CLUSTER on the

Re: CLUSTER vs. VACUUM FULL

Marcos Pegoraro writes: > But wouldn't it be good that VACUUM FULL uses that index defined by > Cluster, if it exists ? No ... what would be the difference then? regards, tom lane

Re: CLUSTER vs. VACUUM FULL

Em dom., 21 de abr. de 2024 às 22:35, David Rowley escreveu: > > 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

Re: CLUSTER vs. VACUUM FULL

On Sun, Apr 21, 2024 at 9:35 PM David Rowley wrote: > On Mon, 22 Apr 2024 at 12:16, Ron Johnson wrote: > > > > On Sun, Apr 21, 2024 at 6:45 PM Tom Lane wrote: > >> > >> Ron Johnson writes: > >> > Why is VACUUM FULL recommended for compressing a table, when CLUSTER > does > >> > the same thing

Re: CLUSTER vs. VACUUM FULL

On Mon, 22 Apr 2024 at 12:16, Ron Johnson wrote: > > On Sun, Apr 21, 2024 at 6:45 PM Tom Lane wrote: >> >> Ron Johnson 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 >> >

Re: CLUSTER vs. VACUUM FULL

On Sun, Apr 21, 2024 at 6:45 PM Tom Lane wrote: > Ron Johnson 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

Re: CLUSTER vs. VACUUM FULL

On Sun, Apr 21, 2024 at 6:45 PM Tom Lane wrote: > Ron Johnson 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

Re: CLUSTER vs. VACUUM FULL

Ron Johnson 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

CLUSTER vs. VACUUM FULL

PG 14.11 on RHEL8 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? My tests: Table: CDSLBXW.public.log Time 1 Time 2 Time 3 secssecssecs VACUUM FULL 44.2