1 <REFENTRY ID="SQL-CLUSTER-1">
6 <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
13 Gives storage clustering advice to PostgreSQL
18 <DATE>1998-04-15</DATE>
21 CLUSTER <REPLACEABLE CLASS="PARAMETER">indexname</REPLACEABLE> ON <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
24 <REFSECT2 ID="R2-SQL-CLUSTER-1">
26 <DATE>1998-04-15</DATE>
37 <REPLACEABLE CLASS="PARAMETER">indexname</REPLACEABLE>
49 <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
61 <REFSECT2 ID="R2-SQL-CLUSTER-2">
63 <DATE>1998-04-15</DATE>
79 <ReturnValue>CLUSTER</ReturnValue>
83 The clustering was done successfully.
89 <ReturnValue>ERROR: relation <<REPLACEABLE CLASS="PARAMETER">tablerelation_number</REPLACEABLE>> inherits "invoice"</ReturnValue>
95 This is not documented anywhere. It seems not to be possible to
96 cluster a table that is inherited.
103 <ReturnValue>ERROR: Relation x does not exist!</ReturnValue>
109 The relation complained of was not shown in the error message,
110 which contained a random string instead of the relation name.
123 <REFSECT1 ID="R1-SQL-CLUSTER-1">
125 <DATE>1998-04-15</DATE>
131 This command instructs PostgreSQL to cluster the class specified
132 by <replaceable class="parameter">classname</replaceable> approximately
133 based on the index specified by
134 <replaceable class="parameter">indexname</replaceable>. The index must
135 already have been defined on <replaceable class="parameter">classname</replaceable>.
138 When a class is clustered, it is physically reordered
139 based on the index information. The clustering is static.
140 In other words, as the class is updated, the changes are
141 not clustered. No attempt is made to keep new instances or
142 updated tuples clustered. If he wishes, the user can
143 recluster manually by issuing the command again.
147 The table is actually copied to a temporary table in index
148 order, then renamed back to the original name. For this
149 reason, all grant permissions and other indexes are lost
150 when clustering is performed.
154 In cases where you are accessing single rows randomly
155 within a table, the actual order of the data in the heap
156 table is unimportant. However, if you tend to access some
157 data more than others, and there is an index that groups
158 them together, you will benefit from using the CLUSTER
163 Another place CLUSTER is good is in cases where you use an
164 index to pull out several rows from a table. If you are
165 requesting a range of indexed values from a table, or a
166 single indexed value that has multiple rows that match,
167 CLUSTER will help because once the index identifies the
168 heap page for the first row that matches, all other rows
169 that match are probably already on the same heap page,
170 saving disk accesses and speeding up the query.
174 There are two ways to cluster data. The first is with the
175 CLUSTER command, which reorders the original table with
176 the ordering of the index you specify. This can be slow
177 on large tables because the rows are fetched from the heap
178 in index order, and if the heap table is unordered, the
179 entries are on random pages, so there is one disk page
180 retrieved for every row moved. PostgreSQL has a cache,
181 but the majority of a big table will not fit in the cache.
185 Another way is to use
186 <programlisting>SELECT ... INTO TABLE temp FROM ... ORDER BY ...</programlisting>
187 This uses the PostgreSQL sorting code in
188 ORDER BY to match the index, and is much faster for
189 unordered data. You then drop the old table, use
190 <programlisting>ALTER TABLE RENAME</programlisting>
191 to rename 'temp' to the old name, and
192 recreate the b bindexes. The only problem is that oids
193 will not be preserved. From then on, CLUSTER should be
194 fast because most of the heap data has already been
195 ordered, and the existing index is used.
199 <REFSECT1 ID="R1-SQL-CLUSTER-2">
204 Cluster the employees relation on the basis of its salary attribute
207 CLUSTER emp_ind ON emp
211 <REFSECT1 ID="R1-SQL-CLUSTER-3">
218 <REFSECT2 ID="R2-SQL-CLUSTER-4">
220 <DATE>1998-04-15</DATE>
226 There is no CLUSTER statement in SQL92.
233 <!-- Keep this comment at the end of the file
238 sgml-minimize-attributes:nil
239 sgml-always-quote-attributes:t
242 sgml-parent-document:nil
243 sgml-default-dtd-file:"../reference.ced"
244 sgml-exposed-tags:nil
245 sgml-local-catalogs:"/usr/lib/sgml/catalog"
246 sgml-local-ecat-files:nil