From: Jim C. Nasby <decibel@decibel.org>
To: Ken Shaw <kshaw@belarc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to determine whether to VACUUM or CLUSTER
Date: Mon, 20 Jun 2005 01:53:49 -0500
Message-ID: <20050620065349.GK5113@decibel.org> (raw)
In-Reply-To: <011301c571bf$b83c77f0$800101df@xyz>
References: <011301c571bf$b83c77f0$800101df@xyz>
On Wed, Jun 15, 2005 at 11:34:18AM -0400, Ken Shaw wrote:
> Hi All,
>
> I have an app that updates a PostgreSQL db in a batch fashion. After
> each batch (or several batches), it issues VACUUM and ANALYZE calls on
> the updated tables. Now I want to cluster some tables for better
> performance. I understand that doing a VACUUM and a CLUSTER on a table
> is wasteful as the CLUSTER makes the VACUUM superfluous. The app does
> not have a built-in list of the tables and whether each is clustered or
> not. 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?
I don't think that's what you want. 'indisclustered' only indicates if
the last time the table was clustered was on that index. The best thing
that comes to mind is looking at the correlation of the first field in
the index for the table. You'll find this info in pg_stats.
> Also, how expensive is CLUSTER compared to VACUUM? Does CLUSTER read in
> the whole table, sort it, and write it back out? Or write out a
> completely new file? Is the time for a CLUSTER the same whether one row
> is out of place or the table is completely disordered?
AFAIK, cluster completely re-creates the table from scratch, then
rebuilds all the indexes. It's basically the most expensive operation
you can perform on a table. There probably will be some increased
performance from the sort if the table is already mostly in the right
order though.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
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: decibel@decibel.org, kshaw@belarc.com
Subject: Re: How to determine whether to VACUUM or CLUSTER
In-Reply-To: <20050620065349.GK5113@decibel.org>
* 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