From 59ac1286cee4a30961cc2661f8bf75ec0969e5ba Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Tue, 24 Feb 2026 16:12:55 -0500 Subject: [PATCH v1 1/2] Add attnum and starelid columns to pg_stats. The primary purpose of this patch is to expose the starelid column of pg_statistic in the pg_stats view. Having this available will allow us to simplify some code in pg_dump which currently has to store arrays of nspname+relname in order to fetch a resonable amount of statistics in one query. Furthermore, the query used in pg_dump requires a redundant qual in the WHERE clause to ensure that it uses a specific index, thus avoiding a hash join and the expensive sequential scan of pg_statistic that that entails. Additionally, there have been times when it would have been nice to have attnum as well as attname available. --- src/backend/catalog/system_views.sql | 8 +- src/test/regress/expected/rules.out | 2 + src/test/regress/expected/stats_import.out | 288 ++++++++++++++------- src/test/regress/sql/stats_import.sql | 144 +++++++++-- doc/src/sgml/system-views.sgml | 19 ++ 5 files changed, 338 insertions(+), 123 deletions(-) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 1ea8f1faa9e..4eeab47a9e9 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -184,9 +184,11 @@ CREATE VIEW pg_sequences AS CREATE VIEW pg_stats WITH (security_barrier) AS SELECT - nspname AS schemaname, - relname AS tablename, - attname AS attname, + n.nspname AS schemaname, + c.relname AS tablename, + s.starelid AS starelid, + a.attnum AS attnum, + a.attname AS attname, stainherit AS inherited, stanullfrac AS null_frac, stawidth AS avg_width, diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 78a37d9fc8f..a2520db28e0 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2513,6 +2513,8 @@ pg_statio_user_tables| SELECT relid, WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_stats| SELECT n.nspname AS schemaname, c.relname AS tablename, + s.starelid, + a.attnum, a.attname, s.stainherit AS inherited, s.stanullfrac AS null_frac, diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index d6cc701500e..2249091ba0e 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -488,15 +488,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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 | 5 | 0.6 | | | | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.2 | 5 | 0.6 | | | | | | | | | | (1 row) -- @@ -515,15 +519,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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 | | | | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.4 | 5 | 0.6 | | | | | | | | | | (1 row) -- warn: unrecognized argument name, rest get set @@ -540,15 +548,19 @@ WARNING: unrecognized argument name: "nope" f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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 | 5 | 0.6 | | | | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.2 | 5 | 0.6 | | | | | | | | | | (1 row) -- warn: mcv / mcf null mismatch part 1, rest get set @@ -566,15 +578,19 @@ WARNING: argument "most_common_vals" must be specified when argument "most_comm f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.21 | 5 | 0.6 | | | | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.21 | 5 | 0.6 | | | | | | | | | | (1 row) -- warn: mcv / mcf null mismatch part 2, rest get set @@ -592,15 +608,19 @@ WARNING: argument "most_common_freqs" must be specified when argument "most_com f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.21 | 5 | 0.6 | | | | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.21 | 5 | 0.6 | | | | | | | | | | (1 row) -- warn: mcf type mismatch, mcv-pair fails, rest get set @@ -620,15 +640,19 @@ WARNING: argument "most_common_freqs" must be specified when argument "most_com f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.22 | 5 | 0.6 | | | | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.22 | 5 | 0.6 | | | | | | | | | | (1 row) -- warn: mcv cast failure, mcv-pair fails, rest get set @@ -647,15 +671,19 @@ WARNING: invalid input syntax for type integer: "four" f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.23 | 5 | 0.6 | | | | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.23 | 5 | 0.6 | | | | | | | | | | (1 row) -- ok: mcv+mcf @@ -672,15 +700,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.23 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.23 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | | (1 row) -- warn: NULL in histogram array, rest get set @@ -698,15 +730,19 @@ WARNING: "histogram_bounds" array must not contain null values f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.24 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.24 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | | (1 row) -- ok: histogram_bounds @@ -722,15 +758,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.24 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.24 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | (1 row) -- warn: elem_count_histogram null element, rest get set @@ -748,15 +788,19 @@ WARNING: argument "elem_count_histogram" array must not contain null values f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.25 | 0 | 0 | | | | | | | | | | + schemaname | tablename | attnum | 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 | 5 | tags | f | 0.25 | 0 | 0 | | | | | | | | | | (1 row) -- ok: elem_count_histogram @@ -773,15 +817,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.26 | 0 | 0 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | | + schemaname | tablename | attnum | 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 | 5 | tags | f | 0.26 | 0 | 0 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,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) -- warn: range stats on a scalar type, rest ok @@ -801,15 +849,19 @@ DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUN f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.27 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.27 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | (1 row) -- warn: range_empty_frac range_length_hist null mismatch, rest ok @@ -827,15 +879,19 @@ WARNING: argument "range_empty_frac" must be specified when argument "range_len f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.28 | 0 | 0 | | | | | | | | | | + schemaname | tablename | attnum | 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 | 4 | arange | f | 0.28 | 0 | 0 | | | | | | | | | | (1 row) -- warn: range_empty_frac range_length_hist null mismatch part 2, rest ok @@ -853,15 +909,19 @@ WARNING: argument "range_length_histogram" must be specified when argument "ran f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.29 | 0 | 0 | | | | | | | | | | + schemaname | tablename | attnum | 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 | 4 | arange | f | 0.29 | 0 | 0 | | | | | | | | | | (1 row) -- ok: range_empty_frac + range_length_hist @@ -878,15 +938,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.29 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 | + schemaname | tablename | attnum | 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 | 4 | arange | f | 0.29 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 | (1 row) -- warn: range bounds histogram on scalar, rest ok @@ -905,15 +969,19 @@ DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUN f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.31 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.31 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | (1 row) -- ok: range_bounds_histogram @@ -929,15 +997,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.29 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"} + schemaname | tablename | attnum | 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 | 4 | arange | f | 0.29 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"} (1 row) -- warn: cannot set most_common_elems for range type, rest ok @@ -957,15 +1029,19 @@ DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.32 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"} + schemaname | tablename | attnum | 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 | 4 | arange | f | 0.32 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"} (1 row) -- warn: scalars can't have mcelem, rest ok @@ -985,15 +1061,19 @@ DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.33 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.33 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | (1 row) -- warn: mcelem / mcelem mismatch, rest ok @@ -1011,15 +1091,19 @@ WARNING: argument "most_common_elem_freqs" must be specified when argument "mos f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.34 | 0 | 0 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | | + schemaname | tablename | attnum | 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 | 5 | tags | f | 0.34 | 0 | 0 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,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) -- warn: mcelem / mcelem null mismatch part 2, rest ok @@ -1037,15 +1121,19 @@ WARNING: argument "most_common_elems" must be specified when argument "most_com f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.35 | 0 | 0 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | | + schemaname | tablename | attnum | 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 | 5 | tags | f | 0.35 | 0 | 0 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,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) -- ok: mcelem @@ -1062,15 +1150,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.35 | 0 | 0 | | | | | {one,three} | {0.3,0.2,0.2,0.3,0} | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | | + schemaname | tablename | attnum | 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 | 5 | tags | f | 0.35 | 0 | 0 | | | | | {one,three} | {0.3,0.2,0.2,0.3,0} | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,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) -- warn: scalars can't have elem_count_histogram, rest ok @@ -1089,15 +1181,19 @@ DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. f (1 row) -SELECT * +SELECT schemaname, tablename, attnum, 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 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.36 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | + schemaname | tablename | attnum | 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 | 1 | id | f | 0.36 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | (1 row) -- test for multiranges diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index 8db7cd93b88..0c368b09a8c 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -368,7 +368,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'avg_width', 5::integer, 'n_distinct', 0.6::real); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -387,7 +391,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'inherited', false::boolean, 'null_frac', 0.4::real); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -403,7 +411,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'null_frac', 0.2::real, 'nope', 0.5::real); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -420,7 +432,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_freqs', '{0.1,0.2,0.3}'::real[] ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -437,7 +453,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_vals', '{1,2,3}'::text ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -455,7 +475,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_freqs', '{0.2,0.1}'::double precision[] ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -473,7 +497,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_freqs', '{0.3,0.25,0.05}'::real[] ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -490,7 +518,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_freqs', '{0.3,0.25,0.05}'::real[] ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -507,7 +539,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'histogram_bounds', '{1,NULL,3,4}'::text ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -523,7 +559,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'histogram_bounds', '{1,2,3,4}'::text ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -540,7 +580,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'elem_count_histogram', '{1,1,NULL,1,1,1,1,1}'::real[] ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -557,7 +601,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( '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 * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -575,7 +623,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_length_histogram', '{399,499,Infinity}'::text ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -592,7 +644,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_length_histogram', '{399,499,Infinity}'::text ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -609,7 +665,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_empty_frac', 0.5::real ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -626,7 +686,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_length_histogram', '{399,499,Infinity}'::text ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -643,7 +707,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -659,7 +727,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -677,7 +749,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[] ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -695,7 +771,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[] ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -712,7 +792,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_elems', '{one,two}'::text ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -729,7 +813,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3}'::real[] ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -746,7 +834,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[] ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' @@ -763,7 +855,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'elem_count_histogram', '{1,1,1,1,1,1,1,1,1,1}'::real[] ); -SELECT * +SELECT schemaname, tablename, attnum, 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 FROM pg_stats WHERE schemaname = 'stats_import' AND tablename = 'test' diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index e5fe423fc61..59f8ff891e7 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -4414,6 +4414,25 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + starelid oid + + + ID of the relation + + + + + + attnum name + (references pg_attribute.attnum) + + + Position of column described by this row + + + attname name base-commit: 4c1a27e53a508f74883cda52a6c8612121d7fd6b -- 2.53.0