> > > > Having some discussion around that would be useful. Is it better to > have a situation where there are stats for some columns but no stats for > other columns? There would be a good chance that this would lead to a > set of queries that were properly planned out and a set which end up > with unexpected and likely poor query plans due to lack of stats. > Arguably that's better overall, but either way an ANALYZE needs to be > done to address the lack of stats for those columns and then that > ANALYZE is going to blow away whatever stats got loaded previously > anyway and all we did with a partial stats load was maybe have a subset > of queries have better plans in the interim, after having expended the > cost to try and individually load the stats and dealing with the case of > some of them succeeding and some failing. >
It is my (incomplete and entirely second-hand) understanding is that pg_upgrade doesn't STOP autovacuum, but sets a delay to a very long value and then resets it on completion, presumably because analyzing a table before its data is loaded and indexes are created would just be a waste of time. > > Overall, I'd suggest we wait to see what Corey comes up with in terms of > doing the stats load for all attributes in a single function call, > perhaps using the VALUES construct as you suggested up-thread, and then > we can contemplate if that's clean enough to work or if it's so grotty > that the better plan would be to do per-attribute function calls. If it > ends up being the latter, then we can revisit this discussion and try to > answer some of the questions raised above. > In the patch below, I ended up doing per-attribute function calls, mostly because it allowed me to avoid creating a custom data type for the portable version of pg_statistic. This comes at the cost of a very high number of parameters, but that's the breaks. I am a bit concerned about the number of locks on pg_statistic and the relation itself, doing CatalogOpenIndexes/CatalogCloseIndexes once per attribute rather than once per relation. But I also see that this will mostly get used at a time when no other traffic is on the machine, and whatever it costs, it's still faster than the smallest table sample (insert joke about "don't have to be faster than the bear" here). This raises questions about whether a failure in one attribute update statement should cause the others in that relation to roll back or not, and I can see situations where both would be desirable. I'm putting this out there ahead of the pg_dump / fe_utils work, mostly because what I do there heavily depends on how this is received. Also, I'm still seeking confirmation that I can create a pg_dump TOC entry with a chain of commands (e.g. BEGIN; ... COMMIT; ) or if I have to fan them out into multiple entries. Anyway, here's v7. Eagerly awaiting feedback.
From 9bc7200b8a67efefe20453e0c48aed8a0a5f62f8 Mon Sep 17 00:00:00 2001 From: Corey Huinker <corey.huin...@gmail.com> Date: Thu, 7 Mar 2024 22:18:48 -0500 Subject: [PATCH v7] Create pg_set_relation_stats, pg_set_attribute_stats. These functions will be used by pg_dump/restore and pg_upgrade to convey relation and attribute statistics from the source database to the target. This would be done instead of vacuumdb --analyze-in-stages. Both functions take an oid to identify the target relation that will receive the statistics. There is nothing requiring that relation to be the same one as the one exported, though the statistics would have to make sense in the context of the new relation. Typecasts for stavaluesN parameters may fail if the destination column is not of the same type as the source column. The parameters of pg_set_attribute_stats identify the attribute by name rather than by attnum. This is intentional because the column order may be different in situations other than binary upgrades. For example, dropped columns do not carry over in a restore. The statistics imported by pg_set_attribute_stats are imported transactionally like any other operation. However, pg_set_relation_stats does it's update in-place, which is to say non-transactionally. This is in line with what ANALYZE does to avoid table bloat in pg_class. These functions 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 | 19 +- src/include/statistics/statistics.h | 3 + src/backend/statistics/Makefile | 3 +- src/backend/statistics/meson.build | 1 + src/backend/statistics/statistics.c | 360 ++++++++++++++++++ .../regress/expected/stats_export_import.out | 211 ++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/stats_export_import.sql | 198 ++++++++++ doc/src/sgml/func.sgml | 89 +++++ 9 files changed, 882 insertions(+), 4 deletions(-) create mode 100644 src/backend/statistics/statistics.c create mode 100644 src/test/regress/expected/stats_export_import.out create mode 100644 src/test/regress/sql/stats_export_import.sql diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 291ed876fc..d12b6e3ca3 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -8818,7 +8818,6 @@ { oid => '3813', descr => 'generate XML text node', proname => 'xmltext', proisstrict => 't', prorettype => 'xml', proargtypes => 'text', prosrc => 'xmltext' }, - { oid => '2923', descr => 'map table contents to XML', proname => 'table_to_xml', procost => '100', provolatile => 's', proparallel => 'r', prorettype => 'xml', @@ -12163,8 +12162,24 @@ # GiST stratnum implementations { oid => '8047', descr => 'GiST support', - proname => 'gist_stratnum_identity', prorettype => 'int2', + proname => 'gist_stratnum_identity',prorettype => 'int2', proargtypes => 'int2', prosrc => 'gist_stratnum_identity' }, +# Statistics Import +{ oid => '8048', + descr => 'set statistics on relation', + proname => 'pg_set_relation_stats', provolatile => 'v', proisstrict => 'f', + proparallel => 'u', prorettype => 'bool', + proargtypes => 'oid float4 int4', + proargnames => '{relation,reltuples,relpages}', + prosrc => 'pg_set_relation_stats' }, + +{ oid => '8049', + descr => 'set statistics on attribute', + proname => 'pg_set_attribute_stats', provolatile => 'v', proisstrict => 'f', + proparallel => 'u', prorettype => 'bool', + proargtypes => 'oid name bool float4 int4 float4 int2 int2 int2 int2 int2 _float4 _float4 _float4 _float4 _float4 text text text text text', + proargnames => '{relation,attname,stainherit,stanullfrac,stawidth,stadistinct,stakind1,stakind2,stakind3,stakind4,stakind5,stanumbers1,stanumbers2,stanumbers3,stanumbers4,stanumbers5,stavalues1,stavalues2,stavalues3,stavalues4,stavalues5}', + prosrc => 'pg_set_attribute_stats' }, ] diff --git a/src/include/statistics/statistics.h b/src/include/statistics/statistics.h index 7f2bf18716..73d3b541dd 100644 --- a/src/include/statistics/statistics.h +++ b/src/include/statistics/statistics.h @@ -127,4 +127,7 @@ extern StatisticExtInfo *choose_best_statistics(List *stats, char requiredkind, int nclauses); extern HeapTuple statext_expressions_load(Oid stxoid, bool inh, int idx); +extern Datum pg_set_relation_stats(PG_FUNCTION_ARGS); +extern Datum pg_set_attribute_stats(PG_FUNCTION_ARGS); + #endif /* STATISTICS_H */ diff --git a/src/backend/statistics/Makefile b/src/backend/statistics/Makefile index 89cf8c2797..e4f8ab7c4f 100644 --- a/src/backend/statistics/Makefile +++ b/src/backend/statistics/Makefile @@ -16,6 +16,7 @@ OBJS = \ dependencies.o \ extended_stats.o \ mcv.o \ - mvdistinct.o + mvdistinct.o \ + statistics.o include $(top_srcdir)/src/backend/common.mk diff --git a/src/backend/statistics/meson.build b/src/backend/statistics/meson.build index 73b29a3d50..331e82c776 100644 --- a/src/backend/statistics/meson.build +++ b/src/backend/statistics/meson.build @@ -5,4 +5,5 @@ backend_sources += files( 'extended_stats.c', 'mcv.c', 'mvdistinct.c', + 'statistics.c', ) diff --git a/src/backend/statistics/statistics.c b/src/backend/statistics/statistics.c new file mode 100644 index 0000000000..999aebdfa9 --- /dev/null +++ b/src/backend/statistics/statistics.c @@ -0,0 +1,360 @@ +/*------------------------------------------------------------------------- * * statistics.c * + * IDENTIFICATION + * src/backend/statistics/statistics.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "access/heapam.h" +#include "catalog/indexing.h" +#include "catalog/pg_type.h" +#include "fmgr.h" +#include "nodes/nodeFuncs.h" +#include "parser/parse_oper.h" +#include "statistics/statistics.h" +#include "utils/builtins.h" +#include "utils/fmgroids.h" +#include "utils/lsyscache.h" +#include "utils/syscache.h" + +/* + * Set statistics for a given pg_class entry. + * + * pg_set_relation_stats(relation Oid, reltuples double, relpages int) + * + * This does an in-place (i.e. non-transactional) update of pg_class, just as + * is done in ANALYZE. + * + */ +Datum +pg_set_relation_stats(PG_FUNCTION_ARGS) +{ + const char *param_names[] = { + "relation", + "reltuples", + "relpages", + }; + + Oid relid; + Relation rel; + HeapTuple ctup; + Form_pg_class pgcform; + + for (int i = 0; i <= 2; i++) + if (PG_ARGISNULL(i)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot be NULL", param_names[i]))); + + relid = PG_GETARG_OID(0); + + /* + * Open the relation, getting ShareUpdateExclusiveLock to ensure that no + * other stat-setting operation can run on it concurrently. + */ + rel = table_open(RelationRelationId, ShareUpdateExclusiveLock); + + 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); + pgcform->reltuples = PG_GETARG_FLOAT4(1); + pgcform->relpages = PG_GETARG_INT32(2); + + heap_inplace_update(rel, ctup); + + table_close(rel, ShareUpdateExclusiveLock); + + PG_RETURN_BOOL(true); +} + +/* + * Import statistics for a given relation attribute + * + * pg_set_attribute_stats(relation Oid, attname name, stainherit bool, + * stanullfrac float4, stawidth int, stadistinct float4, + * stakind1 int2, stakind2 int2, stakind3 int3, + * stakind4 int2, stakind5 int2, stanumbers1 float4[], + * stanumbers2 float4[], stanumbers3 float4[], + * stanumbers4 float4[], stanumbers5 float4[], + * stanumbers1 float4[], stanumbers2 float4[], + * stanumbers3 float4[], stanumbers4 float4[], + * stanumbers5 float4[], stavalues1 text, + * stavalues2 text, stavalues3 text, + * stavalues4 text, stavalues5 text); + * + * + */ +Datum +pg_set_attribute_stats(PG_FUNCTION_ARGS) +{ + enum { + P_RELATION = 0, + P_ATTNAME, + P_STAINHERIT, + P_STANULLFRAC, + P_STAWIDTH, + P_STADISTINCT, + P_STAKIND1, + P_STAKIND2, + P_STAKIND3, + P_STAKIND4, + P_STAKIND5, + P_STANUMBERS1, + P_STANUMBERS2, + P_STANUMBERS3, + P_STANUMBERS4, + P_STANUMBERS5, + P_STAVALUES1, + P_STAVALUES2, + P_STAVALUES3, + P_STAVALUES4, + P_STAVALUES5, + P_NUM_PARAMS + }; + + /* names of columns that cannot be null */ + const char *required_param_names[] = { + "relation", + "attname", + "stainherit", + "stanullfrac", + "stawidth", + "stadistinct", + "stakind1", + "stakind2", + "stakind3", + "stakind4", + "stakind5", + }; + + Oid relid; + Name attname; + Relation rel; + HeapTuple tuple; + + Oid typid; + int32 typmod; + Oid typcoll; + Oid eqopr; + Oid ltopr; + Oid basetypid; + Oid baseeqopr; + Oid baseltopr; + + float4 stanullfrac; + int stawidth; + + Datum values[Natts_pg_statistic] = { 0 }; + bool nulls[Natts_pg_statistic] = { false }; + + Relation sd; + HeapTuple oldtup; + CatalogIndexState indstate; + HeapTuple stup; + Form_pg_attribute attr; + + for (int i = P_RELATION; i <= P_STAKIND5; i++) + if (PG_ARGISNULL(i)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot be NULL", required_param_names[i]))); + + relid = PG_GETARG_OID(P_RELATION); + attname = PG_GETARG_NAME(P_ATTNAME); + rel = relation_open(relid, ShareUpdateExclusiveLock); + tuple = SearchSysCache2(ATTNAME, + ObjectIdGetDatum(relid), + NameGetDatum(attname)); + + if (!HeapTupleIsValid(tuple)) + { + relation_close(rel, NoLock); + PG_RETURN_BOOL(false); + } + + attr = (Form_pg_attribute) GETSTRUCT(tuple); + if (attr->attisdropped) + { + ReleaseSysCache(tuple); + relation_close(rel, NoLock); + PG_RETURN_BOOL(false); + } + + /* + * If this relation is an index and that index has expressions in + * it, and the attnum specified is known to be an expression, then + * we must walk the list attributes up to the specified attnum + * to get the right expression. + */ + if ((rel->rd_rel->relkind == RELKIND_INDEX + || (rel->rd_rel->relkind == RELKIND_PARTITIONED_INDEX)) + && (rel->rd_indexprs != NIL) + && (rel->rd_index->indkey.values[attr->attnum-1] == 0)) + { + ListCell *indexpr_item = list_head(rel->rd_indexprs); + Node *expr; + + for (int i = 0; i < attr->attnum - 1; i++) + if (rel->rd_index->indkey.values[i] == 0) + indexpr_item = lnext(rel->rd_indexprs, indexpr_item); + + if (indexpr_item == NULL) /* shouldn't happen */ + elog(ERROR, "too few entries in indexprs list"); + + expr = (Node *) lfirst(indexpr_item); + + typid = exprType(expr); + typmod = exprTypmod(expr); + + /* + * If a collation has been specified for the index column, use that in + * preference to anything else; but if not, fall back to whatever we + * can get from the expression. + */ + if (OidIsValid(attr->attcollation)) + typcoll = attr->attcollation; + else + typcoll = exprCollation(expr); + } + else + { + typid = attr->atttypid; + typmod = attr->atttypmod; + typcoll = attr->attcollation; + } + + get_sort_group_operators(typid, false, false, false, + <opr, &eqopr, NULL, NULL); + + basetypid = get_base_element_type(typid); + + if (basetypid == InvalidOid) + { + /* type is its own base type */ + basetypid = typid; + baseltopr = ltopr; + baseeqopr = eqopr; + } + else + get_sort_group_operators(basetypid, false, false, false, + &baseltopr, &baseeqopr, NULL, NULL); + + stanullfrac = PG_GETARG_FLOAT4(P_STANULLFRAC); + if ((stanullfrac < 0.0) || (stanullfrac > 1.0)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("stanullfrac %f is out of range 0.0 to 1.0", stanullfrac))); + + stawidth = PG_GETARG_INT32(P_STAWIDTH); + if (stawidth < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("stawidth %d must be >= 0", stawidth))); + + values[Anum_pg_statistic_starelid - 1] = ObjectIdGetDatum(relid); + values[Anum_pg_statistic_staattnum - 1] = Int16GetDatum(attr->attnum); + values[Anum_pg_statistic_stainherit - 1] = PG_GETARG_DATUM(P_STAINHERIT); + values[Anum_pg_statistic_stanullfrac - 1] = PG_GETARG_DATUM(P_STANULLFRAC); + values[Anum_pg_statistic_stawidth - 1] = PG_GETARG_DATUM(P_STAWIDTH); + values[Anum_pg_statistic_stadistinct - 1] = PG_GETARG_DATUM(P_STADISTINCT); + + /* The remaining fields are all parallel arrays, so we iterate over them */ + for (int k = 0; k < 5; k++) + { + int16 kind = PG_GETARG_INT16(P_STAKIND1 + k); + Oid opoid; + Oid colloid; + + switch(kind) { + case 0: + opoid = InvalidOid; + colloid = InvalidOid; + break; + case STATISTIC_KIND_MCV: + opoid = eqopr; + colloid = typcoll; + break; + case STATISTIC_KIND_HISTOGRAM: + case STATISTIC_KIND_CORRELATION: + case STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM: + case STATISTIC_KIND_BOUNDS_HISTOGRAM: + opoid = ltopr; + colloid = typcoll; + break; + case STATISTIC_KIND_MCELEM: + case STATISTIC_KIND_DECHIST: + opoid = baseeqopr; + colloid = typcoll; + break; + default: + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("stakind%d = %d is out of the range 0 to %d", k + 1, + kind, STATISTIC_KIND_BOUNDS_HISTOGRAM))); + } + + values[Anum_pg_statistic_stakind1 - 1 + k] = PG_GETARG_DATUM(P_STAKIND1 + k); + values[Anum_pg_statistic_staop1 - 1 + k] = ObjectIdGetDatum(opoid); + values[Anum_pg_statistic_stacoll1 - 1 + k] = ObjectIdGetDatum(colloid); + + if (PG_ARGISNULL(P_STANUMBERS1 + k)) + nulls[Anum_pg_statistic_stanumbers1 - 1 + k] = true; + else + values[Anum_pg_statistic_stanumbers1 - 1 + k] = PG_GETARG_DATUM(P_STANUMBERS1 + k); + + if (PG_ARGISNULL(P_STAVALUES1 + k)) + nulls[Anum_pg_statistic_stavalues1 - 1 + k] = true; + else + { + char *s = TextDatumGetCString(PG_GETARG_DATUM(P_STAVALUES1 + k)); + FmgrInfo finfo; + + fmgr_info(F_ARRAY_IN, &finfo); + + values[Anum_pg_statistic_stavalues1 - 1 + k] = + FunctionCall3(&finfo, CStringGetDatum(s), ObjectIdGetDatum(basetypid), + Int32GetDatum(typmod)); + + pfree(s); + } + } + + sd = table_open(StatisticRelationId, RowExclusiveLock); + + /* Is there already a pg_statistic tuple for this attribute? */ + oldtup = SearchSysCache3(STATRELATTINH, + ObjectIdGetDatum(relid), + Int16GetDatum(attr->attnum), + PG_GETARG_DATUM(P_STAINHERIT)); + + indstate = CatalogOpenIndexes(sd); + + if (HeapTupleIsValid(oldtup)) + { + /* Yes, replace it */ + bool replaces[Natts_pg_statistic] = { true }; + + stup = heap_modify_tuple(oldtup, RelationGetDescr(sd), + values, nulls, replaces); + ReleaseSysCache(oldtup); + CatalogTupleUpdateWithInfo(sd, &stup->t_self, stup, indstate); + } + else + { + /* No, insert new tuple */ + stup = heap_form_tuple(RelationGetDescr(sd), values, nulls); + CatalogTupleInsertWithInfo(sd, stup, indstate); + } + + heap_freetuple(stup); + CatalogCloseIndexes(indstate); + table_close(sd, RowExclusiveLock); + relation_close(rel, NoLock); + ReleaseSysCache(tuple); + PG_RETURN_BOOL(true); +} diff --git a/src/test/regress/expected/stats_export_import.out b/src/test/regress/expected/stats_export_import.out new file mode 100644 index 0000000000..50ce86693a --- /dev/null +++ b/src/test/regress/expected/stats_export_import.out @@ -0,0 +1,211 @@ +CREATE SCHEMA stats_export_import; +CREATE TYPE stats_export_import.complex_type AS ( + a integer, + b float, + c text, + d date, + e jsonb); +CREATE TABLE stats_export_import.test( + id INTEGER PRIMARY KEY, + name text, + comp stats_export_import.complex_type, + tags text[] +); +SELECT reltuples, relpages FROM pg_class WHERE oid = 'stats_export_import.test'::regclass; + reltuples | relpages +-----------+---------- + -1 | 0 +(1 row) + +SELECT pg_set_relation_stats('stats_export_import.test'::regclass, 3.6::float4, 15000); + pg_set_relation_stats +----------------------- + t +(1 row) + +SELECT reltuples, relpages FROM pg_class WHERE oid = 'stats_export_import.test'::regclass; + reltuples | relpages +-----------+---------- + 3.6 | 15000 +(1 row) + +INSERT INTO stats_export_import.test +SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_export_import.complex_type, array['red','green'] +UNION ALL +SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_export_import.complex_type, array['blue','yellow'] +UNION ALL +SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_export_import.complex_type, array['"orange"', 'purple', 'cyan'] +UNION ALL +SELECT 4, 'four', NULL, NULL; +CREATE INDEX is_odd ON stats_export_import.test(((comp).a % 2 = 1)); +-- Generate statistics on table with data +ANALYZE stats_export_import.test; +CREATE TABLE stats_export_import.test_clone ( LIKE stats_export_import.test ); +CREATE INDEX is_odd_clone ON stats_export_import.test_clone(((comp).a % 2 = 1)); +-- +-- Turn off ECHO for the transfer, because the actual stats generated by +-- ANALYZE could change, and we don't care about the actual stats, we care +-- about the ability to transfer them to another relation. +-- +\set orig_ECHO :ECHO +\set ECHO none + pg_set_attribute_stats +------------------------ + t +(1 row) + + pg_set_attribute_stats +------------------------ + t +(1 row) + + pg_set_attribute_stats +------------------------ + t +(1 row) + + pg_set_attribute_stats +------------------------ + t +(1 row) + + pg_set_attribute_stats +------------------------ + t +(1 row) + +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_export_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 | 4 + test_clone | 4 +(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_export_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_export_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_export_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_export_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_export_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_export_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_export_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_export_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) + diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 1d8a414eea..0c89ffc02d 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -103,7 +103,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo # ---------- # Another group of parallel tests (JSON related) # ---------- -test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson +test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson stats_export_import # ---------- # Another group of parallel tests diff --git a/src/test/regress/sql/stats_export_import.sql b/src/test/regress/sql/stats_export_import.sql new file mode 100644 index 0000000000..75c9d130b1 --- /dev/null +++ b/src/test/regress/sql/stats_export_import.sql @@ -0,0 +1,198 @@ +CREATE SCHEMA stats_export_import; + +CREATE TYPE stats_export_import.complex_type AS ( + a integer, + b float, + c text, + d date, + e jsonb); + +CREATE TABLE stats_export_import.test( + id INTEGER PRIMARY KEY, + name text, + comp stats_export_import.complex_type, + tags text[] +); + +SELECT reltuples, relpages FROM pg_class WHERE oid = 'stats_export_import.test'::regclass; + +SELECT pg_set_relation_stats('stats_export_import.test'::regclass, 3.6::float4, 15000); + +SELECT reltuples, relpages FROM pg_class WHERE oid = 'stats_export_import.test'::regclass; + +INSERT INTO stats_export_import.test +SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_export_import.complex_type, array['red','green'] +UNION ALL +SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_export_import.complex_type, array['blue','yellow'] +UNION ALL +SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_export_import.complex_type, array['"orange"', 'purple', 'cyan'] +UNION ALL +SELECT 4, 'four', NULL, NULL; + +CREATE INDEX is_odd ON stats_export_import.test(((comp).a % 2 = 1)); + +-- Generate statistics on table with data +ANALYZE stats_export_import.test; + +CREATE TABLE stats_export_import.test_clone ( LIKE stats_export_import.test ); + +CREATE INDEX is_odd_clone ON stats_export_import.test_clone(((comp).a % 2 = 1)); + +-- +-- Turn off ECHO for the transfer, because the actual stats generated by +-- ANALYZE could change, and we don't care about the actual stats, we care +-- about the ability to transfer them to another relation. +-- +\set orig_ECHO :ECHO +\set ECHO none + +-- copy stats from test to test_clone and is_odd to is_odd_clone +SELECT + format('SELECT pg_catalog.pg_set_attribute_stats( ' + || 'relation => %L::regclass, attname => %L::name, ' + || 'stainherit => %L::boolean, stanullfrac => %s::real, ' + || 'stawidth => %s::integer, stadistinct => %s::real, ' + || 'stakind1 => %s::smallint, stakind2 => %s::smallint, ' + || 'stakind3 => %s::smallint, stakind4 => %s::smallint, ' + || 'stakind5 => %s::smallint, ' + || 'stanumbers1 => %L::real[], stanumbers2 => %L::real[], ' + || 'stanumbers3 => %L::real[], stanumbers4 => %L::real[], ' + || 'stanumbers5 => %L::real[], ' + || 'stavalues1 => %L::text, stavalues2 => %L::text, ' + || 'stavalues3 => %L::text, stavalues4 => %L::text, ' + || 'stavalues5 => %L::text)', + 'stats_export_import.' || c.relname || '_clone', a.attname, + s.stainherit, s.stanullfrac, + s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, + s.stakind4, s.stakind5, + s.stanumbers1, s.stanumbers2, + s.stanumbers3, s.stanumbers4, + s.stanumbers5, + s.stavalues1::text, s.stavalues2::text, s.stavalues3::text, + s.stavalues4::text, s.stavalues5::text) +FROM pg_class AS c +JOIN pg_attribute a ON a.attrelid = c.oid +JOIN pg_statistic s ON s.starelid = a.attrelid AND s.staattnum = a.attnum +WHERE c.relnamespace = 'stats_export_import'::regnamespace +AND c.relname IN ('test', 'is_odd') +\gexec + +-- restore ECHO to original value +\set ECHO :orig_ECHO + +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_export_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_export_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_export_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_export_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_export_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_export_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_export_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_export_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_export_import.is_odd'::regclass; diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 0bb7aeb40e..3e88aec27f 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -28739,6 +28739,95 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset in identifying the specific disk files associated with database objects. </para> + <table id="functions-admin-statsimport"> + <title>Database Object Statistics Import Functions</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="func_table_entry"><para role="func_signature"> + Function + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_set_relation_stats</primary> + </indexterm> + <function>pg_set_relation_stats</function> ( <parameter>relation</parameter> <type>regclass</type>, <parameter>reltuples</parameter> <type>float4</type>, <parameter>relpages</parameter> <type>integer</type> ) + <returnvalue>boolean</returnvalue> + </para> + <para> + Updates the <structname>pg_class</structname> row for the specified + <parameter>relation</parameter>, setting the values for the columns + <structfield>reltuples</structfield> and <structfield>relpages</structfield>. + To avoid table bloat in <structname>pg_class</structname>, this change + is made with an in-place update, and therefore cannot be rolled back through + normal transaction processing. + </para> + <para> + 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 + <command>pg_upgrade</command> and <command>pg_restore</command> to + convey the statistics from the old system version into the new one. + </para> + </entry> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_set_attribute_stats</primary> + </indexterm> + <function>pg_set_attribute_stats</function> ( + <parameter>relation</parameter> <type>regclass</type>, + <parameter>attname</parameter> <type>name</type>, + <parameter>stainherit</parameter> <type>boolean</type>, + <parameter>stanullfrac</parameter> <type>real</type>, + <parameter>stawidth</parameter> <type>integer</type>, + <parameter>stadistinct</parameter> <type>real</type>, + <parameter>stakind1</parameter> <type>smallint</type>, + <parameter>stakind2</parameter> <type>smallint</type>, + <parameter>stakind3</parameter> <type>smallint</type>, + <parameter>stakind4</parameter> <type>smallint</type>, + <parameter>stakind5</parameter> <type>smallint</type>, + <parameter>stanumbers1</parameter> <type>real[]</type>, + <parameter>stanumbers2</parameter> <type>real[]</type>, + <parameter>stanumbers3</parameter> <type>real[]</type>, + <parameter>stanumbers4</parameter> <type>real[]</type>, + <parameter>stanumbers5</parameter> <type>real[]</type>, + <parameter>stavalues1</parameter> <type>text</type>, + <parameter>stavalues2</parameter> <type>text</type>, + <parameter>stavalues3</parameter> <type>text</type>, + <parameter>stavalues4</parameter> <type>text</type>, + <parameter>stavalues5</parameter> <type>text</type> ) + <returnvalue>boolean</returnvalue> + </para> + <para> + Replaces the <structname>pg_statistic</structname> row for the + <structname>pg_attribute</structname> row specified by + <parameter>relation</parameter> and <parameter>attname</parameter>. + The values for fields <structfield>staopN</structfield> and + <structfield>stacollN</structfield> are derived from the + <structname>pg_attribute</structname> row and the corresponding + <parameter>stakindN</parameter> value. + </para> + <para> + 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 + <command>pg_upgrade</command> and <command>pg_restore</command> to + convey the statistics from the old system version into the new one. + </para> + </entry> + </row> + </tbody> + </tgroup> + </table> + <table id="functions-admin-dblocation"> <title>Database Object Location Functions</title> <tgroup cols="1"> -- 2.44.0