1CREATE STATISTICS(7) PostgreSQL 15.4 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 75.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; see ANALYZE(7) for an
80 explanation of the handling of inheritance and partitions.
81
83 You must be the owner of a table to create a statistics object reading
84 it. Once created, however, the ownership of the statistics object is
85 independent of the underlying table(s).
86 Expression statistics are per-expression and are similar to creating an
88 index on the expression, except that they avoid the overhead of index
89 maintenance. Expression statistics are built automatically for each
90 expression in the statistics object definition.
91 Extended statistics are not currently used by the planner for
93 selectivity estimations made for table joins. This limitation will
94 likely be removed in a future version of PostgreSQL.
95
97 Create table t1 with two functionally dependent columns, i.e.,
98 knowledge of a value in the first column is sufficient for determining
99 the value in the other column. Then functional dependency statistics
100 are built on those columns:
101 CREATE TABLE t1 (
103 a int,
104 b int
105 );
106 INSERT INTO t1 SELECT i/100, i/500
108 FROM generate_series(1,1000000) s(i);
109 ANALYZE t1;
111 -- the number of matching rows will be drastically underestimated:
113 EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
114 CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
116 ANALYZE t1;
118 -- now the row count estimate is more accurate:
120 EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
121 Without functional-dependency statistics, the planner would assume that
123 the two WHERE conditions are independent, and would multiply their
124 selectivities together to arrive at a much-too-small row count
125 estimate. With such statistics, the planner recognizes that the WHERE
126 conditions are redundant and does not underestimate the row count.
127 Create table t2 with two perfectly correlated columns (containing
129 identical data), and an MCV list on those columns:
130 CREATE TABLE t2 (
132 a int,
133 b int
134 );
135 INSERT INTO t2 SELECT mod(i,100), mod(i,100)
137 FROM generate_series(1,1000000) s(i);
138 CREATE STATISTICS s2 (mcv) ON a, b FROM t2;
140 ANALYZE t2;
142 -- valid combination (found in MCV)
144 EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);
145 -- invalid combination (not found in MCV)
147 EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);
148 The MCV list gives the planner more detailed information about the
150 specific values that commonly appear in the table, as well as an upper
151 bound on the selectivities of combinations of values that do not appear
152 in the table, allowing it to generate better estimates in both cases.
153 Create table t3 with a single timestamp column, and run queries using
155 expressions on that column. Without extended statistics, the planner
156 has no information about the data distribution for the expressions, and
157 uses default estimates. The planner also does not realize that the
158 value of the date truncated to the month is fully determined by the
159 value of the date truncated to the day. Then expression and ndistinct
160 statistics are built on those two expressions:
161 CREATE TABLE t3 (
163 a timestamp
164 );
165 INSERT INTO t3 SELECT i FROM generate_series('2020-01-01'::timestamp,
167 '2020-12-31'::timestamp,
168 '1 minute'::interval) s(i);
169 ANALYZE t3;
171 -- the number of matching rows will be drastically underestimated:
173 EXPLAIN ANALYZE SELECT * FROM t3
174 WHERE date_trunc('month', a) = '2020-01-01'::timestamp;
175 EXPLAIN ANALYZE SELECT * FROM t3
177 WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
178 AND '2020-06-30'::timestamp;
179 EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
181 FROM t3 GROUP BY 1, 2;
182 -- build ndistinct statistics on the pair of expressions (per-expression
184 -- statistics are built automatically)
185 CREATE STATISTICS s3 (ndistinct) ON date_trunc('month', a), date_trunc('day', a) FROM t3;
186 ANALYZE t3;
188 -- now the row count estimates are more accurate:
190 EXPLAIN ANALYZE SELECT * FROM t3
191 WHERE date_trunc('month', a) = '2020-01-01'::timestamp;
192 EXPLAIN ANALYZE SELECT * FROM t3
194 WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
195 AND '2020-06-30'::timestamp;
196 EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
198 FROM t3 GROUP BY 1, 2;
199 Without expression and ndistinct statistics, the planner has no
201 information about the number of distinct values for the expressions,
202 and has to rely on default estimates. The equality and range conditions
203 are assumed to have 0.5% selectivity, and the number of distinct values
204 in the expression is assumed to be the same as for the column (i.e.
205 unique). This results in a significant underestimate of the row count
206 in the first two queries. Moreover, the planner has no information
207 about the relationship between the expressions, so it assumes the two
208 WHERE and GROUP BY conditions are independent, and multiplies their
209 selectivities together to arrive at a severe overestimate of the group
210 count in the aggregate query. This is further exacerbated by the lack
211 of accurate statistics for the expressions, forcing the planner to use
212 a default ndistinct estimate for the expression derived from ndistinct
213 for the column. With such statistics, the planner recognizes that the
214 conditions are correlated, and arrives at much more accurate estimates.
215
217 There is no CREATE STATISTICS command in the SQL standard.
218
220 ALTER STATISTICS (ALTER_STATISTICS(7)), DROP STATISTICS
221 (DROP_STATISTICS(7))
222PostgreSQL 15.4 2023 CREATE STATISTICS(7)