2

I am having problems with clustering a table where the key consists of one char(23) field and two TimeStamp fields. The char(23) field contains Alpha-Numeric values. The clustering operation never finishes. I have let it run for 24 hours and it still did not finish.

Has anyone run into this kind of problem before? Is my theory that the reason is the long key field makes sense? We have dealt with much larger tables that do not have long keys and we have always been able to perform DB operations on them without any problem. That makes me think that it might have to do with the size of the key in this case.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
user731288
  • 21
  • 1
  • What exactly is the "clustering operation"? Please show us the (complete) SQL that you are running. –  Apr 29 '11 at 15:47
  • how many rows? char(23) and two timestamps is not really 'long' –  Apr 29 '11 at 16:20
  • The table has about 78.5 million records. It has 25 fields in total including 3 fields involved in the key as described before. It also has a VARCHAR(64000) field. The query i am running is "cluster using " – user731288 Apr 29 '11 at 17:58
  • Try searching in pg_stat_activity if the query really runs... most probably that query hangs on a lock (check the column 'waiting') – Szymon Lipiński Apr 29 '11 at 21:34

1 Answers1

2

Cluster rewrites the table so it must wait on locks. It is possible that it is never getting the lock it needs. Why are you setting varchar(64000)? Why not just unrestricted varchar? And how big is this index?

If size is a problem it has to be based on the index size not the key size. I don't know what the effect of toasted key attributes is on cluster because these are moved into extended storage. TOAST might complicate CLUSTER and I have never heard of anyone clustering on a TOASTed attribute. It wouldn't make much sense to do so. TOASTing is necessary for any attribute more than 4k in size.

A better option is to create an index for the values without the possibly toasted value, and then cluster on that. That should give you something very close to what you'd get otherwise.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182