2
$PostgreSQL: pgsql/doc/src/sgml/ref/cluster.sgml,v 1.35 2005-01-04 00:39:53 tgl Exp $
3
PostgreSQL documentation
6
<refentry id="SQL-CLUSTER">
8
<refentrytitle id="sql-cluster-title">CLUSTER</refentrytitle>
9
<refmiscinfo>SQL - Language Statements</refmiscinfo>
13
<refname>CLUSTER</refname>
14
<refpurpose>cluster a table according to an index</refpurpose>
17
<indexterm zone="sql-cluster">
18
<primary>CLUSTER</primary>
23
CLUSTER <replaceable class="PARAMETER">indexname</replaceable> ON <replaceable class="PARAMETER">tablename</replaceable>
24
CLUSTER <replaceable class="PARAMETER">tablename</replaceable>
30
<title>Description</title>
33
<command>CLUSTER</command> instructs <productname>PostgreSQL</productname>
34
to cluster the table specified
35
by <replaceable class="parameter">tablename</replaceable>
36
based on the index specified by
37
<replaceable class="parameter">indexname</replaceable>. The index must
38
already have been defined on
39
<replaceable class="parameter">tablename</replaceable>.
43
When a table is clustered, it is physically reordered
44
based on the index information. Clustering is a one-time operation:
45
when the table is subsequently updated, the changes are
46
not clustered. That is, no attempt is made to store new or
47
updated rows according to their index order. If one wishes, one can
48
periodically recluster by issuing the command again.
52
When a table is clustered, <productname>PostgreSQL</productname>
53
remembers on which index it was clustered. The form
54
<command>CLUSTER <replaceable class="parameter">tablename</replaceable></command>
55
reclusters the table on the same index that it was clustered before.
59
<command>CLUSTER</command> without any parameter reclusters all the tables
61
current database that the calling user owns, or all tables if called
62
by a superuser. (Never-clustered tables are not included.) This
63
form of <command>CLUSTER</command> cannot be called from inside a
64
transaction or function.
68
When a table is being clustered, an <literal>ACCESS
69
EXCLUSIVE</literal> lock is acquired on it. This prevents any other
70
database operations (both reads and writes) from operating on the
71
table until the <command>CLUSTER</command> is finished.
76
<title>Parameters</title>
80
<term><replaceable class="PARAMETER">indexname</replaceable></term>
89
<term><replaceable class="PARAMETER">tablename</replaceable></term>
92
The name (possibly schema-qualified) of a table.
103
In cases where you are accessing single rows randomly
104
within a table, the actual order of the data in the
105
table is unimportant. However, if you tend to access some
106
data more than others, and there is an index that groups
107
them together, you will benefit from using <command>CLUSTER</command>.
108
If you are requesting a range of indexed values from a table, or a
109
single indexed value that has multiple rows that match,
110
<command>CLUSTER</command> will help because once the index identifies the
111
heap page for the first row that matches, all other rows
112
that match are probably already on the same heap page,
113
and so you save disk accesses and speed up the query.
117
During the cluster operation, a temporary copy of the table is created
118
that contains the table data in the index order. Temporary copies of
119
each index on the table are created as well. Therefore, you need free
120
space on disk at least equal to the sum of the table size and the index
125
Because <command>CLUSTER</command> remembers the clustering information,
126
one can cluster the tables one wants clustered manually the first time, and
127
setup a timed event similar to <command>VACUUM</command> so that the tables
128
are periodically reclustered.
132
Because the planner records statistics about the ordering of
133
tables, it is advisable to run <xref linkend="sql-analyze"
134
endterm="sql-analyze-title"> on the newly clustered table.
135
Otherwise, the planner may make poor choices of query plans.
139
There is another way to cluster data. The
140
<command>CLUSTER</command> command reorders the original table using
141
the ordering of the index you specify. This can be slow
142
on large tables because the rows are fetched from the heap
143
in index order, and if the heap table is unordered, the
144
entries are on random pages, so there is one disk page
145
retrieved for every row moved. (<productname>PostgreSQL</productname> has a cache,
146
but the majority of a big table will not fit in the cache.)
147
The other way to cluster a table is to use
150
CREATE TABLE <replaceable class="parameter">newtable</replaceable> AS
151
SELECT <replaceable class="parameter">columnlist</replaceable> FROM <replaceable class="parameter">table</replaceable> ORDER BY <replaceable class="parameter">columnlist</replaceable>;
154
which uses the <productname>PostgreSQL</productname> sorting code in
155
the <literal>ORDER BY</literal> clause to create the desired order; this is usually much
156
faster than an index scan for
157
unordered data. You then drop the old table, use
158
<command>ALTER TABLE ... RENAME</command>
159
to rename <replaceable class="parameter">newtable</replaceable> to the old name, and
160
recreate the table's indexes. However, this approach does not preserve
161
OIDs, constraints, foreign key relationships, granted privileges, and
162
other ancillary properties of the table — all such items must be
168
<title>Examples</title>
171
Cluster the table <literal>employees</literal> on the basis of
172
its index <literal>emp_ind</literal>:
174
CLUSTER emp_ind ON emp;
179
Cluster the <literal>employees</literal> table using the same
180
index that was used before:
187
Cluster all tables in the database that have previously been clustered:
195
<title>Compatibility</title>
198
There is no <command>CLUSTER</command> statement in the SQL standard.
203
<title>See Also</title>
205
<simplelist type="inline">
206
<member><xref linkend="app-clusterdb" endterm="app-clusterdb-title"></member>
211
<!-- Keep this comment at the end of the file
216
sgml-minimize-attributes:nil
217
sgml-always-quote-attributes:t
220
sgml-parent-document:nil
221
sgml-default-dtd-file:"../reference.ced"
222
sgml-exposed-tags:nil
223
sgml-local-catalogs:"/usr/lib/sgml/catalog"
224
sgml-local-ecat-files:nil