CLUSTER - Gives storage clustering advice to the server


SYNOPSIS

       CLUSTER indexname ON tablename

   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 table specified
       by table approximately based on  the  index  specified  by
       indexname.  The  index  must  already have been defined on
       tablename.

       When a table is  clustered,  it  is  physically  reordered
       based  on the index information. The clustering is static.
       In other words, as the table is updated, the  changes  are
       not clustered. No attempt is made to keep new instances or
       updated tuples clustered. If one wishes, one can  re-clus­
       ter 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.

       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.


Man(1) output converted with man2html