CLUSTER(l)                                             CLUSTER(l)

NAME
       CLUSTER - Gives storage clustering advice to the server

SYNOPSIS
       CLUSTER indexname ON table

   INPUTS
       indexname
              The name of an index.

       table  The name of a table.

   OUTPUTS
       CLUSTER
              The clustering was done successfully.

       ERROR: relation <tablerelation_number> inherits "table"
              [Comment:   This  is  not  documented  anywhere. It
              seems not to be possible to cluster a table that is
              inherited.  ]

       ERROR: Relation table does not exist!
              [Comment:   The specified relation was not shown in
              the error message, which contained a random  string
              instead of the relation name.  ]

DESCRIPTION
       CLUSTER  instructs Postgres to cluster the class specified
       by table approximately based on  the  index  specified  by
       indexname.  The  index  must  already have been defined on
       classname.

       When a class is  clustered,  it  is  physically  reordered
       based  on the index information. The clustering is static.
       In other words, as the class is updated, the  changes  are
       not clustered. No attempt is made to keep new instances or
       updated tuples clustered. If one wishes, one can recluster
       manually by issuing the command again.

   NOTES
       The table is actually copied to a temporary table in index
       order, then renamed back to the original  name.  For  this
       reason,  all  grant permissions and other indexes are lost
       when clustering is performed.

       In cases where you  are  accessing  single  rows  randomly
       within  a  table, the actual order of the data in the heap
       table is unimportant. However, if you tend to access  some
       data  more  than others, and there is an index that groups
       them together, you will benefit from using CLUSTER.

       Another place where CLUSTER is helpful is in  cases  where
       you use an index to pull out several rows from a table. If
       you are requesting a range of indexed values from a table,
       or  a  single  indexed  value  that has multiple rows that
       match, CLUSTER will help because once the index identifies
       the  heap  page  for the first row that matches, all other
       rows that match are probably  already  on  the  same  heap
       page, saving disk accesses and speeding up the query.

       There  are two ways to cluster data. The first is with the
       CLUSTER command, which reorders the  original  table  with
       the ordering of the index you specify. This can be slow on
       large tables because the rows are fetched from the heap in
       index  order,  and  if  the  heap  table is unordered, the
       entries are on random pages, so there  is  one  disk  page
       retrieved  for  every row moved. Postgres has a cache, but
       the majority of a big table will not fit in the cache.

       Another way to cluster data is to use

       SELECT columnlist INTO TABLE newtable
            FROM table ORDER BY columnlist

       which uses the Postgres  sorting  code  in  the  ORDER  BY
       clause  to  match  the index, and which is much faster for
       unordered data. You then drop the  old  table,  use  ALTER
       TABLE/RENAME  to rename temp to the old name, and recreate
       any indexes. The only problem is that  OIDs  will  not  be
       preserved.  From  then  on, CLUSTER should be fast because
       most of the heap data has already been  ordered,  and  the
       existing index is used.

USAGE
       Cluster  the employees relation on the basis of its salary
       attribute

       CLUSTER emp_ind ON emp;

COMPATIBILITY
   SQL92
       There is no CLUSTER statement in SQL92.

SQL - Language Statements 15 August 1999                        1