NAME

ANALYZE - 收集與數(shù)據(jù)庫(kù)有關(guān)的統(tǒng)計(jì)

SYNOPSIS

ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]

DESCRIPTION 描述

ANALYZE 收集有關(guān) PostgreSQL 表的內(nèi)容的統(tǒng)計(jì)，然后把結(jié)果保存在系統(tǒng)表 pg_statistic 里。隨后，查詢規(guī)劃器就可以使用這些統(tǒng)計(jì)幫助判斷查詢的最有效的規(guī)劃。

 如果沒(méi)有參數(shù)，ANALYZE 檢查在當(dāng)前數(shù)據(jù)庫(kù)里的所有表。 如果有參數(shù)，ANALYZE 只檢查那個(gè)表。 你還可以給出一列字段名字，這個(gè)時(shí)候只收集那些字段的統(tǒng)計(jì)信息。  

PARAMETERS 參數(shù)

VERBOSE

 打開(kāi)處理過(guò)程信息的顯示。

table

 要分析的特定表（可能用模式名修飾）的名字。缺省是當(dāng)前數(shù)據(jù)庫(kù)里所有表。

column

 要分析的特定字段的名字。缺省是所有字段。

OUTPUTS 輸出

 如果聲明了 VERBOSE，ANALYZE 發(fā)出進(jìn)度信息，表明當(dāng)前正在處理的是哪行。 同時(shí)打印有關(guān)改表的很多其它信息。  

NOTES 注意

 周期性地運(yùn)行 ANALYZE，或者在對(duì)表的大部分內(nèi)容做了更改之后馬上運(yùn)行它是個(gè)好習(xí)慣， 準(zhǔn)確的統(tǒng)計(jì)信息將幫助規(guī)劃器選擇最合適的查詢規(guī)劃，并因此而改善查詢處理的速度。 一種比較經(jīng)常采用的策略是每天在低負(fù)荷的時(shí)候運(yùn)行一次 VACUUM [vacuum(7)] 和 ANALYZE。

 和 VACUUM FULL 不同的是， ANALYZE 只需要在目標(biāo)表上有一個(gè)讀取鎖， 因此它可以和表上的其它活動(dòng)并行地運(yùn)行。

 收集的統(tǒng)計(jì)信息通常包括一個(gè)每字段最常用數(shù)值的列表以及一個(gè)包線圖，顯示每個(gè)字段里數(shù)據(jù)的近似分布。 如果 ANALYZE 認(rèn)為它們都沒(méi)有什么用， （比如，在一個(gè)唯一鍵字的字段上沒(méi)有公共的數(shù)值）或者是該字段數(shù)據(jù)類型不支持相關(guān)的操作符， 那么它們都可以忽略。在 Chapter 21 ``Routine Database Maintenance'' 中有關(guān)于統(tǒng)計(jì)的更多信息。

 對(duì)于大表，ANALYZE 采集表內(nèi)容的一個(gè)隨機(jī)的抽樣做統(tǒng)計(jì)，而不是檢查每一行。 這樣就保證了即使是在很大的表上，我們也只需要很少的一些時(shí)間就可以完成分析。 不過(guò)，要注意的是統(tǒng)計(jì)只是近似的結(jié)果，而且每次運(yùn)行ANALYZE都會(huì)導(dǎo)致 EXPLAIN 顯示的規(guī)劃器的預(yù)期開(kāi)銷有一些小變化， 即使表內(nèi)容實(shí)際上沒(méi)有改變也這樣。在很小的概率的情況下，這個(gè)不確定的行為會(huì)導(dǎo)致查詢優(yōu)化器在不同 ANALYZE  之間選擇不同的查詢規(guī)劃。為了避免這個(gè)問(wèn)題，可以提高 ANALYZE 收集的統(tǒng)計(jì)數(shù)量，像下面描述的那樣。

 分析的廣度可以通過(guò)用調(diào)整 default_statistics_target 參變量， 或者是以每字段為基礎(chǔ)通過(guò)用 ALTER TABLE ... ALTER COLUMN ... SET STATISTICS  （參閱  ALTER TABLE [alter_table(7)]） 設(shè)置每字段的統(tǒng)計(jì)目標(biāo)來(lái)控制。目標(biāo)數(shù)值設(shè)置最常用數(shù)值列表中的記錄的***數(shù)目以及包線圖中的***塊數(shù)。 缺省的目標(biāo)數(shù)值是 10，不過(guò)我們可以調(diào)節(jié)這個(gè)數(shù)值獲取規(guī)劃器計(jì)算精度和 ANALYZE 運(yùn)行所需要的時(shí)間以及 pg_statistic 里面占據(jù)的空間數(shù)目之間的平衡。 特別是，把統(tǒng)計(jì)目標(biāo)設(shè)置為零就關(guān)閉了該字段的統(tǒng)計(jì)收集。 這么做對(duì)那些從來(lái)不參與到查詢的 WHERE，GROUP BY，或者 ORDER BY 子句里的字段是很有用的，因?yàn)橐?guī)劃器不會(huì)使用到這樣的字段上的統(tǒng)計(jì)。

 在被分析的字段中***的統(tǒng)計(jì)目標(biāo)決定為統(tǒng)計(jì)采樣的表中的行的數(shù)目。 增大目標(biāo)會(huì)導(dǎo)致做 ANALYZE 的時(shí)候成比例地增大對(duì)時(shí)間和空間的需求。

#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.