VACUUM(l)                                               VACUUM(l)

NAME
       VACUUM - Clean and analyze a Postgres database

SYNOPSIS
       VACUUM [ VERBOSE ] [ ANALYZE ] [ table ]
       VACUUM [ VERBOSE ] ANALYZE [ ER">tBLE> [ (column [, ...] ) ] ]

   INPUTS
       VERBOSE
              Prints  a  detailed vacuum activity report for each
              table.

       ANALYZE
              Updates column statistics used by the optimizer  to
              determine  the  most  efficient  way  to  execute a
              query.  The statistics represent the disbursion  of
              the data in each column.  This information is valu-
              able when several execution paths are possible.

       table  The name of a specific table to vacuum. Defaults to
              all tables.

       column The  name of a specific column to analyze. Defaults
              to all columns.

   OUTPUTS
       VACUUM The command has been accepted and the  database  is
              being cleaned.

       NOTICE: --Relation table--
              The report header for table.

       NOTICE: Pages 98: Changed 25, Reapped 74, Empty 0, New 0;
              The analysis for table itself.

       NOTICE: Index index: Pages 28;
              The analysis for an index on the target table.

DESCRIPTION
       VACUUM  serves two purposes in Postgres as both a means to
       reclaim storage and also a means  to  collect  information
       for the optimizer.

       VACUUM  opens  every  class  in  the  database, cleans out
       records from rolled back transactions, and updates statis-
       tics  in  the  system  catalogs. The statistics maintained
       include the number of tuples and number of pages stored in
       all classes.

       Running VACUUM periodically will increase the speed of the
       database in processing user queries.

   NOTES
       The open database is the target for VACUUM.

       We recommend that active production databases  be  cleaned
       nightly,  in  order to keep statistics relatively current.
       The VACUUM query may be executed at any time, however.  In
       particular,  after  copying a large class into Postgres or
       after deleting a large number of records, it may be a good
       idea  to issue a VACUUM query. This will update the system
       catalogs with the results of all recent changes, and allow
       the  Postgres  query  optimizer  to make better choices in
       planning user queries.

       If the server crashes during a VACUUM command, chances are
       it will leave a lock file hanging around.  Attempts to re-
       run the VACUUM command result in an  error  message  about
       the creation of a lock file. If you are sure VACUUM is not
       running, remove the pg_vlock file in your database  direc-
       tory (i.e. PGDATA/base/dbname/pg_vlock).

USAGE
       The following is an example from running VACUUM on a table
       in the regression database:

       regression=> vacuum verbose analyze onek;
       NOTICE:  --Relation onek--
       NOTICE:  Pages 98: Changed 25, Reapped 74, Empty 0, New 0;
                Tup 1000: Vac 3000, Crash 0, UnUsed 0, MinLen 188, MaxLen 188;
                Re-using: Free/Avail. Space 586952/586952; EndEmpty/Avail. Pages 0/74.
                Elapsed 0/0 sec.
       NOTICE:  Index onek_stringu1: Pages 28; Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
       NOTICE:  Index onek_hundred: Pages 12; Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
       NOTICE:  Index onek_unique2: Pages 19; Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
       NOTICE:  Index onek_unique1: Pages 17; Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
       NOTICE:  Rel onek: Pages: 98 --> 25; Tuple(s) moved: 1000. Elapsed 0/1 sec.
       NOTICE:  Index onek_stringu1: Pages 28; Tuples 1000: Deleted 1000. Elapsed 0/0 sec.
       NOTICE:  Index onek_hundred: Pages 12; Tuples 1000: Deleted 1000. Elapsed 0/0 sec.
       NOTICE:  Index onek_unique2: Pages 19; Tuples 1000: Deleted 1000. Elapsed 0/0 sec.
       NOTICE:  Index onek_unique1: Pages 17; Tuples 1000: Deleted 1000. Elapsed 0/0 sec.
       VACUUM

COMPATIBILITY
   SQL92
       There is no VACUUM statement in SQL92.

SQL - Language Statements 15 August 1999                        1