From 77b74acf89bb3b91ec3f3603b099034ea341a5af Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Tue, 17 Sep 2024 01:57:52 -0400 Subject: [PATCH v29 5/7] Create functions pg_restore_relation_stats, pg_restore_attribute_stats These functions are variadic requivalents of the pg_set_*_stats() functions, which have a function signature each stat getting its own defined parameter (or parameter pair, as the case may be). Such a rigid function signature would make future compability difficult, and future compatibility is just what pg_dump and pg_upgrade need. Instead, these functions have all input arguments put into a variable argument list organized in name-value pairs. The leading or "keyword" parameters must all be of type text and the string must exactly correspond to the name of a statistics parameter in the corresponding pg_set_X_stats function. The trailing or "value" parameter must be of the type expected by the same-named parameter in the pg_set_X_stats function. Names that do not match a parameter name and types that do not match the expected type will emit a warning and be ignored. The intention of these functions is to be used in pg_dump/pg_restore and pg_upgrade to allow the user to avoid having to run vacuumdb --analyze-in-stages after an upgrade or restore. --- src/include/catalog/pg_proc.dat | 18 + src/include/statistics/stats_utils.h | 5 + src/backend/statistics/attribute_stats.c | 16 +- src/backend/statistics/relation_stats.c | 72 +- src/backend/statistics/stats_utils.c | 83 ++ src/test/regress/expected/stats_import.out | 952 ++++++++++++++++++++- src/test/regress/sql/stats_import.sql | 694 ++++++++++++++- doc/src/sgml/func.sgml | 157 ++++ 8 files changed, 1975 insertions(+), 22 deletions(-) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 4b7236bbc9..f4d8eb14ec 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12338,5 +12338,23 @@ proargtypes => 'regclass name bool', proargnames => '{relation,attname,bool}', prosrc => 'pg_clear_attribute_stats' }, +{ oid => '8052', + descr => 'restore statistics on relation', + proname => 'pg_restore_relation_stats', provolatile => 'v', proisstrict => 'f', + provariadic => 'any', + proparallel => 'u', prorettype => 'bool', + proargtypes => 'any', + proargnames => '{relation}', + proargmodes => '{v}', + prosrc => 'pg_restore_relation_stats' }, +{ oid => '8053', + descr => 'restore statistics on attribute', + proname => 'pg_restore_attribute_stats', provolatile => 'v', proisstrict => 'f', + provariadic => 'any', + proparallel => 'u', prorettype => 'bool', + proargtypes => 'any', + proargnames => '{relation}', + proargmodes => '{v}', + prosrc => 'pg_restore_attribute_stats' }, ] diff --git a/src/include/statistics/stats_utils.h b/src/include/statistics/stats_utils.h index a6926ce79a..9d723d9efd 100644 --- a/src/include/statistics/stats_utils.h +++ b/src/include/statistics/stats_utils.h @@ -33,4 +33,9 @@ extern void stats_check_arg_pair(FunctionCallInfo fcinfo, StatsArgInfo *arginfo, extern void stats_lock_check_privileges(Oid reloid); +extern void stats_fill_fcinfo_from_arg_pairs(FunctionCallInfo pairs_fcinfo, + FunctionCallInfo positional_fcinfo, + StatsArgInfo *arginfo, + int elevel); + #endif /* STATS_UTILS_H */ diff --git a/src/backend/statistics/attribute_stats.c b/src/backend/statistics/attribute_stats.c index 0f95a1e46b..b93729fb0a 100644 --- a/src/backend/statistics/attribute_stats.c +++ b/src/backend/statistics/attribute_stats.c @@ -71,7 +71,7 @@ static struct StatsArgInfo attarginfo[] = [HISTOGRAM_BOUNDS_ARG] = {"histogram_bounds", TEXTOID}, [CORRELATION_ARG] = {"correlation", FLOAT4OID}, [MOST_COMMON_ELEMS_ARG] = {"most_common_elems", TEXTOID}, - [MOST_COMMON_ELEM_FREQS_ARG] = {"most_common_elems_freq", FLOAT4ARRAYOID}, + [MOST_COMMON_ELEM_FREQS_ARG] = {"most_common_elem_freqs", FLOAT4ARRAYOID}, [ELEM_COUNT_HISTOGRAM_ARG] = {"elem_count_histogram", FLOAT4ARRAYOID}, [RANGE_LENGTH_HISTOGRAM_ARG] = {"range_length_histogram", TEXTOID}, [RANGE_EMPTY_FRAC_ARG] = {"range_empty_frac", FLOAT4OID}, @@ -831,3 +831,17 @@ pg_clear_attribute_stats(PG_FUNCTION_ARGS) PG_RETURN_BOOL(delete_pg_statistic(reloid, attnum, inherited)); } + +Datum +pg_restore_attribute_stats(PG_FUNCTION_ARGS) +{ + LOCAL_FCINFO(positional_fcinfo, NUM_ATTRIBUTE_STATS_ARGS); + + InitFunctionCallInfoData(*positional_fcinfo, NULL, NUM_ATTRIBUTE_STATS_ARGS, + InvalidOid, NULL, NULL); + + stats_fill_fcinfo_from_arg_pairs(fcinfo, positional_fcinfo, attarginfo, + WARNING); + PG_RETURN_BOOL(attribute_statistics_update(positional_fcinfo, WARNING)); +} + diff --git a/src/backend/statistics/relation_stats.c b/src/backend/statistics/relation_stats.c index a6143cc674..adf409d652 100644 --- a/src/backend/statistics/relation_stats.c +++ b/src/backend/statistics/relation_stats.c @@ -15,6 +15,7 @@ *------------------------------------------------------------------------- */ +#include #include "postgres.h" #include "access/heapam.h" @@ -51,13 +52,19 @@ static StatsArgInfo relarginfo[] = [NUM_RELATION_STATS_ARGS] = {0} }; -static bool relation_statistics_update(FunctionCallInfo fcinfo, int elevel); +static bool relation_statistics_update(FunctionCallInfo fcinfo, int elevel, bool implace); /* * Internal function for modifying statistics for a relation. + * + * If inplace is set to true, the function will update pg_class using in-place + * update which is non-transactional. This is the same behavior as in found in + * the ANALYZE command. This behavior makes sense in an upgrade and restore + * situations where we don't want to bloat pg_class, but would be an + * undesirable surprise in most other situations. */ static bool -relation_statistics_update(FunctionCallInfo fcinfo, int elevel) +relation_statistics_update(FunctionCallInfo fcinfo, int elevel, bool inplace) { Oid reloid; Relation crel; @@ -68,8 +75,6 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel) bool nulls[3] = {false, false, false}; int ncols = 0; TupleDesc tupdesc; - HeapTuple newtup; - stats_check_required_arg(fcinfo, relarginfo, RELATION_ARG); reloid = PG_GETARG_OID(RELATION_ARG); @@ -111,8 +116,12 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel) if (relpages != pgcform->relpages) { - replaces[ncols] = Anum_pg_class_relpages; - values[ncols] = Int32GetDatum(relpages); + if (inplace) + pgcform->relpages = relpages; + else { + replaces[ncols] = Anum_pg_class_relpages; + values[ncols] = Int32GetDatum(relpages); + } ncols++; } } @@ -132,8 +141,13 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel) if (reltuples != pgcform->reltuples) { - replaces[ncols] = Anum_pg_class_reltuples; - values[ncols] = Float4GetDatum(reltuples); + if (inplace) + pgcform->reltuples = reltuples; + else + { + replaces[ncols] = Anum_pg_class_reltuples; + values[ncols] = Float4GetDatum(reltuples); + } ncols++; } } @@ -153,8 +167,12 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel) if (relallvisible != pgcform->relallvisible) { - replaces[ncols] = Anum_pg_class_relallvisible; - values[ncols] = Int32GetDatum(relallvisible); + if (inplace) + pgcform->relallvisible = relallvisible; + { + replaces[ncols] = Anum_pg_class_relallvisible; + values[ncols] = Int32GetDatum(relallvisible); + } ncols++; } } @@ -166,11 +184,18 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel) return false; } - newtup = heap_modify_tuple_by_cols(ctup, tupdesc, ncols, replaces, values, - nulls); + if (inplace) + heap_inplace_update(crel, ctup); + else + { + HeapTuple newtup; - CatalogTupleUpdate(crel, &newtup->t_self, newtup); - heap_freetuple(newtup); + newtup = heap_modify_tuple_by_cols(ctup, tupdesc, ncols, replaces, values, + nulls); + + CatalogTupleUpdate(crel, &newtup->t_self, newtup); + heap_freetuple(newtup); + } /* release the lock, consistent with vac_update_relstats() */ table_close(crel, RowExclusiveLock); @@ -184,7 +209,7 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel) Datum pg_set_relation_stats(PG_FUNCTION_ARGS) { - PG_RETURN_BOOL(relation_statistics_update(fcinfo, ERROR)); + PG_RETURN_BOOL(relation_statistics_update(fcinfo, ERROR, false)); } /* @@ -207,5 +232,20 @@ pg_clear_relation_stats(PG_FUNCTION_ARGS) newfcinfo->args[3].value = DEFAULT_RELALLVISIBLE; newfcinfo->args[3].isnull = false; - PG_RETURN_BOOL(relation_statistics_update(newfcinfo, ERROR)); + PG_RETURN_BOOL(relation_statistics_update(newfcinfo, ERROR, false)); +} + +Datum +pg_restore_relation_stats(PG_FUNCTION_ARGS) +{ + LOCAL_FCINFO(positional_fcinfo, NUM_RELATION_STATS_ARGS); + + InitFunctionCallInfoData(*positional_fcinfo, NULL, + NUM_RELATION_STATS_ARGS, + InvalidOid, NULL, NULL); + + stats_fill_fcinfo_from_arg_pairs(fcinfo, positional_fcinfo, relarginfo, + WARNING); + + PG_RETURN_BOOL(relation_statistics_update(positional_fcinfo, WARNING, true)); } diff --git a/src/backend/statistics/stats_utils.c b/src/backend/statistics/stats_utils.c index 4780e98c17..56699a669b 100644 --- a/src/backend/statistics/stats_utils.c +++ b/src/backend/statistics/stats_utils.c @@ -18,6 +18,7 @@ #include "access/relation.h" #include "catalog/pg_database.h" +#include "funcapi.h" #include "miscadmin.h" #include "statistics/stats_utils.h" #include "utils/array.h" @@ -168,3 +169,85 @@ stats_lock_check_privileges(Oid reloid) relation_close(rel, NoLock); } + +/* + * Find the argument number for the given argument name, returning -1 if not + * found. + */ +static int +get_argnum(const char *argname, StatsArgInfo *arginfo, int elevel) +{ + int argnum; + + for (argnum = 0; arginfo[argnum].argname != NULL; argnum++) + if (strcasecmp(argname, arginfo[argnum].argname) == 0) + return argnum; + + ereport(elevel, + (errmsg("unrecognized argument name: \"%s\"", argname))); + + return -1; +} + +/* + * Translate variadic argument pairs from 'pairs_fcinfo' into a + * 'positional_fcinfo' appropriate for calling relation_statistics_update() or + * attribute_statistics_update() with positional arguments. + * + * Caller should have already initialized positional_fcinfo with a size + * appropriate for calling the intended positional function, and arginfo + * should also match the intended positional function. + */ +void +stats_fill_fcinfo_from_arg_pairs(FunctionCallInfo pairs_fcinfo, + FunctionCallInfo positional_fcinfo, + StatsArgInfo *arginfo, + int elevel) +{ + Datum *args; + bool *argnulls; + Oid *types; + int nargs; + + for (int i = 0; arginfo[i].argname != NULL; i++) + positional_fcinfo->args[i].isnull = true; + + nargs = extract_variadic_args(pairs_fcinfo, 0, true, + &args, &types, &argnulls); + + if (nargs % 2 != 0) + ereport(ERROR, + errmsg("need even number of variable arguments")); + + for (int i = 0; i < nargs; i+=2) + { + int argnum; + char *argname; + + if (argnulls[i] || types[i] != TEXTOID) + ereport(ERROR, (errmsg("need text argument names"))); + + if (argnulls[i+1]) + continue; + + argname = TextDatumGetCString(args[i]); + + /* + * Version is not a valid positional argument. In the future, it can + * be used to interpret older statistics properly, but it is ignored + * for now. + */ + if (strcasecmp(argname, "version") == 0) + continue; + + argnum = get_argnum(argname, arginfo, elevel); + + if (argnum >= 0 && + stats_check_arg_type(argname, types[i+1], arginfo[argnum].argtype, + elevel)) + { + positional_fcinfo->args[argnum].value = args[i+1]; + positional_fcinfo->args[argnum].isnull = false; + } + } +} diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index 1a1a6fc6ff..6219c48aa3 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -263,7 +263,7 @@ SELECT pg_catalog.pg_set_attribute_stats( most_common_freqs => '{0.2,0.1}'::real[] ); ERROR: invalid input syntax for type integer: "2023-09-30" --- warning: mcv cast failure +-- warn: mcv cast failure SELECT pg_catalog.pg_set_attribute_stats( relation => 'stats_import.test'::regclass, attname => 'id'::name, @@ -388,7 +388,7 @@ SELECT pg_catalog.pg_set_attribute_stats( n_distinct => -0.1::real, most_common_elems => '{one,two}'::text ); -ERROR: "most_common_elems_freq" must be specified when "most_common_elems" is specified +ERROR: "most_common_elem_freqs" must be specified when "most_common_elems" is specified -- warn: mcelem / mcelem null mismatch part 2 SELECT pg_catalog.pg_set_attribute_stats( relation => 'stats_import.test'::regclass, @@ -399,7 +399,7 @@ SELECT pg_catalog.pg_set_attribute_stats( n_distinct => -0.1::real, most_common_elem_freqs => '{0.3,0.2,0.2,0.3}'::real[] ); -ERROR: "most_common_elems" must be specified when "most_common_elems_freq" is specified +ERROR: "most_common_elems" must be specified when "most_common_elem_freqs" is specified -- ok: mcelem SELECT pg_catalog.pg_set_attribute_stats( relation => 'stats_import.test'::regclass, @@ -596,6 +596,741 @@ SELECT pg_catalog.pg_set_attribute_stats( ); ERROR: maximum number of statistics slots exceeded: 6 -- +-- Clear attribute stats to try again with restore functions +-- (relation stats were already cleared). +-- +SELECT + pg_catalog.pg_clear_attribute_stats( + 'stats_import.test'::regclass, + s.attname, + s.inherited) +FROM pg_catalog.pg_stats AS s +WHERE s.schemaname = 'stats_import' +AND s.tablename = 'test' +ORDER BY s.attname, s.inherited; + pg_clear_attribute_stats +-------------------------- + t + t + t +(3 rows) + +-- reject: object doesn't exist +SELECT * +FROM pg_restore_relation_stats( + 'relation', '0'::oid::regclass, + 'version', 150000::integer, + 'relpages', '17'::integer, + 'reltuples', 400::real, + 'relallvisible', 4::integer); +ERROR: could not open relation with OID 0 +-- ok: set all stats +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass, + 'version', 150000::integer, + 'relpages', '17'::integer, + 'reltuples', 400::real, + 'relallvisible', 4::integer); + pg_restore_relation_stats +--------------------------- + t +(1 row) + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 17 | 400 | 4 +(1 row) + +-- ok: just relpages +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass, + 'version', 150000::integer, + 'relpages', '16'::integer); + pg_restore_relation_stats +--------------------------- + t +(1 row) + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 16 | 400 | 4 +(1 row) + +-- ok: just reltuples +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass, + 'version', 150000::integer, + 'reltuples', '500'::real); + pg_restore_relation_stats +--------------------------- + t +(1 row) + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 16 | 500 | 4 +(1 row) + +-- ok: just relallvisible +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass, + 'version', 150000::integer, + 'relallvisible', 5::integer); + pg_restore_relation_stats +--------------------------- + t +(1 row) + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 16 | 500 | 5 +(1 row) + +-- warn: bad relpages type +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass, + 'version', 150000::integer, + 'relpages', 'nope'::text, + 'reltuples', 400.0::real, + 'relallvisible', 4::integer); +WARNING: argument "relpages" has type "text", expected type "integer" + pg_restore_relation_stats +--------------------------- + t +(1 row) + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 16 | 400 | 4 +(1 row) + +-- error: object does not exist +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', '0'::oid::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); +ERROR: could not open relation with OID 0 +-- error: relation null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', NULL::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); +ERROR: "relation" cannot be NULL +-- error: attname null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', NULL::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); +ERROR: "attname" cannot be NULL +-- error: inherited null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', NULL::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); +ERROR: "inherited" cannot be NULL +-- ok: no stakinds +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.4::real, + 'avg_width', 5::integer, + 'n_distinct', 0.6::real); + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.4 | 5 | 0.6 | | | | | | | | | | +(1 row) + +-- warn: null_frac null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', NULL::real, + 'avg_width', 3::integer, + 'n_distinct', 0.4::real); + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.4 | 3 | 0.4 | | | | | | | | | | +(1 row) + +-- warn: avg_width null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.2::real, + 'avg_width', NULL::integer, + 'n_distinct', 0.5::real); + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.2 | 3 | 0.5 | | | | | | | | | | +(1 row) + +-- warn: n_distinct null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.3::real, + 'avg_width', 4::integer, + 'n_distinct', NULL::real); + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.3 | 4 | 0.5 | | | | | | | | | | +(1 row) + +-- warn: mcv / mcf null mismatch part 1 +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.6::real, + 'avg_width', 7::integer, + 'n_distinct', -0.7::real, + 'most_common_freqs', '{0.1,0.2,0.3}'::real[] + ); +WARNING: "most_common_vals" must be specified when "most_common_freqs" is specified + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.6 | 7 | -0.7 | | | | | | | | | | +(1 row) + +-- warn: mcv / mcf null mismatch part 2 +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.7::real, + 'avg_width', 8::integer, + 'n_distinct', -0.8::real, + 'most_common_vals', '{1,2,3}'::text + ); +WARNING: "most_common_freqs" must be specified when "most_common_vals" is specified + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.7 | 8 | -0.8 | | | | | | | | | | +(1 row) + +-- warn: mcv / mcf type mismatch +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.8::real, + 'avg_width', 9::integer, + 'n_distinct', -0.9::real, + 'most_common_vals', '{2,1,3}'::text, + 'most_common_freqs', '{0.2,0.1}'::double precision[] + ); +WARNING: argument "most_common_freqs" has type "double precision[]", expected type "real[]" +WARNING: "most_common_freqs" must be specified when "most_common_vals" is specified + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.8 | 9 | -0.9 | | | | | | | | | | +(1 row) + +-- warn: mcv cast failure +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.9::real, + 'avg_width', 10::integer, + 'n_distinct', -0.4::real, + 'most_common_vals', '{2,four,3}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[] + ); +WARNING: invalid input syntax for type integer: "four" + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.9 | 10 | -0.4 | | | | | | | | | | +(1 row) + +-- ok: mcv+mcf +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 1::integer, + 'n_distinct', -0.1::real, + 'most_common_vals', '{2,1,3}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[] + ); + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.1 | 1 | -0.1 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | | +(1 row) + +-- warn: NULL in histogram array +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.2::real, + 'avg_width', 2::integer, + 'n_distinct', -0.2::real, + 'histogram_bounds', '{1,NULL,3,4}'::text + ); +WARNING: "histogram_bounds" array cannot contain NULL values + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.2 | 2 | -0.2 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | | +(1 row) + +-- ok: histogram_bounds +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.3::real, + 'avg_width', 3::integer, + 'n_distinct', -0.3::real, + 'histogram_bounds', '{1,2,3,4}'::text ); + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.3 | 3 | -0.3 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | +(1 row) + +-- warn: elem_count_histogram null element +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'tags'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.4::real, + 'avg_width', 5::integer, + 'n_distinct', -0.4::real, + 'elem_count_histogram', '{1,1,NULL,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] + ); +WARNING: "elem_count_histogram" array cannot contain NULL values + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | tags | f | 0.4 | 5 | -0.4 | | | | | | | | | | +(1 row) + +-- ok: elem_count_histogram +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'tags'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 6::integer, + 'n_distinct', -0.55::real, + 'elem_count_histogram', '{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] + ); + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------+------------------------ + stats_import | test | tags | f | 0.5 | 6 | -0.55 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | | +(1 row) + +-- range stats on a scalar type +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.6::real, + 'avg_width', 7::integer, + 'n_distinct', -0.15::real, + 'range_empty_frac', 0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text + ); +WARNING: attribute "id" is not a range type +DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.6 | 7 | -0.15 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | +(1 row) + +-- warn: range_empty_frac range_length_hist null mismatch +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.7::real, + 'avg_width', 8::integer, + 'n_distinct', -0.25::real, + 'range_length_histogram', '{399,499,Infinity}'::text + ); +WARNING: "range_empty_frac" must be specified when "range_length_histogram" is specified + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | arange | f | 0.7 | 8 | -0.25 | | | | | | | | | | +(1 row) + +-- warn: range_empty_frac range_length_hist null mismatch part 2 +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.8::real, + 'avg_width', 9::integer, + 'n_distinct', -0.35::real, + 'range_empty_frac', 0.5::real + ); +WARNING: "range_length_histogram" must be specified when "range_empty_frac" is specified + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | arange | f | 0.8 | 9 | -0.35 | | | | | | | | | | +(1 row) + +-- ok: range_empty_frac + range_length_hist +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.9::real, + 'avg_width', 1::integer, + 'n_distinct', -0.19::real, + 'range_empty_frac', 0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text + ); + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | arange | f | 0.9 | 1 | -0.19 | | | | | | | | {399,499,Infinity} | 0.5 | +(1 row) + +-- warn: range bounds histogram on scalar +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', -0.29::real, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text + ); +WARNING: attribute "id" is not a range type +DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. +WARNING: invalid input syntax for type integer: "[-1,1)" + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.1 | 2 | -0.29 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | +(1 row) + +-- ok: range_bounds_histogram +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.2::real, + 'avg_width', 3::integer, + 'n_distinct', -0.39::real, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text + ); + pg_restore_attribute_stats +---------------------------- + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+-------------------------------------- + stats_import | test | arange | f | 0.2 | 3 | -0.39 | | | | | | | | {399,499,Infinity} | 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"} +(1 row) + +-- warn: too many stat kinds +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_vals', '{"[2,3)","[1,3)","[3,9)"}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[], + 'histogram_bounds', '{"[1,2)","[2,3)","[3,4)","[4,)"}'::text, + 'correlation', 1.1::real, + 'most_common_elems', '{3,1}'::text, + 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[], + 'range_empty_frac', -0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text); +ERROR: maximum number of statistics slots exceeded: 6 +-- -- Test the ability to exactly copy data from one table to an identical table, -- correctly reconstructing the stakind order as well as the staopN and -- stacollN values. Because oids are not stable across databases, we can only @@ -787,6 +1522,217 @@ WHERE s.starelid = 'stats_import.is_odd'::regclass; ---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- (0 rows) +-- +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 1 | 4 | 0 +(1 row) + +-- +-- Clear clone stats to try again with pg_restore_attribute_stats +-- +SELECT + pg_catalog.pg_clear_attribute_stats( + ('stats_import.' || s.tablename)::regclass, + s.attname, + s.inherited) +FROM pg_catalog.pg_stats AS s +WHERE s.schemaname = 'stats_import' +AND s.tablename IN ('test_clone', 'is_odd_clone') +ORDER BY s.tablename, s.attname, s.inherited; + pg_clear_attribute_stats +-------------------------- + t + t + t + t + t + t +(6 rows) + +SELECT +SELECT COUNT(*) +FROM pg_catalog.pg_stats AS s +WHERE s.schemaname = 'stats_import' +AND s.tablename IN ('test_clone', 'is_odd_clone'); +ERROR: syntax error at or near "SELECT" +LINE 2: SELECT COUNT(*) + ^ +-- +-- Copy stats from test to test_clone, and is_odd to is_odd_clone +-- +SELECT s.schemaname, s.tablename, s.attname, s.inherited, r.* +FROM pg_catalog.pg_stats AS s +CROSS JOIN LATERAL + pg_catalog.pg_restore_attribute_stats( + 'relation', ('stats_import.' || s.tablename || '_clone')::regclass, + 'attname', s.attname, + 'inherited', s.inherited, + 'version', 150000, + 'null_frac', s.null_frac, + 'avg_width', s.avg_width, + 'n_distinct', s.n_distinct, + 'most_common_vals', s.most_common_vals::text, + 'most_common_freqs', s.most_common_freqs, + 'histogram_bounds', s.histogram_bounds::text, + 'correlation', s.correlation, + 'most_common_elems', s.most_common_elems::text, + 'most_common_elem_freqs', s.most_common_elem_freqs, + 'elem_count_histogram', s.elem_count_histogram, + 'range_bounds_histogram', s.range_bounds_histogram::text, + 'range_empty_frac', s.range_empty_frac, + 'range_length_histogram', s.range_length_histogram::text) AS r +WHERE s.schemaname = 'stats_import' +AND s.tablename IN ('test', 'is_odd') +ORDER BY s.tablename, s.attname, s.inherited; + schemaname | tablename | attname | inherited | r +--------------+-----------+---------+-----------+--- + stats_import | is_odd | expr | f | t + stats_import | test | arange | f | t + stats_import | test | comp | f | t + stats_import | test | id | f | t + stats_import | test | name | f | t + stats_import | test | tags | f | t +(6 rows) + +SELECT c.relname, COUNT(*) AS num_stats +FROM pg_class AS c +JOIN pg_statistic s ON s.starelid = c.oid +WHERE c.relnamespace = 'stats_import'::regnamespace +AND c.relname IN ('test', 'test_clone', 'is_odd', 'is_odd_clone') +GROUP BY c.relname +ORDER BY c.relname; + relname | num_stats +--------------+----------- + is_odd | 1 + is_odd_clone | 1 + test | 5 + test_clone | 5 +(4 rows) + +-- check test minus test_clone +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test_clone'::regclass; + attname | 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 | direction +---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- +(0 rows) + +-- check test_clone minus test +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test_clone'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test'::regclass; + attname | 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 | direction +---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- +(0 rows) + +-- check is_odd minus is_odd_clone +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd_clone'::regclass; + attname | 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 | direction +---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- +(0 rows) + +-- check is_odd_clone minus is_odd +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd_clone'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd'::regclass; + attname | 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 | direction +---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- +(0 rows) + DROP SCHEMA stats_import CASCADE; NOTICE: drop cascades to 5 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 5651f133c0..cbc363e9f4 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -205,7 +205,7 @@ SELECT pg_catalog.pg_set_attribute_stats( most_common_freqs => '{0.2,0.1}'::real[] ); --- warning: mcv cast failure +-- warn: mcv cast failure SELECT pg_catalog.pg_set_attribute_stats( relation => 'stats_import.test'::regclass, attname => 'id'::name, @@ -470,6 +470,529 @@ SELECT pg_catalog.pg_set_attribute_stats( range_length_histogram => '{399,499,Infinity}'::text, range_bounds_histogram => '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text ); + +-- +-- Clear attribute stats to try again with restore functions +-- (relation stats were already cleared). +-- +SELECT + pg_catalog.pg_clear_attribute_stats( + 'stats_import.test'::regclass, + s.attname, + s.inherited) +FROM pg_catalog.pg_stats AS s +WHERE s.schemaname = 'stats_import' +AND s.tablename = 'test' +ORDER BY s.attname, s.inherited; + +-- reject: object doesn't exist +SELECT * +FROM pg_restore_relation_stats( + 'relation', '0'::oid::regclass, + 'version', 150000::integer, + 'relpages', '17'::integer, + 'reltuples', 400::real, + 'relallvisible', 4::integer); + +-- ok: set all stats +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass, + 'version', 150000::integer, + 'relpages', '17'::integer, + 'reltuples', 400::real, + 'relallvisible', 4::integer); + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +-- ok: just relpages +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass, + 'version', 150000::integer, + 'relpages', '16'::integer); + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +-- ok: just reltuples +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass, + 'version', 150000::integer, + 'reltuples', '500'::real); + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +-- ok: just relallvisible +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass, + 'version', 150000::integer, + 'relallvisible', 5::integer); + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +-- warn: bad relpages type +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass, + 'version', 150000::integer, + 'relpages', 'nope'::text, + 'reltuples', 400.0::real, + 'relallvisible', 4::integer); + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +-- error: object does not exist +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', '0'::oid::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- error: relation null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', NULL::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- error: attname null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', NULL::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- error: inherited null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', NULL::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- ok: no stakinds +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.4::real, + 'avg_width', 5::integer, + 'n_distinct', 0.6::real); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: null_frac null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', NULL::real, + 'avg_width', 3::integer, + 'n_distinct', 0.4::real); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: avg_width null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.2::real, + 'avg_width', NULL::integer, + 'n_distinct', 0.5::real); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: n_distinct null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.3::real, + 'avg_width', 4::integer, + 'n_distinct', NULL::real); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: mcv / mcf null mismatch part 1 +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.6::real, + 'avg_width', 7::integer, + 'n_distinct', -0.7::real, + 'most_common_freqs', '{0.1,0.2,0.3}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: mcv / mcf null mismatch part 2 +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.7::real, + 'avg_width', 8::integer, + 'n_distinct', -0.8::real, + 'most_common_vals', '{1,2,3}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: mcv / mcf type mismatch +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.8::real, + 'avg_width', 9::integer, + 'n_distinct', -0.9::real, + 'most_common_vals', '{2,1,3}'::text, + 'most_common_freqs', '{0.2,0.1}'::double precision[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: mcv cast failure +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.9::real, + 'avg_width', 10::integer, + 'n_distinct', -0.4::real, + 'most_common_vals', '{2,four,3}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- ok: mcv+mcf +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 1::integer, + 'n_distinct', -0.1::real, + 'most_common_vals', '{2,1,3}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: NULL in histogram array +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.2::real, + 'avg_width', 2::integer, + 'n_distinct', -0.2::real, + 'histogram_bounds', '{1,NULL,3,4}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- ok: histogram_bounds +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.3::real, + 'avg_width', 3::integer, + 'n_distinct', -0.3::real, + 'histogram_bounds', '{1,2,3,4}'::text ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: elem_count_histogram null element +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'tags'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.4::real, + 'avg_width', 5::integer, + 'n_distinct', -0.4::real, + 'elem_count_histogram', '{1,1,NULL,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + +-- ok: elem_count_histogram +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'tags'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 6::integer, + 'n_distinct', -0.55::real, + 'elem_count_histogram', '{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + +-- range stats on a scalar type +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.6::real, + 'avg_width', 7::integer, + 'n_distinct', -0.15::real, + 'range_empty_frac', 0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: range_empty_frac range_length_hist null mismatch +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.7::real, + 'avg_width', 8::integer, + 'n_distinct', -0.25::real, + 'range_length_histogram', '{399,499,Infinity}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + +-- warn: range_empty_frac range_length_hist null mismatch part 2 +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.8::real, + 'avg_width', 9::integer, + 'n_distinct', -0.35::real, + 'range_empty_frac', 0.5::real + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + +-- ok: range_empty_frac + range_length_hist +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.9::real, + 'avg_width', 1::integer, + 'n_distinct', -0.19::real, + 'range_empty_frac', 0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + +-- warn: range bounds histogram on scalar +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', -0.29::real, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- ok: range_bounds_histogram +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.2::real, + 'avg_width', 3::integer, + 'n_distinct', -0.39::real, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + +-- warn: too many stat kinds +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_vals', '{"[2,3)","[1,3)","[3,9)"}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[], + 'histogram_bounds', '{"[1,2)","[2,3)","[3,4)","[4,)"}'::text, + 'correlation', 1.1::real, + 'most_common_elems', '{3,1}'::text, + 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[], + 'range_empty_frac', -0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text); + -- -- Test the ability to exactly copy data from one table to an identical table, -- correctly reconstructing the stakind order as well as the staopN and @@ -488,7 +1011,6 @@ UNION ALL SELECT 4, 'four', NULL, int4range(0,100), NULL; CREATE INDEX is_odd ON stats_import.test(((comp).a % 2 = 1)); - -- Generate statistics on table with data ANALYZE stats_import.test; @@ -639,4 +1161,172 @@ FROM pg_statistic s JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum WHERE s.starelid = 'stats_import.is_odd'::regclass; +-- +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +-- +-- Clear clone stats to try again with pg_restore_attribute_stats +-- +SELECT + pg_catalog.pg_clear_attribute_stats( + ('stats_import.' || s.tablename)::regclass, + s.attname, + s.inherited) +FROM pg_catalog.pg_stats AS s +WHERE s.schemaname = 'stats_import' +AND s.tablename IN ('test_clone', 'is_odd_clone') +ORDER BY s.tablename, s.attname, s.inherited; +SELECT + +SELECT COUNT(*) +FROM pg_catalog.pg_stats AS s +WHERE s.schemaname = 'stats_import' +AND s.tablename IN ('test_clone', 'is_odd_clone'); + +-- +-- Copy stats from test to test_clone, and is_odd to is_odd_clone +-- +SELECT s.schemaname, s.tablename, s.attname, s.inherited, r.* +FROM pg_catalog.pg_stats AS s +CROSS JOIN LATERAL + pg_catalog.pg_restore_attribute_stats( + 'relation', ('stats_import.' || s.tablename || '_clone')::regclass, + 'attname', s.attname, + 'inherited', s.inherited, + 'version', 150000, + 'null_frac', s.null_frac, + 'avg_width', s.avg_width, + 'n_distinct', s.n_distinct, + 'most_common_vals', s.most_common_vals::text, + 'most_common_freqs', s.most_common_freqs, + 'histogram_bounds', s.histogram_bounds::text, + 'correlation', s.correlation, + 'most_common_elems', s.most_common_elems::text, + 'most_common_elem_freqs', s.most_common_elem_freqs, + 'elem_count_histogram', s.elem_count_histogram, + 'range_bounds_histogram', s.range_bounds_histogram::text, + 'range_empty_frac', s.range_empty_frac, + 'range_length_histogram', s.range_length_histogram::text) AS r +WHERE s.schemaname = 'stats_import' +AND s.tablename IN ('test', 'is_odd') +ORDER BY s.tablename, s.attname, s.inherited; + +SELECT c.relname, COUNT(*) AS num_stats +FROM pg_class AS c +JOIN pg_statistic s ON s.starelid = c.oid +WHERE c.relnamespace = 'stats_import'::regnamespace +AND c.relname IN ('test', 'test_clone', 'is_odd', 'is_odd_clone') +GROUP BY c.relname +ORDER BY c.relname; + +-- check test minus test_clone +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test_clone'::regclass; + +-- check test_clone minus test +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test_clone'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test'::regclass; + +-- check is_odd minus is_odd_clone +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd_clone'::regclass; + +-- check is_odd_clone minus is_odd +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd_clone'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd'::regclass; + DROP SCHEMA stats_import CASCADE; diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 672d478a90..c7b791e7ac 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -30324,6 +30324,163 @@ DETAIL: Make sure pg_wal_replay_wait() isn't called within a transaction with a + + + + pg_restore_relation_stats + + pg_restore_relation_stats ( + VARIADIC kwargs "any" ) + boolean + + + Similar to pg_set_relation_stats(), but intended + for bulk restore of relation statistics. The tracked statistics may + change from version to version, so the primary purpose of this + function is to maintain a consistent function signature to avoid + errors when restoring statistics from previous versions. + + + Arguments are passed as pairs of argname + and argvalue, where + argname corresponds to a named argument in + pg_set_relation_stats() and + argvalue is of the corresponding type. + + + Additionally, this function supports argument name + version of type integer, which + specifies the version from which the statistics originated, improving + intepretation of older statistics. + + + For example, to set the relpages and + reltuples of the table + mytable: + + SELECT pg_restore_relation_stats( + 'relation', 'mytable'::regclass, + 'relpages', 173::integer, + 'reltuples', 10000::float4); + + + + Minor errors are reported as a WARNING and + ignored, and remaining statistics specified will still be restored. + + + + + + + + + pg_set_attribute_stats + + pg_set_attribute_stats ( + relation regclass, + attname name, + inherited boolean + , null_frac real + , avg_width integer + , n_distinct real + , most_common_vals text, most_common_freqs real[] + , histogram_bounds text + , correlation real + , most_common_elems text, most_common_elem_freqs real[] + , elem_count_histogram real[] + , range_length_histogram text + , range_empty_frac real + , range_bounds_histogram text ) + booolean + + + Creates or updates attribute-level statistics for the given relation + and attribute name to the specified values. The parameters correspond + to to attributes of the same name found in the pg_stats + view. Returns true if a change was made; + false otherwise. + + + All parameters default to NULL, which leave the + corresponding statistic unchanged. + + + Ordinarily, these statistics are collected automatically or updated + as a part of or , so it's not necessary to call this + function. However, it may be useful when testing the effects of + statistics on the planner to understand or anticipate plan changes. + + + The caller must have the MAINTAIN privilege on + the table or be the owner of the database. + + + + Changes made by this function are likely to be overwritten by autovacuum (or manual + VACUUM or ANALYZE) and should + be considered temporary. + + + The signature of this function may change in new major releases if + there are changes to the statistics being tracked. + + + + + + + + + pg_restore_attribute_stats + + pg_restore_attribute_stats ( + VARIADIC kwargs "any" ) + boolean + + + Similar to pg_set_attribute_stats(), but + intended for bulk restore of attribute statistics. The tracked + statistics may change from version to version, so the primary purpose + of this function is to maintain a consistent function signature to + avoid errors when restoring statistics from previous versions. + + + Arguments are passed as pairs of argname + and argvalue, where + argname corresponds to a named argument in + pg_set_attribute_stats() and + argvalue is of the corresponding type. + + + Additionally, this function supports argument name + version of type integer, which + specifies the version from which the statistics originated, improving + intepretation of older statistics. + + + For example, to set the avg_width and + null_frac for the attribute + col1 of the table + mytable: + + SELECT pg_restore_attribute_stats( + 'relation', 'mytable'::regclass, + 'attname', 'col1'::name, + 'inherited', false, + 'avg_width', 125::integer, + 'null_frac', 0.5::real); + + + + Minor errors are reported as a WARNING and + ignored, and remaining statistics specified will still be restored. + + + -- 2.46.0