CREATE_STATISTICS(7)

1CREATE STATISTICS(7)     PostgreSQL 11.3 Documentation    CREATE STATISTICS(7)
2
3
4

NAME

6       CREATE_STATISTICS - define extended statistics
7

SYNOPSIS

9       CREATE STATISTICS [ IF NOT EXISTS ] statistics_name
10           [ ( statistics_kind [, ... ] ) ]
11           ON column_name, column_name [, ...]
12           FROM table_name
13

DESCRIPTION

15       CREATE STATISTICS will create a new extended statistics object tracking
16       data about the specified table, foreign table or materialized view. The
17       statistics object will be created in the current database and will be
18       owned by the user issuing the command.
19
20       If a schema name is given (for example, CREATE STATISTICS
21       myschema.mystat ...) then the statistics object is created in the
22       specified schema. Otherwise it is created in the current schema. The
23       name of the statistics object must be distinct from the name of any
24       other statistics object in the same schema.
25

PARAMETERS

27       IF NOT EXISTS
28           Do not throw an error if a statistics object with the same name
29           already exists. A notice is issued in this case. Note that only the
30           name of the statistics object is considered here, not the details
31           of its definition.
32
33       statistics_name
34           The name (optionally schema-qualified) of the statistics object to
35           be created.
36
37       statistics_kind
38           A statistics kind to be computed in this statistics object.
39           Currently supported kinds are ndistinct, which enables n-distinct
40           statistics, and dependencies, which enables functional dependency
41           statistics. If this clause is omitted, all supported statistics
42           kinds are included in the statistics object. For more information,
43           see Section 14.2.2 and Section 70.2.
44
45       column_name
46           The name of a table column to be covered by the computed
47           statistics. At least two column names must be given.
48
49       table_name
50           The name (optionally schema-qualified) of the table containing the
51           column(s) the statistics are computed on.
52

NOTES

54       You must be the owner of a table to create a statistics object reading
55       it. Once created, however, the ownership of the statistics object is
56       independent of the underlying table(s).
57

EXAMPLES

59       Create table t1 with two functionally dependent columns, i.e. knowledge
60       of a value in the first column is sufficient for determining the value
61       in the other column. Then functional dependency statistics are built on
62       those columns:
63
64           CREATE TABLE t1 (
65               a   int,
66               b   int
67           );
68
69           INSERT INTO t1 SELECT i/100, i/500
70                            FROM generate_series(1,1000000) s(i);
71
72           ANALYZE t1;
73
74           -- the number of matching rows will be drastically underestimated:
75           EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
76
77           CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
78
79           ANALYZE t1;
80
81           -- now the row count estimate is more accurate:
82           EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
83
84       Without functional-dependency statistics, the planner would assume that
85       the two WHERE conditions are independent, and would multiply their
86       selectivities together to arrive at a much-too-small row count
87       estimate. With such statistics, the planner recognizes that the WHERE
88       conditions are redundant and does not underestimate the row count.
89

COMPATIBILITY

91       There is no CREATE STATISTICS command in the SQL standard.
92