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