cluster man page on BSDi

Man page or keyword search:  
man Server   6284 pages
apropos Keyword Search (all sections)
Output format
BSDi logo
[printable version]

CLUSTER()			 SQL Commands			     CLUSTER()

NAME
       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-cluster 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 permis‐
       sions 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 com‐
       mand, 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	 29 March 2001			     CLUSTER()
[top]

List of man pages available for BSDi

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net