1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
|
<!--
doc/src/sgml/ref/create_statistics.sgml
PostgreSQL documentation
-->
<refentry id="SQL-CREATESTATISTICS">
<indexterm zone="sql-createstatistics">
<primary>CREATE STATISTICS</primary>
</indexterm>
<refmeta>
<refentrytitle>CREATE STATISTICS</refentrytitle>
<manvolnum>7</manvolnum>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>CREATE STATISTICS</refname>
<refpurpose>define extended statistics</refpurpose>
</refnamediv>
<refsynopsisdiv>
<synopsis>
CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_name</replaceable>
[ ( <replaceable class="PARAMETER">statistic_type</replaceable> [, ... ] ) ]
ON <replaceable class="PARAMETER">column_name</replaceable>, <replaceable class="PARAMETER">column_name</replaceable> [, ...]
FROM <replaceable class="PARAMETER">table_name</replaceable>
</synopsis>
</refsynopsisdiv>
<refsect1 id="SQL-CREATESTATISTICS-description">
<title>Description</title>
<para>
<command>CREATE STATISTICS</command> will create a new extended statistics
object tracking data about the specified table, foreign table or
materialized view. The statistics object will be created in the current
database and will be owned by the user issuing the command.
</para>
<para>
If a schema name is given (for example, <literal>CREATE STATISTICS
myschema.mystat ...</>) then the statistics object is created in the
specified schema. Otherwise it is created in the current schema.
The name of the statistics object must be distinct from the name of any
other statistics object in the same schema.
</para>
</refsect1>
<refsect1>
<title>Parameters</title>
<variablelist>
<varlistentry>
<term><literal>IF NOT EXISTS</></term>
<listitem>
<para>
Do not throw an error if a statistics object with the same name already
exists. A notice is issued in this case. Note that only the name of
the statistics object is considered here, not the details of its
definition.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">statistics_name</replaceable></term>
<listitem>
<para>
The name (optionally schema-qualified) of the statistics object to be
created.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">statistic_type</replaceable></term>
<listitem>
<para>
A statistic type to be computed in this statistics object. Currently
supported types are <literal>ndistinct</literal>, which enables
n-distinct coefficient tracking,
and <literal>dependencies</literal>, which enables functional
dependencies.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">column_name</replaceable></term>
<listitem>
<para>
The name of a table column to be included in the statistics object.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">table_name</replaceable></term>
<listitem>
<para>
The name (optionally schema-qualified) of the table containing the
column(s) the statistics are computed on.
</para>
</listitem>
</varlistentry>
</variablelist>
</refsect1>
<refsect1>
<title>Notes</title>
<para>
You must be the owner of a table to create or change statistics on it.
</para>
</refsect1>
<refsect1 id="SQL-CREATESTATISTICS-examples">
<title>Examples</title>
<para>
Create table <structname>t1</> with two functionally dependent columns, i.e.
knowledge of a value in the first column is sufficient for determining the
value in the other column. Then functional dependencies are built on those
columns:
<programlisting>
CREATE TABLE t1 (
a int,
b int
);
INSERT INTO t1 SELECT i/100, i/500
FROM generate_series(1,1000000) s(i);
CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
ANALYZE t1;
-- valid combination of values
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
-- invalid combination of values
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 1);
</programlisting>
Without functional-dependency statistics, the planner would make the
same estimate of the number of matching rows for these two queries.
With such statistics, it is able to tell that one case has matches
and the other does not.
</para>
</refsect1>
<refsect1>
<title>Compatibility</title>
<para>
There is no <command>CREATE STATISTICS</command> command in the SQL standard.
</para>
</refsect1>
<refsect1>
<title>See Also</title>
<simplelist type="inline">
<member><xref linkend="sql-alterstatistics"></member>
<member><xref linkend="sql-dropstatistics"></member>
</simplelist>
</refsect1>
</refentry>
|
