{"Entry":{"collection":"sql","key":"create-statistics","name":"CREATE STATISTICS","aliases":["createstatistics"],"metadata":{"aliases":["createstatistics"],"changed_in":["14","16","19"],"changes":[{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"12"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE STATISTICS [ IF NOT EXISTS ] statistics_name","ON ( expression )","FROM table_name","ON { column_name | ( expression ) }, { column_name | ( expression ) } [, ...]"],"removed":[]},"to":"14"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"15"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]","CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]"],"removed":[]},"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"18"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes"],"removed":[]},"status":"changed","synopsis":{"added":["ON { column_name | ( expression ) }"],"removed":[]},"to":"19"}],"content_hash":"0cb2ea1860f82a8f090232206c4dee49bbae2dd2c131d67e9427f2c5726afb50","editorial":{},"first_version":"10","group":"index","imported_at":"2026-09-30T17:43:36.939338+08:00","last_version":"20","name":"CREATE STATISTICS","object":"STATISTICS","position":1004,"present_in":["10","11","12","13","14","15","16","17","18","19","20"],"purpose":"define extended statistics","purpose_zh":"","related":["alter-statistics","drop-statistics"],"slug":"create-statistics","source_rev":"a709ab85","synopsis":"CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]\nON { column_name | ( expression ) }\nFROM table_name\n\nCREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]\n[ ( statistics_kind [, ... ] ) ]\nON { column_name | ( expression ) }, { column_name | ( expression ) } [, ...]\nFROM table_name","verb":"CREATE"}},"Definition":{"Collection":"sql","Key":"create-statistics","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"create-statistics","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-CREATESTATISTICS","file":"sql-createstatistics.html","lang":"en","name":"CREATE STATISTICS","purpose":"define extended statistics","purpose_zh":"","related":["alter-statistics","drop-statistics"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE STATISTICS\u003c/code\u003e will create a new extended statistics object tracking data about the specified table, foreign table or materialized view. The statistics object will be created in the current database and will be owned by the user issuing the command.\u003c/p\u003e\u003cp\u003eThe \u003ccode class=\"command\"\u003eCREATE STATISTICS\u003c/code\u003e command has two basic forms. The first form allows univariate statistics for a single expression to be collected, providing benefits similar to an expression index without the overhead of index maintenance. This form does not allow the statistics kind to be specified, since the various statistics kinds refer only to multivariate statistics. The second form of the command allows multivariate statistics on multiple columns and/or expressions to be collected, optionally specifying which statistics kinds to include. This form will also automatically cause univariate statistics to be collected on any expressions included in the list.\u003c/p\u003e\u003cp\u003eIf a schema name is given (for example, \u003ccode class=\"literal\"\u003eCREATE STATISTICS myschema.mystat ...\u003c/code\u003e) then the statistics object is created in the specified schema. Otherwise it is created in the current schema. If given, the name of the statistics object must be distinct from the name of any other statistics object in the same schema.\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\"\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eDo not throw an error if a statistics object with the same name already exists. A notice is issued in this case. Note that only the name of the statistics object is considered here, not the details of its definition. Statistics name is required when \u003ccode class=\"literal\"\u003eIF NOT EXISTS\u003c/code\u003e is specified.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estatistics_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of the statistics object to be created. If the name is omitted, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e chooses a suitable name based on the parent table's name and the defined column name(s) and/or expression(s).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estatistics_kind\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA multivariate statistics kind to be computed in this statistics object. Currently supported kinds are \u003ccode class=\"literal\"\u003endistinct\u003c/code\u003e, which enables n-distinct statistics, \u003ccode class=\"literal\"\u003edependencies\u003c/code\u003e, which enables functional dependency statistics, and \u003ccode class=\"literal\"\u003emcv\u003c/code\u003e which enables most-common values lists. If this clause is omitted, all supported statistics kinds are included in the statistics object. Univariate expression statistics are built automatically if the statistics definition includes any complex expressions rather than just simple column references. For more information, see \u003ca href=\"/docs/18/planner-stats.html#PLANNER-STATS-EXTENDED\" title=\"14.2.2. Extended Statistics\"\u003eSection 14.2.2\u003c/a\u003e and \u003ca href=\"/docs/18/multivariate-statistics-examples.html\" title=\"69.2. Multivariate Statistics Examples\"\u003eSection 69.2\u003c/a\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\u003eThe name of a table column to be covered by the computed statistics. This is only allowed when building multivariate statistics. At least two column names or expressions must be specified, and their order is not significant.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression to be covered by the computed statistics. This may be used to build univariate statistics on a single expression, or as part of a list of multiple column names and/or expressions to build multivariate statistics. In the latter case, separate univariate statistics are built automatically for each expression in the list.\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 (optionally schema-qualified) of the table containing the column(s) the statistics are computed on; see \u003ca href=\"/docs/18/sql-analyze.html\" title=\"ANALYZE\"\u003e\u003cspan class=\"refentrytitle\"\u003eANALYZE\u003c/span\u003e\u003c/a\u003e for an explanation of the handling of inheritance and partitions.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eYou must be the owner of a table to create a statistics object reading it. Once created, however, the ownership of the statistics object is independent of the underlying table(s).\u003c/p\u003e\u003cp\u003eExpression statistics are per-expression and are similar to creating an index on the expression, except that they avoid the overhead of index maintenance. Expression statistics are built automatically for each expression in the statistics object definition.\u003c/p\u003e\u003cp\u003eExtended statistics are not currently used by the planner for selectivity estimations made for table joins. This limitation will likely be removed in a future version of \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eCreate table \u003ccode class=\"structname\"\u003et1\u003c/code\u003e with two functionally dependent columns, i.e., knowledge of a value in the first column is sufficient for determining the value in the other column. Then functional dependency statistics are built on those columns:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE TABLE t1 (\n    a   int,\n    b   int\n);\n\nINSERT INTO t1 SELECT i/100, i/500\n                 FROM generate_series(1,1000000) s(i);\n\nANALYZE t1;\n\n-- the number of matching rows will be drastically underestimated:\nEXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);\n\nCREATE STATISTICS s1 (dependencies) ON a, b FROM t1;\n\nANALYZE t1;\n\n-- now the row count estimate is more accurate:\nEXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);\n\u003c/pre\u003e\u003cp\u003eWithout functional-dependency statistics, the planner would assume that the two \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e conditions are independent, and would multiply their selectivities together to arrive at a much-too-small row count estimate. With such statistics, the planner recognizes that the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e conditions are redundant and does not underestimate the row count.\u003c/p\u003e\u003cp\u003eCreate table \u003ccode class=\"structname\"\u003et2\u003c/code\u003e with two perfectly correlated columns (containing identical data), and an MCV list on those columns:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE TABLE t2 (\n    a   int,\n    b   int\n);\n\nINSERT INTO t2 SELECT mod(i,100), mod(i,100)\n                 FROM generate_series(1,1000000) s(i);\n\nCREATE STATISTICS s2 (mcv) ON a, b FROM t2;\n\nANALYZE t2;\n\n-- valid combination (found in MCV)\nEXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);\n\n-- invalid combination (not found in MCV)\nEXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);\n\u003c/pre\u003e\u003cp\u003eThe MCV list gives the planner more detailed information about the specific values that commonly appear in the table, as well as an upper bound on the selectivities of combinations of values that do not appear in the table, allowing it to generate better estimates in both cases.\u003c/p\u003e\u003cp\u003eCreate table \u003ccode class=\"structname\"\u003et3\u003c/code\u003e with a single timestamp column, and run queries using expressions on that column. Without extended statistics, the planner has no information about the data distribution for the expressions, and uses default estimates. The planner also does not realize that the value of the date truncated to the month is fully determined by the value of the date truncated to the day. Then expression and ndistinct statistics are built on those two expressions:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE TABLE t3 (\n    a   timestamp\n);\n\nINSERT INTO t3 SELECT i FROM generate_series('2020-01-01'::timestamp,\n                                             '2020-12-31'::timestamp,\n                                             '1 minute'::interval) s(i);\n\nANALYZE t3;\n\n-- the number of matching rows will be drastically underestimated:\nEXPLAIN ANALYZE SELECT * FROM t3\n  WHERE date_trunc('month', a) = '2020-01-01'::timestamp;\n\nEXPLAIN ANALYZE SELECT * FROM t3\n  WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp\n                                 AND '2020-06-30'::timestamp;\n\nEXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)\n   FROM t3 GROUP BY 1, 2;\n\n-- build ndistinct statistics on the pair of expressions (per-expression\n-- statistics are built automatically)\nCREATE STATISTICS s3 (ndistinct) ON date_trunc('month', a), date_trunc('day', a) FROM t3;\n\nANALYZE t3;\n\n-- now the row count estimates are more accurate:\nEXPLAIN ANALYZE SELECT * FROM t3\n  WHERE date_trunc('month', a) = '2020-01-01'::timestamp;\n\nEXPLAIN ANALYZE SELECT * FROM t3\n  WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp\n                                 AND '2020-06-30'::timestamp;\n\nEXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)\n   FROM t3 GROUP BY 1, 2;\n\u003c/pre\u003e\u003cp\u003eWithout expression and ndistinct statistics, the planner has no information about the number of distinct values for the expressions, and has to rely on default estimates. The equality and range conditions are assumed to have 0.5% selectivity, and the number of distinct values in the expression is assumed to be the same as for the column (i.e. unique). This results in a significant underestimate of the row count in the first two queries. Moreover, the planner has no information about the relationship between the expressions, so it assumes the two \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e and \u003ccode class=\"literal\"\u003eGROUP BY\u003c/code\u003e conditions are independent, and multiplies their selectivities together to arrive at a severe overestimate of the group count in the aggregate query. This is further exacerbated by the lack of accurate statistics for the expressions, forcing the planner to use a default ndistinct estimate for the expression derived from ndistinct for the column. With such statistics, the planner recognizes that the conditions are correlated, and arrives at much more accurate estimates.\u003c/p\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThere is no \u003ccode class=\"command\"\u003eCREATE STATISTICS\u003c/code\u003e command in the SQL standard.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-statistics/?v=18\" title=\"ALTER STATISTICS\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER STATISTICS\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-statistics/?v=18\" title=\"DROP STATISTICS\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP STATISTICS\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CREATE STATISTICS [ [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatistics_name\u003c/code\u003e\u003c/em\u003e ]\n    ON ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e )\n    FROM \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\n\nCREATE STATISTICS [ [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatistics_name\u003c/code\u003e\u003c/em\u003e ]\n    [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatistics_kind\u003c/code\u003e\u003c/em\u003e [, ... ] ) ]\n    ON { \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e | ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e ) }, { \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e | ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e ) } [, ...]\n    FROM \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e","synopsis_text":"CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]\nON ( expression )\nFROM table_name\n\nCREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]\n[ ( statistics_kind [, ... ] ) ]\nON { column_name | ( expression ) }, { column_name | ( expression ) } [, ...]\nFROM table_name"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"create-statistics","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"CREATE STATISTICS","Summary":"定义扩展统计信息","BodyHTML":"\u003cpre\u003eCREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]\nON ( expression )\nFROM table_name\n\nCREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]\n[ ( statistics_kind [, ... ] ) ]\nON { column_name | ( expression ) }, { column_name | ( expression ) } [, ...]\nFROM table_name\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE STATISTICS\u003c/code\u003e 创建一个新的扩展统计对象，用于跟踪指定表、外部表或物化视图的数据。该统计对象会在当前数据库中创建，并由发出该命令的用户拥有。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eCREATE STATISTICS\u003c/code\u003e 命令有两种基本形式。第一种形式允许为单个表达式收集单变量统计信息，其效果类似于表达式索引，但无需承担索引维护开销。这种形式不允许指定统计种类，因为各种统计种类只适用于多元统计信息。第二种形式允许收集多个列和/或表达式上的多元统计信息，并可选地指定要包含哪些统计种类。这种形式还会自动为列表中包含的任意表达式收集单变量统计信息。\u003c/p\u003e\u003cp\u003e如果给定了模式名（比如，\u003ccode\u003eCREATE STATISTICS myschema.mystat ...\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\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果同名统计对象已经存在，则不会抛出错误，而是发出一条提示。注意，这里只考虑统计对象的名称，而不考虑其定义细节。指定 \u003ccode\u003eIF NOT EXISTS\u003c/code\u003e 时，必须提供统计对象名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003estatistics_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的统计对象名称（可选模式限定）。如果省略该名称，\u003cspan\u003ePostgreSQL\u003c/span\u003e 会根据所属表的名称以及定义中的列名和/或表达式自动选择一个合适的名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003estatistics_kind\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e将在该统计对象中计算的多元统计种类。当前支持的种类有：\u003ccode\u003endistinct\u003c/code\u003e，用于启用非重复值统计信息；\u003ccode\u003edependencies\u003c/code\u003e，用于启用函数依赖统计信息；以及 \u003ccode\u003emcv\u003c/code\u003e，用于启用 MCV 列表。如果省略该子句，则统计对象中会包含所有支持的统计种类。如果统计信息定义中包含复杂表达式，而不仅仅是简单的列引用，则会自动构建单变量表达式统计信息。更多信息参见\u003ca href=\"/docs/18/planner-stats.html#PLANNER-STATS-EXTENDED\" rel=\"nofollow\"\u003e第 14.2.2 节\u003c/a\u003e和\u003ca href=\"/docs/18/multivariate-statistics-examples.html\" rel=\"nofollow\"\u003e第 69.2 节\u003c/a\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\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\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包含用于计算统计信息的列的表名称（可选模式限定）；关于继承和分区的处理方式，参见\u003ca href=\"/docs/18/sql-analyze.html\" title=\"ANALYZE\" rel=\"nofollow\"\u003e\u003cspan\u003eANALYZE\u003c/span\u003e\u003c/a\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必须是表的所有者，才能创建读取该表数据的统计对象。不过，一旦创建，统计对象的所有权就独立于底层表。\u003c/p\u003e\u003cp\u003e表达式统计信息是按表达式分别维护的，类似于在该表达式上创建索引，只是它们避免了索引维护开销。对于统计对象定义中的每个表达式，都会自动构建表达式统计信息。\u003c/p\u003e\u003cp\u003e扩展统计信息目前尚未被规划器用于表连接的选择率估计。这个限制很可能会在未来版本的 \u003cspan\u003ePostgreSQL\u003c/span\u003e 中被移除。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e创建表 \u003ccode\u003et1\u003c/code\u003e，其中两列存在函数依赖关系，也就是说，知道第一列中的某个值就足以确定另一列中的值。然后，在这些列上构建函数依赖统计信息：\u003c/p\u003e\u003cpre\u003eCREATE TABLE t1 (\n    a   int,\n    b   int\n);\n\nINSERT INTO t1 SELECT i/100, i/500\n                 FROM generate_series(1,1000000) s(i);\n\nANALYZE t1;\n\n-- 匹配行的数量将被大大低估：\nEXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);\n\nCREATE STATISTICS s1 (dependencies) ON a, b FROM t1;\n\nANALYZE t1;\n\n-- 现在行计数估计会更准确：\nEXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);\n\u003c/pre\u003e\u003cp\u003e如果没有函数依赖统计信息，规划器会认为两个 \u003ccode\u003eWHERE\u003c/code\u003e 条件彼此独立，并将它们的选择率相乘，从而得到远小于实际值的行数估计。有了这类统计信息后，规划器就能识别出 \u003ccode\u003eWHERE\u003c/code\u003e 条件存在冗余，因此不会低估行数。\u003c/p\u003e\u003cp\u003e创建表 \u003ccode\u003et2\u003c/code\u003e，其中两列完全相关（包含相同的数据），并在这些列上构建一个 MCV 列表：\u003c/p\u003e\u003cpre\u003eCREATE TABLE t2 (\n    a   int,\n    b   int\n);\n\nINSERT INTO t2 SELECT mod(i,100), mod(i,100)\n                 FROM generate_series(1,1000000) s(i);\n\nCREATE STATISTICS s2 (mcv) ON a, b FROM t2;\n\nANALYZE t2;\n\n-- 有效组合（在 MCV 列表中）\nEXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);\n\n-- 无效组合（不在 MCV 列表中）\nEXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);\n\u003c/pre\u003e\u003cp\u003eMCV 列表为规划器提供了关于表中经常出现的特定值的更详细信息，同时也给出了表中未出现的值组合选择率的上界，因此在这两种情况下都能生成更好的估计。\u003c/p\u003e\u003cp\u003e创建表 \u003ccode\u003et3\u003c/code\u003e，其中只有一个 timestamp 列，并对该列上的表达式运行查询。如果没有扩展统计信息，规划器无法获知这些表达式的数据分布信息，只能使用默认估计值。规划器也不会意识到，按月截断后的日期值完全由按天截断后的日期值决定。然后在这两个表达式上构建表达式统计信息和 ndistinct 统计信息：\u003c/p\u003e\u003cpre\u003eCREATE TABLE t3 (\n    a   timestamp\n);\n\nINSERT INTO t3 SELECT i FROM generate_series(\u0026#39;2020-01-01\u0026#39;::timestamp,\n                                             \u0026#39;2020-12-31\u0026#39;::timestamp,\n                                             \u0026#39;1 minute\u0026#39;::interval) s(i);\n\nANALYZE t3;\n\n-- 匹配行数会被严重低估：\nEXPLAIN ANALYZE SELECT * FROM t3\n  WHERE date_trunc(\u0026#39;month\u0026#39;, a) = \u0026#39;2020-01-01\u0026#39;::timestamp;\n\nEXPLAIN ANALYZE SELECT * FROM t3\n  WHERE date_trunc(\u0026#39;day\u0026#39;, a) BETWEEN \u0026#39;2020-01-01\u0026#39;::timestamp\n                                 AND \u0026#39;2020-06-30\u0026#39;::timestamp;\n\nEXPLAIN ANALYZE SELECT date_trunc(\u0026#39;month\u0026#39;, a), date_trunc(\u0026#39;day\u0026#39;, a)\n   FROM t3 GROUP BY 1, 2;\n\n-- 在这对表达式上构建 ndistinct 统计（每个表达式的统计\n-- 都会自动构建）\nCREATE STATISTICS s3 (ndistinct) ON date_trunc(\u0026#39;month\u0026#39;, a), date_trunc(\u0026#39;day\u0026#39;, a) FROM t3;\n\nANALYZE t3;\n\n-- 现在行数估计更准确：\nEXPLAIN ANALYZE SELECT * FROM t3\n  WHERE date_trunc(\u0026#39;month\u0026#39;, a) = \u0026#39;2020-01-01\u0026#39;::timestamp;\n\nEXPLAIN ANALYZE SELECT * FROM t3\n  WHERE date_trunc(\u0026#39;day\u0026#39;, a) BETWEEN \u0026#39;2020-01-01\u0026#39;::timestamp\n                                 AND \u0026#39;2020-06-30\u0026#39;::timestamp;\n\nEXPLAIN ANALYZE SELECT date_trunc(\u0026#39;month\u0026#39;, a), date_trunc(\u0026#39;day\u0026#39;, a)\n   FROM t3 GROUP BY 1, 2;\n\u003c/pre\u003e\u003cp\u003e如果没有表达式统计信息和 ndistinct 统计信息，规划器就无法知道这些表达式的非重复值个数，只能依赖默认估计值。等值条件和范围条件都会被假定具有 0.5% 的选择率，而表达式的非重复值个数则被假定与该列相同（也就是唯一）。这会导致前两个查询中的行数被严重低估。此外，规划器没有关于这些表达式之间关系的信息，因此会假定两个 \u003ccode\u003eWHERE\u003c/code\u003e 条件和 \u003ccode\u003eGROUP BY\u003c/code\u003e 条件彼此独立，并将它们的选择率相乘，从而对聚合查询中的分组数量作出严重高估。由于缺乏这些表达式的准确统计信息，情况还会进一步恶化，迫使规划器根据列的 ndistinct 值推导表达式的默认 ndistinct 估计值。有了这些统计信息后，规划器就能识别出这些条件之间存在相关性，并得出准确得多的估计。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003eSQL 标准中没有 \u003ccode\u003eCREATE STATISTICS\u003c/code\u003e 命令。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/alter-statistics/?v=18\" title=\"ALTER STATISTICS\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER STATISTICS\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-statistics/?v=18\" title=\"DROP STATISTICS\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP STATISTICS\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"5af0f37c9ebe810f38def79086f929419a2d2f4f59558f19d4662418516bcb9c","Payload":{"purpose_zh":"定义扩展统计信息","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE STATISTICS\u003c/code\u003e 创建一个新的扩展统计对象，用于跟踪指定表、外部表或物化视图的数据。该统计对象会在当前数据库中创建，并由发出该命令的用户拥有。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE STATISTICS\u003c/code\u003e 命令有两种基本形式。第一种形式允许为单个表达式收集单变量统计信息，其效果类似于表达式索引，但无需承担索引维护开销。这种形式不允许指定统计种类，因为各种统计种类只适用于多元统计信息。第二种形式允许收集多个列和/或表达式上的多元统计信息，并可选地指定要包含哪些统计种类。这种形式还会自动为列表中包含的任意表达式收集单变量统计信息。\u003c/p\u003e\u003cp\u003e如果给定了模式名（比如，\u003ccode class=\"literal\"\u003eCREATE STATISTICS myschema.mystat ...\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\"\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果同名统计对象已经存在，则不会抛出错误，而是发出一条提示。注意，这里只考虑统计对象的名称，而不考虑其定义细节。指定 \u003ccode class=\"literal\"\u003eIF NOT EXISTS\u003c/code\u003e 时，必须提供统计对象名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estatistics_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的统计对象名称（可选模式限定）。如果省略该名称，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 会根据所属表的名称以及定义中的列名和/或表达式自动选择一个合适的名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estatistics_kind\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e将在该统计对象中计算的多元统计种类。当前支持的种类有：\u003ccode class=\"literal\"\u003endistinct\u003c/code\u003e，用于启用非重复值统计信息；\u003ccode class=\"literal\"\u003edependencies\u003c/code\u003e，用于启用函数依赖统计信息；以及 \u003ccode class=\"literal\"\u003emcv\u003c/code\u003e，用于启用 MCV 列表。如果省略该子句，则统计对象中会包含所有支持的统计种类。如果统计信息定义中包含复杂表达式，而不仅仅是简单的列引用，则会自动构建单变量表达式统计信息。更多信息参见\u003ca href=\"/docs/18/planner-stats.html#PLANNER-STATS-EXTENDED\" title=\"14.2.2. 扩展统计信息\"\u003e第 14.2.2 节\u003c/a\u003e和\u003ca href=\"/docs/18/multivariate-statistics-examples.html\" title=\"69.2. 多元统计信息示例\"\u003e第 69.2 节\u003c/a\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\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\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包含用于计算统计信息的列的表名称（可选模式限定）；关于继承和分区的处理方式，参见\u003ca href=\"/docs/18/sql-analyze.html\" title=\"ANALYZE\"\u003e\u003cspan class=\"refentrytitle\"\u003eANALYZE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e必须是表的所有者，才能创建读取该表数据的统计对象。不过，一旦创建，统计对象的所有权就独立于底层表。\u003c/p\u003e\u003cp\u003e表达式统计信息是按表达式分别维护的，类似于在该表达式上创建索引，只是它们避免了索引维护开销。对于统计对象定义中的每个表达式，都会自动构建表达式统计信息。\u003c/p\u003e\u003cp\u003e扩展统计信息目前尚未被规划器用于表连接的选择率估计。这个限制很可能会在未来版本的 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 中被移除。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e创建表 \u003ccode class=\"structname\"\u003et1\u003c/code\u003e，其中两列存在函数依赖关系，也就是说，知道第一列中的某个值就足以确定另一列中的值。然后，在这些列上构建函数依赖统计信息：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE TABLE t1 (\n    a   int,\n    b   int\n);\n\nINSERT INTO t1 SELECT i/100, i/500\n                 FROM generate_series(1,1000000) s(i);\n\nANALYZE t1;\n\n-- 匹配行的数量将被大大低估：\nEXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);\n\nCREATE STATISTICS s1 (dependencies) ON a, b FROM t1;\n\nANALYZE t1;\n\n-- 现在行计数估计会更准确：\nEXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);\n\u003c/pre\u003e\u003cp\u003e如果没有函数依赖统计信息，规划器会认为两个 \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e 条件彼此独立，并将它们的选择率相乘，从而得到远小于实际值的行数估计。有了这类统计信息后，规划器就能识别出 \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e 条件存在冗余，因此不会低估行数。\u003c/p\u003e\u003cp\u003e创建表 \u003ccode class=\"structname\"\u003et2\u003c/code\u003e，其中两列完全相关（包含相同的数据），并在这些列上构建一个 MCV 列表：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE TABLE t2 (\n    a   int,\n    b   int\n);\n\nINSERT INTO t2 SELECT mod(i,100), mod(i,100)\n                 FROM generate_series(1,1000000) s(i);\n\nCREATE STATISTICS s2 (mcv) ON a, b FROM t2;\n\nANALYZE t2;\n\n-- 有效组合（在 MCV 列表中）\nEXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);\n\n-- 无效组合（不在 MCV 列表中）\nEXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);\n\u003c/pre\u003e\u003cp\u003eMCV 列表为规划器提供了关于表中经常出现的特定值的更详细信息，同时也给出了表中未出现的值组合选择率的上界，因此在这两种情况下都能生成更好的估计。\u003c/p\u003e\u003cp\u003e创建表 \u003ccode class=\"structname\"\u003et3\u003c/code\u003e，其中只有一个 timestamp 列，并对该列上的表达式运行查询。如果没有扩展统计信息，规划器无法获知这些表达式的数据分布信息，只能使用默认估计值。规划器也不会意识到，按月截断后的日期值完全由按天截断后的日期值决定。然后在这两个表达式上构建表达式统计信息和 ndistinct 统计信息：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE TABLE t3 (\n    a   timestamp\n);\n\nINSERT INTO t3 SELECT i FROM generate_series('2020-01-01'::timestamp,\n                                             '2020-12-31'::timestamp,\n                                             '1 minute'::interval) s(i);\n\nANALYZE t3;\n\n-- 匹配行数会被严重低估：\nEXPLAIN ANALYZE SELECT * FROM t3\n  WHERE date_trunc('month', a) = '2020-01-01'::timestamp;\n\nEXPLAIN ANALYZE SELECT * FROM t3\n  WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp\n                                 AND '2020-06-30'::timestamp;\n\nEXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)\n   FROM t3 GROUP BY 1, 2;\n\n-- 在这对表达式上构建 ndistinct 统计（每个表达式的统计\n-- 都会自动构建）\nCREATE STATISTICS s3 (ndistinct) ON date_trunc('month', a), date_trunc('day', a) FROM t3;\n\nANALYZE t3;\n\n-- 现在行数估计更准确：\nEXPLAIN ANALYZE SELECT * FROM t3\n  WHERE date_trunc('month', a) = '2020-01-01'::timestamp;\n\nEXPLAIN ANALYZE SELECT * FROM t3\n  WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp\n                                 AND '2020-06-30'::timestamp;\n\nEXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)\n   FROM t3 GROUP BY 1, 2;\n\u003c/pre\u003e\u003cp\u003e如果没有表达式统计信息和 ndistinct 统计信息，规划器就无法知道这些表达式的非重复值个数，只能依赖默认估计值。等值条件和范围条件都会被假定具有 0.5% 的选择率，而表达式的非重复值个数则被假定与该列相同（也就是唯一）。这会导致前两个查询中的行数被严重低估。此外，规划器没有关于这些表达式之间关系的信息，因此会假定两个 \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e 条件和 \u003ccode class=\"literal\"\u003eGROUP BY\u003c/code\u003e 条件彼此独立，并将它们的选择率相乘，从而对聚合查询中的分组数量作出严重高估。由于缺乏这些表达式的准确统计信息，情况还会进一步恶化，迫使规划器根据列的 ndistinct 值推导表达式的默认 ndistinct 估计值。有了这些统计信息后，规划器就能识别出这些条件之间存在相关性，并得出准确得多的估计。\u003c/p\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003eSQL 标准中没有 \u003ccode class=\"command\"\u003eCREATE STATISTICS\u003c/code\u003e 命令。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-statistics/?v=18\" title=\"ALTER STATISTICS\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER STATISTICS\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-statistics/?v=18\" title=\"DROP STATISTICS\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP STATISTICS\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"CREATE STATISTICS [ [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatistics_name\u003c/code\u003e\u003c/em\u003e ]\n    ON ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e )\n    FROM \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\n\nCREATE STATISTICS [ [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatistics_name\u003c/code\u003e\u003c/em\u003e ]\n    [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatistics_kind\u003c/code\u003e\u003c/em\u003e [, ... ] ) ]\n    ON { \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e | ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e ) }, { \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e | ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e ) } [, ...]\n    FROM \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e","synopsis_text":"CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]\nON ( expression )\nFROM table_name\n\nCREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]\n[ ( statistics_kind [, ... ] ) ]\nON { column_name | ( expression ) }, { column_name | ( expression ) } [, ...]\nFROM table_name"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}