Re: Cluster using tablespaces?

Rainer Bauer wrote:
> Tom Lane wrote:
>
> >Rainer Bauer <usenet(at)munnin(dot)com> writes:
> >
> >> "During the cluster operation, a temporary copy of the table is created that
> >> contains the table data in the index order. Temporary copies of each index on
> >> the table are created as well."
> >
> >That's probably a bit misleading. There is no "temporary" copy of the
> >table, just the new permanent copy. The document is trying to point out
> >to you that the transient disk space requirement will be 2X the table
> >size, but maybe we could phrase it better.
>
> 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.

> 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.

> >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.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"We are who we choose to be", sang the goldfinch
when the sun is high (Sandman)