ANALYZE(l) SQL - Language Statements (2002-11-22) ANALYZE(l)
NAME
ANALYZE - collect statistics about a database
SYNOPSIS
ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]
INPUTS
VERBOSE
Enables display of progress messages.
table
The name (possibly schema-qualified) of a specific
table to analyze. Defaults to all tables in the current
database.
column
The name of a specific column to analyze. Defaults to
all columns.
OUTPUTS
ANALYZE
The command is complete.
DESCRIPTION
ANALYZE collects statistics about the contents of PostgreSQL
tables, and stores the results in the system table
pg_statistic. Subsequently, the query planner uses the
statistics to help determine the most efficient execution
plans for queries.
With no parameter, ANALYZE examines every table in the
current database. With a parameter, ANALYZE examines only
that table. It is further possible to give a list of column
names, in which case only the statistics for those columns
are updated.
NOTES
It is a good idea to run ANALYZE periodically, or just after
making major changes in the contents of a table. Accurate
statistics will help the planner to choose the most
appropriate query plan, and thereby improve the speed of
query processing. A common strategy is to run VACUUM
[vacuum(l)] and ANALYZE once a day during a low-usage time
of day.
Unlike VACUUM FULL, ANALYZE requires only a read lock on the
target table, so it can run in parallel with other activity
on the table.
For large tables, ANALYZE takes a random sample of the table
Page 1 (printed 3/24/03)
ANALYZE(l) SQL - Language Statements (2002-11-22) ANALYZE(l)
contents, rather than examining every row. This allows even
very large tables to be analyzed in a small amount of time.
Note however that the statistics are only approximate, and
will change slightly each time ANALYZE is run, even if the
actual table contents did not change. This may result in
small changes in the planner's estimated costs shown by
EXPLAIN.
The collected statistics usually include a list of some of
the most common values in each column and a histogram
showing the approximate data distribution in each column.
One or both of these may be omitted if ANALYZE deems them
uninteresting (for example, in a unique-key column, there
are no common values) or if the column data type does not
support the appropriate operators. There is more information
about the statistics in the User's Guide.
The extent of analysis can be controlled by adjusting the
default_statistics_target parameter variable, or on a
column-by-column basis by setting the per-column statistics
target with ALTER TABLE ALTER COLUMN SET STATISTICS (see
ALTER TABLE [alter_table(l)]). The target value sets the
maximum number of entries in the most-common-value list and
the maximum number of bins in the histogram. The default
target value is 10, but this can be adjusted up or down to
trade off accuracy of planner estimates against the time
taken for ANALYZE and the amount of space occupied in
pg_statistic. In particular, setting the statistics target
to zero disables collection of statistics for that column.
It may be useful to do that for columns that are never used
as part of the WHERE, GROUP BY, or ORDER BY clauses of
queries, since the planner will have no use for statistics
on such columns.
The largest statistics target among the columns being
analyzed determines the number of table rows sampled to
prepare the statistics. Increasing the target causes a
proportional increase in the time and space needed to do
ANALYZE.
COMPATIBILITY
SQL92
There is no ANALYZE statement in SQL92.
Page 2 (printed 3/24/03)