Tom Lane wrote:
> Alvaro Herrera <alvhe...@2ndquadrant.com> writes:
> > Tom Lane wrote:
> >> Have you thought further about the upthread suggestion to just borrow
> >> SELECT's syntax lock stock and barrel?
>
> > Bison seems to like the productions below. Is this what you had in
> > mind? These mostly mimic joined_table and table_ref, stripping out the
> > rules that we don't need.
>
> I'd suggest just using the from_list production and then complaining
> at runtime if what you get is too complicated. Otherwise, you have
> to maintain a duplicate set of productions, and you're going to be
> unable to throw anything more informative than "syntax error" when
> somebody tries to exceed the implementation limits.
Hmm, yeah, makes sense. Here's a patch for this approach. I ended up
using on ON again for the list of columns. I suppose the checks in
CreateStatistics() could still be improved, but I'd go ahead and push
this tomorrow morning and we can hammer those details later on, if it's
still needed. Better avoid shipping beta with outdated grammar ...
BTW the new castNode() family of macros don't work with Value nodes
(because the tags are different depending on what's stored, but each
type does not have its own struct. Oh well.)
--
Á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..32e17ee5f8 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -1132,8 +1132,8 @@ WHERE tablename = 'road';
To inspect functional dependencies on a statistics
<literal>stts</literal>, you may do this:
<programlisting>
-CREATE STATISTICS stts WITH (dependencies)
- ON (zip, city) FROM zipcodes;
+CREATE STATISTICS stts (dependencies)
+ ON zip, city FROM zipcodes;
ANALYZE zipcodes;
SELECT stxname, stxkeys, stxdependencies
FROM pg_statistic_ext
@@ -1219,8 +1219,8 @@ 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)
- ON (zip, state, city) FROM zipcodes;
+CREATE STATISTICS stts2 (ndistinct)
+ ON zip, state, city FROM zipcodes;
ANALYZE zipcodes;
SELECT stxkeys AS k, stxndistinct AS nd
FROM pg_statistic_ext
diff --git a/doc/src/sgml/planstats.sgml b/doc/src/sgml/planstats.sgml
index 11580bfd22..ef847b9633 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 (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 (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..84ff52df04 100644
--- a/doc/src/sgml/ref/create_statistics.sgml
+++ b/doc/src/sgml/ref/create_statistics.sgml
@@ -22,8 +22,8 @@ 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>] [, ... ] )
- ON ( <replaceable class="PARAMETER">column_name</replaceable>,
<replaceable class="PARAMETER">column_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>
@@ -75,6 +75,19 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable
class="PARAMETER">statistics_na
</varlistentry>
<varlistentry>
+ <term><replaceable class="PARAMETER">statistic_type</replaceable></term>
+ <listitem>
+ <para>
+ A statistic type to be enabled for this statistics. 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>
@@ -94,42 +107,6 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable
class="PARAMETER">statistics_na
</varlistentry>
</variablelist>
-
- <refsect2 id="SQL-CREATESTATISTICS-parameters">
- <title id="SQL-CREATESTATISTICS-parameters-title">Parameters</title>
-
- <indexterm zone="sql-createstatistics-parameters">
- <primary>statistics parameters</primary>
- </indexterm>
-
- <para>
- The <literal>WITH</> clause can specify <firstterm>options</>
- for the statistics. Available options are listed below.
- </para>
-
- <variablelist>
-
- <varlistentry>
- <term><literal>dependencies</> (<type>boolean</>)</term>
- <listitem>
- <para>
- Enables functional dependencies for the statistics.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>ndistinct</> (<type>boolean</>)</term>
- <listitem>
- <para>
- Enables ndistinct coefficients for the statistics.
- </para>
- </listitem>
- </varlistentry>
-
- </variablelist>
-
- </refsect2>
</refsect1>
<refsect1>
@@ -158,7 +135,7 @@ CREATE TABLE t1 (
INSERT INTO t1 SELECT i/100, i/500
FROM generate_series(1,1000000) s(i);
-CREATE STATISTICS s1 WITH (dependencies) ON (a, b) FROM t1;
+CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
ANALYZE t1;
diff --git a/src/backend/commands/statscmds.c b/src/backend/commands/statscmds.c
index 0b9c33e30a..220a3e92a2 100644
--- a/src/backend/commands/statscmds.c
+++ b/src/backend/commands/statscmds.c
@@ -63,7 +63,7 @@ CreateStatistics(CreateStatsStmt *stmt)
Relation rel;
Oid relid;
ObjectAddress parentobject,
- childobject;
+ childobject;
Datum types[2]; /* one for each possible type
of statistics */
int ntypes;
ArrayType *stxkind;
@@ -71,7 +71,7 @@ CreateStatistics(CreateStatsStmt *stmt)
bool build_dependencies;
bool requested_type = false;
int i;
- ListCell *l;
+ ListCell *cell;
Assert(IsA(stmt, CreateStatsStmt));
@@ -100,42 +100,78 @@ CreateStatistics(CreateStatsStmt *stmt)
errmsg("statistics \"%s\" already exist",
namestr)));
}
- /*
- * CREATE STATISTICS will influence future execution plans but does not
- * interfere with currently executing plans. So it should be enough to
- * take only ShareUpdateExclusiveLock on relation, conflicting with
- * ANALYZE and other DDL that sets statistical information, but not with
- * normal queries.
- */
- rel = relation_openrv(stmt->relation, ShareUpdateExclusiveLock);
- relid = RelationGetRelid(rel);
+ relid = InvalidOid;
- if (rel->rd_rel->relkind != RELKIND_RELATION &&
- rel->rd_rel->relkind != RELKIND_MATVIEW &&
- rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE &&
- rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+ if (list_length(stmt->relations) > 1)
ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("relation \"%s\" is not a table,
foreign table, or materialized view",
- RelationGetRelationName(rel))));
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cross-table statistics not supported
yet")));
+
+ foreach(cell, stmt->relations)
+ {
+ Node *rln = lfirst(cell);
+
+ if (!IsA(rln, RangeVar))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only a single relation is
allowed in CREATE STATISTICS")));
+
+ /*
+ * CREATE STATISTICS will influence future execution plans but
does not
+ * interfere with currently executing plans. So it should be
enough to
+ * take only ShareUpdateExclusiveLock on relation, conflicting
with
+ * ANALYZE and other DDL that sets statistical information, but
not with
+ * normal queries.
+ */
+ rel = relation_openrv(castNode(RangeVar, rln),
ShareUpdateExclusiveLock);
+ relid = RelationGetRelid(rel);
+
+ if (rel->rd_rel->relkind != RELKIND_RELATION &&
+ rel->rd_rel->relkind != RELKIND_MATVIEW &&
+ rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE &&
+ rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("relation \"%s\" is not a
table, foreign table, or materialized view",
+
RelationGetRelationName(rel))));
+ }
+ if (relid == InvalidOid)
+ elog(ERROR, "could not find OID for relation in statistic
object");
/*
* Transform column names to array of attnums. While at it, enforce some
* constraints.
*/
- foreach(l, stmt->keys)
+ foreach(cell, stmt->exprs)
{
- char *attname = strVal(lfirst(l));
+ Node *expr = (Node *) lfirst(cell);
+ ColumnRef *cref;
+ char *attname;
HeapTuple atttuple;
Form_pg_attribute attForm;
TypeCacheEntry *type;
+ if (!IsA(expr, ColumnRef))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("only simple column references
are allowed in %s",
+ "CREATE STATISTICS")));
+
+ cref = castNode(ColumnRef, expr);
+ if (list_length(cref->fields) > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("only simple column references
are allowed in %s",
+ "CREATE STATISTICS")));
+
+ attname = strVal((Value *) linitial(cref->fields));
+
atttuple = SearchSysCacheAttName(relid, attname);
if (!HeapTupleIsValid(atttuple))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("column \"%s\" referenced in statistics does
not exist",
- attname)));
+ errmsg("column \"%s\" referenced in
statistics does not exist",
+ attname)));
attForm = (Form_pg_attribute) GETSTRUCT(atttuple);
/* Disallow use of system attributes in extended stats */
@@ -188,7 +224,7 @@ CreateStatistics(CreateStatsStmt *stmt)
if (attnums[i] == attnums[i - 1])
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_COLUMN),
- errmsg("duplicate column name in statistics
definition")));
+ errmsg("duplicate column name in
statistics definition")));
/* Form an int2vector representation of the sorted column list */
stxkeys = buildint2vector(attnums, numcols);
@@ -199,25 +235,25 @@ CreateStatistics(CreateStatsStmt *stmt)
*/
build_ndistinct = false;
build_dependencies = false;
- foreach(l, stmt->options)
+ foreach(cell, stmt->stat_types)
{
- DefElem *opt = (DefElem *) lfirst(l);
+ char *type = strVal((Value *) lfirst(cell));
- if (strcmp(opt->defname, "ndistinct") == 0)
+ if (strcmp(type, "ndistinct") == 0)
{
- build_ndistinct = defGetBoolean(opt);
+ build_ndistinct = true;
requested_type = true;
}
- else if (strcmp(opt->defname, "dependencies") == 0)
+ else if (strcmp(type, "dependencies") == 0)
{
- build_dependencies = defGetBoolean(opt);
+ build_dependencies = true;
requested_type = true;
}
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("unrecognized STATISTICS option
\"%s\"",
- opt->defname)));
+ errmsg("unrecognized statistics type
\"%s\"",
+ type)));
}
/* If no statistic type was specified, build them all. */
if (!requested_type)
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 2d2a9d00b7..edad16cdb9 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3389,14 +3389,25 @@ _copyCreateStatsStmt(const CreateStatsStmt *from)
CreateStatsStmt *newnode = makeNode(CreateStatsStmt);
COPY_NODE_FIELD(defnames);
- COPY_NODE_FIELD(relation);
- COPY_NODE_FIELD(keys);
+ COPY_NODE_FIELD(relations);
+ COPY_NODE_FIELD(exprs);
COPY_NODE_FIELD(options);
COPY_SCALAR_FIELD(if_not_exists);
return newnode;
}
+static CreateStatsArgument *
+_copyCreateStatsArgument(const CreateStatsArgument *from)
+{
+ CreateStatsArgument *newnode = makeNode(CreateStatsArgument);
+
+ COPY_SCALAR_FIELD(subtype);
+ COPY_NODE_FIELD(elements);
+
+ return newnode;
+}
+
static CreateFunctionStmt *
_copyCreateFunctionStmt(const CreateFunctionStmt *from)
{
@@ -5121,6 +5132,9 @@ copyObjectImpl(const void *from)
case T_CreateStatsStmt:
retval = _copyCreateStatsStmt(from);
break;
+ case T_CreateStatsArgument:
+ retval = _copyCreateStatsArgument(from);
+ break;
case T_CreateFunctionStmt:
retval = _copyCreateFunctionStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index b5459cd726..abfe982d1e 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1349,8 +1349,8 @@ static bool
_equalCreateStatsStmt(const CreateStatsStmt *a, const CreateStatsStmt *b)
{
COMPARE_NODE_FIELD(defnames);
- COMPARE_NODE_FIELD(relation);
- COMPARE_NODE_FIELD(keys);
+ COMPARE_NODE_FIELD(relations);
+ COMPARE_NODE_FIELD(exprs);
COMPARE_NODE_FIELD(options);
COMPARE_SCALAR_FIELD(if_not_exists);
@@ -1358,6 +1358,15 @@ _equalCreateStatsStmt(const CreateStatsStmt *a, const
CreateStatsStmt *b)
}
static bool
+_equalCreateStatsArgument(const CreateStatsArgument *a, const
CreateStatsArgument *b)
+{
+ COMPARE_SCALAR_FIELD(subtype);
+ COMPARE_NODE_FIELD(elements);
+
+ return true;
+}
+
+static bool
_equalCreateFunctionStmt(const CreateFunctionStmt *a, const CreateFunctionStmt
*b)
{
COMPARE_SCALAR_FIELD(replace);
@@ -3270,6 +3279,9 @@ equal(const void *a, const void *b)
case T_CreateStatsStmt:
retval = _equalCreateStatsStmt(a, b);
break;
+ case T_CreateStatsArgument:
+ retval = _equalCreateStatsArgument(a, b);
+ break;
case T_CreateFunctionStmt:
retval = _equalCreateFunctionStmt(a, b);
break;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 98f67681a7..2334abfb19 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2639,13 +2639,22 @@ _outCreateStatsStmt(StringInfo str, const
CreateStatsStmt *node)
WRITE_NODE_TYPE("CREATESTATSSTMT");
WRITE_NODE_FIELD(defnames);
- WRITE_NODE_FIELD(relation);
- WRITE_NODE_FIELD(keys);
+ WRITE_NODE_FIELD(relations);
+ WRITE_NODE_FIELD(exprs);
WRITE_NODE_FIELD(options);
WRITE_BOOL_FIELD(if_not_exists);
}
static void
+_outCreateStatsArgument(StringInfo str, const CreateStatsArgument *node)
+{
+ WRITE_NODE_TYPE("CREATESTATSARG");
+
+ WRITE_INT_FIELD(subtype);
+ WRITE_NODE_FIELD(elements);
+}
+
+static void
_outNotifyStmt(StringInfo str, const NotifyStmt *node)
{
WRITE_NODE_TYPE("NOTIFY");
@@ -4051,6 +4060,9 @@ outNode(StringInfo str, const void *obj)
case T_CreateStatsStmt:
_outCreateStatsStmt(str, obj);
break;
+ case T_CreateStatsArgument:
+ _outCreateStatsArgument(str, obj);
+ break;
case T_NotifyStmt:
_outNotifyStmt(str, obj);
break;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 65c004c509..f59c38d29d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3836,31 +3836,24 @@ ExistingIndex: USING INDEX index_name
{ $$ = $3; }
/*****************************************************************************
*
* QUERY :
- * CREATE STATISTICS stats_name WITH (options) ON
(columns) FROM relname
+ * CREATE STATISTICS stats_name [(stat types)]
+ * ON expression-list FROM joined tables
*
*****************************************************************************/
+CreateStatsStmt:
+ CREATE opt_if_not_exists STATISTICS any_name
+ opt_name_list ON expr_list FROM from_list
+ {
+ CreateStatsStmt *n =
makeNode(CreateStatsStmt);
+ n->defnames = $4;
+ n->stat_types = $5;
+ n->if_not_exists = $2;
-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;
- }
+ n->relations = $9;
+ n->exprs = $7;
+ $$ = (Node *)n;
+ }
;
/*****************************************************************************
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index cbde1fff01..983b9800cc 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -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 the types clause
+ * to show which options are enabled. We omit the types 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, " (");
if (ndistinct_enabled)
appendStringInfoString(&buf, "ndistinct");
else if (dependencies_enabled)
@@ -1521,7 +1521,7 @@ pg_get_statisticsext_worker(Oid statextid, bool
missing_ok)
appendStringInfoChar(&buf, ')');
}
- appendStringInfoString(&buf, " ON (");
+ appendStringInfoString(&buf, " ON ");
for (colno = 0; colno < statextrec->stxkeys.dim1; colno++)
{
@@ -1536,7 +1536,7 @@ pg_get_statisticsext_worker(Oid statextid, bool
missing_ok)
appendStringInfoString(&buf, quote_identifier(attname));
}
- appendStringInfo(&buf, ") FROM %s",
+ appendStringInfo(&buf, " FROM %s",
generate_relation_name(statextrec->stxrelid, NIL));
ReleaseSysCache(statexttup);
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index ce0c9ef54d..f88832e7cb 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -4957,9 +4957,9 @@ qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL
WITH FUNCTION pg_catalog
catch_all => 'CREATE ... commands',
create_order => 97,
create_sql => 'CREATE STATISTICS
dump_test.test_ext_stats_no_options
- ON (col1, col2) FROM
dump_test.test_fifth_table',
+ ON col1, col2 FROM
dump_test.test_fifth_table',
regexp => qr/^
- \QCREATE STATISTICS dump_test.test_ext_stats_no_options
ON (col1, col2) FROM test_fifth_table;\E
+ \QCREATE STATISTICS dump_test.test_ext_stats_no_options
ON col1, col2 FROM test_fifth_table;\E
/xms,
like => {
binary_upgrade => 1,
@@ -4990,10 +4990,10 @@ qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL
WITH FUNCTION pg_catalog
all_runs => 1,
catch_all => 'CREATE ... commands',
create_order => 97,
- create_sql => 'CREATE STATISTICS
dump_test.test_ext_stats_using
- WITH (ndistinct) ON
(col1, col2) FROM dump_test.test_fifth_table',
+ create_sql => 'CREATE STATISTICS dump_test.test_ext_stats_opts
+ (ndistinct) ON col1,
col2 FROM dump_test.test_fifth_table',
regexp => qr/^
- \QCREATE STATISTICS dump_test.test_ext_stats_using WITH
(ndistinct) ON (col1, col2) FROM test_fifth_table;\E
+ \QCREATE STATISTICS dump_test.test_ext_stats_opts
(ndistinct) ON col1, col2 FROM test_fifth_table;\E
/xms,
like => {
binary_upgrade => 1,
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 13395f5ca6..386af6168f 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2355,8 +2355,9 @@ describeOneTableDetails(const char *schemaname,
{
printfPQExpBuffer(&buf,
"SELECT oid, "
+
"stxrelid::pg_catalog.regclass, "
"stxnamespace::pg_catalog.regnamespace AS nsp, "
- "stxname, stxkeys,\n"
+ "stxname,\n"
" (SELECT
pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')\n"
" FROM
pg_catalog.unnest(stxkeys) s(attnum)\n"
" JOIN
pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND\n"
@@ -2385,9 +2386,9 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf, " ");
/* statistics name (qualified with
namespace) */
- appendPQExpBuffer(&buf, "\"%s.%s\" WITH
(",
-
PQgetvalue(result, i, 1),
-
PQgetvalue(result, i, 2));
+ appendPQExpBuffer(&buf, "\"%s\".\"%s\"
(",
+
PQgetvalue(result, i, 2),
+
PQgetvalue(result, i, 3));
/* options */
if (strcmp(PQgetvalue(result, i, 5),
"t") == 0)
@@ -2401,8 +2402,9 @@ describeOneTableDetails(const char *schemaname,
appendPQExpBuffer(&buf,
"%sdependencies", gotone ? ", " : "");
}
- appendPQExpBuffer(&buf, ") ON (%s)",
-
PQgetvalue(result, i, 4));
+ appendPQExpBuffer(&buf, ") ON %s FROM
%s",
+
PQgetvalue(result, i, 4),
+
PQgetvalue(result, i, 1));
printTableAddFooter(&cont, buf.data);
}
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 3bd527700e..7853c259a5 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -453,6 +453,21 @@ static const SchemaQuery Query_for_list_of_foreign_tables
= {
NULL
};
+static const SchemaQuery Query_for_list_of_statistics = {
+ /* catname */
+ "pg_catalog.pg_statistic_ext s",
+ /* selcondition */
+ NULL,
+ /* viscondition */
+ NULL,
+ /* namespace */
+ "s.stxnamespace",
+ /* result */
+ "pg_catalog.quote_ident(s.stxname)",
+ /* qualresult */
+ NULL
+};
+
static const SchemaQuery Query_for_list_of_tables = {
/* catname */
"pg_catalog.pg_class c",
@@ -1023,6 +1038,7 @@ static const pgsql_thing_t words_after_create[] = {
{"SCHEMA", Query_for_list_of_schemas},
{"SEQUENCE", NULL, &Query_for_list_of_sequences},
{"SERVER", Query_for_list_of_servers},
+ {"STATISTICS", NULL, &Query_for_list_of_statistics},
{"SUBSCRIPTION", Query_for_list_of_subscriptions},
{"SYSTEM", NULL, NULL, THING_NO_CREATE | THING_NO_DROP},
{"TABLE", NULL, &Query_for_list_of_tables},
@@ -1783,6 +1799,10 @@ psql_completion(const char *text, int start, int end)
else if (Matches5("ALTER", "RULE", MatchAny, "ON", MatchAny))
COMPLETE_WITH_CONST("RENAME TO");
+ /* ALTER STATISTICS <name> */
+ else if (Matches3("ALTER", "STATISTICS", MatchAny))
+ COMPLETE_WITH_LIST3("OWNER TO", "RENAME TO", "SET SCHEMA");
+
/* ALTER TRIGGER <name>, add ON */
else if (Matches3("ALTER", "TRIGGER", MatchAny))
COMPLETE_WITH_CONST("ON");
@@ -2119,7 +2139,8 @@ psql_completion(const char *text, int start, int end)
{"ACCESS METHOD", "CAST", "COLLATION", "CONVERSION", "DATABASE",
"EVENT TRIGGER", "EXTENSION",
"FOREIGN DATA WRAPPER", "FOREIGN TABLE",
- "SERVER", "INDEX", "LANGUAGE", "POLICY", "PUBLICATION",
"RULE", "SCHEMA", "SEQUENCE", "SUBSCRIPTION",
+ "SERVER", "INDEX", "LANGUAGE", "POLICY", "PUBLICATION",
"RULE",
+ "SCHEMA", "SEQUENCE", "STATISTICS", "SUBSCRIPTION",
"TABLE", "TYPE", "VIEW", "MATERIALIZED VIEW", "COLUMN",
"AGGREGATE", "FUNCTION",
"OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN", "LARGE
OBJECT",
"TABLESPACE", "TEXT SEARCH", "ROLE", NULL};
@@ -2383,6 +2404,19 @@ psql_completion(const char *text, int start, int end)
else if (Matches3("CREATE", "SERVER", MatchAny))
COMPLETE_WITH_LIST3("TYPE", "VERSION", "FOREIGN DATA WRAPPER");
+/* CREATE STATISTICS <name> */
+ else if (Matches3("CREATE", "STATISTICS", MatchAny))
+ COMPLETE_WITH_LIST2("(", "ON");
+ else if (Matches4("CREATE", "STATISTICS", MatchAny, "("))
+ COMPLETE_WITH_LIST2("ndistinct", "dependencies");
+ else if (HeadMatches3("CREATE", "STATISTICS", MatchAny) &&
+ previous_words[0][0] == '(' &&
+ previous_words[0][strlen(previous_words[0]) - 1] ==
')')
+ COMPLETE_WITH_CONST("ON");
+ else if (HeadMatches3("CREATE", "STATISTICS", MatchAny) &&
+ TailMatches1("FROM"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
+
/* CREATE TABLE --- is allowed inside CREATE SCHEMA, so use TailMatches */
/* Complete "CREATE TEMP/TEMPORARY" with the possible temp objects */
else if (TailMatches2("CREATE", "TEMP|TEMPORARY"))
@@ -2589,7 +2623,7 @@ psql_completion(const char *text, int start, int end)
/* DROP */
/* Complete DROP object with CASCADE / RESTRICT */
else if (Matches3("DROP",
-
"COLLATION|CONVERSION|DOMAIN|EXTENSION|LANGUAGE|PUBLICATION|SCHEMA|SEQUENCE|SERVER|SUBSCRIPTION|TABLE|TYPE|VIEW",
+
"COLLATION|CONVERSION|DOMAIN|EXTENSION|LANGUAGE|PUBLICATION|SCHEMA|SEQUENCE|SERVER|SUBSCRIPTION|STATISTICS|TABLE|TYPE|VIEW",
MatchAny) ||
Matches4("DROP", "ACCESS", "METHOD", MatchAny) ||
(Matches4("DROP", "AGGREGATE|FUNCTION", MatchAny,
MatchAny) &&
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index f59d719923..2c4e86b9a3 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -462,6 +462,7 @@ typedef enum NodeTag
T_InferClause,
T_OnConflictClause,
T_CommonTableExpr,
+ T_CreateStatsArgument,
T_RoleSpec,
T_TriggerTransition,
T_PartitionElem,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 46c23c2530..a1c8aace58 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2689,12 +2689,26 @@ typedef struct CreateStatsStmt
{
NodeTag type;
List *defnames; /* qualified name (list of Value
strings) */
- RangeVar *relation; /* relation to build statistics on */
- List *keys; /* String nodes naming
referenced columns */
+ List *stat_types; /* stat types (list of Value strings) */
+ List *relations; /* relations to build statistics on */
+ List *exprs; /* Expressions to build
statistics on */
List *options; /* list of DefElem */
bool if_not_exists; /* do nothing if statistics already
exists */
} CreateStatsStmt;
+typedef enum CSA_Type
+{
+ CSA_Relations,
+ CSA_Expressions
+} CSA_Type;
+
+typedef struct CreateStatsArgument
+{
+ NodeTag type;
+ CSA_Type subtype;
+ List *elements; /* elements (list of Node) */
+} CreateStatsArgument;
+
/* ----------------------
* Create Function Statement
* ----------------------
diff --git a/src/test/regress/expected/alter_generic.out
b/src/test/regress/expected/alter_generic.out
index a81a4edfb2..0a00fcf806 100644
--- a/src/test/regress/expected/alter_generic.out
+++ b/src/test/regress/expected/alter_generic.out
@@ -501,8 +501,8 @@ DROP OPERATOR FAMILY alt_opf18 USING btree;
--
SET SESSION AUTHORIZATION regress_alter_user1;
CREATE TABLE alt_regress_1 (a INTEGER, b INTEGER);
-CREATE STATISTICS alt_stat1 ON (a, b) FROM alt_regress_1;
-CREATE STATISTICS alt_stat2 ON (a, b) FROM alt_regress_1;
+CREATE STATISTICS alt_stat1 ON a, b FROM alt_regress_1;
+CREATE STATISTICS alt_stat2 ON a, b FROM alt_regress_1;
ALTER STATISTICS alt_stat1 RENAME TO alt_stat2; -- failed (name conflict)
ERROR: statistics "alt_stat2" already exists in schema "alt_nsp1"
ALTER STATISTICS alt_stat1 RENAME TO alt_stat3; -- failed (name conflict)
@@ -511,8 +511,8 @@ ERROR: must be member of role "regress_alter_user2"
ALTER STATISTICS alt_stat2 OWNER TO regress_alter_user3; -- OK
ALTER STATISTICS alt_stat2 SET SCHEMA alt_nsp2; -- OK
SET SESSION AUTHORIZATION regress_alter_user2;
-CREATE STATISTICS alt_stat1 ON (a, b) FROM alt_regress_1;
-CREATE STATISTICS alt_stat2 ON (a, b) FROM alt_regress_1;
+CREATE STATISTICS alt_stat1 ON a, b FROM alt_regress_1;
+CREATE STATISTICS alt_stat2 ON a, b FROM alt_regress_1;
ALTER STATISTICS alt_stat3 RENAME TO alt_stat4; -- failed (not owner)
ERROR: must be owner of statistics alt_stat3
ALTER STATISTICS alt_stat1 RENAME TO alt_stat4; -- OK
diff --git a/src/test/regress/expected/object_address.out
b/src/test/regress/expected/object_address.out
index 40eeeed3d2..dce85084f3 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -39,7 +39,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (NOCONNECT,
SLOT NAME = NONE);
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION
... REFRESH PUBLICATION to subscribe the tables
-CREATE STATISTICS addr_nsp.gentable_stat ON (a,b) FROM addr_nsp.gentable;
+CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
-- test some error cases
SELECT pg_get_object_address('stone', '{}', '{}');
ERROR: unrecognized object type "stone"
diff --git a/src/test/regress/expected/stats_ext.out
b/src/test/regress/expected/stats_ext.out
index 92ac84ac67..4ccdf21a01 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -5,24 +5,49 @@
SET max_parallel_workers = 0;
SET max_parallel_workers_per_gather = 0;
SET work_mem = '128kB';
+-- Verify failures
+CREATE STATISTICS tst;
+ERROR: syntax error at or near ";"
+LINE 1: CREATE STATISTICS tst;
+ ^
+CREATE STATISTICS tst ON a, b;
+ERROR: syntax error at or near ";"
+LINE 1: CREATE STATISTICS tst ON a, b;
+ ^
+CREATE STATISTICS tst FROM sometab;
+ERROR: syntax error at or near "FROM"
+LINE 1: CREATE STATISTICS tst FROM sometab;
+ ^
+CREATE STATISTICS tst ON a, b FROM nonexistant;
+ERROR: relation "nonexistant" does not exist
+CREATE STATISTICS tst ON a, b FROM pg_class;
+ERROR: column "a" referenced in statistics does not exist
+CREATE STATISTICS tst ON relname, relname, relnatts FROM pg_class;
+ERROR: duplicate column name in statistics definition
+CREATE STATISTICS tst ON relnatts + relpages FROM pg_class;
+ERROR: only simple column references are allowed in CREATE STATISTICS
+CREATE STATISTICS tst ON (relpages, reltuples) FROM pg_class;
+ERROR: only simple column references are allowed in CREATE STATISTICS
+CREATE STATISTICS tst (unrecognized) ON relname, relnatts FROM pg_class;
+ERROR: unrecognized statistics type "unrecognized"
-- Ensure stats are dropped sanely
CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);
-CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
DROP STATISTICS ab1_a_b_stats;
CREATE SCHEMA regress_schema_2;
-CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1;
-- Let's also verify the pg_get_statisticsextdef output looks sane.
SELECT pg_get_statisticsextdef(oid) FROM pg_statistic_ext WHERE stxname =
'ab1_a_b_stats';
- pg_get_statisticsextdef
----------------------------------------------------------------------
- CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON (a, b) FROM ab1
+ pg_get_statisticsextdef
+-------------------------------------------------------------------
+ CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1
(1 row)
DROP STATISTICS regress_schema_2.ab1_a_b_stats;
-- Ensure statistics are dropped when columns are
-CREATE STATISTICS ab1_b_c_stats ON (b, c) FROM ab1;
-CREATE STATISTICS ab1_a_b_c_stats ON (a, b, c) FROM ab1;
-CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS ab1_b_c_stats ON b, c FROM ab1;
+CREATE STATISTICS ab1_a_b_c_stats ON a, b, c FROM ab1;
+CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ALTER TABLE ab1 DROP COLUMN a;
\d ab1
Table "public.ab1"
@@ -31,14 +56,14 @@ 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" (ndistinct, dependencies) ON b, c FROM ab1
DROP TABLE ab1;
-- Ensure things work sanely with SET STATISTICS 0
CREATE TABLE ab1 (a INTEGER, b INTEGER);
ALTER TABLE ab1 ALTER a SET STATISTICS 0;
INSERT INTO ab1 SELECT a, a%23 FROM generate_series(1, 1000) a;
-CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ANALYZE ab1;
WARNING: extended statistics "public.ab1_a_b_stats" could not be collected
for relation public.ab1
ALTER TABLE ab1 ALTER a SET STATISTICS -1;
@@ -60,24 +85,24 @@ CREATE SERVER extstats_dummy_srv FOREIGN DATA WRAPPER
extstats_dummy_fdw;
CREATE FOREIGN TABLE tststats.f (a int, b int, c text) SERVER
extstats_dummy_srv;
CREATE TABLE tststats.pt (a int, b int, c text) PARTITION BY RANGE (a, b);
CREATE TABLE tststats.pt1 PARTITION OF tststats.pt FOR VALUES FROM (-10, -10)
TO (10, 10);
-CREATE STATISTICS tststats.s1 ON (a, b) FROM tststats.t;
-CREATE STATISTICS tststats.s2 ON (a, b) FROM tststats.ti;
+CREATE STATISTICS tststats.s1 ON a, b FROM tststats.t;
+CREATE STATISTICS tststats.s2 ON a, b FROM tststats.ti;
ERROR: relation "ti" is not a table, foreign table, or materialized view
-CREATE STATISTICS tststats.s3 ON (a, b) FROM tststats.s;
+CREATE STATISTICS tststats.s3 ON a, b FROM tststats.s;
ERROR: relation "s" is not a table, foreign table, or materialized view
-CREATE STATISTICS tststats.s4 ON (a, b) FROM tststats.v;
+CREATE STATISTICS tststats.s4 ON a, b FROM tststats.v;
ERROR: relation "v" is not a table, foreign table, or materialized view
-CREATE STATISTICS tststats.s5 ON (a, b) FROM tststats.mv;
-CREATE STATISTICS tststats.s6 ON (a, b) FROM tststats.ty;
+CREATE STATISTICS tststats.s5 ON a, b FROM tststats.mv;
+CREATE STATISTICS tststats.s6 ON a, b FROM tststats.ty;
ERROR: relation "ty" is not a table, foreign table, or materialized view
-CREATE STATISTICS tststats.s7 ON (a, b) FROM tststats.f;
-CREATE STATISTICS tststats.s8 ON (a, b) FROM tststats.pt;
-CREATE STATISTICS tststats.s9 ON (a, b) FROM tststats.pt1;
+CREATE STATISTICS tststats.s7 ON a, b FROM tststats.f;
+CREATE STATISTICS tststats.s8 ON a, b FROM tststats.pt;
+CREATE STATISTICS tststats.s9 ON a, b FROM tststats.pt1;
DO $$
DECLARE
relname text := reltoastrelid::regclass FROM pg_class WHERE oid =
'tststats.t'::regclass;
BEGIN
- EXECUTE 'CREATE STATISTICS tststats.s10 ON (a, b) FROM ' || relname;
+ EXECUTE 'CREATE STATISTICS tststats.s10 ON a, b FROM ' || relname;
EXCEPTION WHEN wrong_object_type THEN
RAISE NOTICE 'stats on toast table not created';
END;
@@ -158,20 +183,8 @@ EXPLAIN (COSTS off)
-> Seq Scan on ndistinct
(5 rows)
--- unknown column
-CREATE STATISTICS s10 ON (unknown_column) FROM ndistinct;
-ERROR: column "unknown_column" referenced in statistics does not exist
--- single column
-CREATE STATISTICS s10 ON (a) FROM ndistinct;
-ERROR: extended statistics require at least 2 columns
--- single column, duplicated
-CREATE STATISTICS s10 ON (a,a) FROM ndistinct;
-ERROR: duplicate column name in statistics definition
--- two columns, one duplicated
-CREATE STATISTICS s10 ON (a, a, b) FROM ndistinct;
-ERROR: duplicate column name in statistics definition
-- correct command
-CREATE STATISTICS s10 ON (a, b, c) FROM ndistinct;
+CREATE STATISTICS s10 ON a, b, c FROM ndistinct;
ANALYZE ndistinct;
SELECT stxkind, stxndistinct
FROM pg_statistic_ext WHERE stxrelid = 'ndistinct'::regclass;
@@ -352,7 +365,6 @@ EXPLAIN (COSTS off)
-> Seq Scan on ndistinct
(3 rows)
-DROP TABLE ndistinct;
-- functional dependencies tests
CREATE TABLE functional_dependencies (
filler1 TEXT,
@@ -389,7 +401,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 (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 +444,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 (dependencies) ON a, b, c FROM
functional_dependencies;
ANALYZE functional_dependencies;
EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1';
@@ -456,4 +468,3 @@ EXPLAIN (COSTS OFF)
(5 rows)
RESET random_page_cost;
-DROP TABLE functional_dependencies;
diff --git a/src/test/regress/sql/alter_generic.sql
b/src/test/regress/sql/alter_generic.sql
index 88e8d7eb86..8d1cb55a9f 100644
--- a/src/test/regress/sql/alter_generic.sql
+++ b/src/test/regress/sql/alter_generic.sql
@@ -438,8 +438,8 @@ DROP OPERATOR FAMILY alt_opf18 USING btree;
--
SET SESSION AUTHORIZATION regress_alter_user1;
CREATE TABLE alt_regress_1 (a INTEGER, b INTEGER);
-CREATE STATISTICS alt_stat1 ON (a, b) FROM alt_regress_1;
-CREATE STATISTICS alt_stat2 ON (a, b) FROM alt_regress_1;
+CREATE STATISTICS alt_stat1 ON a, b FROM alt_regress_1;
+CREATE STATISTICS alt_stat2 ON a, b FROM alt_regress_1;
ALTER STATISTICS alt_stat1 RENAME TO alt_stat2; -- failed (name conflict)
ALTER STATISTICS alt_stat1 RENAME TO alt_stat3; -- failed (name conflict)
@@ -448,8 +448,8 @@ ALTER STATISTICS alt_stat2 OWNER TO regress_alter_user3;
-- OK
ALTER STATISTICS alt_stat2 SET SCHEMA alt_nsp2; -- OK
SET SESSION AUTHORIZATION regress_alter_user2;
-CREATE STATISTICS alt_stat1 ON (a, b) FROM alt_regress_1;
-CREATE STATISTICS alt_stat2 ON (a, b) FROM alt_regress_1;
+CREATE STATISTICS alt_stat1 ON a, b FROM alt_regress_1;
+CREATE STATISTICS alt_stat2 ON a, b FROM alt_regress_1;
ALTER STATISTICS alt_stat3 RENAME TO alt_stat4; -- failed (not owner)
ALTER STATISTICS alt_stat1 RENAME TO alt_stat4; -- OK
diff --git a/src/test/regress/sql/object_address.sql
b/src/test/regress/sql/object_address.sql
index 6940392c01..70f5f1c1af 100644
--- a/src/test/regress/sql/object_address.sql
+++ b/src/test/regress/sql/object_address.sql
@@ -41,7 +41,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
TO SQL WITH FUNCTION int4recv(internal));
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (NOCONNECT,
SLOT NAME = NONE);
-CREATE STATISTICS addr_nsp.gentable_stat ON (a,b) FROM addr_nsp.gentable;
+CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
-- test some error cases
SELECT pg_get_object_address('stone', '{}', '{}');
diff --git a/src/test/regress/sql/stats_ext.sql
b/src/test/regress/sql/stats_ext.sql
index 72c7659c4b..4050f33c08 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -7,13 +7,24 @@ SET max_parallel_workers = 0;
SET max_parallel_workers_per_gather = 0;
SET work_mem = '128kB';
+-- Verify failures
+CREATE STATISTICS tst;
+CREATE STATISTICS tst ON a, b;
+CREATE STATISTICS tst FROM sometab;
+CREATE STATISTICS tst ON a, b FROM nonexistant;
+CREATE STATISTICS tst ON a, b FROM pg_class;
+CREATE STATISTICS tst ON relname, relname, relnatts FROM pg_class;
+CREATE STATISTICS tst ON relnatts + relpages FROM pg_class;
+CREATE STATISTICS tst ON (relpages, reltuples) FROM pg_class;
+CREATE STATISTICS tst (unrecognized) ON relname, relnatts FROM pg_class;
+
-- Ensure stats are dropped sanely
CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);
-CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
DROP STATISTICS ab1_a_b_stats;
CREATE SCHEMA regress_schema_2;
-CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1;
-- Let's also verify the pg_get_statisticsextdef output looks sane.
SELECT pg_get_statisticsextdef(oid) FROM pg_statistic_ext WHERE stxname =
'ab1_a_b_stats';
@@ -21,9 +32,9 @@ SELECT pg_get_statisticsextdef(oid) FROM pg_statistic_ext
WHERE stxname = 'ab1_a
DROP STATISTICS regress_schema_2.ab1_a_b_stats;
-- Ensure statistics are dropped when columns are
-CREATE STATISTICS ab1_b_c_stats ON (b, c) FROM ab1;
-CREATE STATISTICS ab1_a_b_c_stats ON (a, b, c) FROM ab1;
-CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS ab1_b_c_stats ON b, c FROM ab1;
+CREATE STATISTICS ab1_a_b_c_stats ON a, b, c FROM ab1;
+CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ALTER TABLE ab1 DROP COLUMN a;
\d ab1
DROP TABLE ab1;
@@ -32,7 +43,7 @@ DROP TABLE ab1;
CREATE TABLE ab1 (a INTEGER, b INTEGER);
ALTER TABLE ab1 ALTER a SET STATISTICS 0;
INSERT INTO ab1 SELECT a, a%23 FROM generate_series(1, 1000) a;
-CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ANALYZE ab1;
ALTER TABLE ab1 ALTER a SET STATISTICS -1;
-- partial analyze doesn't build stats either
@@ -55,20 +66,20 @@ CREATE FOREIGN TABLE tststats.f (a int, b int, c text)
SERVER extstats_dummy_srv
CREATE TABLE tststats.pt (a int, b int, c text) PARTITION BY RANGE (a, b);
CREATE TABLE tststats.pt1 PARTITION OF tststats.pt FOR VALUES FROM (-10, -10)
TO (10, 10);
-CREATE STATISTICS tststats.s1 ON (a, b) FROM tststats.t;
-CREATE STATISTICS tststats.s2 ON (a, b) FROM tststats.ti;
-CREATE STATISTICS tststats.s3 ON (a, b) FROM tststats.s;
-CREATE STATISTICS tststats.s4 ON (a, b) FROM tststats.v;
-CREATE STATISTICS tststats.s5 ON (a, b) FROM tststats.mv;
-CREATE STATISTICS tststats.s6 ON (a, b) FROM tststats.ty;
-CREATE STATISTICS tststats.s7 ON (a, b) FROM tststats.f;
-CREATE STATISTICS tststats.s8 ON (a, b) FROM tststats.pt;
-CREATE STATISTICS tststats.s9 ON (a, b) FROM tststats.pt1;
+CREATE STATISTICS tststats.s1 ON a, b FROM tststats.t;
+CREATE STATISTICS tststats.s2 ON a, b FROM tststats.ti;
+CREATE STATISTICS tststats.s3 ON a, b FROM tststats.s;
+CREATE STATISTICS tststats.s4 ON a, b FROM tststats.v;
+CREATE STATISTICS tststats.s5 ON a, b FROM tststats.mv;
+CREATE STATISTICS tststats.s6 ON a, b FROM tststats.ty;
+CREATE STATISTICS tststats.s7 ON a, b FROM tststats.f;
+CREATE STATISTICS tststats.s8 ON a, b FROM tststats.pt;
+CREATE STATISTICS tststats.s9 ON a, b FROM tststats.pt1;
DO $$
DECLARE
relname text := reltoastrelid::regclass FROM pg_class WHERE oid =
'tststats.t'::regclass;
BEGIN
- EXECUTE 'CREATE STATISTICS tststats.s10 ON (a, b) FROM ' || relname;
+ EXECUTE 'CREATE STATISTICS tststats.s10 ON a, b FROM ' || relname;
EXCEPTION WHEN wrong_object_type THEN
RAISE NOTICE 'stats on toast table not created';
END;
@@ -113,20 +124,8 @@ EXPLAIN (COSTS off)
EXPLAIN (COSTS off)
SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
--- unknown column
-CREATE STATISTICS s10 ON (unknown_column) FROM ndistinct;
-
--- single column
-CREATE STATISTICS s10 ON (a) FROM ndistinct;
-
--- single column, duplicated
-CREATE STATISTICS s10 ON (a,a) FROM ndistinct;
-
--- two columns, one duplicated
-CREATE STATISTICS s10 ON (a, a, b) FROM ndistinct;
-
-- correct command
-CREATE STATISTICS s10 ON (a, b, c) FROM ndistinct;
+CREATE STATISTICS s10 ON a, b, c FROM ndistinct;
ANALYZE ndistinct;
@@ -202,8 +201,6 @@ EXPLAIN (COSTS off)
EXPLAIN (COSTS off)
SELECT COUNT(*) FROM ndistinct GROUP BY a, d;
-DROP TABLE ndistinct;
-
-- functional dependencies tests
CREATE TABLE functional_dependencies (
filler1 TEXT,
@@ -233,7 +230,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 (dependencies) ON a, b, c FROM
functional_dependencies;
ANALYZE functional_dependencies;
@@ -259,7 +256,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 (dependencies) ON a, b, c FROM
functional_dependencies;
ANALYZE functional_dependencies;
@@ -270,4 +267,3 @@ EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1;
RESET random_page_cost;
-DROP TABLE functional_dependencies;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
