From: Tom Lane <tgl@sss.pgh.pa.us>
To: ken shaw <kshaw987@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to determine whether to VACUUM or CLUSTER
Date: Thu, 16 Jun 2005 23:47:33 -0400
Message-ID: <15401.1118980053@sss.pgh.pa.us> (raw)
In-Reply-To: <20050616180442.8983.qmail@web33703.mail.mud.yahoo.com>
References: <20050616180442.8983.qmail@web33703.mail.mud.yahoo.com>
ken shaw <kshaw987@yahoo.com> writes:
> It looks to me as if the only way to determine whether to issue a
> VACUUM (on a non-clustered table) or a CLUSTER (on a clustered table)
> is to query the table "pg_index", much like view "pg_indexes" does,
> for the column "indisclustered". Is this right?
indisclustered is certainly the ground truth here, and [ ... digs around
in the source code ... ] it doesn't look like there are any views that
present the information in a different fashion. So yup, that's what
you gotta do.
> Also, how expensive is CLUSTER compared to VACUUM?
Well, it's definitely expensive compared to plain VACUUM, but compared
to VACUUM FULL the case is not clear-cut. I would say that if you had
a seriously bloated table (where VACUUM FULL would have to move all or
most of the live tuples in order to compact the table completely) then
CLUSTER will be faster --- not to mention any possible future benefits
from having the table more or less in order with respect to the index.
As near as I can tell, VACUUM FULL was designed to work nicely when you
had maybe 10%-25% free space in the table and you want it all compacted
out. In a scenario where it has to move all the tuples it is certainly
not faster than CLUSTER; plus the end result is much worse as far as the
state of the indexes goes, because VACUUM FULL does *nothing* for
compacting indexes.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, kshaw987@yahoo.com
Subject: Re: How to determine whether to VACUUM or CLUSTER
In-Reply-To: <15401.1118980053@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox