Re: When does CLUSTER occur?

On Thu, Nov 29, 2012 at 04:11:44PM +0000, Schnabel, Robert D. wrote:
> Hi,
>
> I'm looking for some general info on the behavior of CLUSTER. I add large amounts of data to an existing table something like this:
>
> BEGIN;
> DROP INDEX IF EXISTS xgen1011_si_sn;
> COMMIT;
>
> BEGIN;
> INSERT INTO gen1011
> [snip]
> COMMIT;
>
> BEGIN;
> CREATE INDEX xgen1011_si_sn
> ON gen1011
> USING btree
> (sample_id, snp_number)
> WITH (FILLFACTOR=100)
> TABLESPACE index_tablespace;
> ALTER TABLE gen1011 CLUSTER ON xgen1011_si_sn;
CLUSTER happens right here. Any subsequent modifications to your data
means that the table is no longer "clustered". CLUSTER physically
reorganizes the table then and there, it does not ensure that the table
stays clustered.

> COMMIT;
>
> My question is whether or not the "ALTER TABLE gen1011 CLUSTER ON xgen1011_si_sn" actually clusters the table at that point or if it just tells it to use that index for clustering? If the latter I assume I need to add a "CLUSTER gen1011 ON xgen1011_si_sn" line at the end along with an ANALYSE line?
>
> Thanks,
> Bob

-Ryan Kelly