From 5e393d997411b78ceea01e054765ffed214a303f Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Tue, 24 Feb 2026 16:12:55 -0500 Subject: [PATCH v4 1/3] Add tableid and attnum 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 | 20 ++ 5 files changed, 339 insertions(+), 123 deletions(-) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index ecb7c996e86..70846f1555d 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -189,9 +189,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, + a.attrelid AS tableid, + a.attname AS attname, + a.attnum AS attnum, 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 deb6e2ad6a9..46be5c7ac90 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2524,7 +2524,9 @@ 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, + a.attrelid AS tableid, a.attname, + a.attnum, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index c7adb783da2..8d66d4d9329 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -642,15 +642,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | f | 0.2 | 5 | 0.6 | | | | | | | | | | (1 row) -- @@ -669,15 +673,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | f | 0.4 | 5 | 0.6 | | | | | | | | | | (1 row) -- warn: unrecognized argument name, rest get set @@ -694,15 +702,19 @@ WARNING: unrecognized argument name: "nope" f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | f | 0.2 | 5 | 0.6 | | | | | | | | | | (1 row) -- warn: mcv / mcf null mismatch part 1, rest get set @@ -720,15 +732,19 @@ WARNING: argument "most_common_vals" must be specified when argument "most_comm f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | f | 0.21 | 5 | 0.6 | | | | | | | | | | (1 row) -- warn: mcv / mcf null mismatch part 2, rest get set @@ -746,15 +762,19 @@ WARNING: argument "most_common_freqs" must be specified when argument "most_com f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | f | 0.21 | 5 | 0.6 | | | | | | | | | | (1 row) -- warn: mcf type mismatch, mcv-pair fails, rest get set @@ -774,15 +794,19 @@ WARNING: argument "most_common_freqs" must be specified when argument "most_com f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | f | 0.22 | 5 | 0.6 | | | | | | | | | | (1 row) -- warn: mcv cast failure, mcv-pair fails, rest get set @@ -801,15 +825,19 @@ WARNING: invalid input syntax for type integer: "four" f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | f | 0.23 | 5 | 0.6 | | | | | | | | | | (1 row) -- ok: mcv+mcf @@ -826,15 +854,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | 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 @@ -852,15 +884,19 @@ WARNING: "histogram_bounds" array must not contain null values f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | f | 0.24 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | | (1 row) -- ok: histogram_bounds @@ -876,15 +912,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | 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 @@ -902,15 +942,19 @@ WARNING: argument "elem_count_histogram" array must not contain null values f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 5 | f | 0.25 | 0 | 0 | | | | | | | | | | (1 row) -- ok: elem_count_histogram @@ -927,15 +971,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 5 | 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 @@ -955,15 +1003,19 @@ DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUN f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | 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 @@ -981,15 +1033,19 @@ WARNING: argument "range_empty_frac" must be specified when argument "range_len f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 4 | f | 0.28 | 0 | 0 | | | | | | | | | | (1 row) -- warn: range_empty_frac range_length_hist null mismatch part 2, rest ok @@ -1007,15 +1063,19 @@ WARNING: argument "range_length_histogram" must be specified when argument "ran f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 4 | f | 0.29 | 0 | 0 | | | | | | | | | | (1 row) -- ok: range_empty_frac + range_length_hist @@ -1032,15 +1092,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 4 | f | 0.29 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 | (1 row) -- warn: range bounds histogram on scalar, rest ok @@ -1059,15 +1123,19 @@ DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUN f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | 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 @@ -1083,15 +1151,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 4 | 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 @@ -1111,15 +1183,19 @@ DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 4 | 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 @@ -1139,15 +1215,19 @@ DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | 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 @@ -1165,15 +1245,19 @@ WARNING: argument "most_common_elem_freqs" must be specified when argument "mos f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 5 | 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 @@ -1191,15 +1275,19 @@ WARNING: argument "most_common_elems" must be specified when argument "most_com f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 5 | 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 @@ -1216,15 +1304,19 @@ SELECT pg_catalog.pg_restore_attribute_stats( t (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 5 | 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 @@ -1243,15 +1335,19 @@ DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. f (1 row) -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 | attname | attnum | 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 | 1 | 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 0518bbf6f42..8fbec83b5e0 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -520,7 +520,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'avg_width', 5::integer, 'n_distinct', 0.6::real); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -539,7 +543,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'inherited', false::boolean, 'null_frac', 0.4::real); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -555,7 +563,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'null_frac', 0.2::real, 'nope', 0.5::real); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -572,7 +584,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_freqs', '{0.1,0.2,0.3}'::real[] ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -589,7 +605,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_vals', '{1,2,3}'::text ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -607,7 +627,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_freqs', '{0.2,0.1}'::double precision[] ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -625,7 +649,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_freqs', '{0.3,0.25,0.05}'::real[] ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -642,7 +670,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_freqs', '{0.3,0.25,0.05}'::real[] ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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 +691,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'histogram_bounds', '{1,NULL,3,4}'::text ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -675,7 +711,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'histogram_bounds', '{1,2,3,4}'::text ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -692,7 +732,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'elem_count_histogram', '{1,1,NULL,1,1,1,1,1}'::real[] ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -709,7 +753,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, attname, attnum, 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' @@ -727,7 +775,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_length_histogram', '{399,499,Infinity}'::text ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -744,7 +796,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_length_histogram', '{399,499,Infinity}'::text ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -761,7 +817,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_empty_frac', 0.5::real ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -778,7 +838,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_length_histogram', '{399,499,Infinity}'::text ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -795,7 +859,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -811,7 +879,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -829,7 +901,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, attname, attnum, 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' @@ -847,7 +923,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, attname, attnum, 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' @@ -864,7 +944,11 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'most_common_elems', '{one,two}'::text ); -SELECT * +SELECT schemaname, tablename, attname, attnum, 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' @@ -881,7 +965,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, attname, attnum, 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' @@ -898,7 +986,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, attname, attnum, 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' @@ -915,7 +1007,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, attname, attnum, 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..8214f3cd062 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -4414,6 +4414,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + tableid oid + (references pg_attribute.attrelid) + + + ID of the table + + + attname name @@ -4424,6 +4434,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + attnum int2 + (references pg_attribute.attnum) + + + Position of column described by this row + + + inherited bool base-commit: 6307b096e2599edfe238816118b3f365a73fd12a -- 2.53.0