# CREATE STATISTICS

CREATE STATISTICS — 定義延伸統計資訊

## 語法

``````CREATE STATISTICS [ IF NOT EXISTS ] statistics_name
[ ( statistics_kind [, ... ] ) ]
ON column_name, column_name [, ...]
FROM table_name``````

## 說明

CREATE STATISTICS 將建立一個新的延伸統計資訊物件，追踪指定資料表、外部資料表或具體化檢視表的相關數據。統計資訊物件將在目前的資料庫中建立，並由發出此命令的使用者擁有。

## 參數

`IF NOT EXISTS`

`statistics_name`

`statistics_kind`

`column_name`

`table_name`

## 範例

``````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);``````

``````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);