Here's a patch implementing this idea. From gram.y's comment, the
support syntax is now:
/*****************************************************************************
*
* QUERY :
! * CREATE STATISTICS stats_name [(stat types)] arguments
!
! * where 'arguments' can be one or more of:
! * { ON (columns)
! * | FROM relations
! * | WITH (options)
! * | WHERE expression }
Note that I removed the USING keyword in the stat types list, and also
made it mandatory that that list appears immediately after the new stats
name. This should make it possible to have USING in the relation list
(the FROM clause), if we allow explicit multiple relations with join
syntax there. The other options can appear in any order.
Also, both WITH and WHERE are accepted by the grammar, but immediately
throw "feature not implemented" error at parse time.
I was on the fence about adding copy/equal/out support for the new
StatisticArgument node; it seems pointless because that node does not
leave gram.y anyway.
Unless there are objections, I'll push this tomorrow.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
*** a/doc/src/sgml/perform.sgml
--- b/doc/src/sgml/perform.sgml
***************
*** 1132,1138 **** 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;
ANALYZE zipcodes;
SELECT stxname, stxkeys, stxdependencies
--- 1132,1138 ----
To inspect functional dependencies on a statistics
<literal>stts</literal>, you may do this:
<programlisting>
! CREATE STATISTICS stts (dependencies)
ON (zip, city) FROM zipcodes;
ANALYZE zipcodes;
SELECT stxname, stxkeys, stxdependencies
***************
*** 1219,1225 **** 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;
ANALYZE zipcodes;
SELECT stxkeys AS k, stxndistinct AS nd
--- 1219,1225 ----
Continuing the above example, the n-distinct coefficients in a ZIP
code table may look like the following:
<programlisting>
! CREATE STATISTICS stts2 (ndistinct)
ON (zip, state, city) FROM zipcodes;
ANALYZE zipcodes;
SELECT stxkeys AS k, stxndistinct AS nd
*** a/doc/src/sgml/planstats.sgml
--- b/doc/src/sgml/planstats.sgml
***************
*** 526,532 **** 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;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
QUERY PLAN
--- 526,532 ----
multivariate statistics on the two columns:
<programlisting>
! 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,575 **** 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;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
QUERY PLAN
--- 569,575 ----
calculation, the estimate is much improved:
<programlisting>
DROP STATISTICS stts;
! 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
*** a/doc/src/sgml/ref/create_statistics.sgml
--- b/doc/src/sgml/ref/create_statistics.sgml
***************
*** 22,28 **** 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> [, ...])
FROM <replaceable class="PARAMETER">table_name</replaceable>
</synopsis>
--- 22,28 ----
<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>
***************
*** 75,80 **** CREATE STATISTICS [ IF NOT EXISTS ] <replaceable
class="PARAMETER">statistics_na
--- 75,93 ----
</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,135 **** 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>
--- 107,112 ----
*** a/src/backend/commands/statscmds.c
--- b/src/backend/commands/statscmds.c
***************
*** 199,223 **** CreateStatistics(CreateStatsStmt *stmt)
*/
build_ndistinct = false;
build_dependencies = false;
! foreach(l, stmt->options)
{
! DefElem *opt = (DefElem *) lfirst(l);
! if (strcmp(opt->defname, "ndistinct") == 0)
{
! build_ndistinct = defGetBoolean(opt);
requested_type = true;
}
! else if (strcmp(opt->defname, "dependencies") == 0)
{
! build_dependencies = defGetBoolean(opt);
requested_type = true;
}
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("unrecognized STATISTICS option
\"%s\"",
! opt->defname)));
}
/* If no statistic type was specified, build them all. */
if (!requested_type)
--- 199,223 ----
*/
build_ndistinct = false;
build_dependencies = false;
! foreach(l, stmt->stat_types)
{
! char *type = strVal((Value *) lfirst(l));
! if (strcmp(type, "ndistinct") == 0)
{
! build_ndistinct = true;
requested_type = true;
}
! else if (strcmp(type, "dependencies") == 0)
{
! build_dependencies = true;
requested_type = true;
}
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("unrecognized statistics type
\"%s\"",
! type)));
}
/* If no statistic type was specified, build them all. */
if (!requested_type)
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 3397,3402 **** _copyCreateStatsStmt(const CreateStatsStmt *from)
--- 3397,3413 ----
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,5126 **** copyObjectImpl(const void *from)
--- 5132,5140 ----
case T_CreateStatsStmt:
retval = _copyCreateStatsStmt(from);
break;
+ case T_CreateStatsArgument:
+ retval = _copyCreateStatsArgument(from);
+ break;
case T_CreateFunctionStmt:
retval = _copyCreateFunctionStmt(from);
break;
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 1358,1363 **** _equalCreateStatsStmt(const CreateStatsStmt *a, const
CreateStatsStmt *b)
--- 1358,1372 ----
}
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,3275 **** equal(const void *a, const void *b)
--- 3279,3287 ----
case T_CreateStatsStmt:
retval = _equalCreateStatsStmt(a, b);
break;
+ case T_CreateStatsArgument:
+ retval = _equalCreateStatsArgument(a, b);
+ break;
case T_CreateFunctionStmt:
retval = _equalCreateFunctionStmt(a, b);
break;
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 2646,2651 **** _outCreateStatsStmt(StringInfo str, const CreateStatsStmt
*node)
--- 2646,2660 ----
}
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,4056 **** outNode(StringInfo str, const void *obj)
--- 4060,4068 ----
case T_CreateStatsStmt:
_outCreateStatsStmt(str, obj);
break;
+ case T_CreateStatsArgument:
+ _outCreateStatsArgument(str, obj);
+ break;
case T_NotifyStmt:
_outNotifyStmt(str, obj);
break;
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 183,188 **** static RangeVar *makeRangeVarFromAnyName(List *names, int
position, core_yyscan_
--- 183,189 ----
static void SplitColQualList(List *qualList,
List **constraintList,
CollateClause **collClause,
core_yyscan_t
yyscanner);
+ static void SplitStatsArgList(CreateStatsStmt *stmt, List *arguments);
static void processCASbits(int cas_bits, int location, const char *constrType,
bool *deferrable, bool *initdeferred, bool
*not_valid,
bool *no_inherit, core_yyscan_t yyscanner);
***************
*** 236,241 **** static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
--- 237,243 ----
AccessPriv *accesspriv;
struct ImportQual *importqual;
InsertStmt *istmt;
+ CreateStatsArgument *cstatarg;
VariableSetStmt *vsetstmt;
PartitionElem *partelem;
PartitionSpec *partspec;
***************
*** 397,402 **** static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
--- 399,406 ----
transform_element_list transform_type_list
TriggerTransitions TriggerReferencing
publication_name_list
+ opt_stats_type_list stats_type_list
StatisticArgList
+ %type <cstatarg> StatisticArgument
%type <list> group_by_list
%type <node> group_by_item empty_grouping_set rollup_clause cube_clause
***************
*** 3828,3860 **** ExistingIndex: USING INDEX index_name
{ $$ = $3; }
/*****************************************************************************
*
* QUERY :
! * CREATE STATISTICS stats_name WITH (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;
! }
;
/*****************************************************************************
*
* QUERY :
--- 3832,3905 ----
/*****************************************************************************
*
* QUERY :
! * CREATE STATISTICS stats_name [(stat types)]
arguments
!
! * where 'arguments' can be one or more of:
! * { ON (columns)
! * | FROM relations
! * | WITH (options)
! * | WHERE expression }
*
*****************************************************************************/
+ CreateStatsStmt:
+ CREATE opt_if_not_exists STATISTICS any_name
+ opt_stats_type_list StatisticArgList
+ {
+ CreateStatsStmt *n =
makeNode(CreateStatsStmt);
+ n->defnames = $4;
+ n->stat_types = $5;
+ n->if_not_exists = $2;
! SplitStatsArgList(n, $6);
! $$ = (Node *)n;
! }
;
+ opt_stats_type_list:
+ '(' stats_type_list ')' { $$ = $2; }
+ | /* EMPTY */ { $$ =
NULL; }
+ ;
+
+ stats_type_list:
+ ColId
{ $$ = list_make1(makeString($1)); }
+ | stats_type_list ',' ColId { $$ =
lappend($1, makeString($3)); }
+ ;
+
+ StatisticArgList:
+ StatisticArgument
{ $$ = list_make1($1); }
+ | StatisticArgList StatisticArgument { $$ =
lappend($1, $2); }
+ ;
+
+ StatisticArgument:
+ ON '(' name_list ')'
+ {
+ CreateStatsArgument *n =
makeNode(CreateStatsArgument);
+ n->subtype = CSA_Expressions;
+ n->elements = $3;
+ $$ = n;
+ }
+ | FROM qualified_name_list
+ {
+ CreateStatsArgument *n =
makeNode(CreateStatsArgument);
+ n->subtype = CSA_Relations;
+ n->elements = $2;
+ $$ = n;
+ }
+ | WITH reloptions
+ {
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("WITH clause is
not yet implemented")));
+ }
+ | WHERE a_expr
+ {
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("WHERE clause
is not yet implemented")));
+ }
+ ;
+
/*****************************************************************************
*
* QUERY :
***************
*** 15872,15877 **** processCASbits(int cas_bits, int location, const char
*constrType,
--- 15917,15969 ----
}
}
+ /*
+ * Split out CREATE STATISTICS arguments.
+ */
+ static void
+ SplitStatsArgList(CreateStatsStmt *stmt, List *arguments)
+ {
+ ListCell *cell;
+
+ foreach(cell, arguments)
+ {
+ CreateStatsArgument *n = lfirst_node(CreateStatsArgument, cell);
+
+ switch (n->subtype)
+ {
+ case CSA_Relations:
+ if (stmt->relation)
+ ereport(ERROR,
+
(errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("redundant or
conflicting FROM clauses")));
+ if (list_length(n->elements) > 1)
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("statistics
across multiple relations are not supported yet")));
+ stmt->relation = linitial_node(RangeVar,
n->elements);
+ break;
+ case CSA_Expressions:
+ if (stmt->keys)
+ ereport(ERROR,
+
(errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("redundant or
conflicting ON clauses")));
+ stmt->keys = n->elements;
+ break;
+ default:
+ elog(ERROR, "unsupported node type %d",
n->subtype);
+ }
+ }
+
+ if (!stmt->relation)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("missing FROM clause")));
+ if (!stmt->keys)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("missing ON clause")));
+ }
+
/*----------
* Recursive view transformation
*
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 1504,1518 **** 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
* 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 (");
if (ndistinct_enabled)
appendStringInfoString(&buf, "ndistinct");
else if (dependencies_enabled)
--- 1504,1518 ----
}
/*
! * If any option is disabled, then we'll need to append the types clause
! * to show which options are enabled. We omit the WITH 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, " (");
if (ndistinct_enabled)
appendStringInfoString(&buf, "ndistinct");
else if (dependencies_enabled)
*** a/src/bin/pg_dump/t/002_pg_dump.pl
--- b/src/bin/pg_dump/t/002_pg_dump.pl
***************
*** 4958,4967 **** 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',
regexp => qr/^
! \QCREATE STATISTICS dump_test.test_ext_stats_using WITH
(ndistinct) ON (col1, col2) FROM test_fifth_table;\E
/xms,
like => {
binary_upgrade => 1,
--- 4958,4967 ----
all_runs => 1,
catch_all => 'CREATE ... commands',
create_order => 97,
! 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_opts
(ndistinct) ON (col1, col2) FROM test_fifth_table;\E
/xms,
like => {
binary_upgrade => 1,
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***************
*** 2385,2391 **** describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf, " ");
/* statistics name (qualified with
namespace) */
! appendPQExpBuffer(&buf, "\"%s.%s\" WITH
(",
PQgetvalue(result, i, 1),
PQgetvalue(result, i, 2));
--- 2385,2391 ----
printfPQExpBuffer(&buf, " ");
/* statistics name (qualified with
namespace) */
! appendPQExpBuffer(&buf, "\"%s.%s\" (",
PQgetvalue(result, i, 1),
PQgetvalue(result, i, 2));
*** a/src/include/nodes/nodes.h
--- b/src/include/nodes/nodes.h
***************
*** 462,467 **** typedef enum NodeTag
--- 462,468 ----
T_InferClause,
T_OnConflictClause,
T_CommonTableExpr,
+ T_CreateStatsArgument,
T_RoleSpec,
T_TriggerTransition,
T_PartitionElem,
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 2689,2700 **** typedef struct CreateStatsStmt
--- 2689,2714 ----
{
NodeTag type;
List *defnames; /* qualified name (list of Value
strings) */
+ List *stat_types; /* stat types (list of Value strings) */
RangeVar *relation; /* relation to build statistics on */
List *keys; /* String nodes naming
referenced columns */
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
* ----------------------
*** a/src/test/regress/expected/stats_ext.out
--- b/src/test/regress/expected/stats_ext.out
***************
*** 5,10 ****
--- 5,35 ----
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: missing FROM clause
+ 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: missing ON clause
+ CREATE STATISTICS tst FROM sometab, othertab;
+ ERROR: statistics across multiple relations are not supported yet
+ CREATE STATISTICS tst WITH (fillfactor = 80);
+ ERROR: WITH clause is not yet implemented
+ CREATE STATISTICS tst WHERE mars > earth;
+ ERROR: WHERE clause is not yet implemented
+ 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 (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;
***************
*** 31,37 **** ALTER TABLE ab1 DROP COLUMN a;
b | integer | | |
c | integer | | |
Statistics:
! "public.ab1_b_c_stats" WITH (ndistinct, dependencies) ON (b, c)
DROP TABLE ab1;
-- Ensure things work sanely with SET STATISTICS 0
--- 56,62 ----
b | integer | | |
c | integer | | |
Statistics:
! "public.ab1_b_c_stats" (ndistinct, dependencies) ON (b, c)
DROP TABLE ab1;
-- Ensure things work sanely with SET STATISTICS 0
***************
*** 389,395 **** EXPLAIN (COSTS OFF)
(2 rows)
-- create statistics
! CREATE STATISTICS func_deps_stat WITH (dependencies) ON (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1';
--- 414,420 ----
(2 rows)
-- create statistics
! 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,438 **** EXPLAIN (COSTS OFF)
(2 rows)
-- create statistics
! CREATE STATISTICS func_deps_stat WITH (dependencies) ON (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1';
--- 457,463 ----
(2 rows)
-- create statistics
! 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';
*** a/src/test/regress/sql/stats_ext.sql
--- b/src/test/regress/sql/stats_ext.sql
***************
*** 7,12 **** SET max_parallel_workers = 0;
--- 7,24 ----
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 ON (a + b);
+ CREATE STATISTICS tst FROM sometab;
+ CREATE STATISTICS tst FROM sometab, othertab;
+ CREATE STATISTICS tst WITH (fillfactor = 80);
+ CREATE STATISTICS tst WHERE mars > earth;
+ CREATE STATISTICS tst ON (a, b) FROM nonexistant;
+ CREATE STATISTICS tst ON (a, b) 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;
***************
*** 233,239 **** 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;
ANALYZE functional_dependencies;
--- 245,251 ----
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1;
-- create statistics
! CREATE STATISTICS func_deps_stat (dependencies) ON (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
***************
*** 259,265 **** 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;
ANALYZE functional_dependencies;
--- 271,277 ----
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1;
-- create statistics
! CREATE STATISTICS func_deps_stat (dependencies) ON (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
