On Fri, Jul 19, 2019 at 06:12:20AM +0000, Jamison, Kirk wrote:
On Tuesday, July 9, 2019, Tomas Vondra wrote:
>apparently v1 of the ALTER STATISTICS patch was a bit confused about
>length of the VacAttrStats array passed to statext_compute_stattarget,
>causing segfaults. Attached v2 patch fixes that, and it also makes sure
>we print warnings about ignored statistics only once.
>
v3 of the patch, adding pg_dump support - it works just like when you tweak
statistics target for a column, for example. When the value stored in the
catalog is not -1, pg_dump emits a separate ALTER STATISTICS command setting
it (for the already created statistics object).
Hi Tomas, I stumbled upon your patch.
According to the CF bot, your patch applies cleanly, builds successfully, and
passes make world. Meaning, the pg_dump tap test passed, but there was no
test for the new SET STATISTICS yet. So you might want to add a regression
test for that and integrate it in the existing alter_generic file.
Upon quick read-through, the syntax and docs are correct because it's similar
to the format of ALTER TABLE/INDEX... SET STATISTICS... :
ALTER [ COLUMN ] column_name SET STATISTICS integer
+ /* XXX What if the target is set to 0? Reset the statistic? */
This also makes me wonder. I haven't looked deeply into the code, but since 0 is
a valid value, I believe it should reset the stats.
I agree resetting the stats after setting the target to 0 seems quite
reasonable. But that's not what we do for attribute stats, because in
that case we simply skip the attribute during the future ANALYZE runs -
we don't reset the stats, we keep the existing stats. So I've done the
same thing here, and I've removed the XXX comment.
If we want to change that, I'd do it in a separate patch for both the
regular and extended stats.
After lookup though, this is how it's tested in ALTER TABLE:
/test/regress/sql/stats_ext.sql:-- Ensure things work sanely with SET
STATISTICS 0
Well, yeah. But that tests we skip building the extended statistic
(because we excluded the column from the ANALYZE run).
I've considered making it part of CREATE STATISTICS itself, but it seems a
bit cumbersome and we don't do it for columns either. I'm not against adding
it in the future, but at this point I don't see a need.
I agree. Perhaps that's for another patch should you decide to add it in the
future.
Right.
Attached is v4 of the patch, with a couple more improvements:
1) I've renamed the if_not_exists flag to missing_ok, because that's
more consistent with the "IF EXISTS" clause in the grammar (the old flag
was kinda the exact opposite), and I've added a NOTICE about the skip.
2) I've renamed ComputeExtStatsTarget to ComputeExtStatsRows, because
that's what the function was doing anyway (computing sample size).
3) I've added a couple of regression tests to stats_ext.sql
Aside from that, I've cleaned up a couple of places and improved a bunch
of comments. Nothing huge.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/doc/src/sgml/ref/alter_statistics.sgml
b/doc/src/sgml/ref/alter_statistics.sgml
index 58c7ed020d..987f9dbc6f 100644
--- a/doc/src/sgml/ref/alter_statistics.sgml
+++ b/doc/src/sgml/ref/alter_statistics.sgml
@@ -26,6 +26,7 @@ PostgreSQL documentation
ALTER STATISTICS <replaceable class="parameter">name</replaceable> OWNER TO {
<replaceable class="parameter">new_owner</replaceable> | CURRENT_USER |
SESSION_USER }
ALTER STATISTICS <replaceable class="parameter">name</replaceable> RENAME TO
<replaceable class="parameter">new_name</replaceable>
ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA
<replaceable class="parameter">new_schema</replaceable>
+ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET
STATISTICS <replaceable class="parameter">new_target</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -93,6 +94,22 @@ ALTER STATISTICS <replaceable
class="parameter">name</replaceable> SET SCHEMA <r
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">new_target</replaceable></term>
+ <listitem>
+ <para>
+ The statistic-gathering target for this statistic object for subsequent
+ <xref linkend="sql-analyze"/> operations.
+ The target can be set in the range 0 to 10000; alternatively, set it
+ to -1 to revert to using the system default statistics
+ target (<xref linkend="guc-default-statistics-target"/>).
+ For more information on the use of statistics by the
+ <productname>PostgreSQL</productname> query planner, refer to
+ <xref linkend="planner-stats"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
</refsect1>
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 8d633f2585..edbd24f5e9 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -307,7 +307,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
VacAttrStats **vacattrstats;
AnlIndexData *indexdata;
int targrows,
- numrows;
+ numrows,
+ minrows;
double totalrows,
totaldeadrows;
HeapTuple *rows;
@@ -491,6 +492,16 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
}
}
+ /*
+ * Look at extended statistic objects too, as those may define custom
+ * statistic target. So we may need to sample more rows and then build
+ * the statistics with enough detail.
+ */
+ minrows = ComputeExtStatisticsRows(onerel, attr_cnt, vacattrstats);
+
+ if (targrows < minrows)
+ targrows = minrows;
+
/*
* Acquire the sample rows
*/
diff --git a/src/backend/commands/statscmds.c b/src/backend/commands/statscmds.c
index cf406f6f96..35bb08949b 100644
--- a/src/backend/commands/statscmds.c
+++ b/src/backend/commands/statscmds.c
@@ -14,6 +14,7 @@
*/
#include "postgres.h"
+#include "access/heapam.h"
#include "access/relation.h"
#include "access/relscan.h"
#include "access/table.h"
@@ -21,6 +22,7 @@
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/namespace.h"
+#include "catalog/objectaccess.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_statistic_ext_data.h"
@@ -29,6 +31,7 @@
#include "miscadmin.h"
#include "statistics/statistics.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/inval.h"
#include "utils/memutils.h"
#include "utils/rel.h"
@@ -336,6 +339,7 @@ CreateStatistics(CreateStatsStmt *stmt)
values[Anum_pg_statistic_ext_stxrelid - 1] = ObjectIdGetDatum(relid);
values[Anum_pg_statistic_ext_stxname - 1] = NameGetDatum(&stxname);
values[Anum_pg_statistic_ext_stxnamespace - 1] =
ObjectIdGetDatum(namespaceId);
+ values[Anum_pg_statistic_ext_stxstattarget - 1] = Int32GetDatum(-1);
values[Anum_pg_statistic_ext_stxowner - 1] = ObjectIdGetDatum(stxowner);
values[Anum_pg_statistic_ext_stxkeys - 1] = PointerGetDatum(stxkeys);
values[Anum_pg_statistic_ext_stxkind - 1] = PointerGetDatum(stxkind);
@@ -414,6 +418,111 @@ CreateStatistics(CreateStatsStmt *stmt)
return myself;
}
+
+/*
+ * ALTER STATISTICS
+ */
+ObjectAddress
+AlterStatistics(AlterStatsStmt *stmt)
+{
+ Relation rel;
+ Oid stxoid;
+ HeapTuple oldtup;
+ HeapTuple newtup;
+ Datum repl_val[Natts_pg_statistic_ext];
+ bool repl_null[Natts_pg_statistic_ext];
+ bool repl_repl[Natts_pg_statistic_ext];
+ ObjectAddress address;
+ int newtarget = stmt->stxstattarget;
+
+ /* Limit target to a sane range */
+ if (newtarget < -1)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("statistics target %d is too low",
+ newtarget)));
+ }
+ else if (newtarget > 10000)
+ {
+ newtarget = 10000;
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lowering statistics target to %d",
+ newtarget)));
+ }
+
+ /* lookup OID of the statistic object */
+ stxoid = get_statistics_object_oid(stmt->defnames, stmt->missing_ok);
+
+ /*
+ * If we got here and the OID is not valid, it means the statistics
+ * does not exist, but the command specified IF EXISTS. So report
+ * this as NOTICE and we're done.
+ */
+ if (!OidIsValid(stxoid))
+ {
+ char *schemaname;
+ char *statname;
+
+ Assert(stmt->missing_ok);
+
+ DeconstructQualifiedName(stmt->defnames, &schemaname,
&statname);
+
+ if (schemaname)
+ ereport(NOTICE,
+ (errmsg("statistics object \"%s.%s\"
does not exist, skipping",
+ schemaname, statname)));
+ else
+ ereport(NOTICE,
+ (errmsg("statistics object \"%s\" does
not exist, skipping",
+ statname)));
+
+ return InvalidObjectAddress;
+ }
+
+ /* Search pg_statistic_ext */
+ rel = table_open(StatisticExtRelationId, RowExclusiveLock);
+
+ oldtup = SearchSysCache1(STATEXTOID, ObjectIdGetDatum(stxoid));
+
+ /* Must be owner of the existing statistic object */
+ if (!pg_statistics_object_ownercheck(stxoid, GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_STATISTIC_EXT,
+ NameListToString(stmt->defnames));
+
+ /* Build new tuple. */
+ memset(repl_val, 0, sizeof(repl_val));
+ memset(repl_null, false, sizeof(repl_null));
+ memset(repl_repl, false, sizeof(repl_repl));
+
+ /* replace the stxstattarget column */
+ repl_repl[Anum_pg_statistic_ext_stxstattarget - 1] = true;
+ repl_val[Anum_pg_statistic_ext_stxstattarget - 1] =
Int32GetDatum(newtarget);
+
+ newtup = heap_modify_tuple(oldtup, RelationGetDescr(rel),
+ repl_val, repl_null,
repl_repl);
+
+ /* Update system catalog. */
+ CatalogTupleUpdate(rel, &newtup->t_self, newtup);
+
+ InvokeObjectPostAlterHook(StatisticExtRelationId, stxoid, 0);
+
+ ObjectAddressSet(address, StatisticExtRelationId, stxoid);
+
+ /*
+ * NOTE: because we only support altering the statistic target, not the
+ * other fields, there is no need to update dependencies.
+ */
+
+ heap_freetuple(newtup);
+ ReleaseSysCache(oldtup);
+
+ table_close(rel, RowExclusiveLock);
+
+ return address;
+}
+
/*
* Guts of statistics object deletion.
*/
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 6414aded0e..44267856a9 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3493,6 +3493,18 @@ _copyCreateStatsStmt(const CreateStatsStmt *from)
return newnode;
}
+static AlterStatsStmt *
+_copyAlterStatsStmt(const AlterStatsStmt *from)
+{
+ AlterStatsStmt *newnode = makeNode(AlterStatsStmt);
+
+ COPY_NODE_FIELD(defnames);
+ COPY_SCALAR_FIELD(stxstattarget);
+ COPY_SCALAR_FIELD(missing_ok);
+
+ return newnode;
+}
+
static CreateFunctionStmt *
_copyCreateFunctionStmt(const CreateFunctionStmt *from)
{
@@ -5207,6 +5219,9 @@ copyObjectImpl(const void *from)
case T_CreateStatsStmt:
retval = _copyCreateStatsStmt(from);
break;
+ case T_AlterStatsStmt:
+ retval = _copyAlterStatsStmt(from);
+ break;
case T_CreateFunctionStmt:
retval = _copyCreateFunctionStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 4f2ebe5118..18cb014373 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1365,6 +1365,16 @@ _equalCreateStatsStmt(const CreateStatsStmt *a, const
CreateStatsStmt *b)
return true;
}
+static bool
+_equalAlterStatsStmt(const AlterStatsStmt *a, const AlterStatsStmt *b)
+{
+ COMPARE_NODE_FIELD(defnames);
+ COMPARE_SCALAR_FIELD(stxstattarget);
+ COMPARE_SCALAR_FIELD(missing_ok);
+
+ return true;
+}
+
static bool
_equalCreateFunctionStmt(const CreateFunctionStmt *a, const CreateFunctionStmt
*b)
{
@@ -3309,6 +3319,9 @@ equal(const void *a, const void *b)
case T_CreateStatsStmt:
retval = _equalCreateStatsStmt(a, b);
break;
+ case T_AlterStatsStmt:
+ retval = _equalAlterStatsStmt(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 8e31fae47f..1394bdaaa2 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2662,6 +2662,16 @@ _outCreateStatsStmt(StringInfo str, const
CreateStatsStmt *node)
WRITE_BOOL_FIELD(if_not_exists);
}
+static void
+_outAlterStatsStmt(StringInfo str, const AlterStatsStmt *node)
+{
+ WRITE_NODE_TYPE("ALTERSTATSSTMT");
+
+ WRITE_NODE_FIELD(defnames);
+ WRITE_INT_FIELD(stxstattarget);
+ WRITE_BOOL_FIELD(missing_ok);
+}
+
static void
_outNotifyStmt(StringInfo str, const NotifyStmt *node)
{
@@ -4124,6 +4134,9 @@ outNode(StringInfo str, const void *obj)
case T_CreateStatsStmt:
_outCreateStatsStmt(str, obj);
break;
+ case T_AlterStatsStmt:
+ _outAlterStatsStmt(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 c97bb367f8..e90c5d1be3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -252,7 +252,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
AlterOperatorStmt AlterSeqStmt AlterSystemStmt AlterTableStmt
AlterTblSpcStmt AlterExtensionStmt AlterExtensionContentsStmt
AlterForeignTableStmt
AlterCompositeTypeStmt AlterUserMappingStmt
- AlterRoleStmt AlterRoleSetStmt AlterPolicyStmt
+ AlterRoleStmt AlterRoleSetStmt AlterPolicyStmt AlterStatsStmt
AlterDefaultPrivilegesStmt DefACLAction
AnalyzeStmt CallStmt ClosePortalStmt ClusterStmt CommentStmt
ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
@@ -852,6 +852,7 @@ stmt :
| AlterRoleSetStmt
| AlterRoleStmt
| AlterSubscriptionStmt
+ | AlterStatsStmt
| AlterTSConfigurationStmt
| AlterTSDictionaryStmt
| AlterUserMappingStmt
@@ -3984,6 +3985,34 @@ CreateStatsStmt:
}
;
+
+/*****************************************************************************
+ *
+ * QUERY :
+ * ALTER STATISTICS [IF EXISTS] stats_name
+ * SET STATISTICS <SignedIconst>
+ *
+ *****************************************************************************/
+
+AlterStatsStmt:
+ ALTER STATISTICS any_name SET STATISTICS SignedIconst
+ {
+ AlterStatsStmt *n =
makeNode(AlterStatsStmt);
+ n->defnames = $3;
+ n->missing_ok = false;
+ n->stxstattarget = $6;
+ $$ = (Node *)n;
+ }
+ | ALTER STATISTICS IF_P EXISTS any_name SET STATISTICS
SignedIconst
+ {
+ AlterStatsStmt *n =
makeNode(AlterStatsStmt);
+ n->defnames = $5;
+ n->missing_ok = true;
+ n->stxstattarget = $8;
+ $$ = (Node *)n;
+ }
+ ;
+
/*****************************************************************************
*
* QUERY :
diff --git a/src/backend/statistics/extended_stats.c
b/src/backend/statistics/extended_stats.c
index d2346cbe02..4288d6c6a5 100644
--- a/src/backend/statistics/extended_stats.c
+++ b/src/backend/statistics/extended_stats.c
@@ -61,6 +61,7 @@ typedef struct StatExtEntry
char *name; /* statistics object's name */
Bitmapset *columns; /* attribute numbers covered by the
object */
List *types; /* 'char' list of enabled
statistic kinds */
+ int stattarget; /* statistic target (-1
for default) */
} StatExtEntry;
@@ -70,7 +71,8 @@ static VacAttrStats **lookup_var_attr_stats(Relation rel,
Bitmapset *attrs,
static void statext_store(Oid relid,
MVNDistinct *ndistinct,
MVDependencies *dependencies,
MCVList *mcv, VacAttrStats
**stats);
-
+static int statext_compute_stattarget(int stattarget,
+ int
natts, VacAttrStats **stats);
/*
* Compute requested extended stats, using the rows sampled for the plain
@@ -106,6 +108,7 @@ BuildRelationExtStatistics(Relation onerel, double
totalrows,
MCVList *mcv = NULL;
VacAttrStats **stats;
ListCell *lc2;
+ int stattarget;
/*
* Check if we can build these stats based on the column
analyzed. If
@@ -130,6 +133,19 @@ BuildRelationExtStatistics(Relation onerel, double
totalrows,
Assert(bms_num_members(stat->columns) >= 2 &&
bms_num_members(stat->columns) <=
STATS_MAX_DIMENSIONS);
+ /* compute current statistic target */
+ stattarget = statext_compute_stattarget(stat->stattarget,
+
bms_num_members(stat->columns),
+
stats);
+
+ /*
+ * Don't rebuild statistic objects with statistic target set to
0 (we
+ * just leave the existing values around, just like we do for
regular
+ * per-column statistics).
+ */
+ if (stattarget == 0)
+ continue;
+
/* compute statistic of each requested type */
foreach(lc2, stat->types)
{
@@ -143,7 +159,7 @@ BuildRelationExtStatistics(Relation onerel, double
totalrows,
stat->columns, stats);
else if (t == STATS_EXT_MCV)
mcv = statext_mcv_build(numrows, rows,
stat->columns, stats,
-
totalrows);
+
totalrows, stattarget);
}
/* store the statistics in the catalog */
@@ -156,6 +172,134 @@ BuildRelationExtStatistics(Relation onerel, double
totalrows,
MemoryContextDelete(cxt);
}
+/*
+ * ComputeExtStatisticsRows
+ * Compute number of rows required by extended statistics on a
table.
+ *
+ * Computes number of rows we need to sample to build extended statistics on a
+ * table. This only looks at statistics we can actually build - for example
+ * when analyzing only some of the columns, this will skip statistics objects
+ * that would require additional columns.
+ *
+ * See statext_compute_stattarget for details about how we compute statistics
+ * target for a statistics objects (from the object target, attribute targets
+ * and default statistics target).
+ */
+int
+ComputeExtStatisticsRows(Relation onerel,
+ int natts, VacAttrStats
**vacattrstats)
+{
+ Relation pg_stext;
+ ListCell *lc;
+ List *lstats;
+ MemoryContext cxt;
+ MemoryContext oldcxt;
+ int result = 0;
+
+ cxt = AllocSetContextCreate(CurrentMemoryContext,
+
"ComputeExtStatisticsRows",
+
ALLOCSET_DEFAULT_SIZES);
+ oldcxt = MemoryContextSwitchTo(cxt);
+
+ pg_stext = table_open(StatisticExtRelationId, RowExclusiveLock);
+ lstats = fetch_statentries_for_relation(pg_stext,
RelationGetRelid(onerel));
+
+ foreach(lc, lstats)
+ {
+ StatExtEntry *stat = (StatExtEntry *) lfirst(lc);
+ int stattarget = stat->stattarget;
+ VacAttrStats **stats;
+ int nattrs =
bms_num_members(stat->columns);
+
+ /*
+ * Check if we can build this statistics object based on the
columns
+ * analyzed. If not, ignore it (don't report anything, we'll do
that
+ * during the actual build BuildRelationExtStatistics).
+ */
+ stats = lookup_var_attr_stats(onerel, stat->columns,
+
natts, vacattrstats);
+
+ if (!stats)
+ continue;
+
+ /*
+ * Compute statistic target, based on what's set for the
statistic
+ * object itself, and for it's attributes.
+ */
+ stattarget = statext_compute_stattarget(stat->stattarget,
+
nattrs, stats);
+
+ /* Use the largest value for all statistic objects. */
+ if (stattarget > result)
+ result = stattarget;
+ }
+
+ table_close(pg_stext, RowExclusiveLock);
+
+ MemoryContextSwitchTo(oldcxt);
+ MemoryContextDelete(cxt);
+
+ /* compute sample size based on the statistic target */
+ return (300 * result);
+}
+
+/*
+ * statext_compute_stattarget
+ * compute statistic target for an extended statistic
+ *
+ * When computing target for extended statistic objects, we consider three
+ * places where the target may be set - the statistic object itself, for
+ * attributes it's defined on, and finallly the default statistic target.
+ *
+ * First we look at what's set for the statistic object itself, using the
+ * ALTER STATISTICS ... SET STATISTICS command. If we find a valid target
+ * value there (i.e. not -1) we're done. Otherwise we look at targets set
+ * for any of the attributes the statistic is defined on, and if there are
+ * columns with defined target, we use the maximum value. We do this mostly
+ * for backwards compatibility, because this is what we did before having
+ * statistics target for extended statistics.
+ *
+ * And finally, if we still don't have statistics target, we use the value
+ * set in default_statistics_target.
+ */
+static int
+statext_compute_stattarget(int stattarget, int nattrs, VacAttrStats **stats)
+{
+ int i;
+
+ /*
+ * If there's statistic target set for the statistic object, use it.
+ * It may be set to 0 which disables building of that statistic.
+ */
+ if (stattarget >= 0)
+ return stattarget;
+
+ /*
+ * The target for the statistic object is set to -1, in which case we
+ * look at the maximum target set for any of the attributes the object
+ * is defined on.
+ */
+ for (i = 0; i < nattrs; i++)
+ {
+ /* keep the maximmum statistic target */
+ if (stats[i]->attr->attstattarget > stattarget)
+ stattarget = stats[i]->attr->attstattarget;
+ }
+
+ /*
+ * If the value is still negative (so neither the statistic object nor
+ * any of the columns have custom statistic target set), use the global
+ * default target.
+ */
+ if (stattarget < 0)
+ stattarget = default_statistics_target;
+
+ /* As this point we should have a valid statistic target. */
+ Assert((stattarget >= 0) && (stattarget <= 10000));
+
+ return stattarget;
+}
+
/*
* statext_is_kind_built
* Is this stat kind built in the given pg_statistic_ext_data
tuple?
@@ -224,6 +368,7 @@ fetch_statentries_for_relation(Relation pg_statext, Oid
relid)
entry->statOid = staForm->oid;
entry->schema = get_namespace_name(staForm->stxnamespace);
entry->name = pstrdup(NameStr(staForm->stxname));
+ entry->stattarget = staForm->stxstattarget;
for (i = 0; i < staForm->stxkeys.dim1; i++)
{
entry->columns = bms_add_member(entry->columns,
diff --git a/src/backend/statistics/mcv.c b/src/backend/statistics/mcv.c
index e5a4e86c5d..ff68b9f6c9 100644
--- a/src/backend/statistics/mcv.c
+++ b/src/backend/statistics/mcv.c
@@ -180,7 +180,8 @@ get_mincount_for_mcv_list(int samplerows, double totalrows)
*/
MCVList *
statext_mcv_build(int numrows, HeapTuple *rows, Bitmapset *attrs,
- VacAttrStats **stats, double totalrows)
+ VacAttrStats **stats, double totalrows,
+ int stattarget)
{
int i,
numattrs,
@@ -212,12 +213,7 @@ statext_mcv_build(int numrows, HeapTuple *rows, Bitmapset
*attrs,
* Maximum number of MCV items to store, based on the attribute with the
* largest stats target (and the number of groups we have available).
*/
- nitems = stats[0]->attr->attstattarget;
- for (i = 1; i < numattrs; i++)
- {
- if (stats[i]->attr->attstattarget > nitems)
- nitems = stats[i]->attr->attstattarget;
- }
+ nitems = stattarget;
if (nitems > ngroups)
nitems = ngroups;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 7f6f0b6498..c6faa6619d 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1688,6 +1688,10 @@ ProcessUtilitySlow(ParseState *pstate,
address = CreateStatistics((CreateStatsStmt *)
parsetree);
break;
+ case T_AlterStatsStmt:
+ address = AlterStatistics((AlterStatsStmt *)
parsetree);
+ break;
+
case T_AlterCollationStmt:
address = AlterCollation((AlterCollationStmt *)
parsetree);
break;
@@ -2805,6 +2809,10 @@ CreateCommandTag(Node *parsetree)
tag = "CREATE STATISTICS";
break;
+ case T_AlterStatsStmt:
+ tag = "ALTER STATISTICS";
+ break;
+
case T_DeallocateStmt:
{
DeallocateStmt *stmt = (DeallocateStmt *)
parsetree;
@@ -3393,6 +3401,10 @@ GetCommandLogLevel(Node *parsetree)
lev = LOGSTMT_DDL;
break;
+ case T_AlterStatsStmt:
+ lev = LOGSTMT_DDL;
+ break;
+
case T_AlterCollationStmt:
lev = LOGSTMT_DDL;
break;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bbf44a1820..93798713b4 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -7170,6 +7170,7 @@ getExtendedStatistics(Archive *fout)
int i_stxname;
int i_stxnamespace;
int i_rolname;
+ int i_stattarget;
int i;
/* Extended statistics were new in v10 */
@@ -7178,10 +7179,16 @@ getExtendedStatistics(Archive *fout)
query = createPQExpBuffer();
- appendPQExpBuffer(query, "SELECT tableoid, oid, stxname, "
- "stxnamespace, (%s stxowner) AS
rolname "
- "FROM pg_catalog.pg_statistic_ext",
- username_subquery);
+ if (fout->remoteVersion < 130000)
+ appendPQExpBuffer(query, "SELECT tableoid, oid, stxname, "
+ "stxnamespace, (%s stxowner)
AS rolname, (-1) AS stxstattarget "
+ "FROM
pg_catalog.pg_statistic_ext",
+ username_subquery);
+ else
+ appendPQExpBuffer(query, "SELECT tableoid, oid, stxname, "
+ "stxnamespace, (%s stxowner)
AS rolname, stxstattarget "
+ "FROM
pg_catalog.pg_statistic_ext",
+ username_subquery);
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
@@ -7192,6 +7199,7 @@ getExtendedStatistics(Archive *fout)
i_stxname = PQfnumber(res, "stxname");
i_stxnamespace = PQfnumber(res, "stxnamespace");
i_rolname = PQfnumber(res, "rolname");
+ i_stattarget = PQfnumber(res, "stxstattarget");
statsextinfo = (StatsExtInfo *) pg_malloc(ntups * sizeof(StatsExtInfo));
@@ -7206,7 +7214,8 @@ getExtendedStatistics(Archive *fout)
findNamespace(fout,
atooid(PQgetvalue(res, i,
i_stxnamespace)));
statsextinfo[i].rolname = pg_strdup(PQgetvalue(res, i,
i_rolname));
-
+ statsextinfo[i].stattarget = atoi(PQgetvalue(res, i,
i_stattarget));
+
/* Decide whether we want to dump it */
selectDumpableObject(&(statsextinfo[i].dobj), fout);
@@ -16493,6 +16502,19 @@ dumpStatisticsExt(Archive *fout, StatsExtInfo
*statsextinfo)
/* Result of pg_get_statisticsobjdef is complete except for semicolon */
appendPQExpBuffer(q, "%s;\n", stxdef);
+ /*
+ * We only issue an ALTER STATISTICS statement if the stxstattarget
entry
+ * for this statictics object is non-negative (i.e. it's not the default
+ * value).
+ */
+ if (statsextinfo->stattarget >= 0)
+ {
+ appendPQExpBuffer(q, "ALTER STATISTICS %s ",
+
fmtQualifiedDumpable(statsextinfo));
+ appendPQExpBuffer(q, "SET STATISTICS %d;\n",
+ statsextinfo->stattarget);
+ }
+
appendPQExpBuffer(delq, "DROP STATISTICS %s;\n",
fmtQualifiedDumpable(statsextinfo));
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index ccf2153fac..997d6ecb3a 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -386,6 +386,7 @@ typedef struct _statsExtInfo
{
DumpableObject dobj;
char *rolname; /* name of owner, or empty string */
+ int stattarget; /* statistic target */
} StatsExtInfo;
typedef struct _ruleInfo
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index c56bf00e4b..7617f35a4f 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2504,6 +2504,17 @@ my %tests = (
unlike => { exclude_dump_test_schema => 1, },
},
+ 'ALTER STATISTICS extended_stats_options' => {
+ create_order => 98,
+ create_sql => 'ALTER STATISTICS dump_test.test_ext_stats_opts
SET STATISTICS 1000',
+ regexp => qr/^
+ \QALTER STATISTICS dump_test.test_ext_stats_opts SET
STATISTICS 1000;\E
+ /xms,
+ like =>
+ { %full_runs, %dump_test_schema_runs, section_post_data => 1,
},
+ unlike => { exclude_dump_test_schema => 1, },
+ },
+
'CREATE SEQUENCE test_table_col1_seq' => {
regexp => qr/^
\QCREATE SEQUENCE dump_test.test_table_col1_seq\E
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 3f7001fb69..cebdd7b2a0 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1840,7 +1840,7 @@ psql_completion(const char *text, int start, int end)
/* ALTER STATISTICS <name> */
else if (Matches("ALTER", "STATISTICS", MatchAny))
- COMPLETE_WITH("OWNER TO", "RENAME TO", "SET SCHEMA");
+ COMPLETE_WITH("OWNER TO", "RENAME TO", "SET SCHEMA", "SET
STATISTICS");
/* ALTER TRIGGER <name>, add ON */
else if (Matches("ALTER", "TRIGGER", MatchAny))
diff --git a/src/include/catalog/pg_statistic_ext.h
b/src/include/catalog/pg_statistic_ext.h
index d8c5e0651e..5f6bfdd56c 100644
--- a/src/include/catalog/pg_statistic_ext.h
+++ b/src/include/catalog/pg_statistic_ext.h
@@ -41,6 +41,7 @@ CATALOG(pg_statistic_ext,3381,StatisticExtRelationId)
Oid stxnamespace; /* OID of statistics object's
namespace */
Oid stxowner; /* statistics object's
owner */
+ int32 stxstattarget BKI_DEFAULT(-1); /* statistic
target */
/*
* variable-length fields start here, but we allow direct access to
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index b4e7db67c3..1dc6dc2ca0 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -87,6 +87,7 @@ extern ObjectAddress AlterOperator(AlterOperatorStmt *stmt);
/* commands/statscmds.c */
extern ObjectAddress CreateStatistics(CreateStatsStmt *stmt);
+extern ObjectAddress AlterStatistics(AlterStatsStmt *stmt);
extern void RemoveStatisticsById(Oid statsOid);
extern void UpdateStatisticsForTypeChange(Oid statsOid,
Oid relationOid, int attnum,
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 4e2fb39105..57589d4fac 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -420,6 +420,7 @@ typedef enum NodeTag
T_CreateStatsStmt,
T_AlterCollationStmt,
T_CallStmt,
+ T_AlterStatsStmt,
/*
* TAGS FOR PARSE TREE NODES (parsenodes.h)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 94ded3c135..fdacc54cb8 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2789,6 +2789,18 @@ typedef struct CreateStatsStmt
bool if_not_exists; /* do nothing if stats name already
exists */
} CreateStatsStmt;
+/* ----------------------
+ * Alter Statistics Statement
+ * ----------------------
+ */
+typedef struct AlterStatsStmt
+{
+ NodeTag type;
+ List *defnames; /* qualified name (list of Value
strings) */
+ int stxstattarget; /* statistics target, or NULL */
+ bool missing_ok; /* do nothing if statistics
does not exist */
+} AlterStatsStmt;
+
/* ----------------------
* Create Function Statement
* ----------------------
diff --git a/src/include/statistics/extended_stats_internal.h
b/src/include/statistics/extended_stats_internal.h
index c7f01d4edc..56c5599312 100644
--- a/src/include/statistics/extended_stats_internal.h
+++ b/src/include/statistics/extended_stats_internal.h
@@ -71,7 +71,7 @@ extern MVDependencies *statext_dependencies_deserialize(bytea
*data);
extern MCVList *statext_mcv_build(int numrows, HeapTuple *rows,
Bitmapset
*attrs, VacAttrStats **stats,
- double
totalrows);
+ double
totalrows, int stattarget);
extern bytea *statext_mcv_serialize(MCVList *mcv, VacAttrStats **stats);
extern MCVList *statext_mcv_deserialize(bytea *data);
diff --git a/src/include/statistics/statistics.h
b/src/include/statistics/statistics.h
index cb7bc630e9..33abb22e44 100644
--- a/src/include/statistics/statistics.h
+++ b/src/include/statistics/statistics.h
@@ -100,6 +100,8 @@ extern MCVList *statext_mcv_load(Oid mvoid);
extern void BuildRelationExtStatistics(Relation onerel, double totalrows,
int
numrows, HeapTuple *rows,
int
natts, VacAttrStats **vacattrstats);
+extern int ComputeExtStatisticsRows(Relation onerel,
+ int
natts, VacAttrStats **stats);
extern bool statext_is_kind_built(HeapTuple htup, char kind);
extern Selectivity dependencies_clauselist_selectivity(PlannerInfo *root,
List *clauses,
diff --git a/src/test/regress/expected/stats_ext.out
b/src/test/regress/expected/stats_ext.out
index 94b8a8f8b8..f4534e3f7c 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -98,11 +98,28 @@ CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ANALYZE ab1;
WARNING: statistics object "public.ab1_a_b_stats" could not be computed for
relation "public.ab1"
ALTER TABLE ab1 ALTER a SET STATISTICS -1;
+-- setting statistics target 0 skips the statistics, without printing any
message, so check catalog
+ALTER STATISTICS ab1_a_b_stats SET STATISTICS 0;
+ANALYZE ab1;
+SELECT stxname, stxdndistinct, stxddependencies, stxdmcv
+ FROM pg_statistic_ext s, pg_statistic_ext_data d
+ WHERE s.stxname = 'ab1_a_b_stats'
+ AND d.stxoid = s.oid;
+ stxname | stxdndistinct | stxddependencies | stxdmcv
+---------------+---------------+------------------+---------
+ ab1_a_b_stats | | |
+(1 row)
+
+ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1;
-- partial analyze doesn't build stats either
ANALYZE ab1 (a);
WARNING: statistics object "public.ab1_a_b_stats" could not be computed for
relation "public.ab1"
ANALYZE ab1;
DROP TABLE ab1;
+ALTER STATISTICS ab1_a_b_stats SET STATISTICS 0;
+ERROR: statistics object "ab1_a_b_stats" does not exist
+ALTER STATISTICS IF EXISTS ab1_a_b_stats SET STATISTICS 0;
+NOTICE: statistics object "ab1_a_b_stats" does not exist, skipping
-- Verify supported object types for extended statistics
CREATE schema tststats;
CREATE TABLE tststats.t (a int, b int, c text);
diff --git a/src/test/regress/sql/stats_ext.sql
b/src/test/regress/sql/stats_ext.sql
index 4bc1536727..231d7cac83 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -68,10 +68,20 @@ INSERT INTO ab1 SELECT a, a%23 FROM generate_series(1,
1000) a;
CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ANALYZE ab1;
ALTER TABLE ab1 ALTER a SET STATISTICS -1;
+-- setting statistics target 0 skips the statistics, without printing any
message, so check catalog
+ALTER STATISTICS ab1_a_b_stats SET STATISTICS 0;
+ANALYZE ab1;
+SELECT stxname, stxdndistinct, stxddependencies, stxdmcv
+ FROM pg_statistic_ext s, pg_statistic_ext_data d
+ WHERE s.stxname = 'ab1_a_b_stats'
+ AND d.stxoid = s.oid;
+ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1;
-- partial analyze doesn't build stats either
ANALYZE ab1 (a);
ANALYZE ab1;
DROP TABLE ab1;
+ALTER STATISTICS ab1_a_b_stats SET STATISTICS 0;
+ALTER STATISTICS IF EXISTS ab1_a_b_stats SET STATISTICS 0;
-- Verify supported object types for extended statistics
CREATE schema tststats;
