2024年9月26日:PostgreSQL 17 发布!
支持的版本:当前 (17) / 16 / 15 / 14 / 13 / 12
开发版本:devel
不支持的版本:11 / 10

CREATE STATISTICS

CREATE STATISTICS — 定义扩展统计信息

语法

CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]
    ON ( expression )
    FROM table_name

CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]
    [ ( statistics_kind [, ... ] ) ]
    ON { column_name | ( expression ) }, { column_name | ( expression ) } [, ...]
    FROM table_name

描述

CREATE STATISTICS 将创建一个新的扩展统计信息对象,用于跟踪指定表、外部表或物化视图的数据。统计信息对象将在当前数据库中创建,并由发出该命令的用户拥有。

CREATE STATISTICS 命令有两种基本形式。第一种形式允许收集单个表达式的单变量统计信息,提供类似于表达式索引的好处,而无需索引维护的开销。此形式不允许指定统计信息类型,因为各种统计信息类型仅引用多变量统计信息。命令的第二种形式允许收集多个列和/或表达式的多变量统计信息,并可以选择指定要包含的统计信息类型。此形式还将自动导致对列表中包含的任何表达式收集单变量统计信息。

如果给出了模式名称(例如,CREATE STATISTICS myschema.mystat ...),则统计信息对象将在指定的模式中创建。否则,它将在当前模式中创建。如果给出,统计信息对象的名称必须与同一模式中任何其他统计信息对象的名称不同。

参数

IF NOT EXISTS

如果已存在具有相同名称的统计信息对象,则不抛出错误。在这种情况下会发出通知。请注意,此处仅考虑统计信息对象的名称,而不考虑其定义的详细信息。当指定 IF NOT EXISTS 时,需要统计信息名称。

statistics_name

要创建的统计信息对象的名称(可选地带模式限定)。如果省略名称,PostgreSQL 将根据父表的名称和定义的列名和/或表达式选择合适的名称。

statistics_kind

在此统计信息对象中要计算的多变量统计信息类型。当前支持的类型是 ndistinct,它启用 n-distinct 统计信息,dependencies,它启用函数依赖性统计信息,以及 mcv,它启用最常见值列表。如果省略此子句,则所有支持的统计信息类型都包含在统计信息对象中。如果统计信息定义包含任何复杂表达式,而不仅仅是简单的列引用,则会自动构建单变量表达式统计信息。有关更多信息,请参见第 14.2.2 节第 68.2 节

column_name

要由计算的统计信息覆盖的表列的名称。这仅在构建多变量统计信息时允许。必须指定至少两个列名或表达式,并且它们的顺序无关紧要。

expression

要由计算的统计信息覆盖的表达式。这可用于构建单个表达式的单变量统计信息,或作为构建多变量统计信息的多个列名和/或表达式列表的一部分。在后一种情况下,会自动为列表中的每个表达式构建单独的单变量统计信息。

table_name

包含计算统计信息的列的表的名称(可选地带模式限定);有关继承和分区的处理说明,请参见ANALYZE

注释

您必须是表的拥有者才能创建读取它的统计信息对象。但是,一旦创建,统计信息对象的拥有权就独立于底层表。

表达式统计信息是每个表达式的,类似于在表达式上创建索引,但避免了索引维护的开销。会自动为统计信息对象定义中的每个表达式构建表达式统计信息。

计划程序当前不使用扩展统计信息来进行表联接的选择性估计。此限制可能会在 PostgreSQL 的未来版本中删除。

示例

创建表 t1,其中包含两列函数依赖列,即,第一列中的值的知识足以确定另一列中的值。然后在这些列上构建函数依赖性统计信息

CREATE TABLE t1 (
    a   int,
    b   int
);

INSERT INTO t1 SELECT i/100, i/500
                 FROM generate_series(1,1000000) s(i);

ANALYZE t1;

-- the number of matching rows will be drastically underestimated:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);

CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;

ANALYZE t1;

-- now the row count estimate is more accurate:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);

如果没有函数依赖性统计信息,计划程序将假设这两个 WHERE 条件是独立的,并将它们的选择性相乘以得出过小的行数估计。使用此类统计信息,计划程序会识别出 WHERE 条件是冗余的,并且不会低估行数。

创建表 t2,其中包含两列完全相关的列(包含相同的数据),以及这些列上的 MCV 列表

CREATE TABLE t2 (
    a   int,
    b   int
);

INSERT INTO t2 SELECT mod(i,100), mod(i,100)
                 FROM generate_series(1,1000000) s(i);

CREATE STATISTICS s2 (mcv) ON a, b FROM t2;

ANALYZE t2;

-- valid combination (found in MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);

-- invalid combination (not found in MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);

MCV 列表为计划程序提供了有关表中常见特定值的更详细信息,以及表中未出现的组合值的选择性的上限,从而使其能够在两种情况下生成更好的估计。

创建表 t3,其中包含单个时间戳列,并运行使用该列上表达式的查询。如果没有扩展统计信息,计划程序将没有有关表达式数据分布的信息,并将使用默认估计。计划程序也不会意识到截断到月份的日期的值完全由截断到日期的日期的值决定。然后在这两个表达式上构建表达式和 ndistinct 统计信息

CREATE TABLE t3 (
    a   timestamp
);

INSERT INTO t3 SELECT i FROM generate_series('2020-01-01'::timestamp,
                                             '2020-12-31'::timestamp,
                                             '1 minute'::interval) s(i);

ANALYZE t3;

-- the number of matching rows will be drastically underestimated:
EXPLAIN ANALYZE SELECT * FROM t3
  WHERE date_trunc('month', a) = '2020-01-01'::timestamp;

EXPLAIN ANALYZE SELECT * FROM t3
  WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
                                 AND '2020-06-30'::timestamp;

EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
   FROM t3 GROUP BY 1, 2;

-- build ndistinct statistics on the pair of expressions (per-expression
-- statistics are built automatically)
CREATE STATISTICS s3 (ndistinct) ON date_trunc('month', a), date_trunc('day', a) FROM t3;

ANALYZE t3;

-- now the row count estimates are more accurate:
EXPLAIN ANALYZE SELECT * FROM t3
  WHERE date_trunc('month', a) = '2020-01-01'::timestamp;

EXPLAIN ANALYZE SELECT * FROM t3
  WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
                                 AND '2020-06-30'::timestamp;

EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
   FROM t3 GROUP BY 1, 2;

如果没有表达式和 ndistinct 统计信息,计划程序将没有有关表达式的不同值的数目的信息,并且必须依赖于默认估计。假定相等和范围条件具有 0.5% 的选择性,并且表达式的不同值的数目假定与列相同(即唯一)。这会导致第一个两个查询中行数的显着低估。此外,计划程序没有有关表达式之间关系的信息,因此它假定两个 WHEREGROUP BY 条件是独立的,并将它们的选择性相乘以得出聚合查询中组计数的严重高估。由于缺乏精确的表达式统计信息,这进一步加剧了这种情况,迫使计划程序使用从列的 ndistinct 派生的表达式的默认 ndistinct 估计。使用此类统计信息,计划程序会识别出条件是相关的,并得出更准确的估计。

兼容性

SQL 标准中没有 CREATE STATISTICS 命令。

提交更正

如果您在文档中看到任何不正确的内容、与您对特定功能的体验不符的内容或需要进一步澄清的内容,请使用此表单 报告文档问题。