1CREATE STATISTICS(7) PostgreSQL 14.3 Documentation CREATE STATISTICS(7)
2
6 CREATE_STATISTICS - define extended statistics
7
9 CREATE STATISTICS [ IF NOT EXISTS ] statistics_name
10 ON ( expression )
11 FROM table_name
12 CREATE STATISTICS [ IF NOT EXISTS ] statistics_name
14 [ ( statistics_kind [, ... ] ) ]
15 ON { column_name | ( expression ) }, { column_name | ( expression ) } [, ...]
16 FROM table_name
17
19 CREATE STATISTICS will create a new extended statistics object tracking
20 data about the specified table, foreign table or materialized view. The
21 statistics object will be created in the current database and will be
22 owned by the user issuing the command.
23 The CREATE STATISTICS command has two basic forms. The first form
25 allows univariate statistics for a single expression to be collected,
26 providing benefits similar to an expression index without the overhead
27 of index maintenance. This form does not allow the statistics kind to
28 be specified, since the various statistics kinds refer only to
29 multivariate statistics. The second form of the command allows
30 multivariate statistics on multiple columns and/or expressions to be
31 collected, optionally specifying which statistics kinds to include.
32 This form will also automatically cause univariate statistics to be
33 collected on any expressions included in the list.
34 If a schema name is given (for example, CREATE STATISTICS
36 myschema.mystat ...) then the statistics object is created in the
37 specified schema. Otherwise it is created in the current schema. The
38 name of the statistics object must be distinct from the name of any
39 other statistics object in the same schema.
40
42 IF NOT EXISTS
43 Do not throw an error if a statistics object with the same name
44 already exists. A notice is issued in this case. Note that only the
45 name of the statistics object is considered here, not the details
46 of its definition.
47 statistics_name
49 The name (optionally schema-qualified) of the statistics object to
50 be created.
51 statistics_kind
53 A multivariate statistics kind to be computed in this statistics
54 object. Currently supported kinds are ndistinct, which enables
55 n-distinct statistics, dependencies, which enables functional
56 dependency statistics, and mcv which enables most-common values
57 lists. If this clause is omitted, all supported statistics kinds
58 are included in the statistics object. Univariate expression
59 statistics are built automatically if the statistics definition
60 includes any complex expressions rather than just simple column
61 references. For more information, see Section 14.2.2 and
62 Section 72.2.
63 column_name
65 The name of a table column to be covered by the computed
66 statistics. This is only allowed when building multivariate
67 statistics. At least two column names or expressions must be
68 specified, and their order is not significant.
69 expression
71 An expression to be covered by the computed statistics. This may be
72 used to build univariate statistics on a single expression, or as
73 part of a list of multiple column names and/or expressions to build
74 multivariate statistics. In the latter case, separate univariate
75 statistics are built automatically for each expression in the list.
76 table_name
78 The name (optionally schema-qualified) of the table containing the
79 column(s) the statistics are computed on.
80
82 You must be the owner of a table to create a statistics object reading
83 it. Once created, however, the ownership of the statistics object is
84 independent of the underlying table(s).
85 Expression statistics are per-expression and are similar to creating an
87 index on the expression, except that they avoid the overhead of index
88 maintenance. Expression statistics are built automatically for each
89 expression in the statistics object definition.
90
92 Create table t1 with two functionally dependent columns, i.e.,
93 knowledge of a value in the first column is sufficient for determining
94 the value in the other column. Then functional dependency statistics
95 are built on those columns:
96 CREATE TABLE t1 (
98 a int,
99 b int
100 );
101 INSERT INTO t1 SELECT i/100, i/500
103 FROM generate_series(1,1000000) s(i);
104 ANALYZE t1;
106 -- the number of matching rows will be drastically underestimated:
108 EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
109 CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
111 ANALYZE t1;
113 -- now the row count estimate is more accurate:
115 EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
116 Without functional-dependency statistics, the planner would assume that
118 the two WHERE conditions are independent, and would multiply their
119 selectivities together to arrive at a much-too-small row count
120 estimate. With such statistics, the planner recognizes that the WHERE
121 conditions are redundant and does not underestimate the row count.
122 Create table t2 with two perfectly correlated columns (containing
124 identical data), and an MCV list on those columns:
125 CREATE TABLE t2 (
127 a int,
128 b int
129 );
130 INSERT INTO t2 SELECT mod(i,100), mod(i,100)
132 FROM generate_series(1,1000000) s(i);
133 CREATE STATISTICS s2 (mcv) ON a, b FROM t2;
135 ANALYZE t2;
137 -- valid combination (found in MCV)
139 EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);
140 -- invalid combination (not found in MCV)
142 EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);
143 The MCV list gives the planner more detailed information about the
145 specific values that commonly appear in the table, as well as an upper
146 bound on the selectivities of combinations of values that do not appear
147 in the table, allowing it to generate better estimates in both cases.
148 Create table t3 with a single timestamp column, and run queries using
150 expressions on that column. Without extended statistics, the planner
151 has no information about the data distribution for the expressions, and
152 uses default estimates. The planner also does not realize that the
153 value of the date truncated to the month is fully determined by the
154 value of the date truncated to the day. Then expression and ndistinct
155 statistics are built on those two expressions:
156 CREATE TABLE t3 (
158 a timestamp
159 );
160 INSERT INTO t3 SELECT i FROM generate_series('2020-01-01'::timestamp,
162 '2020-12-31'::timestamp,
163 '1 minute'::interval) s(i);
164 ANALYZE t3;
166 -- the number of matching rows will be drastically underestimated:
168 EXPLAIN ANALYZE SELECT * FROM t3
169 WHERE date_trunc('month', a) = '2020-01-01'::timestamp;
170 EXPLAIN ANALYZE SELECT * FROM t3
172 WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
173 AND '2020-06-30'::timestamp;
174 EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
176 FROM t3 GROUP BY 1, 2;
177 -- build ndistinct statistics on the pair of expressions (per-expression
179 -- statistics are built automatically)
180 CREATE STATISTICS s3 (ndistinct) ON date_trunc('month', a), date_trunc('day', a) FROM t3;
181 ANALYZE t3;
183 -- now the row count estimates are more accurate:
185 EXPLAIN ANALYZE SELECT * FROM t3
186 WHERE date_trunc('month', a) = '2020-01-01'::timestamp;
187 EXPLAIN ANALYZE SELECT * FROM t3
189 WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
190 AND '2020-06-30'::timestamp;
191 EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
193 FROM t3 GROUP BY 1, 2;
194 Without expression and ndistinct statistics, the planner has no
196 information about the number of distinct values for the expressions,
197 and has to rely on default estimates. The equality and range conditions
198 are assumed to have 0.5% selectivity, and the number of distinct values
199 in the expression is assumed to be the same as for the column (i.e.
200 unique). This results in a significant underestimate of the row count
201 in the first two queries. Moreover, the planner has no information
202 about the relationship between the expressions, so it assumes the two
203 WHERE and GROUP BY conditions are independent, and multiplies their
204 selectivities together to arrive at a severe overestimate of the group
205 count in the aggregate query. This is further exacerbated by the lack
206 of accurate statistics for the expressions, forcing the planner to use
207 a default ndistinct estimate for the expression derived from ndistinct
208 for the column. With such statistics, the planner recognizes that the
209 conditions are correlated, and arrives at much more accurate estimates.
210
212 There is no CREATE STATISTICS command in the SQL standard.
213
215 ALTER STATISTICS (ALTER_STATISTICS(7)), DROP STATISTICS
216 (DROP_STATISTICS(7))
217PostgreSQL 14.3 2022 CREATE STATISTICS(7)