NAME
ANALYZE - 收集与数据库有关的统计
SYNOPSIS
ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]
DESCRIPTION 描述
ANALYZE 收集有关 PostgreSQL 表的内容的统计，然后把结果保存在系统表 pg_statistic 里。随后，查询规划器就可以使用这些统计帮助判断查询的最有效的规划。
如果没有参数，ANALYZE 检查在当前数据库里的所有表。 如果有参数，ANALYZE 只检查那个表。 你还可以给出一列字段名字，这个时候只收集那些字段的统计信息。
PARAMETERS 参数
- VERBOSE
打开处理过程信息的显示。- table
要分析的特定表（可能用模式名修饰）的名字。缺省是当前数据库里所有表。- column
要分析的特定字段的名字。缺省是所有字段。
OUTPUTS 输出
如果声明了 VERBOSE，ANALYZE 发出进度信息，表明当前正在处理的是哪行。 同时打印有关改表的很多其它信息。
NOTES 注意
周期性地运行 ANALYZE，或者在对表的大部分内容做了更改之后马上运行它是个好习惯， 准确的统计信息将帮助规划器选择最合适的查询规划，并因此而改善查询处理的速度。 一种比较经常采用的策略是每天在低负荷的时候运行一次 VACUUM [vacuum(7)] 和 ANALYZE。
和 VACUUM FULL 不同的是， ANALYZE 只需要在目标表上有一个读取锁， 因此它可以和表上的其它活动并行地运行。
收集的统计信息通常包括一个每字段最常用数值的列表以及一个包线图，显示每个字段里数据的近似分布。 如果 ANALYZE 认为它们都没有什么用， （比如，在一个唯一键字的字段上没有公共的数值）或者是该字段数据类型不支持相关的操作符， 那么它们都可以忽略。在 Chapter 21 ``Routine Database Maintenance'' 中有关于统计的更多信息。
对于大表，ANALYZE 采集表内容的一个随机的抽样做统计，而不是检查每一行。 这样就保证了即使是在很大的表上，我们也只需要很少的一些时间就可以完成分析。 不过，要注意的是统计只是近似的结果，而且每次运行ANALYZE都会导致 EXPLAIN 显示的规划器的预期开销有一些小变化， 即使表内容实际上没有改变也这样。在很小的概率的情况下，这个不确定的行为会导致查询优化器在不同 ANALYZE 之间选择不同的查询规划。为了避免这个问题，可以提高 ANALYZE 收集的统计数量，像下面描述的那样。
分析的广度可以通过用调整 default_statistics_target 参变量， 或者是以每字段为基础通过用 ALTER TABLE ... ALTER COLUMN ... SET STATISTICS （参阅 ALTER TABLE [alter_table(7)]） 设置每字段的统计目标来控制。目标数值设置最常用数值列表中的记录的***数目以及包线图中的***块数。 缺省的目标数值是 10，不过我们可以调节这个数值获取规划器计算精度和 ANALYZE 运行所需要的时间以及 pg_statistic 里面占据的空间数目之间的平衡。 特别是，把统计目标设置为零就关闭了该字段的统计收集。 这么做对那些从来不参与到查询的 WHERE，GROUP BY，或者 ORDER BY 子句里的字段是很有用的，因为规划器不会使用到这样的字段上的统计。
在被分析的字段中***的统计目标决定为统计采样的表中的行的数目。 增大目标会导致做 ANALYZE 的时候成比例地增大对时间和空间的需求。
#p#
NAME
ANALYZE - collect statistics about a database
SYNOPSIS
ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]
DESCRIPTION
ANALYZE collects statistics about the contents of tables in the database, and stores the results in the system table pg_statistic. Subsequently, the query planner uses these 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 collected.
PARAMETERS
- 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
When VERBOSE is specified, ANALYZE emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well.
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(7)] 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.
The statistics collected by ANALYZE 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 chapter called ``Routine Database Maintenance'' in the documentation.
For large tables, ANALYZE takes a random sample of the table 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. In rare situations, this non-determinism will cause the query optimizer to choose a different query plan between runs of ANALYZE. To avoid this, raise the amount of statistics collected by ANALYZE, as described below.
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(7)]). 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.
