On Wed, Jan 28, 2026 at 11:46:04AM +0900, Michael Paquier wrote: > I'll go fix that now with some tests to cover things, after extracting > the relevant portion of the code from v32-0002.
I have begun putting my head on the MCV part, and found what looks
like a memory overread by injecting buggy values for
most_common_val_nulls. Quick example:
create table test (name text);
CREATE STATISTICS test_stat_mcv_exprs (mcv)
ON lower(name), upper(name)
FROM test;
-- 4 elements in total in most_common_val_nulls, logic reads 8,
-- reads past 4 of them.
SELECT pg_catalog.pg_restore_extended_stats('schemaname', 'public',
'relname', 'test',
'statistics_schemaname', 'public',
'statistics_name', 'test_stat_mcv_exprs',
'inherited', false,
'most_common_vals', '{{four,FOUR},{one,ONE},{tre,TRE},{two,TWO}}'::text[],
'most_common_val_nulls', '{{f},{f},{f},{f}}'::boolean[],
'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[],
'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.625}'::double precision[]);
The boundary checks for the most_common_freqs and
most_common_base_freqs are OK: these should be 1-dimension, with a
number of elements matching the number of items in most_common_vals.
As far as I can see, most_common_vals is also OK, we check after a
2-dimension array made of N arrays with a number of elements matching
with the object definition.
most_common_val_nulls is problematic: we check that it is a
2-dimension array, we also check that its number of internal arrays
match with the number of elements in most_common_vals. However, we do
*not* check that the internal arrays have a number of items matching
with the number of items in most_common_vals. In this example,
{{f},{f},{f},{f}} is too short, {{f,f},{f,f},{f,f},{f,f}} would be
right.
It means that we are missing more sanity checks in import_mcv(), from
what I can see. Without these checks, statext_mcv_import(), that
rebuilds the MCVItems would then read past the contents of nulls_arr
with an index larger than the number of items inserted (second "i"
loop based on "nitems").
Except for this issue, statext_mcv_import() and import_mcv(), which
are the heart of the logic for MCV values, seem pretty clean to me.
I have spent some time looking at the patch and fixed a couple of
issues, adjusting a few things. It would be a good idea to add more
tests for expressions in MCV definitions. I have added a new object in
the regression tests, we should also have some input validations and
checks like the two other kinds.
Could you look at the array bound issue please? Let's use the
attached as a base of work for now, this is what's standing now at the
top of my dev branch for the review of the MCV patch.
--
Michael
From 769d3b23304c2f1310775c5bb2a902bd2646f149 Mon Sep 17 00:00:00 2001 From: Michael Paquier <[email protected]> Date: Wed, 28 Jan 2026 12:44:41 +0900 Subject: [PATCH v33] Add support for "mcv" in pg_restore_extended_stats() This commit adds support for the restore of extended statistics of the kind "mcv". This format is different from n_distinct and dependencies stat types in that it is the combination of four different arrays from the pg_stats_ext view which in turn require four different input parameters on pg_restore_extended_statistics(). Of particular complexity is the parameter "most_common_vals" which will be a two dimensional array of text values. The inner array values form a tuple where each attribute matches the order and datatype of the stxkeys defined for the statistics object. --- .../statistics/extended_stats_internal.h | 5 + src/backend/statistics/extended_stats_funcs.c | 292 +++++++++++++++++- src/backend/statistics/mcv.c | 145 +++++++++ src/bin/pg_dump/pg_dump.c | 35 ++- src/test/regress/expected/stats_import.out | 247 +++++++++++++++ src/test/regress/sql/stats_import.sql | 179 +++++++++++ doc/src/sgml/func/func-admin.sgml | 5 +- 7 files changed, 904 insertions(+), 4 deletions(-) diff --git a/src/include/statistics/extended_stats_internal.h b/src/include/statistics/extended_stats_internal.h index 54b4a26273d8..dff675d6a33e 100644 --- a/src/include/statistics/extended_stats_internal.h +++ b/src/include/statistics/extended_stats_internal.h @@ -90,6 +90,11 @@ extern MCVList *statext_mcv_build(StatsBuildData *data, extern bytea *statext_mcv_serialize(MCVList *mcvlist, VacAttrStats **stats); extern MCVList *statext_mcv_deserialize(bytea *data); extern void statext_mcv_free(MCVList *mcvlist); +extern Datum statext_mcv_import(int elevel, int numattrs, Oid *atttypids, + int32 *atttypmods, Oid *atttypcolls, + int nitems, Datum *mcv_elems, + bool *mcv_nulls, bool *mcv_elem_nulls, + float8 *freqs, float8 *base_freqs); extern MultiSortSupport multi_sort_init(int ndims); extern void multi_sort_add_dimension(MultiSortSupport mss, int sortdim, diff --git a/src/backend/statistics/extended_stats_funcs.c b/src/backend/statistics/extended_stats_funcs.c index 80247cbac21a..28017f6988f9 100644 --- a/src/backend/statistics/extended_stats_funcs.c +++ b/src/backend/statistics/extended_stats_funcs.c @@ -48,6 +48,10 @@ enum extended_stats_argnum INHERITED_ARG, NDISTINCT_ARG, DEPENDENCIES_ARG, + MOST_COMMON_VALS_ARG, + MOST_COMMON_VAL_NULLS_ARG, + MOST_COMMON_FREQS_ARG, + MOST_COMMON_BASE_FREQS_ARG, NUM_EXTENDED_STATS_ARGS, }; @@ -64,6 +68,10 @@ static struct StatsArgInfo extarginfo[] = [INHERITED_ARG] = {"inherited", BOOLOID}, [NDISTINCT_ARG] = {"n_distinct", PG_NDISTINCTOID}, [DEPENDENCIES_ARG] = {"dependencies", PG_DEPENDENCIESOID}, + [MOST_COMMON_VALS_ARG] = {"most_common_vals", TEXTARRAYOID}, + [MOST_COMMON_VAL_NULLS_ARG] = {"most_common_val_nulls", BOOLARRAYOID}, + [MOST_COMMON_FREQS_ARG] = {"most_common_freqs", FLOAT8ARRAYOID}, + [MOST_COMMON_BASE_FREQS_ARG] = {"most_common_base_freqs", FLOAT8ARRAYOID}, [NUM_EXTENDED_STATS_ARGS] = {0}, }; @@ -90,6 +98,17 @@ static void upsert_pg_statistic_ext_data(const Datum *values, const bool *nulls, const bool *replaces); +static bool check_mcvlist_array(const ArrayType *arr, int argindex, + int required_ndims, int mcv_length); +static Datum import_mcv(const ArrayType *mcv_arr, + const ArrayType *nulls_arr, + const ArrayType *freqs_arr, + const ArrayType *base_freqs_arr, + Oid *atttypids, int32 *atttypmods, + Oid *atttypcolls, int numattrs, + bool *ok); + + /* * Fetch a pg_statistic_ext row by name and namespace OID. */ @@ -252,16 +271,32 @@ extended_statistics_update(FunctionCallInfo fcinfo) bool success = true; Datum exprdatum; bool isnull; + List *exprs = NIL; + int numattnums = 0; int numexprs = 0; + int numattrs = 0; /* arrays of type info, if we need them */ + Oid *atttypids = NULL; + int32 *atttypmods = NULL; + Oid *atttypcolls = NULL; Oid relid; Oid locked_table = InvalidOid; /* * Fill out the StakindFlags "has" structure based on which parameters * were provided to the function. + * + * The MCV stats composite value is an array of record type, but this is + * externally represented as four arrays that must be interleaved into the + * array of records. Therefore, none of the four array values is + * meaningful unless the other three are also present and in sync in terms + * of array length. */ + has.mcv = (!PG_ARGISNULL(MOST_COMMON_VALS_ARG) && + !PG_ARGISNULL(MOST_COMMON_VAL_NULLS_ARG) && + !PG_ARGISNULL(MOST_COMMON_FREQS_ARG) && + !PG_ARGISNULL(MOST_COMMON_BASE_FREQS_ARG)); has.ndistinct = !PG_ARGISNULL(NDISTINCT_ARG); has.dependencies = !PG_ARGISNULL(DEPENDENCIES_ARG); @@ -344,6 +379,7 @@ extended_statistics_update(FunctionCallInfo fcinfo) /* Find out what extended statistics kinds we should expect. */ expand_stxkind(tup, &enabled); + numattnums = stxform->stxkeys.dim1; /* decode expression (if any) */ exprdatum = SysCacheGetAttr(STATEXTOID, @@ -353,7 +389,6 @@ extended_statistics_update(FunctionCallInfo fcinfo) if (!isnull) { char *s; - List *exprs; s = TextDatumGetCString(exprdatum); exprs = (List *) stringToNode(s); @@ -377,6 +412,8 @@ extended_statistics_update(FunctionCallInfo fcinfo) numexprs = list_length(exprs); } + numattrs = numattnums + numexprs; + /* * If the object cannot support ndistinct, we should not have data for it. */ @@ -411,6 +448,119 @@ extended_statistics_update(FunctionCallInfo fcinfo) success = false; } + /* + * If the object cannot hold an MCV value, but any of the MCV parameters + * are set, then issue a WARNING and ensure that we do not try to load MCV + * stats later. In pg_stats_ext, most_common_val_nulls, most_common_freqs + * and most_common_base_freqs are NULL if most_common_vals is NULL. + */ + if (!enabled.mcv) + { + if (!PG_ARGISNULL(MOST_COMMON_VALS_ARG) || + !PG_ARGISNULL(MOST_COMMON_VAL_NULLS_ARG) || + !PG_ARGISNULL(MOST_COMMON_FREQS_ARG) || + !PG_ARGISNULL(MOST_COMMON_BASE_FREQS_ARG)) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot specify parameters \"%s\", \"%s\", \"%s\", or \"%s\"", + extarginfo[MOST_COMMON_VALS_ARG].argname, + extarginfo[MOST_COMMON_VAL_NULLS_ARG].argname, + extarginfo[MOST_COMMON_FREQS_ARG].argname, + extarginfo[MOST_COMMON_BASE_FREQS_ARG].argname), + errhint("Extended statistics object \"%s\".\"%s\" does not support statistics of this type.", + quote_identifier(nspname), + quote_identifier(stxname))); + + has.mcv = false; + success = false; + } + } + else if (!has.mcv) + { + /* + * If we do not have all of the MCV arrays set while the extended + * statistics object expects something, something is wrong. This + * issues a WARNING if a partial input has been provided. + */ + if (!PG_ARGISNULL(MOST_COMMON_VALS_ARG) || + !PG_ARGISNULL(MOST_COMMON_VAL_NULLS_ARG) || + !PG_ARGISNULL(MOST_COMMON_FREQS_ARG) || + !PG_ARGISNULL(MOST_COMMON_BASE_FREQS_ARG)) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not use \"%s\", \"%s\", \"%s\", and \"%s\": missing one or more parameters", + extarginfo[MOST_COMMON_VALS_ARG].argname, + extarginfo[MOST_COMMON_VAL_NULLS_ARG].argname, + extarginfo[MOST_COMMON_FREQS_ARG].argname, + extarginfo[MOST_COMMON_BASE_FREQS_ARG].argname)); + success = false; + } + } + + /* + * Either of these statistic types requires that we supply a semi-filled + * VacAttrStatP array. + * + * It is not possible to use the existing lookup_var_attr_stats() and + * examine_attribute() because these functions will skip attributes where + * attstattarget is 0, and we may have statistics data to import for those + * attributes. + */ + if (has.mcv) + { + atttypids = palloc0_array(Oid, numattrs); + atttypmods = palloc0_array(int32, numattrs); + atttypcolls = palloc0_array(Oid, numattrs); + + /* + * The leading stxkeys are attribute numbers up through numattnums. + * These keys must be in ascending AttNumber order, but we do not rely + * on that. + */ + for (int i = 0; i < numattnums; i++) + { + AttrNumber attnum = stxform->stxkeys.values[i]; + HeapTuple atup = SearchSysCache2(ATTNUM, + ObjectIdGetDatum(relid), + Int16GetDatum(attnum)); + + Form_pg_attribute attr; + + /* Attribute not found */ + if (!HeapTupleIsValid(atup)) + elog(ERROR, "stxkeys references nonexistent attnum %d", attnum); + + attr = (Form_pg_attribute) GETSTRUCT(atup); + + if (attr->attisdropped) + elog(ERROR, "stxkeys references dropped attnum %d", attnum); + + atttypids[i] = attr->atttypid; + atttypmods[i] = attr->atttypmod; + atttypcolls[i] = attr->attcollation; + ReleaseSysCache(atup); + } + + /* + * After all the positive number attnums in stxkeys come the negative + * numbers (if any) which represent expressions in the order that they + * appear in stxdexprs. Because the expressions are always + * monotonically decreasing from -1, there is no point in looking at + * the values in stxkeys, it's enough to know how many of them there + * are. + */ + for (int i = numattnums; i < numattrs; i++) + { + Node *expr = list_nth(exprs, i - numattnums); + + atttypids[i] = exprType(expr); + atttypmods[i] = exprTypmod(expr); + atttypcolls[i] = exprCollation(expr); + } + } + /* * Populate the pg_statistic_ext_data result tuple. */ @@ -471,6 +621,29 @@ extended_statistics_update(FunctionCallInfo fcinfo) statext_dependencies_free(dependencies); } + if (has.mcv) + { + Datum datum; + bool val_ok = false; + + datum = import_mcv(PG_GETARG_ARRAYTYPE_P(MOST_COMMON_VALS_ARG), + PG_GETARG_ARRAYTYPE_P(MOST_COMMON_VAL_NULLS_ARG), + PG_GETARG_ARRAYTYPE_P(MOST_COMMON_FREQS_ARG), + PG_GETARG_ARRAYTYPE_P(MOST_COMMON_BASE_FREQS_ARG), + atttypids, atttypmods, atttypcolls, numattrs, + &val_ok); + + if (val_ok) + { + Assert(datum != (Datum) 0); + values[Anum_pg_statistic_ext_data_stxdmcv - 1] = datum; + nulls[Anum_pg_statistic_ext_data_stxdmcv - 1] = false; + replaces[Anum_pg_statistic_ext_data_stxdmcv - 1] = true; + } + else + success = false; + } + upsert_pg_statistic_ext_data(values, nulls, replaces); cleanup: @@ -478,9 +651,126 @@ cleanup: heap_freetuple(tup); if (pg_stext != NULL) table_close(pg_stext, RowExclusiveLock); + if (atttypids != NULL) + pfree(atttypids); + if (atttypmods != NULL) + pfree(atttypmods); + if (atttypcolls != NULL) + pfree(atttypcolls); return success; } +/* + * Consistency checks to ensure that other mcvlist arrays are in alignment + * with the mcv array. + */ +static bool +check_mcvlist_array(const ArrayType *arr, int argindex, int required_ndims, + int mcv_length) +{ + if (ARR_NDIM(arr) != required_ndims) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse array \"%s\": incorrect number of dimensions (%d required)", + extarginfo[argindex].argname, required_ndims)); + return false; + } + + if (array_contains_nulls(arr)) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse array \"%s\": NULL value found", + extarginfo[argindex].argname)); + return false; + } + + if (ARR_DIMS(arr)[0] != mcv_length) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse array \"%s\": incorrect number of elements (same as \"%s\" required)", + extarginfo[argindex].argname, + extarginfo[MOST_COMMON_VALS_ARG].argname)); + return false; + } + + return true; +} + +/* + * Create the stxdmcv datum from the equal-sized arrays of most common values, + * their null flags, and the frequency and base frequency associated with + * each value. + */ +static Datum +import_mcv(const ArrayType *mcv_arr, const ArrayType *nulls_arr, + const ArrayType *freqs_arr, const ArrayType *base_freqs_arr, + Oid *atttypids, int32 *atttypmods, Oid *atttypcolls, int numattrs, + bool *ok) +{ + int nitems; + Datum *mcv_elems; + bool *mcv_nulls; + int check_nummcv; + Datum mcv = (Datum) 0; + + *ok = false; + + /* + * mcv_arr is an array of values each of which in an array with numattrs + * elements. + */ + if (ARR_NDIM(mcv_arr) != 2) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse array \"%s\": incorrect number of dimensions (%d required)", + extarginfo[MOST_COMMON_VALS_ARG].argname, 2)); + + goto mcv_error; + } + + if (ARR_DIMS(mcv_arr)[1] != numattrs) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse array \"%s\": found %d attributes but expected %d", + extarginfo[MOST_COMMON_VALS_ARG].argname, + ARR_DIMS(mcv_arr)[1], numattrs)); + + goto mcv_error; + } + + /* + * All four arrays must be of the same length and cannot contain NULLs. We + * use mcv_arr as the reference array for determining the length. + */ + nitems = ARR_DIMS(mcv_arr)[0]; + if (!check_mcvlist_array(nulls_arr, MOST_COMMON_VAL_NULLS_ARG, + 2, nitems) || + !check_mcvlist_array(freqs_arr, MOST_COMMON_FREQS_ARG, + 1, nitems) || + !check_mcvlist_array(base_freqs_arr, MOST_COMMON_BASE_FREQS_ARG, + 1, nitems)) + goto mcv_error; + + deconstruct_array_builtin(mcv_arr, TEXTOID, &mcv_elems, + &mcv_nulls, &check_nummcv); + + mcv = statext_mcv_import(WARNING, numattrs, + atttypids, atttypmods, atttypcolls, + nitems, mcv_elems, mcv_nulls, + (bool *) ARR_DATA_PTR(nulls_arr), + (float8 *) ARR_DATA_PTR(freqs_arr), + (float8 *) ARR_DATA_PTR(base_freqs_arr)); + + *ok = (mcv != (Datum) 0); +mcv_error: + return mcv; +} + /* * Remove an existing pg_statistic_ext_data row for a given pg_statistic_ext * row and "inherited" pair. diff --git a/src/backend/statistics/mcv.c b/src/backend/statistics/mcv.c index de5a544b390f..ca619d9ae6c1 100644 --- a/src/backend/statistics/mcv.c +++ b/src/backend/statistics/mcv.c @@ -2187,3 +2187,148 @@ statext_mcv_free(MCVList *mcvlist) } pfree(mcvlist); } + +/* + * Create the MCV composite datum, which is a serialization of an array of + * MCVItems. + * + * The inputs consist of four separate arrays of equal length numitems, which + * form an array of composite records defined by the three atttypX arrays of + * equal length numattrs. + * + * If any data element fails to convert to the input type specified for that + * attribute, then function will return a NULL Datum if elevel < ERROR. + */ +Datum +statext_mcv_import(int elevel, int numattrs, + Oid *atttypids, int32 *atttypmods, Oid *atttypcolls, + int nitems, Datum *mcv_elems, bool *mcv_nulls, + bool *mcv_elem_nulls, float8 *freqs, + float8 *base_freqs) +{ + MCVList *mcvlist; + bytea *bytes; + VacAttrStats **vastats; + + /* + * Allocate the MCV list structure, set the global parameters. + */ + mcvlist = (MCVList *) palloc0(offsetof(MCVList, items) + + (sizeof(MCVItem) * nitems)); + + mcvlist->magic = STATS_MCV_MAGIC; + mcvlist->type = STATS_MCV_TYPE_BASIC; + mcvlist->ndimensions = numattrs; + mcvlist->nitems = nitems; + + /* Set the values for the 1-D arrays and allocate space for the 2-D arrays */ + for (int i = 0; i < nitems; i++) + { + MCVItem *item = &mcvlist->items[i]; + + item->frequency = freqs[i]; + item->base_frequency = base_freqs[i]; + item->values = (Datum *) palloc0_array(Datum, numattrs); + item->isnull = (bool *) palloc0_array(bool, numattrs); + } + + /* + * Walk through each dimension, determine the input function for that + * type, and then attempt to convert all values in that column via that + * function. We approach this column-wise because it is simpler to deal + * with one input function at time, and possibly more cache-friendly. + */ + for (int j = 0; j < numattrs; j++) + { + FmgrInfo finfo; + Oid ioparam; + Oid infunc; + int index = j; + + getTypeInputInfo(atttypids[j], &infunc, &ioparam); + fmgr_info(infunc, &finfo); + + /* store info about data type OIDs */ + mcvlist->types[j] = atttypids[j]; + + for (int i = 0; i < nitems; i++) + { + MCVItem *item = &mcvlist->items[i]; + + /* These should be in agreement, but just to be safe check both */ + if (mcv_elem_nulls[index] || mcv_nulls[index]) + { + item->values[j] = (Datum) 0; + item->isnull[j] = true; + } + else + { + char *s = TextDatumGetCString(mcv_elems[index]); + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + if (!InputFunctionCallSafe(&finfo, s, ioparam, atttypmods[j], + (Node *) &escontext, &item->values[j])) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse MCV element \"%s\": incorrect value", s))); + pfree(s); + goto error; + } + + pfree(s); + } + + index += numattrs; + } + } + + /* + * The function statext_mcv_serialize() requires an array of pointers to + * VacAttrStats records, but only a few fields within those records have + * to be filled out. + */ + vastats = (VacAttrStats **) palloc0_array(VacAttrStats *, numattrs); + + for (int i = 0; i < numattrs; i++) + { + Oid typid = atttypids[i]; + HeapTuple typtuple; + + typtuple = SearchSysCacheCopy1(TYPEOID, ObjectIdGetDatum(typid)); + + if (!HeapTupleIsValid(typtuple)) + elog(ERROR, "cache lookup failed for type %u", typid); + + vastats[i] = palloc0_object(VacAttrStats); + + vastats[i]->attrtype = (Form_pg_type) GETSTRUCT(typtuple); + vastats[i]->attrtypid = typid; + vastats[i]->attrcollid = atttypcolls[i]; + } + + bytes = statext_mcv_serialize(mcvlist, vastats); + + for (int i = 0; i < numattrs; i++) + { + pfree(vastats[i]); + } + pfree((void *) vastats); + + pfree(mcv_elems); + pfree(mcv_nulls); + + if (bytes == NULL) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not import MCV list"))); + goto error; + } + + return PointerGetDatum(bytes); + +error: + statext_mcv_free(mcvlist); + return (Datum) 0; +} diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 078ee8500ad2..84c20f22973b 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -18622,7 +18622,7 @@ dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) *-------- */ if (fout->remoteVersion >= 190000) - appendPQExpBufferStr(pq, "e.n_distinct, e.dependencies "); + appendPQExpBufferStr(pq, "e.n_distinct, e.dependencies, "); else appendPQExpBufferStr(pq, "( " @@ -18646,7 +18646,18 @@ dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) " '" PG_DEPENDENCIES_KEY_DEGREE "', " " kv.value::double precision )) " "FROM json_each_text(e.dependencies::text::json) AS kv " - ") AS dependencies "); + ") AS dependencies, "); + + /* MCV was introduced v13 */ + if (fout->remoteVersion >= 130000) + appendPQExpBufferStr(pq, + "e.most_common_vals, e.most_common_val_nulls, " + "e.most_common_freqs, e.most_common_base_freqs "); + else + appendPQExpBufferStr(pq, + "NULL AS most_common_vals, NULL AS most_common_val_nulls, " + "NULL AS most_common_freqs, NULL AS most_common_base_freqs "); + /* pg_stats_ext introduced in v12 */ if (fout->remoteVersion >= 120000) @@ -18697,6 +18708,10 @@ dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) int i_inherited = PQfnumber(res, "inherited"); int i_ndistinct = PQfnumber(res, "n_distinct"); int i_dependencies = PQfnumber(res, "dependencies"); + int i_mcv = PQfnumber(res, "most_common_vals"); + int i_mcv_nulls = PQfnumber(res, "most_common_val_nulls"); + int i_mcf = PQfnumber(res, "most_common_freqs"); + int i_mcbf = PQfnumber(res, "most_common_base_freqs"); for (int i = 0; i < nstats; i++) { @@ -18732,6 +18747,22 @@ dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) appendNamedArgument(out, fout, "dependencies", "pg_dependencies", PQgetvalue(res, i, i_dependencies)); + if (!PQgetisnull(res, i, i_mcv)) + appendNamedArgument(out, fout, "most_common_vals", "text[]", + PQgetvalue(res, i, i_mcv)); + + if (!PQgetisnull(res, i, i_mcv_nulls)) + appendNamedArgument(out, fout, "most_common_val_nulls", "boolean[]", + PQgetvalue(res, i, i_mcv_nulls)); + + if (!PQgetisnull(res, i, i_mcf)) + appendNamedArgument(out, fout, "most_common_freqs", "double precision[]", + PQgetvalue(res, i, i_mcf)); + + if (!PQgetisnull(res, i, i_mcbf)) + appendNamedArgument(out, fout, "most_common_base_freqs", "double precision[]", + PQgetvalue(res, i, i_mcbf)); + appendPQExpBufferStr(out, "\n);\n"); } diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index 0709f19a5e4d..f6a30401f340 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -1147,12 +1147,18 @@ CREATE STATISTICS stats_import.test_stat_ndistinct (ndistinct) CREATE STATISTICS stats_import.test_stat_dependencies (dependencies) ON name, comp FROM stats_import.test; +CREATE STATISTICS stats_import.test_stat_mcv (mcv) + ON name, comp + FROM stats_import.test; CREATE STATISTICS stats_import.test_stat_ndistinct_exprs (ndistinct) ON lower(name), upper(name) FROM stats_import.test; CREATE STATISTICS stats_import.test_stat_dependencies_exprs (dependencies) ON lower(name), upper(name) FROM stats_import.test; +CREATE STATISTICS stats_import.test_stat_mcv_exprs (mcv) + ON lower(name), upper(name) + FROM stats_import.test; -- Generate statistics on table with data ANALYZE stats_import.test; CREATE TABLE stats_import.test_clone ( LIKE stats_import.test ) @@ -1919,6 +1925,247 @@ WHERE e.statistics_schemaname = 'stats_import' AND {"attributes": [-2], "dependency": -1, "degree": 1.000000}] (1 row) +-- Incorrect extended stats kind, mcv not supported +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_dependencies', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); +WARNING: cannot specify parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", or "most_common_base_freqs" +HINT: Extended statistics object "stats_import"."test_stat_dependencies" does not support statistics of this type. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- MCV requires all four parameters +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); +WARNING: could not use "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs": missing one or more parameters + pg_restore_extended_stats +--------------------------- + f +(1 row) + +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); +WARNING: could not use "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs": missing one or more parameters + pg_restore_extended_stats +--------------------------- + f +(1 row) + +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); +WARNING: could not use "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs": missing one or more parameters + pg_restore_extended_stats +--------------------------- + f +(1 row) + +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[]); +WARNING: could not use "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs": missing one or more parameters + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- most_common_vals that is not 2-D +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{four,NULL}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); +WARNING: could not parse array "most_common_vals": incorrect number of dimensions (2 required) + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- most_common_vals with improper length, but since it is the reference array, most_common_vals_nulls gets the blame. +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); +WARNING: could not parse array "most_common_val_nulls": incorrect number of elements (same as "most_common_vals" required) + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- most_common_val_nulls with improper length +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); +WARNING: could not parse array "most_common_val_nulls": incorrect number of elements (same as "most_common_vals" required) + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- most_common_freqs with improper length +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); +WARNING: could not parse array "most_common_freqs": incorrect number of elements (same as "most_common_vals" required) + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- most_common_base_freqs with improper lengths +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625}'::double precision[]); +WARNING: could not parse array "most_common_base_freqs": incorrect number of elements (same as "most_common_vals" required) + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- mcv attributes doesn't match object definition +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[], + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[]); +WARNING: could not parse array "most_common_vals": found 4 attributes but expected 2 + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- ok: mcv +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_mcv' AND + e.inherited = false +\gx +-[ RECORD 1 ]----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +most_common_vals | {{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}} +most_common_val_nulls | {{f,t},{f,f},{f,f},{f,f}} +most_common_freqs | {0.25,0.25,0.25,0.25} +most_common_base_freqs | {0.0625,0.0625,0.0625,0.0625} + +CREATE STATISTICS stats_import.test_mr_stat + ON name, mrange, ( mrange + '{[10000,10200)}'::int4multirange) + FROM stats_import.test_mr; +-- ok: multirange stats +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_mr', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_mr_stat', + 'inherited', false, + 'n_distinct', '[{"attributes": [2, 3], "ndistinct": 3}, + {"attributes": [2, -1], "ndistinct": 3}, + {"attributes": [3, -1], "ndistinct": 3}, + {"attributes": [2, 3, -1], "ndistinct": 3}]'::pg_catalog.pg_ndistinct, + 'dependencies', '[{"attributes": [3], "dependency": 2, "degree": 1.000000}, + {"attributes": [3], "dependency": -1, "degree": 1.000000}, + {"attributes": [-1], "dependency": 2, "degree": 1.000000}, + {"attributes": [-1], "dependency": 3, "degree": 1.000000}, + {"attributes": [2, 3], "dependency": -1, "degree": 1.000000}, + {"attributes": [2, -1], "dependency": 3, "degree": 1.000000}, + {"attributes": [3, -1], "dependency": 2, "degree": 1.000000}]'::pg_catalog.pg_dependencies, + 'most_common_vals', '{{red,"{[1,3),[5,9),[20,30)}","{[1,3),[5,9),[20,30),[10000,10200)}"},{red,"{[11,13),[15,19),[20,30)}","{[11,13),[15,19),[20,30),[10000,10200)}"},{red,"{[21,23),[25,29),[120,130)}","{[21,23),[25,29),[120,130),[10000,10200)}"}}'::text[], + 'most_common_val_nulls', '{{f,f,f},{f,f,f},{f,f,f}}'::boolean[], + 'most_common_freqs', '{0.3333333333333333,0.3333333333333333,0.3333333333333333}'::double precision[], + 'most_common_base_freqs', '{0.1111111111111111,0.1111111111111111,0.1111111111111111}'::double precision[] +); + pg_restore_extended_stats +--------------------------- + t +(1 row) + DROP SCHEMA stats_import CASCADE; NOTICE: drop cascades to 7 other objects DETAIL: drop cascades to type stats_import.complex_type diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index f08743dd002f..aab251babac6 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -819,6 +819,10 @@ CREATE STATISTICS stats_import.test_stat_dependencies (dependencies) ON name, comp FROM stats_import.test; +CREATE STATISTICS stats_import.test_stat_mcv (mcv) + ON name, comp + FROM stats_import.test; + CREATE STATISTICS stats_import.test_stat_ndistinct_exprs (ndistinct) ON lower(name), upper(name) FROM stats_import.test; @@ -827,6 +831,10 @@ CREATE STATISTICS stats_import.test_stat_dependencies_exprs (dependencies) ON lower(name), upper(name) FROM stats_import.test; +CREATE STATISTICS stats_import.test_stat_mcv_exprs (mcv) + ON lower(name), upper(name) + FROM stats_import.test; + -- Generate statistics on table with data ANALYZE stats_import.test; @@ -1368,4 +1376,175 @@ WHERE e.statistics_schemaname = 'stats_import' AND e.statistics_name = 'test_stat_dependencies_exprs' AND e.inherited = false; +-- Incorrect extended stats kind, mcv not supported + +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_dependencies', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); + +-- MCV requires all four parameters +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[]); + +-- most_common_vals that is not 2-D +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{four,NULL}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); + +-- most_common_vals with improper length, but since it is the reference array, most_common_vals_nulls gets the blame. +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); + +-- most_common_val_nulls with improper length +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); + +-- most_common_freqs with improper length +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); + +-- most_common_base_freqs with improper lengths +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625}'::double precision[]); + +-- mcv attributes doesn't match object definition +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[], + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[]); + +-- ok: mcv +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcv', + 'inherited', false, + 'most_common_vals', '{{four,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")"},{tre,"(3,3.3,TRE,03-03-2003,)"},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")"}}'::text[], + 'most_common_val_nulls', '{{f,t},{f,f},{f,f},{f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.0625,0.0625,0.0625,0.0625}'::double precision[]); + +SELECT e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_mcv' AND + e.inherited = false +\gx + +CREATE STATISTICS stats_import.test_mr_stat + ON name, mrange, ( mrange + '{[10000,10200)}'::int4multirange) + FROM stats_import.test_mr; + +-- ok: multirange stats +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_mr', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_mr_stat', + 'inherited', false, + 'n_distinct', '[{"attributes": [2, 3], "ndistinct": 3}, + {"attributes": [2, -1], "ndistinct": 3}, + {"attributes": [3, -1], "ndistinct": 3}, + {"attributes": [2, 3, -1], "ndistinct": 3}]'::pg_catalog.pg_ndistinct, + 'dependencies', '[{"attributes": [3], "dependency": 2, "degree": 1.000000}, + {"attributes": [3], "dependency": -1, "degree": 1.000000}, + {"attributes": [-1], "dependency": 2, "degree": 1.000000}, + {"attributes": [-1], "dependency": 3, "degree": 1.000000}, + {"attributes": [2, 3], "dependency": -1, "degree": 1.000000}, + {"attributes": [2, -1], "dependency": 3, "degree": 1.000000}, + {"attributes": [3, -1], "dependency": 2, "degree": 1.000000}]'::pg_catalog.pg_dependencies, + 'most_common_vals', '{{red,"{[1,3),[5,9),[20,30)}","{[1,3),[5,9),[20,30),[10000,10200)}"},{red,"{[11,13),[15,19),[20,30)}","{[11,13),[15,19),[20,30),[10000,10200)}"},{red,"{[21,23),[25,29),[120,130)}","{[21,23),[25,29),[120,130),[10000,10200)}"}}'::text[], + 'most_common_val_nulls', '{{f,f,f},{f,f,f},{f,f,f}}'::boolean[], + 'most_common_freqs', '{0.3333333333333333,0.3333333333333333,0.3333333333333333}'::double precision[], + 'most_common_base_freqs', '{0.1111111111111111,0.1111111111111111,0.1111111111111111}'::double precision[] +); + DROP SCHEMA stats_import CASCADE; diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml index ea42056bbc9b..3d1fd9c23933 100644 --- a/doc/src/sgml/func/func-admin.sgml +++ b/doc/src/sgml/func/func-admin.sgml @@ -2223,7 +2223,10 @@ SELECT pg_restore_attribute_stats( to columns in <link linkend="view-pg-stats-ext"><structname>pg_stats_ext</structname> </link>. This function currently supports <literal>n_distinct</literal> and - <literal>dependencies</literal>. + <literal>dependencies</literal>. <literal>mcv</literal> statistics + require the parameters <literal>most_common_vals</literal>, + <literal>most_common_val_nulls</literal>, <literal>most_common_freqs</literal> + and <literal>most_common_base_freqs</literal>. </para> <para> Additionally, this function accepts argument name -- 2.51.0
signature.asc
Description: PGP signature
