create_stats_opt_name.v3.patch
application/octet-stream
Filename: create_stats_opt_name.v3.patch
Type: application/octet-stream
Part: 0
Patch
Format: unified
Series: patch v3
| File | + | − |
|---|---|---|
| doc/src/sgml/ref/create_statistics.sgml | 8 | 4 |
| src/backend/parser/gram.y | 12 | 0 |
| src/test/regress/expected/stats_ext.out | 51 | 36 |
| src/test/regress/sql/stats_ext.sql | 14 | 6 |
diff --git a/doc/src/sgml/ref/create_statistics.sgml b/doc/src/sgml/ref/create_statistics.sgml
index 9a8c904c08..a7a55553e1 100644
--- a/doc/src/sgml/ref/create_statistics.sgml
+++ b/doc/src/sgml/ref/create_statistics.sgml
@@ -21,11 +21,11 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="parameter">statistics_name</replaceable>
+CREATE STATISTICS [ [ IF NOT EXISTS ] <replaceable class="parameter">statistics_name</replaceable>]
ON ( <replaceable class="parameter">expression</replaceable> )
FROM <replaceable class="parameter">table_name</replaceable>
-CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="parameter">statistics_name</replaceable>
+CREATE STATISTICS [ [ IF NOT EXISTS ] <replaceable class="parameter">statistics_name</replaceable>]
[ ( <replaceable class="parameter">statistics_kind</replaceable> [, ... ] ) ]
ON { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) }, { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [, ...]
FROM <replaceable class="parameter">table_name</replaceable>
@@ -60,8 +60,8 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="parameter">statistics_na
If a schema name is given (for example, <literal>CREATE STATISTICS
myschema.mystat ...</literal>) 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.
+ If given, the name of the statistics object must be distinct from the name
+ of any other statistics object in the same schema.
</para>
</refsect1>
@@ -78,6 +78,7 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="parameter">statistics_na
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.
+ Statistics name is required when <literal>IF NOT EXISTS</literal> is specified.
</para>
</listitem>
</varlistentry>
@@ -88,6 +89,9 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="parameter">statistics_na
<para>
The name (optionally schema-qualified) of the statistics object to be
created.
+ If the name is omitted, <productname>PostgreSQL</productname> chooses a
+ suitable name based on the parent table's name and the defined column
+ name(s) and/or expression(s).
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c9941d9cb4..484935b264 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4362,6 +4362,18 @@ CreateStatsStmt:
n->if_not_exists = true;
$$ = (Node *)n;
}
+ | CREATE STATISTICS
+ opt_name_list ON stats_params FROM from_list
+ {
+ CreateStatsStmt *n = makeNode(CreateStatsStmt);
+ n->defnames = NULL;
+ n->stat_types = $3;
+ n->exprs = $5;
+ n->relations = $7;
+ n->stxcomment = NULL;
+ n->if_not_exists = false;
+ $$ = (Node *)n;
+ }
;
/*
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 042316aeed..8f5fd546eb 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -3062,11 +3062,11 @@ CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
ANALYZE tststats.priv_test_tbl;
-- Check printing info about extended statistics by \dX
create table stts_t1 (a int, b int);
-create statistics stts_1 (ndistinct) on a, b from stts_t1;
-create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;
-create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;
+create statistics (ndistinct) on a, b from stts_t1;
+create statistics (ndistinct, dependencies) on a, b from stts_t1;
+create statistics (ndistinct, dependencies, mcv) on a, b from stts_t1;
create table stts_t2 (a int, b int, c int);
-create statistics stts_4 on b, c from stts_t2;
+create statistics on b, c from stts_t2;
create table stts_t3 (col1 int, col2 int, col3 int);
create statistics stts_hoge on col1, col2, col3 from stts_t3;
create schema stts_s1;
@@ -3084,24 +3084,24 @@ set search_path to public, stts_s1, stts_s2, tststats;
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
- public | stts_1 | a, b FROM stts_t1 | defined | |
- public | stts_2 | a, b FROM stts_t1 | defined | defined |
- public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
- public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
+ public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | |
+ public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined |
+ public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined
+ public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
tststats | priv_test_stats | a, b FROM priv_test_tbl | | | defined
(12 rows)
-\dX stts_?
- List of extended statistics
- Schema | Name | Definition | Ndistinct | Dependencies | MCV
---------+--------+-------------------+-----------+--------------+---------
- public | stts_1 | a, b FROM stts_t1 | defined | |
- public | stts_2 | a, b FROM stts_t1 | defined | defined |
- public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
- public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
+\dX stts_t*
+ List of extended statistics
+ Schema | Name | Definition | Ndistinct | Dependencies | MCV
+--------+-------------------+-------------------+-----------+--------------+---------
+ public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | |
+ public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined |
+ public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined
+ public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined
(4 rows)
\dX *stts_hoge
@@ -3119,24 +3119,24 @@ set search_path to public, stts_s1, stts_s2, tststats;
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
- public | stts_1 | a, b FROM stts_t1 | defined | |
- public | stts_2 | a, b FROM stts_t1 | defined | defined |
- public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
- public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
+ public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | |
+ public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined |
+ public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined
+ public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
tststats | priv_test_stats | a, b FROM priv_test_tbl | | | defined
(12 rows)
-\dX+ stts_?
- List of extended statistics
- Schema | Name | Definition | Ndistinct | Dependencies | MCV
---------+--------+-------------------+-----------+--------------+---------
- public | stts_1 | a, b FROM stts_t1 | defined | |
- public | stts_2 | a, b FROM stts_t1 | defined | defined |
- public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
- public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
+\dX+ stts_t*
+ List of extended statistics
+ Schema | Name | Definition | Ndistinct | Dependencies | MCV
+--------+-------------------+-------------------+-----------+--------------+---------
+ public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | |
+ public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined |
+ public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined
+ public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined
(4 rows)
\dX+ *stts_hoge
@@ -3153,6 +3153,21 @@ set search_path to public, stts_s1, stts_s2, tststats;
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
(1 row)
+create statistics (mcv) ON a, b, (a+b), (a-b) FROM stts_t1;
+create statistics (mcv) ON a, b, (a+b), (a-b) FROM stts_t1;
+create statistics (mcv) ON (a+b), (a-b) FROM stts_t1;
+\dX stts_t*expr*
+ List of extended statistics
+ Schema | Name | Definition | Ndistinct | Dependencies | MCV
+--------+-----------------------------+-------------------------------------+-----------+--------------+---------
+ public | stts_t1_a_b_expr_expr_stat | a, b, (a + b), (a - b) FROM stts_t1 | | | defined
+ public | stts_t1_a_b_expr_expr_stat1 | a, b, (a + b), (a - b) FROM stts_t1 | | | defined
+ public | stts_t1_expr_expr_stat | (a + b), (a - b) FROM stts_t1 | | | defined
+(3 rows)
+
+drop statistics stts_t1_a_b_expr_expr_stat;
+drop statistics stts_t1_a_b_expr_expr_stat1;
+drop statistics stts_t1_expr_expr_stat;
set search_path to public, stts_s1;
\dX
List of extended statistics
@@ -3162,11 +3177,11 @@ set search_path to public, stts_s1;
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
- public | stts_1 | a, b FROM stts_t1 | defined | |
- public | stts_2 | a, b FROM stts_t1 | defined | defined |
- public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
- public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
+ public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | |
+ public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined |
+ public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined
+ public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
(10 rows)
@@ -3180,11 +3195,11 @@ set role regress_stats_ext;
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
- public | stts_1 | a, b FROM stts_t1 | defined | |
- public | stts_2 | a, b FROM stts_t1 | defined | defined |
- public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
- public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
+ public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | |
+ public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined |
+ public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined
+ public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined
(9 rows)
reset role;
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 6b954c9e50..5fd865f509 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -1555,12 +1555,12 @@ ANALYZE tststats.priv_test_tbl;
-- Check printing info about extended statistics by \dX
create table stts_t1 (a int, b int);
-create statistics stts_1 (ndistinct) on a, b from stts_t1;
-create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;
-create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;
+create statistics (ndistinct) on a, b from stts_t1;
+create statistics (ndistinct, dependencies) on a, b from stts_t1;
+create statistics (ndistinct, dependencies, mcv) on a, b from stts_t1;
create table stts_t2 (a int, b int, c int);
-create statistics stts_4 on b, c from stts_t2;
+create statistics on b, c from stts_t2;
create table stts_t3 (col1 int, col2 int, col3 int);
create statistics stts_hoge on col1, col2, col3 from stts_t3;
@@ -1575,13 +1575,21 @@ analyze stts_t1;
set search_path to public, stts_s1, stts_s2, tststats;
\dX
-\dX stts_?
+\dX stts_t*
\dX *stts_hoge
\dX+
-\dX+ stts_?
+\dX+ stts_t*
\dX+ *stts_hoge
\dX+ stts_s2.stts_yama
+create statistics (mcv) ON a, b, (a+b), (a-b) FROM stts_t1;
+create statistics (mcv) ON a, b, (a+b), (a-b) FROM stts_t1;
+create statistics (mcv) ON (a+b), (a-b) FROM stts_t1;
+\dX stts_t*expr*
+drop statistics stts_t1_a_b_expr_expr_stat;
+drop statistics stts_t1_a_b_expr_expr_stat1;
+drop statistics stts_t1_expr_expr_stat;
+
set search_path to public, stts_s1;
\dX