{"Entry":{"collection":"sql","key":"analyze","name":"ANALYZE","aliases":["analyze"],"metadata":{"aliases":["analyze"],"changed_in":["9.2","11","12","16","17","18"],"changes":[{"from":"7.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"7.3"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters","outputs","notes"],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.1"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":["see_also"],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes","see_also"],"removed":[]},"status":"changed","synopsis":null,"to":"8.4"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes","see_also"],"removed":[]},"status":"changed","synopsis":null,"to":"9.0"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","see_also"],"removed":[]},"status":"changed","synopsis":{"added":["ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]"],"removed":["ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]"]},"to":"9.2"},{"from":"9.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.4"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.5"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes","see_also"],"removed":[]},"status":"changed","synopsis":null,"to":"9.6"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":{"added":["ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]","ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ]","VERBOSE"],"removed":["ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]"]},"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["VERBOSE [ boolean ]","SKIP_LOCKED [ boolean ]"],"removed":[]},"to":"12"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes","see_also"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["BUFFER_USAGE_LIMIT size"],"removed":[]},"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes","compatibility","see_also"],"removed":[]},"status":"changed","synopsis":{"added":[],"removed":["ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]","ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ]"]},"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","see_also"],"removed":[]},"status":"changed","synopsis":{"added":["[ ONLY ] table_name [ * ] [ ( column_name [, ...] ) ]"],"removed":[]},"to":"18"},{"from":"19","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","outputs"],"removed":[]},"status":"changed","synopsis":null,"to":"20"}],"content_hash":"c5decc0a7cc8791c183affb758be50632008ea510bab6d64ca12976eeac20813","editorial":{},"first_version":"7.2","group":"maintenance","imported_at":"2026-09-30T17:43:39.57664+08:00","last_version":"20","name":"ANALYZE","object":"","position":15000,"present_in":["7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"collect statistics about a database","purpose_zh":"","related":["vacuum"],"slug":"analyze","source_rev":"a709ab85","synopsis":"ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]\n\nwhere option can be one of:\n\nVERBOSE [ boolean ]\nSKIP_LOCKED [ boolean ]\nBUFFER_USAGE_LIMIT size\nand table_and_columns is:\n\n[ ONLY ] table_name [ * ] [ ( column_name [, ...] ) ]","verb":"ANALYZE"}},"Definition":{"Collection":"sql","Key":"analyze","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"analyze","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-ANALYZE","file":"sql-analyze.html","lang":"en","name":"ANALYZE","purpose":"collect statistics about a database","purpose_zh":"","related":["vacuum"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e collects statistics about the contents of tables in the database, and stores the results in the \u003ca href=\"/docs/18/catalog-pg-statistic.html\" title=\"52.51. pg_statistic\"\u003e\u003ccode class=\"structname\"\u003epg_statistic\u003c/code\u003e\u003c/a\u003e system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.\u003c/p\u003e\u003cp\u003eWithout a \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e list, \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e processes every table and materialized view in the current database that the current user has permission to analyze. With a list, \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e processes only those table(s). It is further possible to give a list of column names for a table, in which case only the statistics for those columns are collected.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eVERBOSE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eEnables display of progress messages at \u003ccode class=\"literal\"\u003eINFO\u003c/code\u003e level.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSKIP_LOCKED\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies that \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e should not wait for any conflicting locks to be released when beginning work on a relation: if a relation cannot be locked immediately without waiting, the relation is skipped. Note that even with this option, \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e may still block when opening the relation's indexes or when acquiring sample rows from partitions, table inheritance children, and some types of foreign tables. Also, while \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e ordinarily processes all partitions of specified partitioned tables, this option will cause \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e to skip all partitions if there is a conflicting lock on the partitioned table.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eBUFFER_USAGE_LIMIT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies the \u003ca href=\"/docs/18/glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY\"\u003e\u003c/a\u003e\u003ca href=\"/docs/18/glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY\" title=\"Buffer Access Strategy\"\u003eBuffer Access Strategy\u003c/a\u003e ring buffer size for \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e. This size is used to calculate the number of shared buffers which will be reused as part of this strategy. \u003ccode class=\"literal\"\u003e0\u003c/code\u003e disables use of a \u003ccode class=\"literal\"\u003eBuffer Access Strategy\u003c/code\u003e. When this option is not specified, \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e uses the value from \u003ca href=\"/docs/18/runtime-config-resource.html#GUC-VACUUM-BUFFER-USAGE-LIMIT\"\u003evacuum_buffer_usage_limit\u003c/a\u003e. Higher settings can allow \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e to run more quickly, but having too large a setting may cause too many other useful pages to be evicted from shared buffers. The minimum value is \u003ccode class=\"literal\"\u003e128 kB\u003c/code\u003e and the maximum value is \u003ccode class=\"literal\"\u003e16 GB\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies whether the selected option should be turned on or off. You can write \u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eON\u003c/code\u003e, or \u003ccode class=\"literal\"\u003e1\u003c/code\u003e to enable the option, and \u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eOFF\u003c/code\u003e, or \u003ccode class=\"literal\"\u003e0\u003c/code\u003e to disable it. The \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e value can also be omitted, in which case \u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e is assumed.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esize\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies an amount of memory in kilobytes. Sizes may also be specified as a string containing the numerical size followed by any one of the following memory units: \u003ccode class=\"literal\"\u003eB\u003c/code\u003e (bytes), \u003ccode class=\"literal\"\u003ekB\u003c/code\u003e (kilobytes), \u003ccode class=\"literal\"\u003eMB\u003c/code\u003e (megabytes), \u003ccode class=\"literal\"\u003eGB\u003c/code\u003e (gigabytes), or \u003ccode class=\"literal\"\u003eTB\u003c/code\u003e (terabytes).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (possibly schema-qualified) of a specific table to analyze. If omitted, all regular tables, partitioned tables, and materialized views in the current database are analyzed (but not foreign tables). If \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e is specified before the table name, only that table is analyzed. If \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e is not specified, the table and all its inheritance child tables or partitions (if any) are analyzed. Optionally, \u003ccode class=\"literal\"\u003e*\u003c/code\u003e can be specified after the table name to explicitly indicate that inheritance child tables (or partitions) are to be analyzed.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of a specific column to analyze. Defaults to all columns.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eWhen \u003ccode class=\"literal\"\u003eVERBOSE\u003c/code\u003e is specified, \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well.\u003c/p\u003e","key":"outputs","title":"Outputs"},{"html":"\u003cp\u003eTo analyze a table, one must ordinarily have the \u003ccode class=\"literal\"\u003eMAINTAIN\u003c/code\u003e privilege on the table. However, database owners are allowed to analyze all tables in their databases, except shared catalogs. \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e will skip over any tables that the calling user does not have permission to analyze.\u003c/p\u003e\u003cp\u003eForeign tables are analyzed only when explicitly selected. Not all foreign data wrappers support \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e. If the table's wrapper does not support \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e, the command prints a warning and does nothing.\u003c/p\u003e\u003cp\u003eIn the default \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e configuration, the autovacuum daemon (see \u003ca href=\"/docs/18/routine-vacuuming.html#AUTOVACUUM\" title=\"24.1.6. The Autovacuum Daemon\"\u003eSection 24.1.6\u003c/a\u003e) takes care of automatic analyzing of tables when they are first loaded with data, and as they change throughout regular operation. When autovacuum is disabled, it is a good idea to run \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e 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 for read-mostly databases is to run \u003ca href=\"/docs/18/sql-vacuum.html\" title=\"VACUUM\"\u003e\u003ccode class=\"command\"\u003eVACUUM\u003c/code\u003e\u003c/a\u003e and \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e once a day during a low-usage time of day. (This will not be sufficient if there is heavy update activity.)\u003c/p\u003e\u003cp\u003eWhile \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e is running, the \u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\"\u003esearch_path\u003c/a\u003e is temporarily changed to \u003ccode class=\"literal\"\u003epg_catalog, pg_temp\u003c/code\u003e.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e requires only a read lock on the target table, so it can run in parallel with other non-DDL activity on the table.\u003c/p\u003e\u003cp\u003eThe statistics collected by \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e 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 can be omitted if \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e 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 \u003ca href=\"/docs/18/maintenance.html\" title=\"Chapter 24. Routine Database Maintenance Tasks\"\u003eChapter 24\u003c/a\u003e.\u003c/p\u003e\u003cp\u003eFor large tables, \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e 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 \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e is run, even if the actual table contents did not change. This might result in small changes in the planner's estimated costs shown by \u003ca href=\"/docs/18/sql-explain.html\" title=\"EXPLAIN\"\u003e\u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e\u003c/a\u003e. In rare situations, this non-determinism will cause the planner's choices of query plans to change after \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e is run. To avoid this, raise the amount of statistics collected by \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e, as described below.\u003c/p\u003e\u003cp\u003eThe extent of analysis can be controlled by adjusting the \u003ca href=\"/docs/18/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET\"\u003edefault_statistics_target\u003c/a\u003e configuration variable, or on a column-by-column basis by setting the per-column statistics target with \u003ca href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\"\u003e\u003ccode class=\"command\"\u003eALTER TABLE ... ALTER COLUMN ... SET STATISTICS\u003c/code\u003e\u003c/a\u003e. 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 100, but this can be adjusted up or down to trade off accuracy of planner estimates against the time taken for \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e and the amount of space occupied in \u003ccode class=\"literal\"\u003epg_statistic\u003c/code\u003e. In particular, setting the statistics target to zero disables collection of statistics for that column. It might be useful to do that for columns that are never used as part of the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eGROUP BY\u003c/code\u003e, or \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e clauses of queries, since the planner will have no use for statistics on such columns.\u003c/p\u003e\u003cp\u003eThe 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 \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eOne of the values estimated by \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e is the number of distinct values that appear in each column. Because only a subset of the rows are examined, this estimate can sometimes be quite inaccurate, even with the largest possible statistics target. If this inaccuracy leads to bad query plans, a more accurate value can be determined manually and then installed with \u003ca href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\"\u003e\u003ccode class=\"command\"\u003eALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...)\u003c/code\u003e\u003c/a\u003e.\u003c/p\u003e\u003cp\u003eIf the table being analyzed has inheritance children, \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e gathers two sets of statistics: one on the rows of the parent table only, and a second including rows of both the parent table and all of its children. This second set of statistics is needed when planning queries that process the inheritance tree as a whole. The autovacuum daemon, however, will only consider inserts or updates on the parent table itself when deciding whether to trigger an automatic analyze for that table. If that table is rarely inserted into or updated, the inheritance statistics will not be up to date unless you run \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e manually. By default, \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e will also recursively collect and update the statistics for each inheritance child table. The \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e keyword may be used to disable this.\u003c/p\u003e\u003cp\u003eFor partitioned tables, \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e gathers statistics by sampling rows from all partitions. By default, \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e will also recursively collect and update the statistics for each partition. The \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e keyword may be used to disable this.\u003c/p\u003e\u003cp\u003eThe autovacuum daemon does not process partitioned tables, nor does it process inheritance parents if only the children are ever modified. It is usually necessary to periodically run a manual \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e to keep the statistics of the table hierarchy up to date.\u003c/p\u003e\u003cp\u003eIf any child tables or partitions are foreign tables whose foreign data wrappers do not support \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e, those tables are ignored while gathering inheritance statistics.\u003c/p\u003e\u003cp\u003eIf the table being analyzed is completely empty, \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e will not record new statistics for that table. Any existing statistics will be retained.\u003c/p\u003e\u003cp\u003eEach backend running \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e will report its progress in the \u003ccode class=\"structname\"\u003epg_stat_progress_analyze\u003c/code\u003e view. See \u003ca href=\"/docs/18/progress-reporting.html#ANALYZE-PROGRESS-REPORTING\" title=\"27.4.1. ANALYZE Progress Reporting\"\u003eSection 27.4.1\u003c/a\u003e for details.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eThere is no \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e statement in the SQL standard.\u003c/p\u003e\u003cp\u003eThe following syntax was used before \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e version 11 and is still supported:\u003c/p\u003e\u003cpre class=\"synopsis\"\u003eANALYZE [ VERBOSE ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e [, ...] ]\n\u003c/pre\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/vacuum/?v=18\" title=\"VACUUM\"\u003e\u003cspan class=\"refentrytitle\"\u003eVACUUM\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/app-vacuumdb.html\" title=\"vacuumdb\"\u003e\u003cspan class=\"refentrytitle\"\u003e\u003cspan class=\"application\"\u003evacuumdb\u003c/span\u003e\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/runtime-config-vacuum.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST\" title=\"19.10.2. Cost-based Vacuum Delay\"\u003eSection 19.10.2\u003c/a\u003e, \u003ca href=\"/docs/18/routine-vacuuming.html#AUTOVACUUM\" title=\"24.1.6. The Autovacuum Daemon\"\u003eSection 24.1.6\u003c/a\u003e, \u003ca href=\"/docs/18/progress-reporting.html#ANALYZE-PROGRESS-REPORTING\" title=\"27.4.1. ANALYZE Progress Reporting\"\u003eSection 27.4.1\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"ANALYZE [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] ) ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e [, ...] ]\n\n\u003cspan class=\"phrase\"\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e can be one of:\u003c/span\u003e\n\n    VERBOSE [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    SKIP_LOCKED [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    BUFFER_USAGE_LIMIT \u003cem class=\"replaceable\"\u003e\u003ccode\u003esize\u003c/code\u003e\u003c/em\u003e\n\n\u003cspan class=\"phrase\"\u003eand \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e is:\u003c/span\u003e\n\n    [ ONLY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ * ] [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) ]","synopsis_text":"ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]\n\nwhere option can be one of:\n\nVERBOSE [ boolean ]\nSKIP_LOCKED [ boolean ]\nBUFFER_USAGE_LIMIT size\nand table_and_columns is:\n\n[ ONLY ] table_name [ * ] [ ( column_name [, ...] ) ]"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"analyze","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"ANALYZE","Summary":"收集数据库的统计信息","BodyHTML":"\u003cpre\u003eANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]\n\n其中option可以是下列之一：\n\nVERBOSE [ boolean ]\nSKIP_LOCKED [ boolean ]\nBUFFER_USAGE_LIMIT size\n其中table_and_columns是：\n\n[ ONLY ] table_name [ * ] [ ( column_name [, ...] ) ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eANALYZE\u003c/code\u003e收集数据库中各表内容的统计信息，并将结果存储到\u003ca href=\"/docs/18/catalog-pg-statistic.html\" rel=\"nofollow\"\u003e\u003ccode\u003epg_statistic\u003c/code\u003e\u003c/a\u003e 系统目录中。随后，查询规划器会使用这些统计信息来帮助确定查询的最高效执行计划。\u003c/p\u003e\u003cp\u003e如果没有给出\u003cem\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e 列表，\u003ccode\u003eANALYZE\u003c/code\u003e会处理当前数据库中当前用户有权分析的每个表和物化视图。如果给出了列表，\u003ccode\u003eANALYZE\u003c/code\u003e则只处理其中列出的表。还可以为某个表给出列名列表，这时只会收集这些列的统计信息。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eVERBOSE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e启用 \u003ccode\u003eINFO\u003c/code\u003e 级别的进度消息显示。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSKIP_LOCKED\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定\u003ccode\u003eANALYZE\u003c/code\u003e在开始处理某个关系时，不要等待任何冲突锁被释放：如果无法在不等待的情况下立即锁定某个关系，就跳过该关系。请注意，即使使用了此选项，\u003ccode\u003eANALYZE\u003c/code\u003e在打开该关系的索引时，或者在从分区、继承子表以及某些类型的外部表获取样本行时，仍然可能发生阻塞。另外，虽然\u003ccode\u003eANALYZE\u003c/code\u003e通常会处理指定分区表的所有分区，但如果该分区表上存在冲突锁，此选项会导致\u003ccode\u003eANALYZE\u003c/code\u003e跳过全部分区。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eBUFFER_USAGE_LIMIT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定\u003ccode\u003eANALYZE\u003c/code\u003e所使用的\u003ca href=\"/docs/18/glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY\" rel=\"nofollow\"\u003e\u003c/a\u003e\u003ca href=\"/docs/18/glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY\" title=\"缓冲区访问策略\" rel=\"nofollow\"\u003e缓冲区访问策略（Buffer Access Strategy）\u003c/a\u003e环形缓冲区大小。该大小用于计算在此策略中会被重用的共享缓冲区数量。\u003ccode\u003e0\u003c/code\u003e表示禁用\u003ccode\u003e缓冲区访问策略\u003c/code\u003e。如果未指定此选项，\u003ccode\u003eANALYZE\u003c/code\u003e会使用\u003ca href=\"/docs/18/runtime-config-resource.html#GUC-VACUUM-BUFFER-USAGE-LIMIT\" rel=\"nofollow\"\u003evacuum_buffer_usage_limit\u003c/a\u003e中的值。更高的设置可以让 \u003ccode\u003eANALYZE\u003c/code\u003e运行得更快，但设置过大可能会把过多其他有用页面从共享缓冲区中逐出。最小值是\u003ccode\u003e128 kB\u003c/code\u003e，最大值是\u003ccode\u003e16 GB\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定所选选项是否开启。可以写\u003ccode\u003eTRUE\u003c/code\u003e、\u003ccode\u003eON\u003c/code\u003e或\u003ccode\u003e1\u003c/code\u003e来启用选项，写\u003ccode\u003eFALSE\u003c/code\u003e、\u003ccode\u003eOFF\u003c/code\u003e或\u003ccode\u003e0\u003c/code\u003e来禁用它。也可以省略\u003cem\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e值，此时假定为\u003ccode\u003eTRUE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003esize\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定以千字节为单位的内存大小。也可以把大小写成一个字符串，即数值后跟下列任一种内存单位：\u003ccode\u003eB\u003c/code\u003e（字节）、\u003ccode\u003ekB\u003c/code\u003e（千字节）、\u003ccode\u003eMB\u003c/code\u003e（兆字节）、\u003ccode\u003eGB\u003c/code\u003e（吉字节）或\u003ccode\u003eTB\u003c/code\u003e（太字节）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要分析的特定表的名称（可以用模式限定）。如果省略，则会分析当前数据库中的所有普通表、分区表和物化视图（但不包括外部表）。如果在表名前指定\u003ccode\u003eONLY\u003c/code\u003e，则只分析该表。如果未指定\u003ccode\u003eONLY\u003c/code\u003e，则会分析该表及其所有继承子表或分区（如果有）。还可以在表名之后指定\u003ccode\u003e*\u003c/code\u003e，以显式指明要分析继承子表（或分区）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要分析的特定列的名称。默认为所有列。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e输出\u003c/h2\u003e\u003cp\u003e指定\u003ccode\u003eVERBOSE\u003c/code\u003e时，\u003ccode\u003eANALYZE\u003c/code\u003e会输出进度消息，指示当前正在处理哪个表，同时还会打印这些表的各种统计信息。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e要分析一个表，通常必须拥有该表上的\u003ccode\u003eMAINTAIN\u003c/code\u003e权限。不过，数据库拥有者可以分析其数据库中的所有表，但共享系统目录除外。\u003ccode\u003eANALYZE\u003c/code\u003e会跳过调用用户无权分析的任何表。\u003c/p\u003e\u003cp\u003e只有在显式选中时才会分析外部表。并非所有外部数据包装器都支持\u003ccode\u003eANALYZE\u003c/code\u003e。如果该表的包装器不支持\u003ccode\u003eANALYZE\u003c/code\u003e，命令会打印一条警告且不执行任何操作。\u003c/p\u003e\u003cp\u003e在默认的\u003cspan\u003ePostgreSQL\u003c/span\u003e配置中，自动清理守护进程（见\u003ca href=\"/docs/18/routine-vacuuming.html#AUTOVACUUM\" rel=\"nofollow\"\u003e第 24.1.6 节\u003c/a\u003e）会在表首次装载数据时，以及在常规运行过程中数据发生变化时，自动分析这些表。当自动清理被禁用时，最好定期运行\u003ccode\u003eANALYZE\u003c/code\u003e，或者在对表内容做出重大更改之后立即运行。准确的统计信息有助于规划器选择最合适的查询计划，从而提高查询处理速度。对于以读取为主的数据库，一个常见策略是在每天使用率较低的时段运行一次 \u003ca href=\"/docs/18/sql-vacuum.html\" title=\"VACUUM\" rel=\"nofollow\"\u003e\u003ccode\u003eVACUUM\u003c/code\u003e\u003c/a\u003e和\u003ccode\u003eANALYZE\u003c/code\u003e。（如果更新活动很频繁，这样做仍然不够。）\u003c/p\u003e\u003cp\u003e当\u003ccode\u003eANALYZE\u003c/code\u003e正在运行时，\u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\" rel=\"nofollow\"\u003esearch_path\u003c/a\u003e会被临时改为 \u003ccode\u003epg_catalog, pg_temp\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eANALYZE\u003c/code\u003e在目标表上只需要获取读锁，因此可以与该表上的其他非 DDL 活动并行运行。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eANALYZE\u003c/code\u003e收集的统计信息通常包括每列中某些高频值的列表，以及显示每列大致数据分布的直方图。如果\u003ccode\u003eANALYZE\u003c/code\u003e认为它们没有意义（例如，唯一键列中没有高频值），或者列的数据类型不支持相应的操作符，则可能省略其中一项或两项。更多统计信息见\u003ca href=\"/docs/18/maintenance.html\" rel=\"nofollow\"\u003e第 24 章\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e对于大型表，\u003ccode\u003eANALYZE\u003c/code\u003e会对表内容进行随机采样，而不是检查每一行。这使得即使是很大的表，也能在较短时间内完成分析。不过要注意，这些统计信息只是近似值，并且即使实际表内容没有变化，每次运行\u003ccode\u003eANALYZE\u003c/code\u003e时统计信息也会略有变化。这可能导致\u003ca href=\"/docs/18/sql-explain.html\" title=\"EXPLAIN\" rel=\"nofollow\"\u003e\u003ccode\u003eEXPLAIN\u003c/code\u003e\u003c/a\u003e中显示的规划器估算代价略有变化。在少数情况下，这种非确定性会导致规划器在运行\u003ccode\u003eANALYZE\u003c/code\u003e之后改用不同的查询计划。要避免这种情况，可以按下文所述提高\u003ccode\u003eANALYZE\u003c/code\u003e收集的统计信息量。\u003c/p\u003e\u003cp\u003e可以通过调整配置变量\u003ca href=\"/docs/18/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET\" rel=\"nofollow\"\u003edefault_statistics_target\u003c/a\u003e来控制分析程度，也可以针对单个列使用\u003ca href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\" rel=\"nofollow\"\u003e\u003ccode\u003eALTER TABLE ... ALTER COLUMN ... SET STATISTICS\u003c/code\u003e\u003c/a\u003e设置每列的统计信息目标。目标值会设置高频值列表中的最大条目数，以及直方图中的最大桶数。默认目标值是 100，但可以把它调高或调低，以在规划器估算精度、\u003ccode\u003eANALYZE\u003c/code\u003e耗费的时间以及\u003ccode\u003epg_statistic\u003c/code\u003e占用的空间之间作出权衡。特别地，将统计信息目标设置为零会禁用该列的统计信息收集。对于那些从不出现在查询 \u003ccode\u003eWHERE\u003c/code\u003e、\u003ccode\u003eGROUP BY\u003c/code\u003e或\u003ccode\u003eORDER BY\u003c/code\u003e子句中的列，这样做可能很有用，因为规划器不会用到这些列的统计信息。\u003c/p\u003e\u003cp\u003e被分析列中最大的统计信息目标决定了为了生成统计信息而需要采样的表行数。增加该目标会导致执行\u003ccode\u003eANALYZE\u003c/code\u003e所需的时间和空间按比例增加。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eANALYZE\u003c/code\u003e估算的值之一是每一列中出现的非重复值数量。由于只检查了部分行，即使使用可能的最大统计信息目标，这种估计有时也可能相当不精确。如果这种不精确导致查询计划不佳，就可以手工确定一个更精确的值，然后用 \u003ca href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\" rel=\"nofollow\"\u003e\u003ccode\u003eALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...)\u003c/code\u003e\u003c/a\u003e 把该值设置进去。\u003c/p\u003e\u003cp\u003e如果被分析的表有继承子表，\u003ccode\u003eANALYZE\u003c/code\u003e会收集两组统计信息：一组只针对父表中的行，另一组则同时包含父表及其所有子表中的行。规划处理整个继承树的查询时，需要第二组统计信息。不过，自动清理守护进程在决定是否为该表触发自动分析时，只会考虑对父表本身的插入或更新。如果该表很少被插入或更新，那么除非手工运行 \u003ccode\u003eANALYZE\u003c/code\u003e，否则继承统计信息就不会保持最新。默认情况下，\u003ccode\u003eANALYZE\u003c/code\u003e还会递归地收集并更新每个继承子表的统计信息。可以使用\u003ccode\u003eONLY\u003c/code\u003e关键字来禁用这一行为。\u003c/p\u003e\u003cp\u003e对于分区表，\u003ccode\u003eANALYZE\u003c/code\u003e会通过对所有分区中的行进行采样来收集统计信息。默认情况下，\u003ccode\u003eANALYZE\u003c/code\u003e还会递归地收集并更新每个分区的统计信息。可以使用\u003ccode\u003eONLY\u003c/code\u003e关键字来禁用这一行为。\u003c/p\u003e\u003cp\u003e自动清理守护进程不会处理分区表；如果继承体系中只有子表被修改，它也不会处理继承父表。因此，通常需要定期手工运行\u003ccode\u003eANALYZE\u003c/code\u003e，以保持表层次结构的统计信息为最新状态。\u003c/p\u003e\u003cp\u003e如果某些子表或分区是外部表，而其外部数据包装器不支持\u003ccode\u003eANALYZE\u003c/code\u003e，那么在收集继承统计信息时会忽略这些表。\u003c/p\u003e\u003cp\u003e如果被分析的表完全为空，\u003ccode\u003eANALYZE\u003c/code\u003e将不会为该表记录新的统计信息。任何现有统计信息都会被保留。\u003c/p\u003e\u003cp\u003e每个运行\u003ccode\u003eANALYZE\u003c/code\u003e的后端都会在 \u003ccode\u003epg_stat_progress_analyze\u003c/code\u003e视图中报告其进度。详见\u003ca href=\"/docs/18/progress-reporting.html#ANALYZE-PROGRESS-REPORTING\" rel=\"nofollow\"\u003e第 27.4.1 节\u003c/a\u003e。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003eSQL 标准中没有\u003ccode\u003eANALYZE\u003c/code\u003e语句。\u003c/p\u003e\u003cp\u003e以下语法在 \u003cspan\u003ePostgreSQL\u003c/span\u003e 11 之前的版本中使用过，当前仍然受支持：\u003c/p\u003e\u003cpre\u003eANALYZE [ VERBOSE ] [ \u003cem\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e [, ...] ]\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/vacuum/?v=18\" title=\"VACUUM\" rel=\"nofollow\"\u003e\u003cspan\u003eVACUUM\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/app-vacuumdb.html\" title=\"vacuumdb\" rel=\"nofollow\"\u003e\u003cspan\u003e\u003cspan\u003evacuumdb\u003c/span\u003e\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/runtime-config-vacuum.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST\" rel=\"nofollow\"\u003e第 19.10.2 节\u003c/a\u003e, \u003ca href=\"/docs/18/routine-vacuuming.html#AUTOVACUUM\" rel=\"nofollow\"\u003e第 24.1.6 节\u003c/a\u003e, \u003ca href=\"/docs/18/progress-reporting.html#ANALYZE-PROGRESS-REPORTING\" rel=\"nofollow\"\u003e第 27.4.1 节\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"9be6e3bb0bdfd55f02fac3b29ab8ee7522851e7cbdaac41d0e63de15532aaab6","Payload":{"purpose_zh":"收集数据库的统计信息","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e收集数据库中各表内容的统计信息，并将结果存储到\u003ca href=\"/docs/18/catalog-pg-statistic.html\" title=\"52.51. pg_statistic\"\u003e\u003ccode class=\"structname\"\u003epg_statistic\u003c/code\u003e\u003c/a\u003e 系统目录中。随后，查询规划器会使用这些统计信息来帮助确定查询的最高效执行计划。\u003c/p\u003e\u003cp\u003e如果没有给出\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e 列表，\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e会处理当前数据库中当前用户有权分析的每个表和物化视图。如果给出了列表，\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e则只处理其中列出的表。还可以为某个表给出列名列表，这时只会收集这些列的统计信息。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eVERBOSE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e启用 \u003ccode class=\"literal\"\u003eINFO\u003c/code\u003e 级别的进度消息显示。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSKIP_LOCKED\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e在开始处理某个关系时，不要等待任何冲突锁被释放：如果无法在不等待的情况下立即锁定某个关系，就跳过该关系。请注意，即使使用了此选项，\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e在打开该关系的索引时，或者在从分区、继承子表以及某些类型的外部表获取样本行时，仍然可能发生阻塞。另外，虽然\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e通常会处理指定分区表的所有分区，但如果该分区表上存在冲突锁，此选项会导致\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e跳过全部分区。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eBUFFER_USAGE_LIMIT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e所使用的\u003ca href=\"/docs/18/glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY\"\u003e\u003c/a\u003e\u003ca href=\"/docs/18/glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY\" title=\"缓冲区访问策略\"\u003e缓冲区访问策略（Buffer Access Strategy）\u003c/a\u003e环形缓冲区大小。该大小用于计算在此策略中会被重用的共享缓冲区数量。\u003ccode class=\"literal\"\u003e0\u003c/code\u003e表示禁用\u003ccode class=\"literal\"\u003e缓冲区访问策略\u003c/code\u003e。如果未指定此选项，\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e会使用\u003ca href=\"/docs/18/runtime-config-resource.html#GUC-VACUUM-BUFFER-USAGE-LIMIT\"\u003evacuum_buffer_usage_limit\u003c/a\u003e中的值。更高的设置可以让 \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e运行得更快，但设置过大可能会把过多其他有用页面从共享缓冲区中逐出。最小值是\u003ccode class=\"literal\"\u003e128 kB\u003c/code\u003e，最大值是\u003ccode class=\"literal\"\u003e16 GB\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定所选选项是否开启。可以写\u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eON\u003c/code\u003e或\u003ccode class=\"literal\"\u003e1\u003c/code\u003e来启用选项，写\u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eOFF\u003c/code\u003e或\u003ccode class=\"literal\"\u003e0\u003c/code\u003e来禁用它。也可以省略\u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e值，此时假定为\u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esize\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定以千字节为单位的内存大小。也可以把大小写成一个字符串，即数值后跟下列任一种内存单位：\u003ccode class=\"literal\"\u003eB\u003c/code\u003e（字节）、\u003ccode class=\"literal\"\u003ekB\u003c/code\u003e（千字节）、\u003ccode class=\"literal\"\u003eMB\u003c/code\u003e（兆字节）、\u003ccode class=\"literal\"\u003eGB\u003c/code\u003e（吉字节）或\u003ccode class=\"literal\"\u003eTB\u003c/code\u003e（太字节）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要分析的特定表的名称（可以用模式限定）。如果省略，则会分析当前数据库中的所有普通表、分区表和物化视图（但不包括外部表）。如果在表名前指定\u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e，则只分析该表。如果未指定\u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e，则会分析该表及其所有继承子表或分区（如果有）。还可以在表名之后指定\u003ccode class=\"literal\"\u003e*\u003c/code\u003e，以显式指明要分析继承子表（或分区）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要分析的特定列的名称。默认为所有列。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e指定\u003ccode class=\"literal\"\u003eVERBOSE\u003c/code\u003e时，\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e会输出进度消息，指示当前正在处理哪个表，同时还会打印这些表的各种统计信息。\u003c/p\u003e","key":"outputs","title":"输出"},{"html":"\u003cp\u003e要分析一个表，通常必须拥有该表上的\u003ccode class=\"literal\"\u003eMAINTAIN\u003c/code\u003e权限。不过，数据库拥有者可以分析其数据库中的所有表，但共享系统目录除外。\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e会跳过调用用户无权分析的任何表。\u003c/p\u003e\u003cp\u003e只有在显式选中时才会分析外部表。并非所有外部数据包装器都支持\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e。如果该表的包装器不支持\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e，命令会打印一条警告且不执行任何操作。\u003c/p\u003e\u003cp\u003e在默认的\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e配置中，自动清理守护进程（见\u003ca href=\"/docs/18/routine-vacuuming.html#AUTOVACUUM\" title=\"24.1.6. 自动清理守护进程\"\u003e第 24.1.6 节\u003c/a\u003e）会在表首次装载数据时，以及在常规运行过程中数据发生变化时，自动分析这些表。当自动清理被禁用时，最好定期运行\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e，或者在对表内容做出重大更改之后立即运行。准确的统计信息有助于规划器选择最合适的查询计划，从而提高查询处理速度。对于以读取为主的数据库，一个常见策略是在每天使用率较低的时段运行一次 \u003ca href=\"/docs/18/sql-vacuum.html\" title=\"VACUUM\"\u003e\u003ccode class=\"command\"\u003eVACUUM\u003c/code\u003e\u003c/a\u003e和\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e。（如果更新活动很频繁，这样做仍然不够。）\u003c/p\u003e\u003cp\u003e当\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e正在运行时，\u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\"\u003esearch_path\u003c/a\u003e会被临时改为 \u003ccode class=\"literal\"\u003epg_catalog, pg_temp\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e在目标表上只需要获取读锁，因此可以与该表上的其他非 DDL 活动并行运行。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e收集的统计信息通常包括每列中某些高频值的列表，以及显示每列大致数据分布的直方图。如果\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e认为它们没有意义（例如，唯一键列中没有高频值），或者列的数据类型不支持相应的操作符，则可能省略其中一项或两项。更多统计信息见\u003ca href=\"/docs/18/maintenance.html\" title=\"第 24 章 日常数据库维护任务\"\u003e第 24 章\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e对于大型表，\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e会对表内容进行随机采样，而不是检查每一行。这使得即使是很大的表，也能在较短时间内完成分析。不过要注意，这些统计信息只是近似值，并且即使实际表内容没有变化，每次运行\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e时统计信息也会略有变化。这可能导致\u003ca href=\"/docs/18/sql-explain.html\" title=\"EXPLAIN\"\u003e\u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e\u003c/a\u003e中显示的规划器估算代价略有变化。在少数情况下，这种非确定性会导致规划器在运行\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e之后改用不同的查询计划。要避免这种情况，可以按下文所述提高\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e收集的统计信息量。\u003c/p\u003e\u003cp\u003e可以通过调整配置变量\u003ca href=\"/docs/18/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET\"\u003edefault_statistics_target\u003c/a\u003e来控制分析程度，也可以针对单个列使用\u003ca href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\"\u003e\u003ccode class=\"command\"\u003eALTER TABLE ... ALTER COLUMN ... SET STATISTICS\u003c/code\u003e\u003c/a\u003e设置每列的统计信息目标。目标值会设置高频值列表中的最大条目数，以及直方图中的最大桶数。默认目标值是 100，但可以把它调高或调低，以在规划器估算精度、\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e耗费的时间以及\u003ccode class=\"literal\"\u003epg_statistic\u003c/code\u003e占用的空间之间作出权衡。特别地，将统计信息目标设置为零会禁用该列的统计信息收集。对于那些从不出现在查询 \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eGROUP BY\u003c/code\u003e或\u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e子句中的列，这样做可能很有用，因为规划器不会用到这些列的统计信息。\u003c/p\u003e\u003cp\u003e被分析列中最大的统计信息目标决定了为了生成统计信息而需要采样的表行数。增加该目标会导致执行\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e所需的时间和空间按比例增加。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e估算的值之一是每一列中出现的非重复值数量。由于只检查了部分行，即使使用可能的最大统计信息目标，这种估计有时也可能相当不精确。如果这种不精确导致查询计划不佳，就可以手工确定一个更精确的值，然后用 \u003ca href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\"\u003e\u003ccode class=\"command\"\u003eALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...)\u003c/code\u003e\u003c/a\u003e 把该值设置进去。\u003c/p\u003e\u003cp\u003e如果被分析的表有继承子表，\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e会收集两组统计信息：一组只针对父表中的行，另一组则同时包含父表及其所有子表中的行。规划处理整个继承树的查询时，需要第二组统计信息。不过，自动清理守护进程在决定是否为该表触发自动分析时，只会考虑对父表本身的插入或更新。如果该表很少被插入或更新，那么除非手工运行 \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e，否则继承统计信息就不会保持最新。默认情况下，\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e还会递归地收集并更新每个继承子表的统计信息。可以使用\u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e关键字来禁用这一行为。\u003c/p\u003e\u003cp\u003e对于分区表，\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e会通过对所有分区中的行进行采样来收集统计信息。默认情况下，\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e还会递归地收集并更新每个分区的统计信息。可以使用\u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e关键字来禁用这一行为。\u003c/p\u003e\u003cp\u003e自动清理守护进程不会处理分区表；如果继承体系中只有子表被修改，它也不会处理继承父表。因此，通常需要定期手工运行\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e，以保持表层次结构的统计信息为最新状态。\u003c/p\u003e\u003cp\u003e如果某些子表或分区是外部表，而其外部数据包装器不支持\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e，那么在收集继承统计信息时会忽略这些表。\u003c/p\u003e\u003cp\u003e如果被分析的表完全为空，\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e将不会为该表记录新的统计信息。任何现有统计信息都会被保留。\u003c/p\u003e\u003cp\u003e每个运行\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e的后端都会在 \u003ccode class=\"structname\"\u003epg_stat_progress_analyze\u003c/code\u003e视图中报告其进度。详见\u003ca href=\"/docs/18/progress-reporting.html#ANALYZE-PROGRESS-REPORTING\" title=\"27.4.1. ANALYZE 进度报告\"\u003e第 27.4.1 节\u003c/a\u003e。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003eSQL 标准中没有\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e语句。\u003c/p\u003e\u003cp\u003e以下语法在 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 11 之前的版本中使用过，当前仍然受支持：\u003c/p\u003e\u003cpre class=\"synopsis\"\u003eANALYZE [ VERBOSE ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e [, ...] ]\n\u003c/pre\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/vacuum/?v=18\" title=\"VACUUM\"\u003e\u003cspan class=\"refentrytitle\"\u003eVACUUM\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/app-vacuumdb.html\" title=\"vacuumdb\"\u003e\u003cspan class=\"refentrytitle\"\u003e\u003cspan class=\"application\"\u003evacuumdb\u003c/span\u003e\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/runtime-config-vacuum.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST\" title=\"19.10.2. 基于代价的清理延迟\"\u003e第 19.10.2 节\u003c/a\u003e, \u003ca href=\"/docs/18/routine-vacuuming.html#AUTOVACUUM\" title=\"24.1.6. 自动清理守护进程\"\u003e第 24.1.6 节\u003c/a\u003e, \u003ca href=\"/docs/18/progress-reporting.html#ANALYZE-PROGRESS-REPORTING\" title=\"27.4.1. ANALYZE 进度报告\"\u003e第 27.4.1 节\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"ANALYZE [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] ) ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e [, ...] ]\n\n\u003cspan class=\"phrase\"\u003e其中\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e可以是下列之一：\u003c/span\u003e\n\n    VERBOSE [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    SKIP_LOCKED [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    BUFFER_USAGE_LIMIT \u003cem class=\"replaceable\"\u003e\u003ccode\u003esize\u003c/code\u003e\u003c/em\u003e\n\n\u003cspan class=\"phrase\"\u003e其中\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e是：\u003c/span\u003e\n\n    [ ONLY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ * ] [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) ]","synopsis_text":"ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]\n\n其中option可以是下列之一：\n\nVERBOSE [ boolean ]\nSKIP_LOCKED [ boolean ]\nBUFFER_USAGE_LIMIT size\n其中table_and_columns是：\n\n[ ONLY ] table_name [ * ] [ ( column_name [, ...] ) ]"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}