File: sql-vacuum-1.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (401 lines) | stat: -rw-r--r-- 7,079 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>VACUUM</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="UPDATE"
HREF="sql-update.html"><LINK
REL="NEXT"
TITLE="Utility Applications"
HREF="utilities.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="sql-update.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="utilities.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
>VACUUM</H1
><DIV
CLASS="REFNAMEDIV"
><H2
>Name</H2
>VACUUM &#8212; Clean and analyze a <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> database</DIV
><DIV
CLASS="REFSYNOPSISDIV"
><PRE
CLASS="SYNOPSIS"
>VACUUM [ VERBOSE ] [ ANALYZE ] [ <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
> ]
VACUUM [ VERBOSE ] ANALYZE [ <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
> [ (<TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> [, ...] ) ] ]</PRE
><DIV
CLASS="REFSECT2"
><H3
>Inputs</H3
><P
>&#13;<P
></P
></P><DL
><DT
>VERBOSE</DT
><DD
><P
>Prints a detailed vacuum activity report for each table.&#13;</P
></DD
><DT
>ANALYZE</DT
><DD
><P
>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 valuable when several execution paths are possible.&#13;</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
></DT
><DD
><P
>The name of a specific table to vacuum. Defaults to all tables.&#13;</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
></DT
><DD
><P
>The name of a specific column to analyze. Defaults to all columns.&#13;</P
></DD
></DL
><P>&#13;</P
></DIV
><DIV
CLASS="REFSECT2"
><H3
>Outputs</H3
><P
>&#13;<P
></P
></P><DL
><DT
><SPAN
CLASS="RETURNVALUE"
>VACUUM</SPAN
></DT
><DD
><P
>The command has been accepted and the database is being cleaned.&#13;</P
></DD
><DT
>NOTICE:  --Relation <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>--</DT
><DD
><P
>The report header for <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>.&#13;</P
></DD
><DT
>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.</DT
><DD
><P
>The analysis for <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
> itself.&#13;</P
></DD
><DT
>NOTICE:  Index <TT
CLASS="REPLACEABLE"
><I
>index</I
></TT
>: Pages 28;
         Tuples 1000: Deleted 3000. Elapsed 0/0 sec.</DT
><DD
><P
>The analysis for an index on the target table.&#13;</P
></DD
></DL
><P>&#13;</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Description</H2
><P
><B
CLASS="COMMAND"
>VACUUM</B
> serves two purposes in 
<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> as both a means to reclaim storage and
also a means to collect information for the optimizer.&#13;</P
><P
><B
CLASS="COMMAND"
>VACUUM</B
> opens every class in the database,
cleans out records from rolled back transactions, and updates statistics in the
system catalogs.  The statistics maintained include the number of
tuples and number of pages stored in all classes.

Running <B
CLASS="COMMAND"
>VACUUM</B
>
periodically will increase the speed of the database in processing user queries.&#13;</P
><DIV
CLASS="REFSECT2"
><H3
>Notes</H3
><P
>The open database is target for <B
CLASS="COMMAND"
>VACUUM</B
>.&#13;</P
><P
>We recommend that active production databases be cleaned nightly, in order
to keep statistics relatively current.  The <B
CLASS="COMMAND"
>VACUUM</B
>
query may be executed at any time, however.  In particular, after
copying a large class into <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> 
or after deleting a large number of
records, it may be a good idea to issue a <B
CLASS="COMMAND"
>VACUUM</B
>
query.  This will update the system catalogs with the results of all
recent changes, and allow the <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> 
query optimizer to make better choices in planning user queries.&#13;</P
><P
>If the server crashes during a <B
CLASS="COMMAND"
>VACUUM</B
> command, 
chances are it will leave a lock file hanging around.  
Attempts to re-run the <B
CLASS="COMMAND"
>VACUUM</B
> command
result in an error message about the creation of a lock file.  If you
are sure <B
CLASS="COMMAND"
>VACUUM</B
> is not running, 
remove the <TT
CLASS="FILENAME"
>pg_vlock</TT
> file in your
database directory 
(i.e. <TT
CLASS="FILENAME"
><TT
CLASS="ENVAR"
>PGDATA</TT
>/base/dbname/pg_vlock</TT
>).</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Usage</H2
><P
>The following is an example from running <B
CLASS="COMMAND"
>VACUUM</B
> on a table
in the regression database:

<PRE
CLASS="PROGRAMLISTING"
>regression=&#62; 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 --&#62; 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</PRE
>&#13;</P
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Compatibility</H2
><P
></P
><DIV
CLASS="REFSECT2"
><H3
>SQL92</H3
><P
>There is no <B
CLASS="COMMAND"
>VACUUM</B
> statement in SQL92.&#13;</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-update.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="utilities.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>UPDATE</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Utility Applications</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>