cluster man page

Section 5 - Solaris 10 11/06 Man Pages

Other operating system man pages available here

Hopefully, this page is exactly what you are looking for, but if not, you can always find further assistance on Unix/Linux Forum!

SQL Commands                                            CLUSTER()



NAME
     CLUSTER - cluster a table according to an index


SYNOPSIS
     CLUSTER indexname ON tablename
     CLUSTER tablename
     CLUSTER


DESCRIPTION
     CLUSTER instructs PostgreSQL to cluster the table  specified
     by  tablename 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. Clustering is a one-time opera-
     tion:  when the table is subsequently updated,  the  changes
     are  not clustered. That is, no attempt is made to store new
     or updated rows according  to  their  index  order.  If  one
     wishes,  one  can periodically recluster by issuing the com-
     mand again.

     When a table is clustered,  PostgreSQL  remembers  on  which
     index  it  was clustered. The form CLUSTER tablename reclus-
     ters the table on the  same  index  that  it  was  clustered
     before.

     CLUSTER without any parameter reclusters all the  tables  in
     the  current  database  that  the  calling user owns, or all
     tables if called by a superuser. (Never-clustered tables are
     not  included.)  This  form of CLUSTER cannot be called from
     inside a transaction or function.

     When a table is being clustered, an ACCESS EXCLUSIVE lock is
     acquired  on it. This prevents any other database operations
     (both reads and writes) from operating on  the  table  until
     the CLUSTER is finished.

PARAMETERS
     indexname
          The name of an index.

     tablename
          The name (possibly schema-qualified) of a table.

NOTES
     In cases where you are accessing single rows randomly within
     a  table, the actual order of the data in the table is unim-
     portant. 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.  If you  are  requesting  a

SQL - Language StatemLast change: 2005-11-05 1

SQL Commands                                            CLUSTER()



     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  prob-
     ably  already  on  the  same heap page, and so you save disk
     accesses and speed up the query.

     During the cluster operation, a temporary copy of the  table
     is  created that contains the table data in the index order.
     Temporary copies of each index on the table are  created  as
     well.  Therefore, you need free space on disk at least equal
     to the sum of the table size and the index sizes.

     Because CLUSTER remembers the  clustering  information,  one
     can  cluster  the  tables  one  wants clustered manually the
     first time, and setup a timed event  similar  to  VACUUM  so
     that the tables are periodically reclustered.

     Because the planner records statistics about the ordering of
     tables,  it  is advisable to run ANALYZE [analyze(5)] on the
     newly clustered table.  Otherwise, the planner may make poor
     choices of query plans.

     There is another way to cluster data.  The  CLUSTER  command
     reorders  the original table using 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. (Post-
     greSQL has a cache, but the majority of a big table will not
     fit  in  the cache.)  The other way to cluster a table is to
     use

     CREATE TABLE newtable AS
         SELECT columnlist FROM table ORDER BY columnlist;

     which uses the PostgreSQL  sorting  code  in  the  ORDER  BY
     clause  to  create  the  desired order; this is usually much
     faster than an index scan for unordered data. You then  drop
     the old table, use ALTER TABLE ... RENAME to rename newtable
     to the old name, and recreate the table's indexes.  However,
     this  approach  does not preserve OIDs, constraints, foreign
     key relationships, granted privileges, and  other  ancillary
     properties  of  the  table - all such items must be manually
     recreated.

EXAMPLES
     Cluster the table  employees  on  the  basis  of  its  index
     emp_ind:

     CLUSTER emp_ind ON emp;

SQL - Language StatemLast change: 2005-11-05 2

SQL Commands                                            CLUSTER()



     Cluster the employees table using the same  index  that  was
     used before:

     CLUSTER emp;


     Cluster all tables in the database that have previously been
     clustered:

     CLUSTER;


COMPATIBILITY
     There is no CLUSTER statement in the SQL standard.

SEE ALSO
     clusterdb [clusterdb(1)]

SQL - Language StatemLast change: 2005-11-05 3

Your favourite pages:

No pages logged yet.
Trying to save cookie...

Top 10 most popular pages:

CPAN man page (4210 hits)
(Suse Linux 10.1)

ssh man page (4117 hits)
(Suse Linux 10.1)

svn man page (2480 hits)
(FreeBSD 6.2)

adv_cap_autoneg man page (1782 hits)
(Solaris 10 11_06)

startproc man page (1711 hits)
(Suse Linux 10.1)

sqlite3 man page (1663 hits)
(openSUSE 10.2)

signal man page (1393 hits)
(Suse Linux 10.1)

pprosetup man page (1241 hits)
(Solaris 10 11_06)

netcat man page (1237 hits)
(Suse Linux 10.1)

ssh-socks5-proxy-connect man page (1126 hits)
(Solaris 10 11_06)