From 05b0a3537765a85fda8b59912b658eec1e6b5a81 Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Tue, 31 Oct 2023 03:12:45 -0400 Subject: [PATCH v2 3/4] Add pg_import_rel_stats(). The function pg_import_rel_stats imports rowcount, pagecount, and column statistics for a given table, as well as column statistics for the underlying indexes and extended statistics for any named statistics objects for that table. The most likely application of this function is to quickly apply stats to a newly upgraded database faster than could be accomplished by vacuumdb --analyze-in-stages. The function takes a best-effort approach, skipping statistics that are expected but omitted, skipping object that are specified but do not exist on the target system. The goal is to get better-than-empty statistics into the table quickly, so that business operations can resume sooner. It should be noted that index statistics _are_ rebuilt as normal, but use the imported column statistics from the table, so it is not necessary for the indexes on the target table to match the indexes on the extracted source table. The statistics applied are not locked in any way, and will be overwritten by the next analyze, either explicit or via autovacuum. Extended statistics are identified by name, so those names on the target system must match the source. If not, they are skipped. While the column, index column, and extended statistics are applied transactionally, the changes to pg_class (reltuples and relpages) are not. This decision was made to avoid bloat of pg_class and is in line with the behavior of VACUUM. The medium of exchange is jsonb, the format of which is specified in the view pg_statistic_export. Obviously this view does not exist in older versions of the database, but the view definition can be extracted and adapted to older versions. This function also allows for tweaking of table statistics in-place, allowing the user to inflate rowcounts, skew histograms, etc, to see what those changes will evoke from the query planner. --- src/include/catalog/pg_proc.dat | 5 + src/include/commands/vacuum.h | 4 + .../statistics/extended_stats_internal.h | 3 + src/include/statistics/statistics.h | 10 + src/backend/commands/analyze.c | 709 ++++++++++++++++-- src/backend/statistics/dependencies.c | 133 ++++ src/backend/statistics/extended_stats.c | 259 +++++++ src/backend/statistics/mcv.c | 6 + src/backend/statistics/mvdistinct.c | 120 +++ src/test/regress/expected/vacuum.out | 153 ++++ src/test/regress/sql/vacuum.sql | 146 ++++ doc/src/sgml/func.sgml | 42 ++ 12 files changed, 1527 insertions(+), 63 deletions(-) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 568aa80d92..01aeb54fe3 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5639,6 +5639,11 @@ proname => 'pg_stat_get_db_checksum_last_failure', provolatile => 's', proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid', prosrc => 'pg_stat_get_db_checksum_last_failure' }, +{ oid => '3813', + descr => 'statistics: import to relation', + proname => 'pg_import_rel_stats', provolatile => 'v', proisstrict => 'f', + proparallel => 'u', prorettype => 'bool', proargtypes => 'oid int4 float4 int4 jsonb', + prosrc => 'pg_import_rel_stats' }, { oid => '3074', descr => 'statistics: last reset for a database', proname => 'pg_stat_get_db_stat_reset_time', provolatile => 's', proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid', diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 4af02940c5..72c31eb882 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -24,6 +24,7 @@ #include "storage/buf.h" #include "storage/lock.h" #include "utils/relcache.h" +#include "utils/jsonb.h" /* * Flags for amparallelvacuumoptions to control the participation of bulkdelete @@ -386,4 +387,7 @@ extern double anl_random_fract(void); extern double anl_init_selection_state(int n); extern double anl_get_next_S(double t, int n, double *stateptr); +extern Datum pg_import_rel_stats(PG_FUNCTION_ARGS); + + #endif /* VACUUM_H */ diff --git a/src/include/statistics/extended_stats_internal.h b/src/include/statistics/extended_stats_internal.h index 7b55eb8ffa..fdefaa28f5 100644 --- a/src/include/statistics/extended_stats_internal.h +++ b/src/include/statistics/extended_stats_internal.h @@ -72,15 +72,18 @@ typedef struct StatsBuildData extern MVNDistinct *statext_ndistinct_build(double totalrows, StatsBuildData *data); extern bytea *statext_ndistinct_serialize(MVNDistinct *ndistinct); extern MVNDistinct *statext_ndistinct_deserialize(bytea *data); +extern MVNDistinct *import_ndistinct(JsonbContainer *cont); extern MVDependencies *statext_dependencies_build(StatsBuildData *data); extern bytea *statext_dependencies_serialize(MVDependencies *dependencies); extern MVDependencies *statext_dependencies_deserialize(bytea *data); +extern MVDependencies * import_dependencies(JsonbContainer *cont); extern MCVList *statext_mcv_build(StatsBuildData *data, double totalrows, int stattarget); extern bytea *statext_mcv_serialize(MCVList *mcvlist, VacAttrStats **stats); extern MCVList *statext_mcv_deserialize(bytea *data); +extern MCVList *import_mcv(JsonbContainer *cont); extern MultiSortSupport multi_sort_init(int ndims); extern void multi_sort_add_dimension(MultiSortSupport mss, int sortdim, diff --git a/src/include/statistics/statistics.h b/src/include/statistics/statistics.h index 5e538fec32..2b37e77887 100644 --- a/src/include/statistics/statistics.h +++ b/src/include/statistics/statistics.h @@ -101,6 +101,7 @@ extern MCVList *statext_mcv_load(Oid mvoid, bool inh); extern void BuildRelationExtStatistics(Relation onerel, bool inh, double totalrows, int numrows, HeapTuple *rows, int natts, VacAttrStats **vacattrstats); + extern int ComputeExtStatisticsRows(Relation onerel, int natts, VacAttrStats **vacattrstats); extern bool statext_is_kind_built(HeapTuple htup, char type); @@ -127,4 +128,13 @@ extern StatisticExtInfo *choose_best_statistics(List *stats, char requiredkind, int nclauses); extern HeapTuple statext_expressions_load(Oid stxoid, bool inh, int idx); +extern void ImportVacAttrStats( VacAttrStats *stats, JsonbContainer *cont); +extern void ImportRelationExtStatistics(Relation onerel, bool inh, int natts, + VacAttrStats **vacattrstats, + JsonbContainer *cont); + +extern char *key_lookup_cstring(JsonbContainer *cont, const char *key); +extern JsonbContainer *key_lookup_object(JsonbContainer *cont, const char *key); +extern JsonbContainer *key_lookup_array(JsonbContainer *cont, const char *key); + #endif /* STATISTICS_H */ diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c index 206d1689ef..a9715c3eb3 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -18,6 +18,7 @@ #include "access/detoast.h" #include "access/genam.h" +#include "access/heapam.h" #include "access/multixact.h" #include "access/relation.h" #include "access/sysattr.h" @@ -33,6 +34,7 @@ #include "catalog/pg_collation.h" #include "catalog/pg_inherits.h" #include "catalog/pg_namespace.h" +#include "catalog/pg_operator.h" #include "catalog/pg_statistic_ext.h" #include "commands/dbcommands.h" #include "commands/progress.h" @@ -40,6 +42,7 @@ #include "commands/vacuum.h" #include "common/pg_prng.h" #include "executor/executor.h" +#include "fmgr.h" #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" @@ -57,16 +60,20 @@ #include "utils/attoptcache.h" #include "utils/builtins.h" #include "utils/datum.h" +#include "utils/float.h" #include "utils/fmgroids.h" #include "utils/guc.h" +#include "utils/jsonb.h" #include "utils/lsyscache.h" #include "utils/memutils.h" +#include "utils/numeric.h" #include "utils/pg_rusage.h" #include "utils/sampling.h" #include "utils/sortsupport.h" #include "utils/spccache.h" #include "utils/syscache.h" #include "utils/timestamp.h" +#include "utils/typcache.h" /* Per-index data for ANALYZE */ @@ -109,6 +116,12 @@ static void update_attstats(Oid relid, bool inh, static Datum std_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull); static Datum ind_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull); +static AnlIndexData *build_indexdata(Relation onerel, Relation *Irel, + int nindexes, bool all_columns); +static VacAttrStats **examine_rel_attributes(Relation onerel, int *attr_cnt); + +static +void import_pg_statistics(Relation onerel, bool inh, JsonbContainer *cont); /* * analyze_rel() -- analyze one relation @@ -403,29 +416,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params, attr_cnt = tcnt; } else - { - attr_cnt = onerel->rd_att->natts; - vacattrstats = (VacAttrStats **) - palloc(attr_cnt * sizeof(VacAttrStats *)); - tcnt = 0; - for (i = 1; i <= attr_cnt; i++) - { - vacattrstats[tcnt] = examine_attribute(onerel, i, NULL); - if (vacattrstats[tcnt] != NULL) - tcnt++; - } - attr_cnt = tcnt; - } + vacattrstats = examine_rel_attributes(onerel, &attr_cnt); - /* - * Open all indexes of the relation, and see if there are any analyzable - * columns in the indexes. We do not analyze index columns if there was - * an explicit column list in the ANALYZE command, however. - * - * If we are doing a recursive scan, we don't want to touch the parent's - * indexes at all. If we're processing a partitioned table, we need to - * know if there are any indexes, but we don't want to process them. - */ if (onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) { List *idxs = RelationGetIndexList(onerel); @@ -446,48 +438,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params, nindexes = 0; hasindex = false; } - indexdata = NULL; - if (nindexes > 0) - { - indexdata = (AnlIndexData *) palloc0(nindexes * sizeof(AnlIndexData)); - for (ind = 0; ind < nindexes; ind++) - { - AnlIndexData *thisdata = &indexdata[ind]; - IndexInfo *indexInfo; - thisdata->indexInfo = indexInfo = BuildIndexInfo(Irel[ind]); - thisdata->tupleFract = 1.0; /* fix later if partial */ - if (indexInfo->ii_Expressions != NIL && va_cols == NIL) - { - ListCell *indexpr_item = list_head(indexInfo->ii_Expressions); - - thisdata->vacattrstats = (VacAttrStats **) - palloc(indexInfo->ii_NumIndexAttrs * sizeof(VacAttrStats *)); - tcnt = 0; - for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++) - { - int keycol = indexInfo->ii_IndexAttrNumbers[i]; - - if (keycol == 0) - { - /* Found an index expression */ - Node *indexkey; - - if (indexpr_item == NULL) /* shouldn't happen */ - elog(ERROR, "too few entries in indexprs list"); - indexkey = (Node *) lfirst(indexpr_item); - indexpr_item = lnext(indexInfo->ii_Expressions, - indexpr_item); - thisdata->vacattrstats[tcnt] = - examine_attribute(Irel[ind], i + 1, indexkey); - if (thisdata->vacattrstats[tcnt] != NULL) - tcnt++; - } - } - thisdata->attr_cnt = tcnt; - } - } - } + indexdata = build_indexdata(onerel, Irel, nindexes, (va_cols == NIL)); /* * Determine how many rows we need to sample, using the worst case from @@ -823,6 +775,92 @@ do_analyze_rel(Relation onerel, VacuumParams *params, anl_context = NULL; } + +/* + * Create VacAttrStats entries for all attributes in a relation. + */ +static +VacAttrStats **examine_rel_attributes(Relation onerel, int *attr_cnt) +{ + int natts = onerel->rd_att->natts; + int nfound = 0; + int i; + VacAttrStats **attrstats; + + *attr_cnt = natts; + attrstats = (VacAttrStats **) palloc(natts * sizeof(VacAttrStats *)); + for (i = 1; i <= natts; i++) + { + attrstats[nfound] = examine_attribute(onerel, i, NULL); + if (attrstats[nfound] != NULL) + nfound++; + } + *attr_cnt = nfound; + return attrstats; +} + +/* + * Open all indexes of the relation, and see if there are any analyzable + * columns in the indexes. We do not analyze index columns if there was + * an explicit column list in the ANALYZE command, however. + * + * If we are doing a recursive scan, we don't want to touch the parent's + * indexes at all. If we're processing a partitioned table, we need to + * know if there are any indexes, but we don't want to process them. + */ +static +AnlIndexData *build_indexdata(Relation onerel, Relation *Irel, int nindexes, bool all_columns) +{ + AnlIndexData *indexdata; + int ind, + tcnt, + i; + + if (nindexes == 0) + return NULL; + + indexdata = (AnlIndexData *) palloc0(nindexes * sizeof(AnlIndexData)); + + for (ind = 0; ind < nindexes; ind++) + { + AnlIndexData *thisdata = &indexdata[ind]; + IndexInfo *indexInfo; + + thisdata->indexInfo = indexInfo = BuildIndexInfo(Irel[ind]); + thisdata->tupleFract = 1.0; /* fix later if partial */ + if (indexInfo->ii_Expressions != NIL && all_columns) + { + ListCell *indexpr_item = list_head(indexInfo->ii_Expressions); + + thisdata->vacattrstats = (VacAttrStats **) + palloc(indexInfo->ii_NumIndexAttrs * sizeof(VacAttrStats *)); + tcnt = 0; + for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++) + { + int keycol = indexInfo->ii_IndexAttrNumbers[i]; + + if (keycol == 0) + { + /* Found an index expression */ + Node *indexkey; + + if (indexpr_item == NULL) /* shouldn't happen */ + elog(ERROR, "too few entries in indexprs list"); + indexkey = (Node *) lfirst(indexpr_item); + indexpr_item = lnext(indexInfo->ii_Expressions, + indexpr_item); + thisdata->vacattrstats[tcnt] = + examine_attribute(Irel[ind], i + 1, indexkey); + if (thisdata->vacattrstats[tcnt] != NULL) + tcnt++; + } + } + thisdata->attr_cnt = tcnt; + } + } + return indexdata; +} + /* * Compute statistics about indexes of a relation */ @@ -3073,3 +3111,548 @@ analyze_mcv_list(int *mcv_counts, } return num_mcv; } + +/* + * Get a JsonbValue from a JsonbContainer and ensure that it is a string, + * and return the cstring. + */ +char *key_lookup_cstring(JsonbContainer *cont, const char *key) +{ + JsonbValue j; + + if (!getKeyJsonValueFromContainer(cont, key, strlen(key), &j)) + return NULL; + + if (j.type != jbvString) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, %s must be a string but is type %s", + key, JsonbTypeName(&j)))); + + return JsonbStringValueToCString(&j); +} + +/* + * Get a JsonbContainer from a JsonbContainer and ensure that it is a object + */ +JsonbContainer *key_lookup_object(JsonbContainer *cont, const char *key) +{ + JsonbValue j; + + if (!getKeyJsonValueFromContainer(cont, key, strlen(key), &j)) + return NULL; + + if (j.type != jbvBinary) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, %s must be an object but is type %s", + key, JsonbTypeName(&j)))); + + if (!JsonContainerIsObject(j.val.binary.data)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, %s must be an object but is type %s", + key, JsonbContainerTypeName(j.val.binary.data)))); + + return j.val.binary.data; +} + +/* + * Get a JsonbContainer from a JsonbContainer and ensure that it is an array + */ +JsonbContainer *key_lookup_array(JsonbContainer *cont, const char *key) +{ + JsonbValue j; + + if (!getKeyJsonValueFromContainer(cont, key, strlen(key), &j)) + return NULL; + + if (j.type != jbvBinary) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, %s must be an array but is type %s", + key, JsonbTypeName(&j)))); + + if (!JsonContainerIsArray(j.val.binary.data)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, %s must be an array but is type %s", + key, JsonbContainerTypeName(j.val.binary.data)))); + + return j.val.binary.data; +} + +/* + * Import statistics from JSONB export into relation + * + * Format is: + * + * { + * "columns": { "colname1": ... }, + * "extended": { "statname1": ...} + * } + */ +Datum +pg_import_rel_stats(PG_FUNCTION_ARGS) +{ + Oid relid; + int32 stats_version_num; + Jsonb *jb; + Relation onerel; + Oid save_userid; + int save_sec_context; + int save_nestlevel; + + if (PG_ARGISNULL(0)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relation cannot be NULL"))); + relid = PG_GETARG_OID(0); + + if (PG_ARGISNULL(1)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("server_version_number cannot be NULL"))); + stats_version_num = PG_GETARG_INT32(1); + + if (stats_version_num < 80000) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics version: %d is earlier than earliest supported version", + stats_version_num))); + + if (PG_ARGISNULL(4)) + jb = NULL; + else + { + jb = PG_GETARG_JSONB_P(4); + if (!JB_ROOT_IS_OBJECT(jb)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("columns must be jsonb object at root"))); + } + + onerel = vacuum_open_relation(relid, NULL, VACOPT_ANALYZE, true, + ShareUpdateExclusiveLock); + + if (onerel == NULL) + PG_RETURN_BOOL(false); + + if (!vacuum_is_relation_owner(RelationGetRelid(onerel), + onerel->rd_rel, + VACOPT_ANALYZE)) + { + relation_close(onerel, ShareUpdateExclusiveLock); + PG_RETURN_BOOL(false); + } + + /* + * Switch to the table owner's userid, so that any index functions are run + * as that user. Also lock down security-restricted operations and + * arrange to make GUC variable changes local to this command. + */ + GetUserIdAndSecContext(&save_userid, &save_sec_context); + SetUserIdAndSecContext(onerel->rd_rel->relowner, + save_sec_context | SECURITY_RESTRICTED_OPERATION); + save_nestlevel = NewGUCNestLevel(); + + /* + * Apply statistical updates, if any, to copied tuple. + * + * Format is: + * { + * "regular": { "columns": ..., "extended": ...}, + * "inherited": { "columns": ..., "extended": ...} + * } + * + */ + if (jb != NULL) + { + JsonbContainer *cont; + + cont = key_lookup_object(&jb->root, "regular"); + import_pg_statistics(onerel, false, cont); + + if (onerel->rd_rel->relhassubclass) + { + cont = key_lookup_object(&jb->root, "inherited"); + import_pg_statistics(onerel, true, cont); + } + } + + /* only modify pg_class row if changes are to be made */ + if ( ! PG_ARGISNULL(2) || ! PG_ARGISNULL(3) ) + { + Relation pg_class_rel; + HeapTuple ctup; + Form_pg_class pgcform; + + /* + * Open the relation, getting ShareUpdateExclusiveLock to ensure that no + * other stat-setting operation can run on it concurrently. + */ + pg_class_rel = table_open(RelationRelationId, ShareUpdateExclusiveLock); + + /* leave if relation could not be opened or locked */ + if (!pg_class_rel) + PG_RETURN_BOOL(false); + + ctup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(ctup)) + elog(ERROR, "pg_class entry for relid %u vanished during statistics import", + relid); + pgcform = (Form_pg_class) GETSTRUCT(ctup); + + /* leave un-set values alone */ + if (! PG_ARGISNULL(2)) + pgcform->reltuples = PG_GETARG_FLOAT4(2); + if (! PG_ARGISNULL(3)) + pgcform->relpages = PG_GETARG_INT32(3); + + heap_inplace_update(pg_class_rel, ctup); + table_close(pg_class_rel, ShareUpdateExclusiveLock); + } + + /* relation_close(onerel, ShareUpdateExclusiveLock); */ + relation_close(onerel, NoLock); + + /* Roll back any GUC changes executed by index functions */ + AtEOXact_GUC(false, save_nestlevel); + + /* Restore userid and security context */ + SetUserIdAndSecContext(save_userid, save_sec_context); + + PG_RETURN_BOOL(true); +} + +/* + * Convert the STATISTICS_KIND strings defined in pg_statistic_export + * back to their defined enum values. + */ +static int16 +decode_stakind_string(char *s) +{ + if (strcmp(s,"MCV") == 0) + return STATISTIC_KIND_MCV; + if (strcmp(s,"HISTOGRAM") == 0) + return STATISTIC_KIND_HISTOGRAM; + if (strcmp(s,"CORRELATION") == 0) + return STATISTIC_KIND_CORRELATION; + if (strcmp(s,"MCELEM") == 0) + return STATISTIC_KIND_MCELEM; + if (strcmp(s,"DECHIST") == 0) + return STATISTIC_KIND_DECHIST; + if (strcmp(s,"RANGE_LENGTH_HISTOGRAM") == 0) + return STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM; + if (strcmp(s,"BOUNDS_HISTOGRAM") == 0) + return STATISTIC_KIND_BOUNDS_HISTOGRAM; + if (strcmp(s,"TRIVIAL") != 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("unknown statistics kind: %s", s))); + + return 0; +} + +/* + * + * Format is: + * + * { + * "regular": + * { + * "columns": { "colname1": ... }}, + * "extended": { "statname1": ...} + * }, + * "inherited": + * { + * "columns": { "colname1": ... }}, + * "extended": { "statname1": ...} + * }, + * } + */ +static +void import_pg_statistics(Relation onerel, bool inh, JsonbContainer *cont) +{ + VacAttrStats **vacattrstats; + JsonbContainer *colscont; + JsonbContainer *extscont; + int natts; + AnlIndexData *indexdata = NULL; + int nindexes = 0; + int i, + ind; + Relation *Irel = NULL; + + /* skip if no statistics of this inheritance type available */ + if (cont == NULL) + return; + + vacattrstats = examine_rel_attributes(onerel, &natts); + + if ((!inh) && + (onerel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)) + { + vac_open_indexes(onerel, AccessShareLock, &nindexes, &Irel); + if (nindexes > 0) + indexdata = build_indexdata(onerel, Irel, nindexes, true); + } + + colscont = key_lookup_object(cont, "columns"); + if (colscont != NULL) + { + for (i = 0; i < natts; i++) + { + VacAttrStats *stats; + JsonbContainer *attrcont; + const char *name; + + stats = vacattrstats[i]; + name = NameStr(*attnumAttName(onerel, stats->tupattnum)); + + attrcont = key_lookup_object(colscont, name); + ImportVacAttrStats(stats, attrcont); + } + update_attstats(RelationGetRelid(onerel), inh, natts, vacattrstats); + + /* now compute the index stats based on imported table stats */ + for (ind = 0; ind < nindexes; ind++) + { + AnlIndexData *thisdata = &indexdata[ind]; + + update_attstats(RelationGetRelid(Irel[ind]), false, + thisdata->attr_cnt, thisdata->vacattrstats); + } + } + + extscont = key_lookup_object(cont, "extended"); + + if (extscont != NULL) + { + /* Build extended statistics (if there are any). */ + + ImportRelationExtStatistics(onerel, false, natts, vacattrstats, extscont); + + if (onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + ImportRelationExtStatistics(onerel, true, natts, vacattrstats, extscont); + } + + if ((!inh) && + (onerel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)) + vac_close_indexes(nindexes, Irel, NoLock); +} + +/* + * Apply all found statistics within a JSON structure to a pre-examined + * VacAttrStats. + */ +void +ImportVacAttrStats( VacAttrStats *stats, JsonbContainer *cont) +{ + JsonbContainer *arraycont; + char *s; + int k; + + /* nothing to import, skip */ + if (cont == NULL) + return; + + stats->stats_valid = true; + + s = key_lookup_cstring(cont, "stanullfrac"); + if (s != NULL) + { + stats->stanullfrac = float4in_internal(s, NULL, "real", s, NULL); + pfree(s); + } + s = key_lookup_cstring(cont, "stawidth"); + if (s != NULL) + { + stats->stawidth = pg_strtoint32(s); + pfree(s); + } + + s = key_lookup_cstring(cont, "stadistinct"); + if (s != NULL) + { + stats->stadistinct = float4in_internal(s, NULL, "real", s, NULL); + pfree(s); + } + + arraycont = key_lookup_array(cont, "stakinds"); + if (arraycont != NULL) + { + for (k = 0; k < STATISTIC_NUM_SLOTS; k++) + { + StdAnalyzeData *mystats; + JsonbValue *e; + char *stakindstr; + + mystats = (StdAnalyzeData *) stats->extra_data; + e = getIthJsonbValueFromContainer(arraycont, k); + + if (e == NULL || (e->type != jbvString)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid format: stakind elements must be strings"))); + + stakindstr = JsonbStringValueToCString(e); + stats->stakind[k] = decode_stakind_string(stakindstr); + pfree(stakindstr); + pfree(e); + + switch(stats->stakind[k]) + { + case STATISTIC_KIND_MCV: + case STATISTIC_KIND_DECHIST: + stats->staop[k] = mystats->eqopr; + stats->stacoll[k] = stats->attrcollid; + break; + + case STATISTIC_KIND_HISTOGRAM: + case STATISTIC_KIND_CORRELATION: + stats->staop[k] = mystats->ltopr; + stats->stacoll[k] = stats->attrcollid; + break; + + case STATISTIC_KIND_MCELEM: + stats->staop[k] = TextEqualOperator; + stats->stacoll[k] = DEFAULT_COLLATION_OID; + break; + + case STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM: + stats->staop[k] = Float8LessOperator; + stats->stacoll[k] = InvalidOid; + break; + + case STATISTIC_KIND_BOUNDS_HISTOGRAM: + default: + stats->staop[k] = InvalidOid; + stats->stacoll[k] = InvalidOid; + break; + } + } + } + + /* stanumbers is an array of arrays of floats */ + arraycont = key_lookup_array(cont, "stanumbers"); + if (arraycont != NULL) + { + for (k = 0; k < STATISTIC_NUM_SLOTS; k++) + { + JsonbValue *j; + JsonbContainer *numarr; + int nelems; + int e; + + j = getIthJsonbValueFromContainer(arraycont, k); + + /* skip out-of-bounds and explicit nulls */ + if (j == NULL) + continue; + + if (j->type == jbvNull) + { + pfree(j); + continue; + } + + if ((j->type != jbvBinary) || + (!JsonContainerIsArray(j->val.binary.data))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, stanumbers must be a binary array or null"))); + + numarr = j->val.binary.data; + pfree(j); + + nelems = JsonContainerSize(numarr); + stats->numnumbers[k] = nelems; + stats->stanumbers[k] = (float4 *) palloc(nelems * sizeof(float4)); + + for (e = 0; e < nelems; e++) + { + JsonbValue *f = getIthJsonbValueFromContainer(numarr, e); + float4 floatval; + char *fstr; + + /* skip out-of-bounds and explicit nulls */ + if (f == NULL || f->type != jbvString) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, elements of stanumbers must a string"))); + + fstr = JsonbStringValueToCString(f); + floatval = float4in_internal(fstr, NULL, "realx3", fstr, NULL); + stats->stanumbers[k][e] = floatval; + pfree(f); + pfree(fstr); + } + } + } + + /* stavalues is an array of arrays of the column attr type */ + arraycont = key_lookup_array(cont, "stavalues"); + if (arraycont != NULL) + { + Oid in_func; + Oid typioparam; + FmgrInfo finfo; + + getTypeInputInfo(stats->attrtypid, &in_func, &typioparam); + fmgr_info(in_func, &finfo); + + for (k = 0; k < STATISTIC_NUM_SLOTS; k++) + { + JsonbValue *j; + JsonbContainer *attrarr; + int nelems; + int e; + + j = getIthJsonbValueFromContainer(arraycont, k); + + /* skip out-of-bounds and explicit nulls */ + if (j == NULL) + continue; + + if (j->type == jbvNull) + { + pfree(j); + continue; + } + + if ((j->type != jbvBinary) || + (!JsonContainerIsArray(j->val.binary.data))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, stavalues must be a binary array or null"))); + + attrarr = j->val.binary.data; + pfree(j); + nelems = JsonContainerSize(attrarr); + stats->numvalues[k] = nelems; + stats->stavalues[k] = (Datum *) palloc(nelems * sizeof(Datum)); + + for (e = 0; e < nelems; e++) + { + JsonbValue *f; + char *fstr; + Datum datum; + + f = getIthJsonbValueFromContainer(attrarr, e); + + /* skip out-of-bounds and explicit nulls */ + if (f == NULL || f->type != jbvString) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, elements of stavalues must be a string"))); + + fstr = JsonbStringValueToCString(f); + datum = InputFunctionCall(&finfo, fstr, typioparam, stats->attrtypmod); + stats->stavalues[k][e] = datum; + pfree(fstr); + pfree(f); + } + } + } +} diff --git a/src/backend/statistics/dependencies.c b/src/backend/statistics/dependencies.c index edb2e5347d..f410510a28 100644 --- a/src/backend/statistics/dependencies.c +++ b/src/backend/statistics/dependencies.c @@ -27,7 +27,9 @@ #include "parser/parsetree.h" #include "statistics/extended_stats_internal.h" #include "statistics/statistics.h" +#include "utils/builtins.h" #include "utils/bytea.h" +#include "utils/float.h" #include "utils/fmgroids.h" #include "utils/fmgrprotos.h" #include "utils/lsyscache.h" @@ -1829,3 +1831,134 @@ dependencies_clauselist_selectivity(PlannerInfo *root, return s1; } + + +/* + * imports functional dependencies between groups of columns + * + * Generates all possible subsets of columns (variations) and computes + * the degree of validity for each one. For example when creating statistics + * on three columns (a,b,c) there are 9 possible dependencies + * + * two columns three columns + * ----------- ------------- + * (a) -> b (a,b) -> c + * (a) -> c (a,c) -> b + * (b) -> a (b,c) -> a + * (b) -> c + * (c) -> a + * (c) -> b + */ +MVDependencies * +import_dependencies(JsonbContainer *cont) +{ + MVDependencies *dependencies = NULL; + + int numcombs; + int i; + + /* no dependencies to import */ + if (cont == NULL) + return NULL; + + /* + * + * format example: + * + * "stxdndependencies": <= you are here + * [ + * { + * "attnums": [ "2", "-1" ], + * "degree": "0.500" + * }, + * ... + * ] + * + */ + numcombs = JsonContainerSize(cont); + + /* no dependencies to import */ + if (numcombs == 0) + return NULL; + + dependencies = palloc(offsetof(MVDependencies, deps) + + numcombs * sizeof(MVDependency *)); + + dependencies->magic = STATS_DEPS_MAGIC; + dependencies->type = STATS_DEPS_TYPE_BASIC; + dependencies->ndeps = numcombs; + + for (i = 0; i < numcombs; i++) + { + MVDependency *d = dependencies->deps[i]; + JsonbValue *elem; + JsonbContainer *dep; + JsonbValue *attnums; + JsonbContainer *attnumscont; + JsonbValue *degree; + char *degree_str; + int numattnums; + int j; + + elem = getIthJsonbValueFromContainer(cont, i); + + if ((elem->type != jbvBinary) || + (!JsonContainerIsObject(elem->val.binary.data))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, stxnddependencies elements must be binary objects"))); + + dep = elem->val.binary.data; + pfree(elem); + + attnums = getKeyJsonValueFromContainer(dep, "attnums", strlen("attnums"), NULL); + if ((attnums == NULL) || (attnums->type != jbvBinary) || + (!JsonContainerIsArray(attnums->val.binary.data))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, stxndependencies must contain attnums array"))); + + attnumscont = attnums->val.binary.data; + pfree(attnums); + numattnums = JsonContainerSize(attnumscont); + + d = palloc(offsetof(MVDependency, attributes) + + numattnums * sizeof(AttrNumber)); + + d->nattributes = numattnums; + + for (j = 0; j < numattnums; j++) + { + JsonbValue *attnum; + char *s; + + attnum = getIthJsonbValueFromContainer(attnumscont, j); + + if (attnum->type != jbvString) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, stxndependencies attnums elements must be strings"))); + + s = JsonbStringValueToCString(attnum); + pfree(attnum); + + d->attributes[j] = pg_strtoint16(s); + pfree(s); + } + + degree = getKeyJsonValueFromContainer(dep, "degree", strlen("degree"), NULL); + if ((degree == NULL) || (degree->type != jbvString)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, stxndependencies elements must have degree element"))); + + degree_str = JsonbStringValueToCString(degree); + pfree(degree); + d->degree = float8in_internal(degree_str, NULL, "double", degree_str, NULL); + pfree(degree_str); + dependencies->deps[i] = d; + } + + + return dependencies; +} diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c index 9f67a57724..4e6e444f4c 100644 --- a/src/backend/statistics/extended_stats.c +++ b/src/backend/statistics/extended_stats.c @@ -2636,3 +2636,262 @@ make_build_data(Relation rel, StatExtEntry *stat, int numrows, HeapTuple *rows, return result; } + +/* form an array of pg_statistic rows (per update_attstats) */ +static Datum +serialize_vacattrstats(VacAttrStats **exprstats, int nexprs) +{ + int exprno; + Oid typOid; + Relation sd; + + ArrayBuildState *astate = NULL; + + sd = table_open(StatisticRelationId, RowExclusiveLock); + + /* lookup OID of composite type for pg_statistic */ + typOid = get_rel_type_id(StatisticRelationId); + if (!OidIsValid(typOid)) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("relation \"%s\" does not have a composite type", + "pg_statistic"))); + + for (exprno = 0; exprno < nexprs; exprno++) + { + int i, + k; + VacAttrStats *stats = exprstats[exprno]; + + Datum values[Natts_pg_statistic]; + bool nulls[Natts_pg_statistic]; + HeapTuple stup; + + if (!stats->stats_valid) + { + astate = accumArrayResult(astate, + (Datum) 0, + true, + typOid, + CurrentMemoryContext); + continue; + } + + /* + * Construct a new pg_statistic tuple + */ + for (i = 0; i < Natts_pg_statistic; ++i) + { + nulls[i] = false; + } + + values[Anum_pg_statistic_starelid - 1] = ObjectIdGetDatum(InvalidOid); + values[Anum_pg_statistic_staattnum - 1] = Int16GetDatum(InvalidAttrNumber); + values[Anum_pg_statistic_stainherit - 1] = BoolGetDatum(false); + values[Anum_pg_statistic_stanullfrac - 1] = Float4GetDatum(stats->stanullfrac); + values[Anum_pg_statistic_stawidth - 1] = Int32GetDatum(stats->stawidth); + values[Anum_pg_statistic_stadistinct - 1] = Float4GetDatum(stats->stadistinct); + i = Anum_pg_statistic_stakind1 - 1; + for (k = 0; k < STATISTIC_NUM_SLOTS; k++) + { + values[i++] = Int16GetDatum(stats->stakind[k]); /* stakindN */ + } + i = Anum_pg_statistic_staop1 - 1; + for (k = 0; k < STATISTIC_NUM_SLOTS; k++) + { + values[i++] = ObjectIdGetDatum(stats->staop[k]); /* staopN */ + } + i = Anum_pg_statistic_stacoll1 - 1; + for (k = 0; k < STATISTIC_NUM_SLOTS; k++) + { + values[i++] = ObjectIdGetDatum(stats->stacoll[k]); /* stacollN */ + } + i = Anum_pg_statistic_stanumbers1 - 1; + for (k = 0; k < STATISTIC_NUM_SLOTS; k++) + { + int nnum = stats->numnumbers[k]; + + if (nnum > 0) + { + int n; + Datum *numdatums = (Datum *) palloc(nnum * sizeof(Datum)); + ArrayType *arry; + + for (n = 0; n < nnum; n++) + numdatums[n] = Float4GetDatum(stats->stanumbers[k][n]); + arry = construct_array_builtin(numdatums, nnum, FLOAT4OID); + values[i++] = PointerGetDatum(arry); /* stanumbersN */ + } + else + { + nulls[i] = true; + values[i++] = (Datum) 0; + } + } + i = Anum_pg_statistic_stavalues1 - 1; + for (k = 0; k < STATISTIC_NUM_SLOTS; k++) + { + if (stats->numvalues[k] > 0) + { + ArrayType *arry; + + arry = construct_array(stats->stavalues[k], + stats->numvalues[k], + stats->statypid[k], + stats->statyplen[k], + stats->statypbyval[k], + stats->statypalign[k]); + values[i++] = PointerGetDatum(arry); /* stavaluesN */ + } + else + { + nulls[i] = true; + values[i++] = (Datum) 0; + } + } + + stup = heap_form_tuple(RelationGetDescr(sd), values, nulls); + + astate = accumArrayResult(astate, + heap_copy_tuple_as_datum(stup, RelationGetDescr(sd)), + false, + typOid, + CurrentMemoryContext); + } + + table_close(sd, RowExclusiveLock); + + return makeArrayResult(astate, CurrentMemoryContext); +} + +/* + * Import requested extended stats, using the pre-computed (single-column) + * stats. + * + * This fetches a list of stats types from pg_statistic_ext, extracts the + * requested stats from the jsonb, and serializes them back into the catalog. + */ +void +ImportRelationExtStatistics(Relation onerel, bool inh, + int natts, + VacAttrStats **vacattrstats, + JsonbContainer *cont) +{ + Relation pg_stext; + ListCell *lc; + List *statslist; + + /* Do nothing if there are no columns to analyze. */ + if (!natts) + return; + + /* Do nothing if there are no stats to import */ + if (cont == NULL) + return; + + /* the list of stats has to be allocated outside the memory context */ + pg_stext = table_open(StatisticExtRelationId, RowExclusiveLock); + statslist = fetch_statentries_for_relation(pg_stext, RelationGetRelid(onerel)); + + /* + * format: + * + * { <= you are here + * : + * { + * "stxkinds": array of single characters (up to 3?), + * "stxdndistinct": [ {ndistinct}, ... ], + * "stxdndependencies": [ {dependency}, ... ] + * "stxdmcv": [ {mcv}, ... ] + * "stxdexprs" : [ {pg_statistic}, ... ] + * }, + * ... + * } + */ + + foreach(lc, statslist) + { + StatExtEntry *stat = (StatExtEntry *) lfirst(lc); + MVNDistinct *ndistinct = NULL; + MVDependencies *dependencies = NULL; + MCVList *mcv = NULL; + Datum exprstats = (Datum) 0; + VacAttrStats **stats; + JsonbValue *exprval; + JsonbContainer *attrcont, + *ndistcont, + *depcont, + *exprcont; + + /* + * Check if we can build these stats based on the column analyzed. If + * not, report this fact (except in autovacuum) and move on. + */ + stats = lookup_var_attr_stats(onerel, stat->columns, stat->exprs, + natts, vacattrstats); + if (!stats) + continue; + + attrcont = key_lookup_object(cont, stat->name); + + /* staname not found, skip */ + if (attrcont == NULL) + continue; + + ndistcont = key_lookup_array(attrcont, "stxdndistinct"); + + if (ndistcont != NULL) + ndistinct = import_ndistinct(ndistcont); + + depcont = key_lookup_array(attrcont, "stxdndependencies"); + if (depcont != NULL) + dependencies = import_dependencies(depcont); + + /* TODO mcvcont for "stxdmcv" and import_mcv() */ + + exprval = getKeyJsonValueFromContainer(attrcont, "stxdexprs", strlen("stxdexprs"), NULL); + if (exprval != NULL) + { + if (exprval->type != jbvNull) + { + int nexprs; + int k; + + if (!JsonContainerIsArray(exprval->val.binary.data)) + errmsg("invalid statistics format, stxndeprs must be array or null"); + + exprcont = exprval->val.binary.data; + + nexprs = JsonContainerSize(exprcont); + + for (k = 0; k < nexprs; k++) + { + JsonbValue *statelem; + + statelem = getIthJsonbValueFromContainer(exprcont, k); + if ((statelem->type != jbvBinary) || + (!JsonContainerIsObject(statelem->val.binary.data))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, stxdexprs elements must be binary objects"))); + + ImportVacAttrStats(stats[k], statelem->val.binary.data); + pfree(statelem); + } + + exprstats = serialize_vacattrstats(stats, nexprs); + + } + pfree(exprval); + } + + + /* store the statistics in the catalog */ + statext_store(stat->statOid, inh, + ndistinct, dependencies, mcv, exprstats, stats); + } + + list_free(statslist); + + table_close(pg_stext, RowExclusiveLock); +} diff --git a/src/backend/statistics/mcv.c b/src/backend/statistics/mcv.c index 03b9f04bb5..8fbb388028 100644 --- a/src/backend/statistics/mcv.c +++ b/src/backend/statistics/mcv.c @@ -2177,3 +2177,9 @@ mcv_clause_selectivity_or(PlannerInfo *root, StatisticExtInfo *stat, return s; } + +MCVList *import_mcv(JsonbContainer *cont) +{ + return NULL; +} + diff --git a/src/backend/statistics/mvdistinct.c b/src/backend/statistics/mvdistinct.c index 6d25c14644..8489a758a6 100644 --- a/src/backend/statistics/mvdistinct.c +++ b/src/backend/statistics/mvdistinct.c @@ -31,6 +31,8 @@ #include "lib/stringinfo.h" #include "statistics/extended_stats_internal.h" #include "statistics/statistics.h" +#include "utils/builtins.h" +#include "utils/float.h" #include "utils/fmgrprotos.h" #include "utils/lsyscache.h" #include "utils/syscache.h" @@ -698,3 +700,121 @@ generate_combinations(CombinationGenerator *state) pfree(current); } + + +/* + * Import ndistinct values from JsonB. + * + * To handle expressions easily, we treat them as system attributes with + * negative attnums, and offset everything by number of expressions to + * allow using Bitmapsets. + */ +MVNDistinct * +import_ndistinct(JsonbContainer *cont) +{ + MVNDistinct *result; + int numcombs; + int i; + + /* no ndistinct to import */ + if (cont == NULL) + return NULL; + + /* each row of the JSON is a combination */ + numcombs = JsonContainerSize(cont); + + /* no ndistinct to import */ + if (numcombs == 0) + return NULL; + + result = palloc(offsetof(MVNDistinct, items) + + numcombs * sizeof(MVNDistinctItem)); + result->magic = STATS_NDISTINCT_MAGIC; + result->type = STATS_NDISTINCT_TYPE_BASIC; + result->nitems = numcombs; + + /* + * format example: + * + * "stxdndistinct": [ + * { + * "attnums": [ "2", "1" ], + * "ndistinct": "4" + * }, + * ... + * ] + * + */ + for (i = 0; i < numcombs; i++) + { + MVNDistinctItem *item; + JsonbValue *elem; + JsonbContainer *combo; + JsonbValue *attnums; + JsonbContainer *attnumscont; + JsonbValue *ndistinct; + char *ndist_str; + int numattnums; + int j; + + item = &result->items[i]; + + elem = getIthJsonbValueFromContainer(cont, i); + + if ((elem->type != jbvBinary) || + (!JsonContainerIsObject(elem->val.binary.data))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, stxndistinct elements must be a binary objects"))); + + combo = elem->val.binary.data; + + attnums = getKeyJsonValueFromContainer(combo, "attnums", strlen("attnums"), NULL); + if ((attnums == NULL) || (attnums->type != jbvBinary) || + (!JsonContainerIsArray(attnums->val.binary.data))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, stxndistinct must contain attnums array"))); + + attnumscont = attnums->val.binary.data; + numattnums = JsonContainerSize(attnumscont); + + item->attributes = palloc(sizeof(AttrNumber) * numattnums); + item->nattributes = numattnums; + + for (j = 0; j < numattnums; j++) + { + JsonbValue *attnum; + char *s; + attnum = getIthJsonbValueFromContainer(attnumscont, j); + + if (attnum->type != jbvString) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, stxndistinct attnums elements must be strings, but one is %s", + JsonbTypeName(attnum)))); + + s = JsonbStringValueToCString(attnum); + + item->attributes[j] = pg_strtoint16(s); + pfree(s); + pfree(attnum); + } + + ndistinct = getKeyJsonValueFromContainer(combo, "ndistinct", strlen("ndistinct"), NULL); + if ((ndistinct == NULL) || (ndistinct->type != jbvString)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, stxndistinct elements must have ndistinct element"))); + + ndist_str = JsonbStringValueToCString(ndistinct); + item->ndistinct = float8in_internal(ndist_str, NULL, "double", ndist_str, NULL); + + pfree(ndist_str); + pfree(ndistinct); + pfree(attnums); + pfree(elem); + } + + return result; +} diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index 4def90b805..4e609ca3b6 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -508,3 +508,156 @@ RESET ROLE; DROP TABLE vacowned; DROP TABLE vacowned_parted; DROP ROLE regress_vacuum; +CREATE TYPE stats_import_complex_type AS ( + a integer, + b float, + c text, + d date, + e jsonb); +CREATE TABLE stats_import_test( + id INTEGER PRIMARY KEY, + name text, + comp stats_import_complex_type +); +INSERT INTO stats_import_test +SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_import_complex_type +UNION ALL +SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_import_complex_type +UNION ALL +SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_import_complex_type +UNION ALL +SELECT 4, 'four', NULL; +CREATE INDEX is_odd ON stats_import_test(((comp).a % 2 = 1)); +CREATE STATISTICS oddness ON name, ((comp).a % 2 = 1) FROM stats_import_test; +ANALYZE stats_import_test; +CREATE TABLE stats_export AS +SELECT e.* +FROM pg_catalog.pg_statistic_export AS e +WHERE e.schemaname = 'public' +AND e.relname = 'stats_import_test'; +SELECT c.reltuples AS before_tuples, c.relpages AS before_pages +FROM pg_class AS c +WHERE oid = 'stats_import_test'::regclass; + before_tuples | before_pages +---------------+-------------- + 4 | 1 +(1 row) + +-- test settting tuples and pages but no columns +SELECT pg_import_rel_stats(c.oid, current_setting('server_version_num')::integer, + 1000.0, 200, NULL::jsonb) +FROM pg_class AS c +WHERE oid = 'stats_import_test'::regclass; + pg_import_rel_stats +--------------------- + t +(1 row) + +SELECT c.reltuples AS after_tuples, c.relpages AS after_pages +FROM pg_class AS c +WHERE oid = 'stats_import_test'::regclass; + after_tuples | after_pages +--------------+------------- + 1000 | 200 +(1 row) + +CREATE TEMPORARY TABLE orig_stats +AS +SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct, + stakind1, stakind2, stakind3, stakind4, stakind5, + staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, + stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, + stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3, + stavalues4::text AS sv4, stavalues5::text AS sv5 +FROM pg_statistic AS s +WHERE s.starelid = 'stats_import_test'::regclass; +CREATE TEMPORARY TABLE orig_export +AS +SELECT e.* +FROM stats_export AS e +WHERE e.schemaname = 'public' +AND e.relname = 'stats_import_test'; +-- create a table just like stats_import_test +CREATE TABLE stats_import_clone ( LIKE stats_import_test ); +-- create an index that does not exist on stats_import_test +CREATE INDEX is_even ON stats_import_clone(((comp).a % 2 = 0)); +-- rename statistics object to allow for a same-named one on the clone +ALTER STATISTICS oddness RENAME TO oddness_original; +CREATE STATISTICS oddness ON name, ((comp).a % 2 = 1) FROM stats_import_clone; +-- copy table stats to clone table +SELECT pg_import_rel_stats(c.oid, e.server_version_num, + e.n_tuples, e.n_pages, e.stats) +FROM pg_class AS c +JOIN pg_namespace AS n +ON n.oid = c.relnamespace +JOIN orig_export AS e +ON e.schemaname = 'public' +AND e.relname = 'stats_import_test' +WHERE c.oid = 'stats_import_clone'::regclass; + pg_import_rel_stats +--------------------- + t +(1 row) + +-- stats should match +SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct, + stakind1, stakind2, stakind3, stakind4, stakind5, + staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, + stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, + stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3, + stavalues4::text AS sv4, stavalues5::text AS sv5 +FROM pg_statistic AS s +WHERE s.starelid = 'stats_import_test'::regclass +EXCEPT +SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct, + stakind1, stakind2, stakind3, stakind4, stakind5, + staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, + stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, + stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3, + stavalues4::text AS sv4, stavalues5::text AS sv5 +FROM pg_statistic AS s +WHERE s.starelid = 'stats_import_clone'::regclass; + staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 +-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+----- +(0 rows) + +-- compare all except stxdmcv and oid-related stxdexprs +SELECT d.stxdinherit, d.stxdndistinct, d.stxddependencies, xpr.xpr +FROM pg_statistic_ext AS e +JOIN pg_statistic_ext_data AS d ON d.stxoid = e.oid +LEFT JOIN LATERAL ( + SELECT + array_agg(ROW(x.staattnum, x.stainherit, x.stanullfrac, x.stawidth, + x.stadistinct, x.stakind1, x.stakind2, x.stakind3, + x.stakind4, x.stakind5, x.stanumbers1, x.stanumbers2, + x.stanumbers3, x.stanumbers4, x.stanumbers5, x.stavalues1, + x.stavalues2, x.stavalues3, x.stavalues4, x.stavalues5 + )::text ORDER BY x.ordinality) + FROM unnest(d.stxdexpr) WITH ORDINALITY AS x +) AS xpr(xpr) ON d.stxdexpr IS NOT NULL +WHERE e.stxrelid = 'stats_import_test'::regclass +AND e.stxname = 'oddness_original' +EXCEPT +SELECT d.stxdinherit, d.stxdndistinct, d.stxddependencies, xpr.xpr +FROM pg_statistic_ext AS e +JOIN pg_statistic_ext_data AS d ON d.stxoid = e.oid +LEFT JOIN LATERAL ( + SELECT + array_agg(ROW(x.staattnum, x.stainherit, x.stanullfrac, x.stawidth, + x.stadistinct, x.stakind1, x.stakind2, x.stakind3, + x.stakind4, x.stakind5, x.stanumbers1, x.stanumbers2, + x.stanumbers3, x.stanumbers4, x.stanumbers5, x.stavalues1, + x.stavalues2, x.stavalues3, x.stavalues4, x.stavalues5 + )::text ORDER BY x.ordinality) + FROM unnest(d.stxdexpr) WITH ORDINALITY AS x +) AS xpr(xpr) ON d.stxdexpr IS NOT NULL +WHERE e.stxrelid = 'stats_import_clone'::regclass +AND e.stxname = 'oddness'; + stxdinherit | stxdndistinct | stxddependencies | xpr +-------------+---------------+------------------+----- +(0 rows) + +DROP TABLE stats_export; +DROP TABLE stats_import_clone; +DROP TABLE stats_import_test; +DROP TYPE stats_import_complex_type; diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql index 51d7b1fecc..b42f35a8e3 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -377,3 +377,149 @@ RESET ROLE; DROP TABLE vacowned; DROP TABLE vacowned_parted; DROP ROLE regress_vacuum; + + +CREATE TYPE stats_import_complex_type AS ( + a integer, + b float, + c text, + d date, + e jsonb); + +CREATE TABLE stats_import_test( + id INTEGER PRIMARY KEY, + name text, + comp stats_import_complex_type +); + +INSERT INTO stats_import_test +SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_import_complex_type +UNION ALL +SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_import_complex_type +UNION ALL +SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_import_complex_type +UNION ALL +SELECT 4, 'four', NULL; + +CREATE INDEX is_odd ON stats_import_test(((comp).a % 2 = 1)); + +CREATE STATISTICS oddness ON name, ((comp).a % 2 = 1) FROM stats_import_test; + +ANALYZE stats_import_test; + +CREATE TABLE stats_export AS +SELECT e.* +FROM pg_catalog.pg_statistic_export AS e +WHERE e.schemaname = 'public' +AND e.relname = 'stats_import_test'; + +SELECT c.reltuples AS before_tuples, c.relpages AS before_pages +FROM pg_class AS c +WHERE oid = 'stats_import_test'::regclass; + +-- test settting tuples and pages but no columns +SELECT pg_import_rel_stats(c.oid, current_setting('server_version_num')::integer, + 1000.0, 200, NULL::jsonb) +FROM pg_class AS c +WHERE oid = 'stats_import_test'::regclass; + +SELECT c.reltuples AS after_tuples, c.relpages AS after_pages +FROM pg_class AS c +WHERE oid = 'stats_import_test'::regclass; + +CREATE TEMPORARY TABLE orig_stats +AS +SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct, + stakind1, stakind2, stakind3, stakind4, stakind5, + staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, + stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, + stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3, + stavalues4::text AS sv4, stavalues5::text AS sv5 +FROM pg_statistic AS s +WHERE s.starelid = 'stats_import_test'::regclass; + +CREATE TEMPORARY TABLE orig_export +AS +SELECT e.* +FROM stats_export AS e +WHERE e.schemaname = 'public' +AND e.relname = 'stats_import_test'; + +-- create a table just like stats_import_test +CREATE TABLE stats_import_clone ( LIKE stats_import_test ); + +-- create an index that does not exist on stats_import_test +CREATE INDEX is_even ON stats_import_clone(((comp).a % 2 = 0)); + +-- rename statistics object to allow for a same-named one on the clone +ALTER STATISTICS oddness RENAME TO oddness_original; + +CREATE STATISTICS oddness ON name, ((comp).a % 2 = 1) FROM stats_import_clone; + +-- copy table stats to clone table +SELECT pg_import_rel_stats(c.oid, e.server_version_num, + e.n_tuples, e.n_pages, e.stats) +FROM pg_class AS c +JOIN pg_namespace AS n +ON n.oid = c.relnamespace +JOIN orig_export AS e +ON e.schemaname = 'public' +AND e.relname = 'stats_import_test' +WHERE c.oid = 'stats_import_clone'::regclass; + +-- stats should match +SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct, + stakind1, stakind2, stakind3, stakind4, stakind5, + staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, + stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, + stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3, + stavalues4::text AS sv4, stavalues5::text AS sv5 +FROM pg_statistic AS s +WHERE s.starelid = 'stats_import_test'::regclass +EXCEPT +SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct, + stakind1, stakind2, stakind3, stakind4, stakind5, + staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, + stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, + stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3, + stavalues4::text AS sv4, stavalues5::text AS sv5 +FROM pg_statistic AS s +WHERE s.starelid = 'stats_import_clone'::regclass; + +-- compare all except stxdmcv and oid-related stxdexprs +SELECT d.stxdinherit, d.stxdndistinct, d.stxddependencies, xpr.xpr +FROM pg_statistic_ext AS e +JOIN pg_statistic_ext_data AS d ON d.stxoid = e.oid +LEFT JOIN LATERAL ( + SELECT + array_agg(ROW(x.staattnum, x.stainherit, x.stanullfrac, x.stawidth, + x.stadistinct, x.stakind1, x.stakind2, x.stakind3, + x.stakind4, x.stakind5, x.stanumbers1, x.stanumbers2, + x.stanumbers3, x.stanumbers4, x.stanumbers5, x.stavalues1, + x.stavalues2, x.stavalues3, x.stavalues4, x.stavalues5 + )::text ORDER BY x.ordinality) + FROM unnest(d.stxdexpr) WITH ORDINALITY AS x +) AS xpr(xpr) ON d.stxdexpr IS NOT NULL +WHERE e.stxrelid = 'stats_import_test'::regclass +AND e.stxname = 'oddness_original' +EXCEPT +SELECT d.stxdinherit, d.stxdndistinct, d.stxddependencies, xpr.xpr +FROM pg_statistic_ext AS e +JOIN pg_statistic_ext_data AS d ON d.stxoid = e.oid +LEFT JOIN LATERAL ( + SELECT + array_agg(ROW(x.staattnum, x.stainherit, x.stanullfrac, x.stawidth, + x.stadistinct, x.stakind1, x.stakind2, x.stakind3, + x.stakind4, x.stakind5, x.stanumbers1, x.stanumbers2, + x.stanumbers3, x.stanumbers4, x.stanumbers5, x.stavalues1, + x.stavalues2, x.stavalues3, x.stavalues4, x.stavalues5 + )::text ORDER BY x.ordinality) + FROM unnest(d.stxdexpr) WITH ORDINALITY AS x +) AS xpr(xpr) ON d.stxdexpr IS NOT NULL +WHERE e.stxrelid = 'stats_import_clone'::regclass +AND e.stxname = 'oddness'; + +DROP TABLE stats_export; +DROP TABLE stats_import_clone; +DROP TABLE stats_import_test; +DROP TYPE stats_import_complex_type; diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index c76ec52c55..61e01d1b90 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -28014,6 +28014,48 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset in identifying the specific disk files associated with database objects. + + Database Object Statistics Import Functions + + + + + Function + + + Description + + + + + + + + + pg_import_rel_stats + + pg_import_rel_stats ( relation regclass, server_version_num integer, num_tuples float4, num_pages integer, column_stats jsonb ) + boolean + + + Modifies the pg_class row with the + oid matching relation + to set the reltuples and + relpages fields. This is done nontransactionally. + The pg_statistic rows for the + statrelid matching relation + are replaced with the values found in column_stats, + and this is done transactionally. The purpose of this function is to apply + statistics values in an upgrade situation that are "good enough" for system + operation until they are replaced by the next auto-analyze. This function + is used by pg_upgrade and pg_restore + to convey the statistics from the old system version into the new one. + + + + +
+ Database Object Location Functions -- 2.41.0