Re: Cluster using tablespaces?

Alvaro Herrera wrote:

>Rainer Bauer wrote:
>> Ok, I expected that. Does this work:
>> ALTER TABLE foo CLUSTER ON index_name SET TABLESPACE new_tablespace
>>
>> I.e. is the table moved to the other tablespace and clustered at the same time
>> or are these independant operations?
>
>No, the ALTER TABLE/CLUSTER ON only defines what index will the table be
>clustered on in the future, but it doesn't cluster it at that time.
>Perhaps it could be improved so that if a table rewrite is going to be
>done anyway for some other reason, then make sure the rewrite uses the
>cluster order. I think it's far from trivial though.

Yeah that is what I was originally looking for.

>> What I am trying to achieve is cutting down the time the cluster command
>> takes. I thought the most promising way would be if the new data is written to
>> different drive.
>
>It has been theorized that cluster would be faster in general if instead
>of doing an indexscan we would instead use a seqscan + sort step. It
>would be good to measure it.

Could a reindex on the clustered index speed up the clustering (when executed
immediatelly before the cluster command)? As I understand it, this index is
used to fetch the table data in the correct order. Or is most of the time
spend fetching the table data?

Also, would it make sense to increase <shared_buffers> for the cluster
operation. This is set to 32MB here on my Windows box as was recommended.

From my questions you can see that I don't know how the clustering is working
internally. I.e. I don't have a concrete idea how to make cluster any faster.

>> >For btree indexes, there is a temporary copy of the index data, which
>> >will go wherever you have arranged for temp files to go. (I think that
>> >easy user control of this may be new for 8.3, though.)
>>
>> Could you give me a hint where that would be on Windows? I guess this might be
>> worth a try since there are a couple of btree indexes in the database.
>
>I think Tom is referring to the new temp_tablespaces config variable.

I moved the pgsql_tmp directory to another disk, but that didn't speed up the
cluster command.

Rainer