1ANALYZE(7)                       SQL Commands                       ANALYZE(7)
2
3
4

NAME

6       ANALYZE - collect statistics about a database
7
8

SYNOPSIS

10       ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]
11
12

DESCRIPTION

14       ANALYZE  collects  statistics about the contents of tables in the data‐
15       base, and stores the results in the pg_statistic system catalog. Subse‐
16       quently,  the query planner uses these statistics to help determine the
17       most efficient execution plans for queries.
18
19       With no parameter, ANALYZE examines every table in  the  current  data‐
20       base. With a parameter, ANALYZE examines only that table. It is further
21       possible to give a list of column names, in which case only the statis‐
22       tics for those columns are collected.
23

PARAMETERS

25       VERBOSE
26              Enables display of progress messages.
27
28       table  The name (possibly schema-qualified) of a specific table to ana‐
29              lyze. Defaults to all tables in the current database.
30
31       column The name of a specific column to analyze. Defaults to  all  col‐
32              umns.
33

OUTPUTS

35       When  VERBOSE is specified, ANALYZE emits progress messages to indicate
36       which table is currently being processed. Various statistics about  the
37       tables are printed as well.
38

NOTES

40       In  the  default  PostgreSQL  configuration, in the documentation takes
41       care of automatic analyzing of tables when they are first  loaded  with
42       data, and as they change throughout regular operation.  When autovacuum
43       is disabled, it is a good idea to run  ANALYZE  periodically,  or  just
44       after making major changes in the contents of a table. Accurate statis‐
45       tics will help the planner to choose the most appropriate  query  plan,
46       and thereby improve the speed of query processing. A common strategy is
47       to run VACUUM [vacuum(7)] and ANALYZE once a  day  during  a  low-usage
48       time of day.
49
50       ANALYZE requires only a read lock on the target table, so it can run in
51       parallel with other activity on the table.
52
53       The statistics collected by ANALYZE usually include a list of  some  of
54       the  most  common  values  in  each  column and a histogram showing the
55       approximate data distribution in each column. One or both of these  can
56       be  omitted  if  ANALYZE  deems  them  uninteresting (for example, in a
57       unique-key column, there are no common values) or if  the  column  data
58       type does not support the appropriate operators. There is more informa‐
59       tion about the statistics in in the documentation.
60
61       For large tables, ANALYZE takes a random sample of the table  contents,
62       rather  than examining every row. This allows even very large tables to
63       be analyzed in a small amount of time. Note, however, that the  statis‐
64       tics  are  only approximate, and will change slightly each time ANALYZE
65       is run, even if the actual table contents did not  change.  This  might
66       result  in  small  changes  in  the  planner's estimated costs shown by
67       EXPLAIN [explain(7)].  In rare situations,  this  non-determinism  will
68       cause  the  planner's choices of query plans to change after ANALYZE is
69       run.  To avoid this, raise the amount of statistics collected  by  ANA‐
70       LYZE, as described below.
71
72       The  extent of analysis can be controlled by adjusting the default_sta‐
73       tistics_target configuration variable, or on a  column-by-column  basis
74       by  setting the per-column statistics target with ALTER TABLE ... ALTER
75       COLUMN ... SET STATISTICS (see ALTER TABLE [alter_table(7)]). The  tar‐
76       get  value  sets the maximum number of entries in the most-common-value
77       list and the maximum number of bins in the histogram. The default  tar‐
78       get  value  is  100,  but  this can be adjusted up or down to trade off
79       accuracy of planner estimates against the time taken  for  ANALYZE  and
80       the  amount  of  space occupied in pg_statistic. In particular, setting
81       the statistics target to zero disables  collection  of  statistics  for
82       that  column.  It might be useful to do that for columns that are never
83       used as part of the WHERE, GROUP BY, or ORDER BY  clauses  of  queries,
84       since the planner will have no use for statistics on such columns.
85
86       The  largest  statistics target among the columns being analyzed deter‐
87       mines the number of table  rows  sampled  to  prepare  the  statistics.
88       Increasing  the  target  causes a proportional increase in the time and
89       space needed to do ANALYZE.
90

COMPATIBILITY

92       There is no ANALYZE statement in the SQL standard.
93

SEE ALSO

95       VACUUM [vacuum(7)], vacuumdb [vacuumdb(1)], in  the  documentation,  in
96       the documentation
97
98
99
100SQL - Language Statements         2011-09-22                        ANALYZE(7)
Impressum