Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbc08520

Browse files
committed
Change CREATE STATISTICS syntax
Previously, we had the WITH clause in the middle of the command, whereyou'd specify both generic options as well as statistic types. Fewpeople liked this, so this commit changes it to remove the WITH keywordfrom that clause and makes it accept statistic types only. (Wecurrently don't have any generic options, but if we invent in thefuture, we will gain a new WITH clause, probably at the end of thecommand).Also, the column list is now specified without parens, which makes thewhole command look more similar to a SELECT command. This change willlet us expand the command to supporting expressions (not just columnsnames) as well as multiple tables and their join conditions.Tom added lots of code comments and fixed some parts of the CREATESTATISTICS reference page, too; more changes in this area areforthcoming. He also fixed a potential problem in the alter_genericregression test, reducing verbosity on a cascaded drop to avoiddependency on message ordering, as we do in other tests.Tom also closed a security bug: we documented that table ownership wasrequired in order to create a statistics object on it, but didn'tactually implement it.Implement tab-completion for statistics objects. This can stand somemore improvement.Authors: Alvaro Herrera, with lots of cleanup by Tom LaneDiscussion:https://postgr.es/m/20170420212426.ltvgyhnefvhixm6i@alvherre.pgsql
1 parent46052d9 commitbc08520

File tree

21 files changed

+321
-286
lines changed

21 files changed

+321
-286
lines changed

‎doc/src/sgml/perform.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1132,8 +1132,8 @@ WHERE tablename = 'road';
11321132
To inspect functional dependencies on a statistics
11331133
<literal>stts</literal>, you may do this:
11341134
<programlisting>
1135-
CREATE STATISTICS sttsWITH(dependencies)
1136-
ON(zip, city) FROM zipcodes;
1135+
CREATE STATISTICS stts (dependencies)
1136+
ON zip, city FROM zipcodes;
11371137
ANALYZE zipcodes;
11381138
SELECT stxname, stxkeys, stxdependencies
11391139
FROM pg_statistic_ext
@@ -1219,8 +1219,8 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 10;
12191219
Continuing the above example, the n-distinct coefficients in a ZIP
12201220
code table may look like the following:
12211221
<programlisting>
1222-
CREATE STATISTICS stts2WITH(ndistinct)
1223-
ON(zip, state, city) FROM zipcodes;
1222+
CREATE STATISTICS stts2 (ndistinct)
1223+
ON zip, state, city FROM zipcodes;
12241224
ANALYZE zipcodes;
12251225
SELECT stxkeys AS k, stxndistinct AS nd
12261226
FROM pg_statistic_ext

‎doc/src/sgml/planstats.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -526,7 +526,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
526526
multivariate statistics on the two columns:
527527

528528
<programlisting>
529-
CREATE STATISTICS sttsWITH(dependencies) ON(a, b) FROM t;
529+
CREATE STATISTICS stts (dependencies) ON a, b FROM t;
530530
ANALYZE t;
531531
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
532532
QUERY PLAN
@@ -569,7 +569,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
569569
calculation, the estimate is much improved:
570570
<programlisting>
571571
DROP STATISTICS stts;
572-
CREATE STATISTICS sttsWITH(dependencies, ndistinct) ON(a, b) FROM t;
572+
CREATE STATISTICS stts (dependencies, ndistinct) ON a, b FROM t;
573573
ANALYZE t;
574574
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
575575
QUERY PLAN

‎doc/src/sgml/ref/alter_statistics.sgml

Lines changed: 15 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,7 @@ PostgreSQL documentation
1717
<refnamediv>
1818
<refname>ALTER STATISTICS</refname>
1919
<refpurpose>
20-
change the definition ofa extended statistics
20+
change the definition ofan extended statistics object
2121
</refpurpose>
2222
</refnamediv>
2323

@@ -34,19 +34,20 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
3434

3535
<para>
3636
<command>ALTER STATISTICS</command> changes the parameters of an existing
37-
extended statistics. Any parameters not specifically set in the
37+
extended statistics object. Any parameters not specifically set in the
3838
<command>ALTER STATISTICS</command> command retain their prior settings.
3939
</para>
4040

4141
<para>
42-
You must own the statistics to use <command>ALTER STATISTICS</>.
43-
To change a statistics' schema, you must also have <literal>CREATE</>
44-
privilege on the new schema.
42+
You must own the statisticsobjectto use <command>ALTER STATISTICS</>.
43+
To change a statistics object's schema, you must also
44+
have <literal>CREATE</>privilege on the new schema.
4545
To alter the owner, you must also be a direct or indirect member of the new
4646
owning role, and that role must have <literal>CREATE</literal> privilege on
47-
the statistics' schema. (These restrictions enforce that altering the owner
48-
doesn't do anything you couldn't do by dropping and recreating the statistics.
49-
However, a superuser can alter ownership of any statistics anyway.)
47+
the statistics object's schema. (These restrictions enforce that altering
48+
the owner doesn't do anything you couldn't do by dropping and recreating
49+
the statistics object. However, a superuser can alter ownership of any
50+
statistics object anyway.)
5051
</para>
5152
</refsect1>
5253

@@ -59,7 +60,8 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
5960
<term><replaceable class="parameter">name</replaceable></term>
6061
<listitem>
6162
<para>
62-
The name (optionally schema-qualified) of the statistics to be altered.
63+
The name (optionally schema-qualified) of the statistics object to be
64+
altered.
6365
</para>
6466
</listitem>
6567
</varlistentry>
@@ -68,7 +70,7 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
6870
<term><replaceable class="PARAMETER">new_owner</replaceable></term>
6971
<listitem>
7072
<para>
71-
The user name of the new owner of the statistics.
73+
The user name of the new owner of the statistics object.
7274
</para>
7375
</listitem>
7476
</varlistentry>
@@ -77,7 +79,7 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
7779
<term><replaceable class="parameter">new_name</replaceable></term>
7880
<listitem>
7981
<para>
80-
The new name for the statistics.
82+
The new name for the statistics object.
8183
</para>
8284
</listitem>
8385
</varlistentry>
@@ -86,7 +88,7 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
8688
<term><replaceable class="parameter">new_schema</replaceable></term>
8789
<listitem>
8890
<para>
89-
The new schema for the statistics.
91+
The new schema for the statistics object.
9092
</para>
9193
</listitem>
9294
</varlistentry>
@@ -99,7 +101,7 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
99101
<title>Compatibility</title>
100102

101103
<para>
102-
There's no <command>ALTER STATISTICS</command> command in the SQL standard.
104+
There is no <command>ALTER STATISTICS</command> command in the SQL standard.
103105
</para>
104106
</refsect1>
105107

‎doc/src/sgml/ref/create_statistics.sgml

Lines changed: 34 additions & 51 deletions
Original file line numberDiff line numberDiff line change
@@ -22,8 +22,8 @@ PostgreSQL documentation
2222
<refsynopsisdiv>
2323
<synopsis>
2424
CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_name</replaceable>
25-
WITH ( <replaceable class="PARAMETER">option</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] )
26-
ON(<replaceable class="PARAMETER">column_name</replaceable>, <replaceable class="PARAMETER">column_name</replaceable> [, ...])
25+
[ ( <replaceable class="PARAMETER">statistic_type</replaceable> [, ... ] ) ]
26+
ON <replaceable class="PARAMETER">column_name</replaceable>, <replaceable class="PARAMETER">column_name</replaceable> [, ...]
2727
FROM <replaceable class="PARAMETER">table_name</replaceable>
2828
</synopsis>
2929

@@ -34,17 +34,17 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_na
3434

3535
<para>
3636
<command>CREATE STATISTICS</command> will create a new extended statistics
37-
objectonthe specified table, foreign table or materialized view.
38-
The statistics will be created in the current database and
39-
will be owned by the user issuing the command.
37+
objecttracking data aboutthe specified table, foreign table or
38+
materialized view.The statisticsobjectwill be created in the current
39+
database andwill be owned by the user issuing the command.
4040
</para>
4141

4242
<para>
4343
If a schema name is given (for example, <literal>CREATE STATISTICS
44-
myschema.mystat ...</>) then the statistics is created in the specified
45-
schema. Otherwise it is created in the current schema. The name of
46-
the statistics must be distinct from the name of any other statistics in the
47-
same schema.
44+
myschema.mystat ...</>) then the statisticsobjectis created in the
45+
specifiedschema. Otherwise it is created in the current schema.
46+
The name ofthe statisticsobjectmust be distinct from the name of any
47+
other statistics object in thesame schema.
4848
</para>
4949
</refsect1>
5050

@@ -57,10 +57,10 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_na
5757
<term><literal>IF NOT EXISTS</></term>
5858
<listitem>
5959
<para>
60-
Do not throw an error if a statistics with the same name already exists.
61-
A notice is issued in this case. Note that only the name of the
62-
statistics object is considered here. The definition ofthestatistics is
63-
not considered.
60+
Do not throw an error if a statisticsobjectwith the same name already
61+
exists.A notice is issued in this case. Note that only the name of
62+
thestatistics object is considered here, notthedetails of its
63+
definition.
6464
</para>
6565
</listitem>
6666
</varlistentry>
@@ -69,67 +69,45 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_na
6969
<term><replaceable class="PARAMETER">statistics_name</replaceable></term>
7070
<listitem>
7171
<para>
72-
The name (optionally schema-qualified) of the statistics to be created.
72+
The name (optionally schema-qualified) of the statistics object to be
73+
created.
7374
</para>
7475
</listitem>
7576
</varlistentry>
7677

7778
<varlistentry>
78-
<term><replaceable class="PARAMETER">column_name</replaceable></term>
79+
<term><replaceable class="PARAMETER">statistic_type</replaceable></term>
7980
<listitem>
8081
<para>
81-
The name of a column to be included in the statistics.
82+
A statistic type to be computed in this statistics object. Currently
83+
supported types are <literal>ndistinct</literal>, which enables
84+
n-distinct coefficient tracking,
85+
and <literal>dependencies</literal>, which enables functional
86+
dependencies.
8287
</para>
8388
</listitem>
8489
</varlistentry>
8590

8691
<varlistentry>
87-
<term><replaceable class="PARAMETER">table_name</replaceable></term>
88-
<listitem>
89-
<para>
90-
The name (optionally schema-qualified) of the table the statistics should
91-
be created on.
92-
</para>
93-
</listitem>
94-
</varlistentry>
95-
96-
</variablelist>
97-
98-
<refsect2 id="SQL-CREATESTATISTICS-parameters">
99-
<title id="SQL-CREATESTATISTICS-parameters-title">Parameters</title>
100-
101-
<indexterm zone="sql-createstatistics-parameters">
102-
<primary>statistics parameters</primary>
103-
</indexterm>
104-
105-
<para>
106-
The <literal>WITH</> clause can specify <firstterm>options</>
107-
for the statistics. Available options are listed below.
108-
</para>
109-
110-
<variablelist>
111-
112-
<varlistentry>
113-
<term><literal>dependencies</> (<type>boolean</>)</term>
92+
<term><replaceable class="PARAMETER">column_name</replaceable></term>
11493
<listitem>
11594
<para>
116-
Enables functional dependencies forthe statistics.
95+
The name of a table column to be included inthe statistics object.
11796
</para>
11897
</listitem>
11998
</varlistentry>
12099

121100
<varlistentry>
122-
<term><literal>ndistinct</> (<type>boolean</>)</term>
101+
<term><replaceable class="PARAMETER">table_name</replaceable></term>
123102
<listitem>
124103
<para>
125-
Enables ndistinct coefficients for the statistics.
104+
The name (optionally schema-qualified) of the table containing the
105+
column(s) the statistics are computed on.
126106
</para>
127107
</listitem>
128108
</varlistentry>
129109

130-
</variablelist>
131-
132-
</refsect2>
110+
</variablelist>
133111
</refsect1>
134112

135113
<refsect1>
@@ -158,7 +136,7 @@ CREATE TABLE t1 (
158136
INSERT INTO t1 SELECT i/100, i/500
159137
FROM generate_series(1,1000000) s(i);
160138

161-
CREATE STATISTICS s1WITH(dependencies) ON(a, b) FROM t1;
139+
CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
162140

163141
ANALYZE t1;
164142

@@ -168,6 +146,11 @@ EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
168146
-- invalid combination of values
169147
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 1);
170148
</programlisting>
149+
150+
Without functional-dependency statistics, the planner would make the
151+
same estimate of the number of matching rows for these two queries.
152+
With such statistics, it is able to tell that one case has matches
153+
and the other does not.
171154
</para>
172155

173156
</refsect1>
@@ -176,7 +159,7 @@ EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 1);
176159
<title>Compatibility</title>
177160

178161
<para>
179-
There's no <command>CREATE STATISTICS</command> command in the SQL standard.
162+
There is no <command>CREATE STATISTICS</command> command in the SQL standard.
180163
</para>
181164
</refsect1>
182165

‎doc/src/sgml/ref/drop_statistics.sgml

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -29,9 +29,9 @@ DROP STATISTICS [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
2929
<title>Description</title>
3030

3131
<para>
32-
<command>DROP STATISTICS</command> removes statistics from the database.
33-
Only the statistics owner, the schema owner,and superuser can drop a
34-
statistics.
32+
<command>DROP STATISTICS</command> removes statisticsobject(s)from the
33+
database.Only the statisticsobject'sowner, the schema owner,or a
34+
superuser can drop astatistics object.
3535
</para>
3636

3737
</refsect1>
@@ -44,8 +44,8 @@ DROP STATISTICS [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
4444
<term><literal>IF EXISTS</literal></term>
4545
<listitem>
4646
<para>
47-
Do not throw an error if the statisticsdonot exist. A notice is
48-
issued in this case.
47+
Do not throw an error if the statisticsobject doesnot exist. A notice
48+
isissued in this case.
4949
</para>
5050
</listitem>
5151
</varlistentry>
@@ -54,7 +54,7 @@ DROP STATISTICS [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
5454
<term><replaceable class="PARAMETER">name</replaceable></term>
5555
<listitem>
5656
<para>
57-
The name (optionally schema-qualified) of the statistics to drop.
57+
The name (optionally schema-qualified) of the statisticsobjectto drop.
5858
</para>
5959
</listitem>
6060
</varlistentry>
@@ -66,7 +66,7 @@ DROP STATISTICS [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
6666
<title>Examples</title>
6767

6868
<para>
69-
To destroy two statistics objectson different schemas, without failing
69+
To destroy two statistics objectsin different schemas, without failing
7070
if they don't exist:
7171

7272
<programlisting>
@@ -82,7 +82,7 @@ DROP STATISTICS IF EXISTS
8282
<title>Compatibility</title>
8383

8484
<para>
85-
There's no <command>DROP STATISTICS</command> command in the SQL standard.
85+
There is no <command>DROP STATISTICS</command> command in the SQL standard.
8686
</para>
8787
</refsect1>
8888

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp