描述

CREATE STATISTICS 將會建立一個新的擴充統計資訊物件，追蹤指定表格、外部表格或實體化檢視表的資料。統計資訊物件將會在目前的資料庫中建立，並且由發出指令的使用者所擁有。

CREATE STATISTICS 指令有兩種基本形式。第一種形式允許收集單一運算式的單變量統計資訊，提供類似於運算式索引的好處，而沒有索引維護的額外負擔。這種形式不允許指定統計資訊種類，因為各種統計資訊種類僅適用於多變量統計資訊。該指令的第二種形式允許收集多個欄位和/或運算式的多變量統計資訊，並可選擇指定要包含哪些統計資訊種類。這種形式也會自動導致在清單中包含的任何運算式上收集單變量統計資訊。

如果給定了綱要名稱（例如，CREATE STATISTICS myschema.mystat ...），則統計資訊物件會在指定的綱要中建立。否則，它會在目前的綱要中建立。如果給定，統計資訊物件的名稱必須與同一綱要中任何其他統計資訊物件的名稱不同。

參數

備註

您必須是表格的所有者才能建立讀取它的統計資訊物件。但是，一旦建立，統計資訊物件的所有權就獨立於底層表格。

運算式統計資訊是基於每個運算式的，並且類似於在運算式上建立索引，不同之處在於它們避免了索引維護的額外負擔。運算式統計資訊會自動為統計資訊物件定義中的每個運算式建立。

擴充統計資訊目前未被規劃器用於表格連接的選擇性估計。此限制可能會在未來版本的 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;

若沒有表達式 (expression) 和 ndistinct 統計資訊，查詢規劃器無法得知表達式中相異值的數量，只能依賴預設估計值。等式和範圍條件會假設有 0.5% 的選擇率 (selectivity)，而表達式中相異值的數量會假設與欄位相同（也就是唯一）。這會導致在前兩個查詢中，低估了列數。此外，查詢規劃器沒有關於表達式之間關係的資訊，因此它假設兩個 WHERE 和 GROUP BY 條件是獨立的，並且將它們的選擇率相乘，從而在聚合查詢中嚴重高估了群組計數。由於缺乏準確的表達式統計資訊，情況更加惡化，迫使查詢規劃器對從欄位 ndistinct 值衍生的表達式使用預設的 ndistinct 估計值。有了這些統計資訊，查詢規劃器就能夠識別條件之間的關聯性，並得出更準確的估計值。

相容性

SQL 標準中沒有 CREATE STATISTICS 指令。

參見