1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
16 HREF="sql-commands.html"><LINK
19 HREF="sql-close.html"><LINK
22 HREF="sql-comment.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
73 HREF="sql-comment.html"
81 HREF="sql-comment.html"
102 >CLUSTER -- cluster a table according to an index</DIV
107 CLASS="REFSYNOPSISDIV"
143 to cluster the table specified
148 based on the index specified by
153 already have been defined on
160 > When a table is clustered, it is physically reordered
161 based on the index information. Clustering is a one-time operation:
162 when the table is subsequently updated, the changes are
163 not clustered. That is, no attempt is made to store new or
164 updated rows according to their index order. If one wishes, one can
165 periodically recluster by issuing the command again.
168 > When a table is clustered, <SPAN
172 remembers on which index it was clustered. The form
180 reclusters the table on the same index that it was clustered before.
186 > without any parameter reclusters all the tables
188 current database that the calling user owns, or all tables if called
189 by a superuser. (Never-clustered tables are not included.) This
193 > cannot be called from inside a
194 transaction or function.
197 > When a table is being clustered, an <TT
201 > lock is acquired on it. This prevents any other
202 database operations (both reads and writes) from operating on the
228 > The name of an index.
238 > The name (possibly schema-qualified) of a table.
252 > In cases where you are accessing single rows randomly
253 within a table, the actual order of the data in the
254 table is unimportant. However, if you tend to access some
255 data more than others, and there is an index that groups
256 them together, you will benefit from using <TT
260 If you are requesting a range of indexed values from a table, or a
261 single indexed value that has multiple rows that match,
265 > will help because once the index identifies the
266 heap page for the first row that matches, all other rows
267 that match are probably already on the same heap page,
268 and so you save disk accesses and speed up the query.
271 > During the cluster operation, a temporary copy of the table is created
272 that contains the table data in the index order. Temporary copies of
273 each index on the table are created as well. Therefore, you need free
274 space on disk at least equal to the sum of the table size and the index
281 > remembers the clustering information,
282 one can cluster the tables one wants clustered manually the first time, and
283 setup a timed event similar to <TT
287 are periodically reclustered.
290 > Because the planner records statistics about the ordering of tables, it
291 is advisable to run <TT
294 > on the newly clustered
295 table. Otherwise, the planner may make poor choices of query plans.
298 > There is another way to cluster data. The
302 > command reorders the original table using
303 the ordering of the index you specify. This can be slow
304 on large tables because the rows are fetched from the heap
305 in index order, and if the heap table is unordered, the
306 entries are on random pages, so there is one disk page
307 retrieved for every row moved. (<SPAN
311 but the majority of a big table will not fit in the cache.)
312 The other way to cluster a table is to use
315 CLASS="PROGRAMLISTING"
339 > clause to create the desired order; this is usually much
340 faster than an index scan for
341 unordered data. You then drop the old table, use
344 >ALTER TABLE ... RENAME</TT
349 > to the old name, and
350 recreate the table's indexes. However, this approach does not preserve
351 OIDs, constraints, foreign key relationships, granted privileges, and
352 other ancillary properties of the table --- all such items must be
364 > Cluster the table <TT
373 CLASS="PROGRAMLISTING"
374 >CLUSTER emp_ind ON emp;</PRE
381 > relation using the same
382 index that was used before:
384 CLASS="PROGRAMLISTING"
389 > Cluster all the tables on the database that have previously been clustered:
391 CLASS="PROGRAMLISTING"
407 > statement in the SQL standard.
418 HREF="app-clusterdb.html"
430 SUMMARY="Footer navigation table"
441 HREF="sql-close.html"
459 HREF="sql-comment.html"
475 HREF="sql-commands.html"