PostgreSQL 教程

Appearance

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 节 和 第 76.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条件是冗余的，并且不会低估行计数。

使用两个完全相关的列（包含相同数据）和一个 MCV 列表创建表t2

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% 的选择性，并且表达式中的不同值数量假定与列相同（即唯一）。这导致前两个查询中的行计数明显低估。此外，规划器对表达式之间的关系没有任何信息，因此它假定两个WHERE和GROUP BY条件是独立的，并将它们的选择性相乘以得出聚合查询中组计数的严重高估。由于缺乏表达式的准确统计信息，这进一步加剧了，迫使规划器对表达式使用从列的 ndistinct 得出的默认 ndistinct 估计。有了这样的统计信息，规划器会识别条件是相关的，并得出更准确的估计。

兼容性 ​

SQL 标准中没有CREATE STATISTICS命令。

另请参阅 ​

ALTER STATISTICS，DROP STATISTICS