Thread: CLUSTER trashing indexes

Hi all

I have a rather large table, around 7 million rows. I created 3 indexes on
this table, which resulted in rather large indexes. As there will be no
more rows added to this table, I thought optimizing the indexes with the
CLUSTER command might be a good idea to speed up lookups. After the first
index had finished being processed by the cluster command, my remaining
indexes diappeared. The actual index files in the ~/data/base/dbname
directory had completely disappeared. Therefore I have to go and re-create
the indexes. Have I missed something in the release notes or known bugs
list, or is there a size limitation or problem when dealing with indexes
and/or clustering them? The average physical index file size being around
200MB each.

My version btw is 7.0.2 running on FreeBSD 3.4.

cheers,

Jason Davis
DB Admin/Programmer
Tas Access
www.tassie.net.au

Jason Davis <jdavis@tassie.net.au> writes:
> Have I missed something in the release notes or known bugs list,

From the CLUSTER manual page:

: The table is actually copied to a temporary table in index order, then
: renamed back to the original name. For this reason, all grant
: permissions and other indexes are lost when clustering is performed.

The man page fails to point out that you also lose constraints, etc.
What's left after cluster is not much except the data and the one index.
You can rebuild all that stuff, but it's a pain to have to do so.

This is a really sucky implementation, and fixing it has been on the
TODO list for a long time.  We need a way of doing filename versioning
first...

            regards, tom lane