1CREATE STATISTICS(7) PostgreSQL 16.1 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. If
38 given, the name of the statistics object must be distinct from the name
39 of any 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. Statistics name is required when IF NOT EXISTS
47 is specified.
48 statistics_name
50 The name (optionally schema-qualified) of the statistics object to
51 be created. If the name is omitted, PostgreSQL chooses a suitable
52 name based on the parent table's name and the defined column
53 name(s) and/or expression(s).
54 statistics_kind
56 A multivariate statistics kind to be computed in this statistics
57 object. Currently supported kinds are ndistinct, which enables
58 n-distinct statistics, dependencies, which enables functional
59 dependency statistics, and mcv which enables most-common values
60 lists. If this clause is omitted, all supported statistics kinds
61 are included in the statistics object. Univariate expression
62 statistics are built automatically if the statistics definition
63 includes any complex expressions rather than just simple column
64 references. For more information, see Section 14.2.2 and
65 Section 76.2.
66 column_name
68 The name of a table column to be covered by the computed
69 statistics. This is only allowed when building multivariate
70 statistics. At least two column names or expressions must be
71 specified, and their order is not significant.
72 expression
74 An expression to be covered by the computed statistics. This may be
75 used to build univariate statistics on a single expression, or as
76 part of a list of multiple column names and/or expressions to build
77 multivariate statistics. In the latter case, separate univariate
78 statistics are built automatically for each expression in the list.
79 table_name
81 The name (optionally schema-qualified) of the table containing the
82 column(s) the statistics are computed on; see ANALYZE(7) for an
83 explanation of the handling of inheritance and partitions.
84
86 You must be the owner of a table to create a statistics object reading
87 it. Once created, however, the ownership of the statistics object is
88 independent of the underlying table(s).
89 Expression statistics are per-expression and are similar to creating an
91 index on the expression, except that they avoid the overhead of index
92 maintenance. Expression statistics are built automatically for each
93 expression in the statistics object definition.
94 Extended statistics are not currently used by the planner for
96 selectivity estimations made for table joins. This limitation will
97 likely be removed in a future version of PostgreSQL.
98
100 Create table t1 with two functionally dependent columns, i.e.,
101 knowledge of a value in the first column is sufficient for determining
102 the value in the other column. Then functional dependency statistics
103 are built on those columns:
104 CREATE TABLE t1 (
106 a int,
107 b int
108 );
109 INSERT INTO t1 SELECT i/100, i/500
111 FROM generate_series(1,1000000) s(i);
112 ANALYZE t1;
114 -- the number of matching rows will be drastically underestimated:
116 EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
117 CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
119 ANALYZE t1;
121 -- now the row count estimate is more accurate:
123 EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
124 Without functional-dependency statistics, the planner would assume that
126 the two WHERE conditions are independent, and would multiply their
127 selectivities together to arrive at a much-too-small row count
128 estimate. With such statistics, the planner recognizes that the WHERE
129 conditions are redundant and does not underestimate the row count.
130 Create table t2 with two perfectly correlated columns (containing
132 identical data), and an MCV list on those columns:
133 CREATE TABLE t2 (
135 a int,
136 b int
137 );
138 INSERT INTO t2 SELECT mod(i,100), mod(i,100)
140 FROM generate_series(1,1000000) s(i);
141 CREATE STATISTICS s2 (mcv) ON a, b FROM t2;
143 ANALYZE t2;
145 -- valid combination (found in MCV)
147 EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);
148 -- invalid combination (not found in MCV)
150 EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);
151 The MCV list gives the planner more detailed information about the
153 specific values that commonly appear in the table, as well as an upper
154 bound on the selectivities of combinations of values that do not appear
155 in the table, allowing it to generate better estimates in both cases.
156 Create table t3 with a single timestamp column, and run queries using
158 expressions on that column. Without extended statistics, the planner
159 has no information about the data distribution for the expressions, and
160 uses default estimates. The planner also does not realize that the
161 value of the date truncated to the month is fully determined by the
162 value of the date truncated to the day. Then expression and ndistinct
163 statistics are built on those two expressions:
164 CREATE TABLE t3 (
166 a timestamp
167 );
168 INSERT INTO t3 SELECT i FROM generate_series('2020-01-01'::timestamp,
170 '2020-12-31'::timestamp,
171 '1 minute'::interval) s(i);
172 ANALYZE t3;
174 -- the number of matching rows will be drastically underestimated:
176 EXPLAIN ANALYZE SELECT * FROM t3
177 WHERE date_trunc('month', a) = '2020-01-01'::timestamp;
178 EXPLAIN ANALYZE SELECT * FROM t3
180 WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
181 AND '2020-06-30'::timestamp;
182 EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
184 FROM t3 GROUP BY 1, 2;
185 -- build ndistinct statistics on the pair of expressions (per-expression
187 -- statistics are built automatically)
188 CREATE STATISTICS s3 (ndistinct) ON date_trunc('month', a), date_trunc('day', a) FROM t3;
189 ANALYZE t3;
191 -- now the row count estimates are more accurate:
193 EXPLAIN ANALYZE SELECT * FROM t3
194 WHERE date_trunc('month', a) = '2020-01-01'::timestamp;
195 EXPLAIN ANALYZE SELECT * FROM t3
197 WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
198 AND '2020-06-30'::timestamp;
199 EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
201 FROM t3 GROUP BY 1, 2;
202 Without expression and ndistinct statistics, the planner has no
204 information about the number of distinct values for the expressions,
205 and has to rely on default estimates. The equality and range conditions
206 are assumed to have 0.5% selectivity, and the number of distinct values
207 in the expression is assumed to be the same as for the column (i.e.
208 unique). This results in a significant underestimate of the row count
209 in the first two queries. Moreover, the planner has no information
210 about the relationship between the expressions, so it assumes the two
211 WHERE and GROUP BY conditions are independent, and multiplies their
212 selectivities together to arrive at a severe overestimate of the group
213 count in the aggregate query. This is further exacerbated by the lack
214 of accurate statistics for the expressions, forcing the planner to use
215 a default ndistinct estimate for the expression derived from ndistinct
216 for the column. With such statistics, the planner recognizes that the
217 conditions are correlated, and arrives at much more accurate estimates.
218
220 There is no CREATE STATISTICS command in the SQL standard.
221
223 ALTER STATISTICS (ALTER_STATISTICS(7)), DROP STATISTICS
224 (DROP_STATISTICS(7))
225PostgreSQL 16.1 2023 CREATE STATISTICS(7)