2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/vacuum.sgml,v 1.11 2000/10/05 19:48:19 momjian Exp $
6 <refentry id="SQL-VACUUM">
8 <refentrytitle id="sql-vacuum-title">
11 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 Clean and analyze a <productname>Postgres</productname> database
23 <date>1999-07-20</date>
26 VACUUM [ VERBOSE ] [ ANALYZE ] [ <replaceable class="PARAMETER">table</replaceable> ]
27 VACUUM [ VERBOSE ] ANALYZE [ <replaceable class="PARAMETER">table</replaceable> [ (<replaceable class="PARAMETER">column</replaceable> [, ...] ) ] ]
30 <refsect2 id="R2-SQL-VACUUM-1">
32 <date>1998-10-04</date>
44 Prints a detailed vacuum activity report for each table.
52 Updates column statistics used by the optimizer to
53 determine the most efficient way to execute a query.
58 <term><replaceable class="PARAMETER">table</replaceable></term>
61 The name of a specific table to vacuum. Defaults to all tables.
66 <term><replaceable class="PARAMETER">column</replaceable></term>
69 The name of a specific column to analyze. Defaults to all columns.
77 <refsect2 id="R2-SQL-VACUUM-2">
79 <date>1998-10-04</date>
88 <term><computeroutput>
89 <returnvalue>VACUUM</returnvalue>
90 </computeroutput></term>
93 The command has been accepted and the database is being cleaned.
99 <term><computeroutput>
100 NOTICE: --Relation <replaceable class="PARAMETER">table</replaceable>--
101 </computeroutput></term>
104 The report header for <replaceable class="PARAMETER">table</replaceable>.
110 <term><computeroutput>
111 NOTICE: Pages 98: Changed 25, Reapped 74, Empty 0, New 0;
112 Tup 1000: Vac 3000, Crash 0, UnUsed 0, MinLen 188, MaxLen 188;
113 Re-using: Free/Avail. Space 586952/586952; EndEmpty/Avail. Pages 0/74.
115 </computeroutput></term>
118 The analysis for <replaceable class="PARAMETER">table</replaceable> itself.
124 <term><computeroutput>
125 NOTICE: Index <replaceable class="PARAMETER">index</replaceable>: Pages 28;
126 Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
127 </computeroutput></term>
130 The analysis for an index on the target table.
139 <refsect1 id="R1-SQL-VACUUM-1">
141 <date>1998-10-04</date>
147 <command>VACUUM</command> serves two purposes in
148 <productname>Postgres</productname> as both a means to reclaim storage and
149 also a means to collect information for the optimizer.
153 <command>VACUUM</command> opens every class in the database,
154 cleans out records from rolled back transactions, and updates statistics in the
155 system catalogs. The statistics maintained include the number of
156 tuples and number of pages stored in all classes.
161 <command>VACUUM ANALYZE</command> collects statistics representing the
162 dispersion of the data in each column.
163 This information is valuable when several query execution paths are possible.
167 Running <command>VACUUM</command>
168 periodically will increase the speed of the database in processing user queries.
171 <refsect2 id="R2-SQL-VACUUM-3">
173 <date>1998-10-04</date>
179 The open database is the target for <command>VACUUM</command>.
182 We recommend that active production databases be
183 <command>VACUUM</command>-ed nightly, in order to keep remove
184 expired rows. After copying a large class into
185 <productname>Postgres</productname> or after deleting a large number
186 of records, it may be a good idea to issue a <command>VACUUM
187 ANALYZE</command> query. This will update the system catalogs with
188 the results of all recent changes, and allow the
189 <productname>Postgres</productname> query optimizer to make better
190 choices in planning user queries.
196 <refsect1 id="R1-SQL-VACUUM-2">
201 The following is an example from running <command>VACUUM</command> on a table
202 in the regression database:
205 regression=> vacuum verbose analyze onek;
206 NOTICE: --Relation onek--
207 NOTICE: Pages 98: Changed 25, Reapped 74, Empty 0, New 0;
208 Tup 1000: Vac 3000, Crash 0, UnUsed 0, MinLen 188, MaxLen 188;
209 Re-using: Free/Avail. Space 586952/586952; EndEmpty/Avail. Pages 0/74.
211 NOTICE: Index onek_stringu1: Pages 28; Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
212 NOTICE: Index onek_hundred: Pages 12; Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
213 NOTICE: Index onek_unique2: Pages 19; Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
214 NOTICE: Index onek_unique1: Pages 17; Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
215 NOTICE: Rel onek: Pages: 98 --> 25; Tuple(s) moved: 1000. Elapsed 0/1 sec.
216 NOTICE: Index onek_stringu1: Pages 28; Tuples 1000: Deleted 1000. Elapsed 0/0 sec.
217 NOTICE: Index onek_hundred: Pages 12; Tuples 1000: Deleted 1000. Elapsed 0/0 sec.
218 NOTICE: Index onek_unique2: Pages 19; Tuples 1000: Deleted 1000. Elapsed 0/0 sec.
219 NOTICE: Index onek_unique1: Pages 17; Tuples 1000: Deleted 1000. Elapsed 0/0 sec.
225 <refsect1 id="R1-SQL-VACUUM-3">
230 <refsect2 id="R2-SQL-VACUUM-4">
232 <date>1998-10-04</date>
238 There is no <command>VACUUM</command> statement in <acronym>SQL92</acronym>.
244 <!-- Keep this comment at the end of the file
249 sgml-minimize-attributes:nil
250 sgml-always-quote-attributes:t
253 sgml-parent-document:nil
254 sgml-default-dtd-file:"../reference.ced"
255 sgml-exposed-tags:nil
256 sgml-local-catalogs:"/usr/lib/sgml/catalog"
257 sgml-local-ecat-files:nil