Simon Riggs wrote:
> 2.
> USING keyword, no brackets
> CREATE STATISTICS s1 USING (dependencies, ndistinct) ON (a, b) FROM t1
> WHERE partial-stuff;
>
> and if there are options, use the WITH for the optional parameters like this
> CREATE STATISTICS s1 USING (dependencies, ndistinct) WITH (options) ON
> (a, b) FROM t1 WHERE partial-stuff;
>
>
> I think I like (2)
OK, sounds sensible.
Note that the USING list is also optional -- if you don't specify it, we
default to creating all stat types. Also note that we currently don't
have any option other than stat types, so the WITH would always be empty
-- in other words we should remove it until we implement some option.
(I can readily see two possible options to implement for pg11, so the
omission of the WITH clause would be temporary:
1. sample size to use instead of the per-column values
2. whether to forcibly collect stats for all column for this stat
object even if the column has gotten a SET STATISTICS 0
Surely there can be others.)
Patch attached that adds the USING clause replacing the WITH clause,
which is also optional and only accepts statistic types (it doesn't
accept "foo = OFF" anymore, as it seems pointless, but I'm open to
accepting it if people care about it.)
(This patch removes WITH, but I verified that bison accepts having both.
The second attached reversed patch is what I used for removal.)
In the meantime, I noticed that pg_dump support for extstats is not
covered, which I'll go fix next.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index b10b734b90..3406b7a1cd 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -1132,7 +1132,7 @@ WHERE tablename = 'road';
To inspect functional dependencies on a statistics
<literal>stts</literal>, you may do this:
<programlisting>
-CREATE STATISTICS stts WITH (dependencies)
+CREATE STATISTICS stts USING (dependencies)
ON (zip, city) FROM zipcodes;
ANALYZE zipcodes;
SELECT stxname, stxkeys, stxdependencies
@@ -1219,7 +1219,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1
AND b = 10;
Continuing the above example, the n-distinct coefficients in a ZIP
code table may look like the following:
<programlisting>
-CREATE STATISTICS stts2 WITH (ndistinct)
+CREATE STATISTICS stts2 USING (ndistinct)
ON (zip, state, city) FROM zipcodes;
ANALYZE zipcodes;
SELECT stxkeys AS k, stxndistinct AS nd
diff --git a/doc/src/sgml/planstats.sgml b/doc/src/sgml/planstats.sgml
index f4430eb23c..16c433c3a2 100644
--- a/doc/src/sgml/planstats.sgml
+++ b/doc/src/sgml/planstats.sgml
@@ -526,7 +526,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1
AND b = 1;
multivariate statistics on the two columns:
<programlisting>
-CREATE STATISTICS stts WITH (dependencies) ON (a, b) FROM t;
+CREATE STATISTICS stts USING (dependencies) ON (a, b) FROM t;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
QUERY PLAN
@@ -569,7 +569,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP
BY a, b;
calculation, the estimate is much improved:
<programlisting>
DROP STATISTICS stts;
-CREATE STATISTICS stts WITH (dependencies, ndistinct) ON (a, b) FROM t;
+CREATE STATISTICS stts USING (dependencies, ndistinct) ON (a, b) FROM t;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
QUERY PLAN
diff --git a/doc/src/sgml/ref/create_statistics.sgml
b/doc/src/sgml/ref/create_statistics.sgml
index edbcf5840b..ff6ed0668f 100644
--- a/doc/src/sgml/ref/create_statistics.sgml
+++ b/doc/src/sgml/ref/create_statistics.sgml
@@ -22,7 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
CREATE STATISTICS [ IF NOT EXISTS ] <replaceable
class="PARAMETER">statistics_name</replaceable>
- WITH ( <replaceable class="PARAMETER">option</replaceable> [= <replaceable
class="PARAMETER">value</replaceable>] [, ... ] )
+ USING ( <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>
@@ -103,14 +103,14 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable
class="PARAMETER">statistics_na
</indexterm>
<para>
- The <literal>WITH</> clause can specify <firstterm>options</>
- for the statistics. Available options are listed below.
+ The <literal>USING</> clause can specify types of statistics
+ to be enabled. Available types are listed below.
</para>
<variablelist>
<varlistentry>
- <term><literal>dependencies</> (<type>boolean</>)</term>
+ <term><literal>dependencies</></term>
<listitem>
<para>
Enables functional dependencies for the statistics.
@@ -119,7 +119,7 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable
class="PARAMETER">statistics_na
</varlistentry>
<varlistentry>
- <term><literal>ndistinct</> (<type>boolean</>)</term>
+ <term><literal>ndistinct</></term>
<listitem>
<para>
Enables ndistinct coefficients for the statistics.
diff --git a/src/backend/commands/statscmds.c b/src/backend/commands/statscmds.c
index 0b9c33e30a..f4d1712091 100644
--- a/src/backend/commands/statscmds.c
+++ b/src/backend/commands/statscmds.c
@@ -194,23 +194,22 @@ CreateStatistics(CreateStatsStmt *stmt)
stxkeys = buildint2vector(attnums, numcols);
/*
- * Parse the statistics options. Currently only statistics types are
- * recognized.
+ * Parse the statistic type options.
*/
build_ndistinct = false;
build_dependencies = false;
- foreach(l, stmt->options)
+ foreach(l, stmt->stat_options)
{
DefElem *opt = (DefElem *) lfirst(l);
if (strcmp(opt->defname, "ndistinct") == 0)
{
- build_ndistinct = defGetBoolean(opt);
+ build_ndistinct = true;
requested_type = true;
}
else if (strcmp(opt->defname, "dependencies") == 0)
{
- build_dependencies = defGetBoolean(opt);
+ build_dependencies = true;
requested_type = true;
}
else
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 35a237a000..01c57e4783 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3391,7 +3391,7 @@ _copyCreateStatsStmt(const CreateStatsStmt *from)
COPY_NODE_FIELD(defnames);
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(keys);
- COPY_NODE_FIELD(options);
+ COPY_NODE_FIELD(stat_options);
COPY_SCALAR_FIELD(if_not_exists);
return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 21dfbb0d75..01e2124883 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1351,7 +1351,7 @@ _equalCreateStatsStmt(const CreateStatsStmt *a, const
CreateStatsStmt *b)
COMPARE_NODE_FIELD(defnames);
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(keys);
- COMPARE_NODE_FIELD(options);
+ COMPARE_NODE_FIELD(stat_options);
COMPARE_SCALAR_FIELD(if_not_exists);
return true;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 98f67681a7..015537a4c8 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2641,7 +2641,7 @@ _outCreateStatsStmt(StringInfo str, const CreateStatsStmt
*node)
WRITE_NODE_FIELD(defnames);
WRITE_NODE_FIELD(relation);
WRITE_NODE_FIELD(keys);
- WRITE_NODE_FIELD(options);
+ WRITE_NODE_FIELD(stat_options);
WRITE_BOOL_FIELD(if_not_exists);
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 818d2c29d4..a3467f1c47 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -371,6 +371,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
OptTableElementList TableElementList OptInherit
definition
OptTypedTableElementList TypedTableElementList
reloptions opt_reloptions
+ stat_option_list opt_stat_option_list
OptWith distinct_clause opt_all_clause
opt_definition func_args func_args_list
func_args_with_defaults
func_args_with_defaults_list
aggr_args aggr_args_list
@@ -464,6 +465,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
%type <node> TableElement TypedTableElement ConstraintElem TableFuncElement
%type <node> columnDef columnOptions
%type <defelt> def_elem reloption_elem old_aggr_elem operator_def_elem
+ stat_option_elem
%type <node> def_arg columnElem where_clause where_or_current_clause
a_expr b_expr c_expr AexprConst indirection_el
opt_slice_bound
columnref in_expr having_clause func_table
xmltable array_expr
@@ -3828,32 +3830,59 @@ ExistingIndex: USING INDEX index_name
{ $$ = $3; }
/*****************************************************************************
*
* QUERY :
- * CREATE STATISTICS stats_name WITH (options) ON
(columns) FROM relname
+ * CREATE STATISTICS stats_name [USING
(stat_options)]
+ * ON (columns) FROM relname
*
*****************************************************************************/
-CreateStatsStmt: CREATE STATISTICS any_name opt_reloptions ON '('
columnList ')' FROM qualified_name
- {
- CreateStatsStmt *n =
makeNode(CreateStatsStmt);
- n->defnames = $3;
- n->relation = $10;
- n->keys = $7;
- n->options = $4;
- n->if_not_exists =
false;
- $$ = (Node *)n;
- }
- | CREATE STATISTICS IF_P NOT EXISTS
any_name opt_reloptions ON '(' columnList ')' FROM qualified_name
- {
- CreateStatsStmt *n =
makeNode(CreateStatsStmt);
- n->defnames = $6;
- n->relation = $13;
- n->keys = $10;
- n->options = $7;
- n->if_not_exists = true;
- $$ = (Node *)n;
- }
- ;
+opt_stat_option_list:
+ USING '(' stat_option_list ')' { $$ =
$3; }
+ | /* EMPTY */
{ $$ = NIL; }
+ ;
+
+stat_option_list:
+ stat_option_elem
+ {
+ $$ = list_make1($1);
+ }
+ | stat_option_list ',' stat_option_elem
+ {
+ $$ = lappend($1, $3);
+ }
+ ;
+
+stat_option_elem:
+ ColLabel
+ {
+ $$ = makeDefElem($1, NULL, @1);
+ }
+ ;
+
+CreateStatsStmt:
+ CREATE STATISTICS any_name opt_stat_option_list
+ ON '(' columnList ')' FROM qualified_name
+ {
+ CreateStatsStmt *n =
makeNode(CreateStatsStmt);
+ n->defnames = $3;
+ n->relation = $10;
+ n->keys = $7;
+ n->stat_options = $4;
+ n->if_not_exists = false;
+ $$ = (Node *)n;
+ }
+ | CREATE STATISTICS IF_P NOT EXISTS any_name
opt_stat_option_list
+ ON '(' columnList ')' FROM qualified_name
+ {
+ CreateStatsStmt *n =
makeNode(CreateStatsStmt);
+ n->defnames = $6;
+ n->relation = $13;
+ n->keys = $10;
+ n->stat_options = $7;
+ n->if_not_exists = true;
+ $$ = (Node *)n;
+ }
+ ;
/*****************************************************************************
*
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index cbde1fff01..cdc1712928 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1479,7 +1479,7 @@ pg_get_statisticsext_worker(Oid statextid, bool
missing_ok)
NameStr(statextrec->stxname)));
/*
- * Lookup the stxkind column so that we know how to handle the WITH
+ * Lookup the stxkind column so that we know how to handle the USING
* clause.
*/
datum = SysCacheGetAttr(STATEXTOID, statexttup,
@@ -1504,15 +1504,15 @@ pg_get_statisticsext_worker(Oid statextid, bool
missing_ok)
}
/*
- * If any option is disabled, then we'll need to append a WITH clause to
- * show which options are enabled. We omit the WITH clause on purpose
+ * If any option is disabled, then we'll need to append a USING clause
to
+ * show which options are enabled. We omit the USING clause on purpose
* when all options are enabled, so a pg_dump/pg_restore will create all
* statistics types on a newer postgres version, if the statistics had
all
* options enabled on the original version.
*/
if (!ndistinct_enabled || !dependencies_enabled)
{
- appendStringInfoString(&buf, " WITH (");
+ appendStringInfoString(&buf, " USING (");
if (ndistinct_enabled)
appendStringInfoString(&buf, "ndistinct");
else if (dependencies_enabled)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index dbfc7339e5..dbea7fa677 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2385,7 +2385,7 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf, " ");
/* statistics name (qualified with
namespace) */
- appendPQExpBuffer(&buf, "\"%s.%s\" WITH
(",
+ appendPQExpBuffer(&buf, "\"%s.%s\"
USING (",
PQgetvalue(result, i, 1),
PQgetvalue(result, i, 2));
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e1d454a07d..0409f9d814 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2691,7 +2691,7 @@ typedef struct CreateStatsStmt
List *defnames; /* qualified name (list of Value
strings) */
RangeVar *relation; /* relation to build statistics on */
List *keys; /* String nodes naming
referenced columns */
- List *options; /* list of DefElem */
+ List *stat_options; /* list of name-only DefElem for stat
types */
bool if_not_exists; /* do nothing if statistics already
exists */
} CreateStatsStmt;
diff --git a/src/test/regress/expected/stats_ext.out
b/src/test/regress/expected/stats_ext.out
index 92ac84ac67..e756f58e2d 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -31,7 +31,7 @@ ALTER TABLE ab1 DROP COLUMN a;
b | integer | | |
c | integer | | |
Statistics:
- "public.ab1_b_c_stats" WITH (ndistinct, dependencies) ON (b, c)
+ "public.ab1_b_c_stats" USING (ndistinct, dependencies) ON (b, c)
DROP TABLE ab1;
-- Ensure things work sanely with SET STATISTICS 0
@@ -389,7 +389,7 @@ EXPLAIN (COSTS OFF)
(2 rows)
-- create statistics
-CREATE STATISTICS func_deps_stat WITH (dependencies) ON (a, b, c) FROM
functional_dependencies;
+CREATE STATISTICS func_deps_stat USING (dependencies) ON (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1';
@@ -432,7 +432,7 @@ EXPLAIN (COSTS OFF)
(2 rows)
-- create statistics
-CREATE STATISTICS func_deps_stat WITH (dependencies) ON (a, b, c) FROM
functional_dependencies;
+CREATE STATISTICS func_deps_stat USING (dependencies) ON (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1';
diff --git a/src/test/regress/sql/stats_ext.sql
b/src/test/regress/sql/stats_ext.sql
index 72c7659c4b..15f863f3e0 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -233,7 +233,7 @@ EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1;
-- create statistics
-CREATE STATISTICS func_deps_stat WITH (dependencies) ON (a, b, c) FROM
functional_dependencies;
+CREATE STATISTICS func_deps_stat USING (dependencies) ON (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
@@ -259,7 +259,7 @@ EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1;
-- create statistics
-CREATE STATISTICS func_deps_stat WITH (dependencies) ON (a, b, c) FROM
functional_dependencies;
+CREATE STATISTICS func_deps_stat USING (dependencies) ON (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
commit 1119bfda3f8f03bdc9d8f01e4dd32888cb7b9556[m
Author: Alvaro Herrera <alvhe...@alvh.no-ip.org>
AuthorDate: Wed May 3 12:05:11 2017 -0300
CommitDate: Wed May 3 12:08:55 2017 -0300
remove unused WITH
diff --git b/src/backend/commands/statscmds.c a/src/backend/commands/statscmds.c
index f4d1712091..5cd2d809ae 100644
--- b/src/backend/commands/statscmds.c
+++ a/src/backend/commands/statscmds.c
@@ -193,6 +193,13 @@ CreateStatistics(CreateStatsStmt *stmt)
/* Form an int2vector representation of the sorted column list */
stxkeys = buildint2vector(attnums, numcols);
+ foreach (l, stmt->options)
+ {
+ DefElem *opt = (DefElem *) lfirst(l);
+
+ elog(ERROR, "option \"%s\" not supported", opt->defname);
+ }
+
/*
* Parse the statistic type options.
*/
diff --git b/src/backend/nodes/copyfuncs.c a/src/backend/nodes/copyfuncs.c
index 01c57e4783..62540aadf1 100644
--- b/src/backend/nodes/copyfuncs.c
+++ a/src/backend/nodes/copyfuncs.c
@@ -3391,6 +3391,7 @@ _copyCreateStatsStmt(const CreateStatsStmt *from)
COPY_NODE_FIELD(defnames);
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(keys);
+ COPY_NODE_FIELD(options);
COPY_NODE_FIELD(stat_options);
COPY_SCALAR_FIELD(if_not_exists);
diff --git b/src/backend/nodes/equalfuncs.c a/src/backend/nodes/equalfuncs.c
index 01e2124883..66d07110d5 100644
--- b/src/backend/nodes/equalfuncs.c
+++ a/src/backend/nodes/equalfuncs.c
@@ -1351,6 +1351,7 @@ _equalCreateStatsStmt(const CreateStatsStmt *a, const
CreateStatsStmt *b)
COMPARE_NODE_FIELD(defnames);
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(keys);
+ COMPARE_NODE_FIELD(options);
COMPARE_NODE_FIELD(stat_options);
COMPARE_SCALAR_FIELD(if_not_exists);
diff --git b/src/backend/nodes/outfuncs.c a/src/backend/nodes/outfuncs.c
index 015537a4c8..f589976823 100644
--- b/src/backend/nodes/outfuncs.c
+++ a/src/backend/nodes/outfuncs.c
@@ -2641,6 +2641,7 @@ _outCreateStatsStmt(StringInfo str, const CreateStatsStmt
*node)
WRITE_NODE_FIELD(defnames);
WRITE_NODE_FIELD(relation);
WRITE_NODE_FIELD(keys);
+ WRITE_NODE_FIELD(options);
WRITE_NODE_FIELD(stat_options);
WRITE_BOOL_FIELD(if_not_exists);
}
diff --git b/src/backend/parser/gram.y a/src/backend/parser/gram.y
index a3467f1c47..be7d35c816 100644
--- b/src/backend/parser/gram.y
+++ a/src/backend/parser/gram.y
@@ -3831,7 +3831,10 @@ ExistingIndex: USING INDEX index_name
{ $$ = $3; }
*
* QUERY :
* CREATE STATISTICS stats_name [USING
(stat_options)]
- * ON (columns) FROM relname
+ * [WITH (options)] ON (columns) FROM relname
+ *
+ * Note that currently there are no "options" implemented, so the WITH clause
+ * must always be omitted.
*
*****************************************************************************/
@@ -3861,24 +3864,26 @@ stat_option_elem:
CreateStatsStmt:
CREATE STATISTICS any_name opt_stat_option_list
- ON '(' columnList ')' FROM qualified_name
+ opt_reloptions ON '(' columnList ')' FROM qualified_name
{
CreateStatsStmt *n =
makeNode(CreateStatsStmt);
n->defnames = $3;
- n->relation = $10;
- n->keys = $7;
+ n->relation = $11;
+ n->keys = $8;
n->stat_options = $4;
+ n->options = $5;
n->if_not_exists = false;
$$ = (Node *)n;
}
| CREATE STATISTICS IF_P NOT EXISTS any_name
opt_stat_option_list
- ON '(' columnList ')' FROM qualified_name
+ opt_reloptions ON '(' columnList ')' FROM qualified_name
{
CreateStatsStmt *n =
makeNode(CreateStatsStmt);
n->defnames = $6;
- n->relation = $13;
- n->keys = $10;
+ n->relation = $14;
+ n->keys = $11;
n->stat_options = $7;
+ n->options = $8;
n->if_not_exists = true;
$$ = (Node *)n;
}
diff --git b/src/include/nodes/parsenodes.h a/src/include/nodes/parsenodes.h
index 0409f9d814..ab69c593cc 100644
--- b/src/include/nodes/parsenodes.h
+++ a/src/include/nodes/parsenodes.h
@@ -2692,6 +2692,7 @@ typedef struct CreateStatsStmt
RangeVar *relation; /* relation to build statistics on */
List *keys; /* String nodes naming
referenced columns */
List *stat_options; /* list of name-only DefElem for stat
types */
+ List *options; /* list of DefElem for general options
*/
bool if_not_exists; /* do nothing if statistics already
exists */
} CreateStatsStmt;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
