From 9e4b64f8d7ea2089fe001b81c852aa7e11928f15 Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Mon, 9 Feb 2026 14:26:30 -0500 Subject: [PATCH v9 5/5] Add range statistics to pg_restore_extended_stats expressions. Add support for the statistics range_length_histogram, range_empty_frac, and range_bounds_histogram to statistics for expressions. These were previously not supported because they were not exposed via pg_stats_ext_exprs. --- src/backend/statistics/extended_stats_funcs.c | 96 ++++++++- src/bin/pg_dump/pg_dump.c | 5 +- src/test/regress/expected/stats_import.out | 200 ++++++++++++++++-- src/test/regress/sql/stats_import.sql | 172 ++++++++++++++- 4 files changed, 446 insertions(+), 27 deletions(-) diff --git a/src/backend/statistics/extended_stats_funcs.c b/src/backend/statistics/extended_stats_funcs.c index 229092e852d..e94f297f1a4 100644 --- a/src/backend/statistics/extended_stats_funcs.c +++ b/src/backend/statistics/extended_stats_funcs.c @@ -21,6 +21,7 @@ #include "catalog/namespace.h" #include "catalog/pg_collation_d.h" #include "catalog/pg_database.h" +#include "catalog/pg_operator.h" #include "catalog/pg_statistic_ext.h" #include "catalog/pg_statistic_ext_data.h" #include "miscadmin.h" @@ -97,6 +98,9 @@ enum extended_stats_exprs_element MOST_COMMON_ELEMS_ELEM, MOST_COMMON_ELEM_FREQS_ELEM, ELEM_COUNT_HISTOGRAM_ELEM, + RANGE_LENGTH_HISTOGRAM_ELEM, + RANGE_EMPTY_FRAC_ELEM, + RANGE_BOUNDS_HISTOGRAM_ELEM, NUM_ATTRIBUTE_STATS_ELEMS }; @@ -115,6 +119,9 @@ static const char *extexprargname[NUM_ATTRIBUTE_STATS_ELEMS] = "most_common_elems", "most_common_elem_freqs", "elem_count_histogram", + "range_length_histogram", + "range_empty_frac", + "range_bounds_histogram" }; static bool extended_statistics_update(FunctionCallInfo fcinfo); @@ -1163,10 +1170,10 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont, goto pg_statistic_error; /* - * There are two arg pairs, MCV+MCF and MCEV+MCEF. Both values must either - * be found or not be found. Any disagreement is a warning. Once we have - * ruled out disagreeing pairs, we can use either found flag as a proxy - * for the other. + * There are three arg pairs, MCV+MCF, MCEV+MCEF, and RLE+REF. Both values + * must either be found or not found. Any disagreement is a warning. Once + * we have ruled out diagreeing pairs, we can use either found flag as a + * proxy for the other. */ if (found[MOST_COMMON_VALS_ELEM] != found[MOST_COMMON_FREQS_ELEM]) { @@ -1190,6 +1197,17 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont, extexprargname[MOST_COMMON_ELEM_FREQS_ELEM])); goto pg_statistic_error; } + if (found[RANGE_LENGTH_HISTOGRAM_ELEM] != found[RANGE_EMPTY_FRAC_ELEM]) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse \"%s\": invalid element in expression %d", + argname, exprnum), + errhint("\"%s\" and \"%s\" must be both either strings or nulls.", + extexprargname[RANGE_BOUNDS_HISTOGRAM_ELEM], + extexprargname[RANGE_EMPTY_FRAC_ELEM])); + goto pg_statistic_error; + } /* This finds the right operators even if atttypid is a domain */ typcache = lookup_type_cache(typid, TYPECACHE_LT_OPR | TYPECACHE_EQ_OPR); @@ -1400,10 +1418,74 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont, } /* - * Currently there is no extended stats export of the statistic kinds - * BOUNDS_HISTOGRAM or RANGE_LENGTH_HISTOGRAM so these cannot be imported. - * These may be added in the future. + * STATISTIC_KIND_BOUNDS_HISTOGRAM + * + * This stakind appears before STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM even + * though it is numerically greater, and all other stakinds appear in + * numerical order. We duplicate this quirk for consistency. */ + if (found[RANGE_BOUNDS_HISTOGRAM_ELEM]) + { + Datum stavalues; + bool val_ok = false; + char *s; + Oid rtypid = typid; + + /* + * If it's a multirange, step down to the range type, as is done by + * multirange_typanalyze(). + */ + if (type_is_multirange(typid)) + rtypid = get_multirange_range(typid); + + s = jbv_string_get_cstr(&val[RANGE_BOUNDS_HISTOGRAM_ELEM]); + + stavalues = array_in_safe(array_in_fn, s, rtypid, typmod, exprnum, + extexprargname[RANGE_BOUNDS_HISTOGRAM_ELEM], + &val_ok); + + if (val_ok) + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_BOUNDS_HISTOGRAM, + InvalidOid, InvalidOid, + 0, true, stavalues, false); + else + goto pg_statistic_error; + } + + /* STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM */ + if (found[RANGE_LENGTH_HISTOGRAM_ELEM]) + { + Datum empty_frac[] = {(Datum) 0}; + Datum stavalues; + Datum stanumbers; + bool val_ok = false; + char *s; + + if (jbv_to_infunc_datum(&val[RANGE_EMPTY_FRAC_ELEM], float4in, exprnum, + extexprargname[RANGE_EMPTY_FRAC_ELEM], &empty_frac[0])) + { + ArrayType *arry = construct_array_builtin(empty_frac, 1, FLOAT4OID); + + stanumbers = PointerGetDatum(arry); + } + else + goto pg_statistic_error; + + s = jbv_string_get_cstr(&val[RANGE_LENGTH_HISTOGRAM_ELEM]); + stavalues = array_in_safe(array_in_fn, s, FLOAT8OID, -1, exprnum, + extexprargname[RANGE_LENGTH_HISTOGRAM_ELEM], + &val_ok); + + if (val_ok) + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM, + Float8LessOperator, InvalidOid, + stanumbers, false, stavalues, false); + else + goto pg_statistic_error; + } + pgstup = heap_form_tuple(RelationGetDescr(pgsd), values, nulls); pgstdat = heap_copy_tuple_as_datum(pgstup, RelationGetDescr(pgsd)); diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 293aee02866..229e20859fd 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -18682,7 +18682,10 @@ dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) " 'correlation', ee.correlation::text, " " 'most_common_elems', ee.most_common_elems::text, " " 'most_common_elem_freqs', ee.most_common_elem_freqs::text, " - " 'elem_count_histogram', ee.elem_count_histogram::text " + " 'elem_count_histogram', ee.elem_count_histogram::text, " + " 'range_length_histogram', ee.range_length_histogram::text, " + " 'range_empty_frac', ee.range_empty_frac::text, " + " 'range_bounds_histogram', ee.range_bounds_histogram::text " " )) AS j(obj)" "WHERE ee.statistics_schemaname = $1 " "AND ee.statistics_name = $2 "); diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index 3d2fe6b881f..739ae36747a 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -2133,6 +2133,11 @@ most_common_base_freqs | {0.0625,0.0625,0.0625,0.0625} CREATE STATISTICS stats_import.test_mr_stat ON name, mrange, ( mrange + '{[10000,10200)}'::int4multirange) FROM stats_import.test_mr; +CREATE TABLE stats_import.test_mr_clone ( LIKE stats_import.test_mr ) + WITH (autovacuum_enabled = false); +CREATE STATISTICS stats_import.test_mr_stat_clone + ON name, mrange, ( mrange + '{[10000,10200)}'::int4multirange) + FROM stats_import.test_mr_clone; -- ok: multirange stats SELECT pg_catalog.pg_restore_extended_stats( 'schemaname', 'stats_import', @@ -2156,7 +2161,11 @@ SELECT pg_catalog.pg_restore_extended_stats( {red,"{[21,23),[25,29),[120,130)}","{[21,23),[25,29),[120,130),[10000,10200)}"}}'::text[], 'most_common_freqs', '{0.3333333333333333,0.3333333333333333,0.3333333333333333}'::double precision[], 'most_common_base_freqs', '{0.1111111111111111,0.1111111111111111,0.1111111111111111}'::double precision[], - 'exprs', '[{ "avg_width": "60", "null_frac": "0", "n_distinct": "-1" }]'::jsonb); + 'exprs', '[{ "avg_width": "60", "null_frac": "0", "n_distinct": "-1", + "range_length_histogram": "{10179,10189,10199}", + "range_empty_frac": "0", + "range_bounds_histogram": "{\"[1,10200)\",\"[11,10200)\",\"[21,10200)\"}" + }]'::jsonb); pg_restore_extended_stats --------------------------- t @@ -2193,7 +2202,8 @@ most_common_base_freqs | {0.1111111111111111,0.1111111111111111,0.11111111111111 SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, e.most_common_freqs, e.histogram_bounds, e.correlation, - e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram, + e.range_length_histogram, e.range_empty_frac, e.range_bounds_histogram FROM pg_stats_ext_exprs AS e WHERE e.statistics_schemaname = 'stats_import' AND e.statistics_name = 'test_mr_stat' AND @@ -2211,6 +2221,9 @@ correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | +range_length_histogram | {10179,10189,10199} +range_empty_frac | 0 +range_bounds_histogram | {"[1,10200)","[11,10200)","[21,10200)"} -- Incorrect extended stats kind, exprs not supported SELECT pg_catalog.pg_restore_extended_stats( @@ -3008,7 +3021,10 @@ CROSS JOIN LATERAL ( 'correlation', ee.correlation::text, 'most_common_elems', ee.most_common_elems::text, 'most_common_elem_freqs', ee.most_common_elem_freqs::text, - 'elem_count_histogram', ee.elem_count_histogram::text))) + 'elem_count_histogram', ee.elem_count_histogram::text, + 'range_length_histogram', ee.range_length_histogram::text, + 'range_empty_frac', ee.range_empty_frac::text, + 'range_bounds_histogram', ee.range_bounds_histogram::text))) FROM pg_stats_ext_exprs AS ee WHERE ee.statistics_schemaname = e.statistics_schemaname AND ee.statistics_name = e.statistics_name AND @@ -3065,7 +3081,10 @@ SELECT o.inherited, o.histogram_bounds::text AS histogram_bounds, o.correlation, o.most_common_elems::text AS most_common_elems, - o.most_common_elem_freqs, o.elem_count_histogram + o.most_common_elem_freqs, o.elem_count_histogram, + o.range_length_histogram::text AS range_length_histogram, + o.range_empty_frac, + o.range_bounds_histogram::text AS range_bounds_histogram FROM pg_stats_ext_exprs AS o WHERE o.statistics_schemaname = 'stats_import' AND o.statistics_name = 'test_stat' @@ -3077,12 +3096,15 @@ SELECT n.inherited, n.histogram_bounds::text AS histogram_bounds, n.correlation, n.most_common_elems::text AS most_common_elems, - n.most_common_elem_freqs, n.elem_count_histogram + n.most_common_elem_freqs, n.elem_count_histogram, + n.range_length_histogram::text AS range_length_histogram, + n.range_empty_frac, + n.range_bounds_histogram::text AS range_bounds_histogram FROM pg_stats_ext_exprs AS n WHERE n.statistics_schemaname = 'stats_import' AND n.statistics_name = 'test_stat_clone'; - 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 ------------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+---------------------- + 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 +-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ (0 rows) -- Set difference for exprs: new MINUS old. @@ -3093,7 +3115,10 @@ SELECT n.inherited, n.histogram_bounds::text AS histogram_bounds, n.correlation, n.most_common_elems::text AS most_common_elems, - n.most_common_elem_freqs, n.elem_count_histogram + n.most_common_elem_freqs, n.elem_count_histogram, + n.range_length_histogram::text AS range_length_histogram, + n.range_empty_frac, + n.range_bounds_histogram::text AS range_bounds_histogram FROM pg_stats_ext_exprs AS n WHERE n.statistics_schemaname = 'stats_import' AND n.statistics_name = 'test_stat_clone' @@ -3105,16 +3130,166 @@ SELECT o.inherited, o.histogram_bounds::text AS histogram_bounds, o.correlation, o.most_common_elems::text AS most_common_elems, - o.most_common_elem_freqs, o.elem_count_histogram + o.most_common_elem_freqs, o.elem_count_histogram, + o.range_length_histogram::text AS range_length_histogram, + o.range_empty_frac, + o.range_bounds_histogram::text AS range_bounds_histogram FROM pg_stats_ext_exprs AS o WHERE o.statistics_schemaname = 'stats_import' AND o.statistics_name = 'test_stat'; - 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 ------------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+---------------------- + 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 +-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ +(0 rows) + +ANALYZE stats_import.test_mr; +-- Copy stats from test_mr_stat to test_mr_stat_clone +SELECT e.statistics_name, + pg_catalog.pg_restore_extended_stats( + 'schemaname', e.statistics_schemaname::text, + 'relname', 'test_mr_clone', + 'statistics_schemaname', e.statistics_schemaname::text, + 'statistics_name', 'test_mr_stat_clone', + 'inherited', e.inherited, + 'n_distinct', e.n_distinct, + 'dependencies', e.dependencies, + 'most_common_vals', e.most_common_vals, + 'most_common_freqs', e.most_common_freqs, + 'most_common_base_freqs', e.most_common_base_freqs, + 'exprs', x.exprs) +FROM pg_stats_ext AS e +CROSS JOIN LATERAL ( + SELECT jsonb_agg(jsonb_strip_nulls(jsonb_build_object( + 'null_frac', ee.null_frac::text, + 'avg_width', ee.avg_width::text, + 'n_distinct', ee.n_distinct::text, + 'most_common_vals', ee.most_common_vals::text, + 'most_common_freqs', ee.most_common_freqs::text, + 'histogram_bounds', ee.histogram_bounds::text, + 'correlation', ee.correlation::text, + 'most_common_elems', ee.most_common_elems::text, + 'most_common_elem_freqs', ee.most_common_elem_freqs::text, + 'elem_count_histogram', ee.elem_count_histogram::text, + 'range_length_histogram', ee.range_length_histogram::text, + 'range_empty_frac', ee.range_empty_frac::text, + 'range_bounds_histogram', ee.range_bounds_histogram::text))) + FROM pg_stats_ext_exprs AS ee + WHERE ee.statistics_schemaname = e.statistics_schemaname AND + ee.statistics_name = e.statistics_name AND + ee.inherited = e.inherited + ) AS x(exprs) +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_mr_stat'; + statistics_name | pg_restore_extended_stats +-----------------+--------------------------- + test_mr_stat | t +(1 row) + +-- Set difference old MINUS new. +SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_freqs, o.most_common_base_freqs + FROM pg_stats_ext AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_mr_stat' +EXCEPT +SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_freqs, n.most_common_base_freqs + FROM pg_stats_ext AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_mr_stat_clone'; + inherited | n_distinct | dependencies | most_common_vals | most_common_freqs | most_common_base_freqs +-----------+------------+--------------+------------------+-------------------+------------------------ +(0 rows) + +-- Set difference new MINUS old. +SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_freqs, n.most_common_base_freqs + FROM pg_stats_ext AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_mr_stat_clone' +EXCEPT +SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_freqs, o.most_common_base_freqs + FROM pg_stats_ext AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_mr_stat'; + inherited | n_distinct | dependencies | most_common_vals | most_common_freqs | most_common_base_freqs +-----------+------------+--------------+------------------+-------------------+------------------------ +(0 rows) + +-- Set difference for exprs: old MINUS new. +SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram, + o.range_length_histogram::text AS range_length_histogram, + o.range_empty_frac, + o.range_bounds_histogram::text AS range_bounds_histogram + FROM pg_stats_ext_exprs AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_mr_stat' +EXCEPT +SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram, + n.range_length_histogram::text AS range_length_histogram, + n.range_empty_frac, + n.range_bounds_histogram::text AS range_bounds_histogram + FROM pg_stats_ext_exprs AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_mr_stat_clone'; + 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 +-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ +(0 rows) + +-- Set difference for exprs: new MINUS old. +SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram, + n.range_length_histogram::text AS range_length_histogram, + n.range_empty_frac, + n.range_bounds_histogram::text AS range_bounds_histogram + FROM pg_stats_ext_exprs AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_mr_stat_clone' +EXCEPT +SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram, + o.range_length_histogram::text AS range_length_histogram, + o.range_empty_frac, + o.range_bounds_histogram::text AS range_bounds_histogram + FROM pg_stats_ext_exprs AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_mr_stat'; + 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 +-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ (0 rows) DROP SCHEMA stats_import CASCADE; -NOTICE: drop cascades to 7 other objects +NOTICE: drop cascades to 8 other objects DETAIL: drop cascades to type stats_import.complex_type drop cascades to table stats_import.test drop cascades to table stats_import.test_mr @@ -3122,3 +3297,4 @@ drop cascades to table stats_import.part_parent drop cascades to sequence stats_import.testseq drop cascades to view stats_import.testview drop cascades to table stats_import.test_clone +drop cascades to table stats_import.test_mr_clone diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index 4ec8565c622..a2d7247e485 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -1521,6 +1521,12 @@ CREATE STATISTICS stats_import.test_mr_stat ON name, mrange, ( mrange + '{[10000,10200)}'::int4multirange) FROM stats_import.test_mr; +CREATE TABLE stats_import.test_mr_clone ( LIKE stats_import.test_mr ) + WITH (autovacuum_enabled = false); +CREATE STATISTICS stats_import.test_mr_stat_clone + ON name, mrange, ( mrange + '{[10000,10200)}'::int4multirange) + FROM stats_import.test_mr_clone; + -- ok: multirange stats SELECT pg_catalog.pg_restore_extended_stats( 'schemaname', 'stats_import', @@ -1544,7 +1550,11 @@ SELECT pg_catalog.pg_restore_extended_stats( {red,"{[21,23),[25,29),[120,130)}","{[21,23),[25,29),[120,130),[10000,10200)}"}}'::text[], 'most_common_freqs', '{0.3333333333333333,0.3333333333333333,0.3333333333333333}'::double precision[], 'most_common_base_freqs', '{0.1111111111111111,0.1111111111111111,0.1111111111111111}'::double precision[], - 'exprs', '[{ "avg_width": "60", "null_frac": "0", "n_distinct": "-1" }]'::jsonb); + 'exprs', '[{ "avg_width": "60", "null_frac": "0", "n_distinct": "-1", + "range_length_histogram": "{10179,10189,10199}", + "range_empty_frac": "0", + "range_bounds_histogram": "{\"[1,10200)\",\"[11,10200)\",\"[21,10200)\"}" + }]'::jsonb); SELECT replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, replace(e.dependencies, '}, ', E'},\n') AS dependencies, @@ -1559,7 +1569,8 @@ WHERE e.statistics_schemaname = 'stats_import' AND SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, e.most_common_freqs, e.histogram_bounds, e.correlation, - e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram, + e.range_length_histogram, e.range_empty_frac, e.range_bounds_histogram FROM pg_stats_ext_exprs AS e WHERE e.statistics_schemaname = 'stats_import' AND e.statistics_name = 'test_mr_stat' AND @@ -2068,7 +2079,10 @@ CROSS JOIN LATERAL ( 'correlation', ee.correlation::text, 'most_common_elems', ee.most_common_elems::text, 'most_common_elem_freqs', ee.most_common_elem_freqs::text, - 'elem_count_histogram', ee.elem_count_histogram::text))) + 'elem_count_histogram', ee.elem_count_histogram::text, + 'range_length_histogram', ee.range_length_histogram::text, + 'range_empty_frac', ee.range_empty_frac::text, + 'range_bounds_histogram', ee.range_bounds_histogram::text))) FROM pg_stats_ext_exprs AS ee WHERE ee.statistics_schemaname = e.statistics_schemaname AND ee.statistics_name = e.statistics_name AND @@ -2114,7 +2128,10 @@ SELECT o.inherited, o.histogram_bounds::text AS histogram_bounds, o.correlation, o.most_common_elems::text AS most_common_elems, - o.most_common_elem_freqs, o.elem_count_histogram + o.most_common_elem_freqs, o.elem_count_histogram, + o.range_length_histogram::text AS range_length_histogram, + o.range_empty_frac, + o.range_bounds_histogram::text AS range_bounds_histogram FROM pg_stats_ext_exprs AS o WHERE o.statistics_schemaname = 'stats_import' AND o.statistics_name = 'test_stat' @@ -2126,7 +2143,10 @@ SELECT n.inherited, n.histogram_bounds::text AS histogram_bounds, n.correlation, n.most_common_elems::text AS most_common_elems, - n.most_common_elem_freqs, n.elem_count_histogram + n.most_common_elem_freqs, n.elem_count_histogram, + n.range_length_histogram::text AS range_length_histogram, + n.range_empty_frac, + n.range_bounds_histogram::text AS range_bounds_histogram FROM pg_stats_ext_exprs AS n WHERE n.statistics_schemaname = 'stats_import' AND n.statistics_name = 'test_stat_clone'; @@ -2139,7 +2159,10 @@ SELECT n.inherited, n.histogram_bounds::text AS histogram_bounds, n.correlation, n.most_common_elems::text AS most_common_elems, - n.most_common_elem_freqs, n.elem_count_histogram + n.most_common_elem_freqs, n.elem_count_histogram, + n.range_length_histogram::text AS range_length_histogram, + n.range_empty_frac, + n.range_bounds_histogram::text AS range_bounds_histogram FROM pg_stats_ext_exprs AS n WHERE n.statistics_schemaname = 'stats_import' AND n.statistics_name = 'test_stat_clone' @@ -2151,9 +2174,144 @@ SELECT o.inherited, o.histogram_bounds::text AS histogram_bounds, o.correlation, o.most_common_elems::text AS most_common_elems, - o.most_common_elem_freqs, o.elem_count_histogram + o.most_common_elem_freqs, o.elem_count_histogram, + o.range_length_histogram::text AS range_length_histogram, + o.range_empty_frac, + o.range_bounds_histogram::text AS range_bounds_histogram FROM pg_stats_ext_exprs AS o WHERE o.statistics_schemaname = 'stats_import' AND o.statistics_name = 'test_stat'; +ANALYZE stats_import.test_mr; + +-- Copy stats from test_mr_stat to test_mr_stat_clone +SELECT e.statistics_name, + pg_catalog.pg_restore_extended_stats( + 'schemaname', e.statistics_schemaname::text, + 'relname', 'test_mr_clone', + 'statistics_schemaname', e.statistics_schemaname::text, + 'statistics_name', 'test_mr_stat_clone', + 'inherited', e.inherited, + 'n_distinct', e.n_distinct, + 'dependencies', e.dependencies, + 'most_common_vals', e.most_common_vals, + 'most_common_freqs', e.most_common_freqs, + 'most_common_base_freqs', e.most_common_base_freqs, + 'exprs', x.exprs) +FROM pg_stats_ext AS e +CROSS JOIN LATERAL ( + SELECT jsonb_agg(jsonb_strip_nulls(jsonb_build_object( + 'null_frac', ee.null_frac::text, + 'avg_width', ee.avg_width::text, + 'n_distinct', ee.n_distinct::text, + 'most_common_vals', ee.most_common_vals::text, + 'most_common_freqs', ee.most_common_freqs::text, + 'histogram_bounds', ee.histogram_bounds::text, + 'correlation', ee.correlation::text, + 'most_common_elems', ee.most_common_elems::text, + 'most_common_elem_freqs', ee.most_common_elem_freqs::text, + 'elem_count_histogram', ee.elem_count_histogram::text, + 'range_length_histogram', ee.range_length_histogram::text, + 'range_empty_frac', ee.range_empty_frac::text, + 'range_bounds_histogram', ee.range_bounds_histogram::text))) + FROM pg_stats_ext_exprs AS ee + WHERE ee.statistics_schemaname = e.statistics_schemaname AND + ee.statistics_name = e.statistics_name AND + ee.inherited = e.inherited + ) AS x(exprs) +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_mr_stat'; + +-- Set difference old MINUS new. +SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_freqs, o.most_common_base_freqs + FROM pg_stats_ext AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_mr_stat' +EXCEPT +SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_freqs, n.most_common_base_freqs + FROM pg_stats_ext AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_mr_stat_clone'; +-- Set difference new MINUS old. +SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_freqs, n.most_common_base_freqs + FROM pg_stats_ext AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_mr_stat_clone' +EXCEPT +SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_freqs, o.most_common_base_freqs + FROM pg_stats_ext AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_mr_stat'; + +-- Set difference for exprs: old MINUS new. +SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram, + o.range_length_histogram::text AS range_length_histogram, + o.range_empty_frac, + o.range_bounds_histogram::text AS range_bounds_histogram + FROM pg_stats_ext_exprs AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_mr_stat' +EXCEPT +SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram, + n.range_length_histogram::text AS range_length_histogram, + n.range_empty_frac, + n.range_bounds_histogram::text AS range_bounds_histogram + FROM pg_stats_ext_exprs AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_mr_stat_clone'; + +-- Set difference for exprs: new MINUS old. +SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram, + n.range_length_histogram::text AS range_length_histogram, + n.range_empty_frac, + n.range_bounds_histogram::text AS range_bounds_histogram + FROM pg_stats_ext_exprs AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_mr_stat_clone' +EXCEPT +SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram, + o.range_length_histogram::text AS range_length_histogram, + o.range_empty_frac, + o.range_bounds_histogram::text AS range_bounds_histogram + FROM pg_stats_ext_exprs AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_mr_stat'; + + DROP SCHEMA stats_import CASCADE; -- 2.53.0