Postgresql 中文操作指南

CREATE STATISTICS

CREATE STATISTICS — 定义扩展统计信息

Synopsis

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

Description

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

CREATE STATISTICS 命令具有两种基本形式。第一种形式允许收集单个表达式的单变量统计信息，提供类似于表达式索引的好处，而没有维护索引的开销。此表单不允许指定统计种类，因为各种统计种类仅指多变量统计。该命令的第二种形式允许收集多个列和/或表达式的多变量统计信息，并选择性地指定要包括的统计种类。此表单还会自动收集列表中包含的任何表达式上的单变量统计信息。

如果给定模式名称（例如 CREATE STATISTICS myschema.mystat …​ ），则统计对象将创建在指定模式中。否则，它将创建在当前模式中。如果给定，则统计对象名称必须不同于同一模式中任何其他统计对象的名称。

Parameters

  • IF NOT EXISTS

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

  • statistics_name

    • 要创建的统计信息对象的名称（可选schema-qualified）。如果省略名称，PostgreSQL 将根据父表的名称和定义的列名和/或表达式选择合适的名称。

  • statistics_kind

    • 在此统计信息对象中要计算的多元统计信息类型。目前支持的类型为 ndistinct ，它启用 n-distinct 统计信息， dependencies ，它启用函数相关统计信息，以及 mcv ，它启用最常见值列表。如果省略此子句，所有受支持的统计信息类型都将包含在统计信息对象中。如果有复杂表达式而非仅是简单列引用的统计信息定义，则会自动构建单变量表达式统计信息。有关详细信息，请参阅 Section 14.2.2 和 Section 76.2 。

  • column_name

    • 由计算的统计信息覆盖的表列的名称。仅在构建多元统计信息时允许这样做。至少要指定两个列名或表达式，它们的顺序并不重要。

  • expression

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

  • table_name

    • 计算统计信息的列所属表的名称（可选schema-qualified）；有关继承和分区处理的说明，请参阅 ANALYZE 。

Notes

您必须是表的拥有者才能创建该表的读取统计信息对象。但是，一旦创建统计信息对象，它便独立于基础表的所有权。

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

计划程序目前未使用扩展统计信息进行表连接计算的选择性估计值。此限制可能会在未来版本的 PostgreSQL 中得到解决。

Examples

使用两个函数相关列创建表 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 得出的。通过此类统计信息，计划程序能认识到这些条件是相关的，并且得出更准确的估计值。

Compatibility

SQL 标准中没有 CREATE STATISTICS 命令。

See Also

ALTER STATISTICS ， DROP STATISTICS