</para>

<para>

During the cluster operation, a temporary copy ofthe tableis created

that containsthetable data inthe indexorder. Temporary copies of

space on disk at least equal to the sum of the table sizeandthe index

sizes.

<command>CLUSTER</> can re-sortthe tableusing either an indexscan

onthespecified index, or (ifthe indexis a b-tree) a sequential

will be faster, based on planner cost parametersandavailable statistical

information.

</para>

<para>

Because <command>CLUSTER</command> remembers the clustering information,

one can cluster the tables one wants clustered manually the first time, and

setup a timed event similar to <command>VACUUM</command> so that the tables

are periodically reclustered.

When an indexscan is used, 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. Therefore, you need free space on

disk at least equal to the sum of the table size and the index sizes.

</para>

<para>

When a sequential scan and sort is used, a temporary sort file is

also created, so that the peak temporary space requirement is as much

as double the table size, plus the index sizes. This method is often

faster than the indexscan method, but if the disk space requirement is

intolerable, you can disable this choice by temporarily setting <xref

linkend="guc-enable-sort"> to <literal>off</>.

</para>

<para>

It is advisable to set <xref linkend="guc-maintenance-work-mem"> to

a reasonably large value (but not more than the amount of RAM you can

dedicate to the <command>CLUSTER</> operation) before clustering.

</para>

<para>

</para>

<para>

There is another way to cluster data. The

<command>CLUSTER</command> command reorders the original table by

scanning it using the index you specify. This can be slow

on large tables because the rows are fetched from the table

in index order, and if the table is disordered, the

entries are on random pages, so there is one disk page

retrieved for every row moved. (<productname>PostgreSQL</productname> has

a cache, but the majority of a big table will not fit in the cache.)

The other way to cluster a table is to use:

<programlisting>

CREATE TABLE <replaceable class="parameter">newtable</replaceable> AS

SELECT * FROM <replaceable class="parameter">table</replaceable> ORDER BY <replaceable class="parameter">columnlist</replaceable>;

</programlisting>

which uses the <productname>PostgreSQL</productname> sorting code

to produce the desired order;

this is usually much faster than an index scan for disordered data.

Then you drop the old table, use

<command>ALTER TABLE ... RENAME</command>

to rename <replaceable class="parameter">newtable</replaceable> to the

old name, and recreate the table's indexes.

The big disadvantage of this approach is that it does not preserve

OIDs, constraints, foreign key relationships, granted privileges, and

other ancillary properties of the table &mdash; all such items must be

manually recreated. Another disadvantage is that this way requires a sort

temporary file about the same size as the table itself, so peak disk usage

is about three times the table size instead of twice the table size.

Because <command>CLUSTER</command> remembers which indexes are clustered,

one can cluster the tables one wants clustered manually the first time,

then set up a periodic maintenance script that executes

<command>CLUSTER</> without any parameters, so that the desired tables

are periodically reclustered.

</para>

</refsect1>

<refsect1>

intfreeze_min_age,intfreeze_table_age,

bool*pSwapToastByContent,TransactionId*pFreezeXid);

staticList*get_tables_to_cluster(MemoryContextcluster_context);

staticvoidreform_and_rewrite_tuple(HeapTupletuple,

TupleDescoldTupDesc,TupleDescnewTupDesc,

Datum*values,bool*isnull,

boolnewRelHasOids,RewriteStaterwstate);

TransactionIdOldestXmin;

TransactionIdFreezeXid;

RewriteStaterwstate;

booluse_sort;

Tuplesortstate*tuplesort;

rwstate=begin_heap_rewrite(NewHeap,OldestXmin,FreezeXid,use_wal);

if (OldIndex!=NULL&&OldIndex->rd_rel->relam==BTREE_AM_OID)

use_sort=plan_cluster_use_sort(OIDOldHeap,OIDOldIndex);

use_sort= false;

if (use_sort)

tuplesort=tuplesort_begin_cluster(oldTupDesc,OldIndex,

maintenance_work_mem, false);

tuplesort=NULL;

if (OldIndex!=NULL)

if (OldIndex!=NULL&& !use_sort)

{

heapScan=NULL;

indexScan=index_beginscan(OldHeap,OldIndex,

indexScan=NULL;

}

for (;;)

{

HeapTupletuple;

HeapTuplecopiedTuple;

Bufferbuf;

boolisdead;

inti;

CHECK_FOR_INTERRUPTS();

if (OldIndex!=NULL)

if (indexScan!=NULL)

{

tuple=index_getnext(indexScan,ForwardScanDirection);

if (tuple==NULL)

continue;

}

heap_deform_tuple(tuple,oldTupDesc,values,isnull);

if (tuplesort!=NULL)

tuplesort_putheaptuple(tuplesort,tuple);

reform_and_rewrite_tuple(tuple,

oldTupDesc,newTupDesc,

values,isnull,

NewHeap->rd_rel->relhasoids,rwstate);

}

for (i=0;i<natts;i++)

if (indexScan!=NULL)

index_endscan(indexScan);

if (heapScan!=NULL)

heap_endscan(heapScan);

if (tuplesort!=NULL)

{

tuplesort_performsort(tuplesort);

for (;;)

{

if (newTupDesc->attrs[i]->attisdropped)

isnull[i]= true;

}

HeapTupletuple;

boolshouldfree;

copiedTuple=heap_form_tuple(newTupDesc,values,isnull);

CHECK_FOR_INTERRUPTS();

if (NewHeap->rd_rel->relhasoids)

HeapTupleSetOid(copiedTuple,HeapTupleGetOid(tuple));

if (tuple==NULL)

break;

rewrite_heap_tuple(rwstate,tuple,copiedTuple);

reform_and_rewrite_tuple(tuple,

oldTupDesc,newTupDesc,

values,isnull,

NewHeap->rd_rel->relhasoids,rwstate);

heap_freetuple(copiedTuple);

}

if (shouldfree)

heap_freetuple(tuple);

}

if (OldIndex!=NULL)

index_endscan(indexScan);

heap_endscan(heapScan);

tuplesort_end(tuplesort);

}

end_heap_rewrite(rwstate);

returnrvs;

}

reform_and_rewrite_tuple(HeapTupletuple,

TupleDescoldTupDesc,TupleDescnewTupDesc,

Datum*values,bool*isnull,

boolnewRelHasOids,RewriteStaterwstate)

{

HeapTuplecopiedTuple;

inti;

heap_deform_tuple(tuple,oldTupDesc,values,isnull);

for (i=0;i<newTupDesc->natts;i++)

{

if (newTupDesc->attrs[i]->attisdropped)

isnull[i]= true;

}

copiedTuple=heap_form_tuple(newTupDesc,values,isnull);

if (newRelHasOids)

HeapTupleSetOid(copiedTuple,HeapTupleGetOid(tuple));

rewrite_heap_tuple(rwstate,tuple,copiedTuple);

heap_freetuple(copiedTuple);

}