> It will be good to add more commentary here to the next person adding > a test, and mention > that only stable values should be included in this view. what do you think? >
I added a comment, but I don't think a test writer would be confused for long if they did break that rule. > > > + (references <link > > linkend="catalog-pg-class"><structname>pg_statistic_ext</structname></link>.<structfield>stxrelid</structfield>) > Fixed catalog-pg-attribute and catalog-pg-statistic-ext
From a03ce9ded18ee7ffc5c48f937d314aba375bbe8c Mon Sep 17 00:00:00 2001 From: Corey Huinker <[email protected]> Date: Tue, 10 Mar 2026 13:25:29 -0400 Subject: [PATCH v6 1/3] Add test view pg_stats_check. Add the view stats_import.pg_stats_check, allowing the columns selected to be changed across all tests using the view by just changing the the view definition. This will allow for us to seamlessly add new columns that we do want to see in the test results (attnum) while also allowing us to screen out new columns that cannot be in the test results (tableid). --- src/test/regress/expected/stats_import.out | 64 +++++++++++++--------- src/test/regress/sql/stats_import.sql | 60 ++++++++++++-------- 2 files changed, 74 insertions(+), 50 deletions(-) diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index c7adb783da2..12d70bc4723 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -1,5 +1,16 @@ CREATE SCHEMA stats_import; -- +-- Set up convenience views +-- +CREATE VIEW stats_import.pg_stats_check AS +SELECT schemaname, tablename, attname, inherited, null_frac, avg_width, + n_distinct, most_common_vals::text AS most_common_vals, most_common_freqs, + histogram_bounds::text AS histogram_bounds, correlation, + most_common_elems::text AS most_common_elems, most_common_elem_freqs, + elem_count_histogram, range_length_histogram::text AS range_length_histogram, + range_empty_frac, range_bounds_histogram::text AS range_bounds_histogram +FROM pg_stats; +-- -- Setup functions for set-difference convenience functions -- -- Test to detect any new columns added to pg_statistic. If any columns @@ -643,7 +654,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -670,7 +681,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -695,7 +706,7 @@ WARNING: unrecognized argument name: "nope" (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -721,7 +732,7 @@ WARNING: argument "most_common_vals" must be specified when argument "most_comm (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -747,7 +758,7 @@ WARNING: argument "most_common_freqs" must be specified when argument "most_com (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -775,7 +786,7 @@ WARNING: argument "most_common_freqs" must be specified when argument "most_com (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -802,7 +813,7 @@ WARNING: invalid input syntax for type integer: "four" (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -827,7 +838,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -853,7 +864,7 @@ WARNING: "histogram_bounds" array must not contain null values (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -877,7 +888,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -903,7 +914,7 @@ WARNING: argument "elem_count_histogram" array must not contain null values (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -928,7 +939,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -956,7 +967,7 @@ DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUN (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -982,7 +993,7 @@ WARNING: argument "range_empty_frac" must be specified when argument "range_len (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -1008,7 +1019,7 @@ WARNING: argument "range_length_histogram" must be specified when argument "ran (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -1033,7 +1044,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -1060,7 +1071,7 @@ DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUN (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -1084,7 +1095,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -1112,7 +1123,7 @@ DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -1140,7 +1151,7 @@ DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -1166,7 +1177,7 @@ WARNING: argument "most_common_elem_freqs" must be specified when argument "mos (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -1192,7 +1203,7 @@ WARNING: argument "most_common_elems" must be specified when argument "most_com (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -1217,7 +1228,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -1244,7 +1255,7 @@ DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. (1 row) SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -3362,8 +3373,9 @@ SELECT COUNT(*) FROM stats_import.test_range_expr_null (1 row) DROP SCHEMA stats_import CASCADE; -NOTICE: drop cascades to 18 other objects -DETAIL: drop cascades to view stats_import.pg_statistic_flat_t +NOTICE: drop cascades to 19 other objects +DETAIL: drop cascades to view stats_import.pg_stats_check +drop cascades to view stats_import.pg_statistic_flat_t drop cascades to function stats_import.pg_statistic_flat(text) drop cascades to function stats_import.pg_statistic_get_difference(text,text) drop cascades to view stats_import.pg_stats_ext_flat_t diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index 0518bbf6f42..fe54eb46f43 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -1,5 +1,17 @@ CREATE SCHEMA stats_import; +-- +-- Set up convenience views +-- +CREATE VIEW stats_import.pg_stats_check AS +SELECT schemaname, tablename, attname, inherited, null_frac, avg_width, + n_distinct, most_common_vals::text AS most_common_vals, most_common_freqs, + histogram_bounds::text AS histogram_bounds, correlation, + most_common_elems::text AS most_common_elems, most_common_elem_freqs, + elem_count_histogram, range_length_histogram::text AS range_length_histogram, + range_empty_frac, range_bounds_histogram::text AS range_bounds_histogram +FROM pg_stats; + -- -- Setup functions for set-difference convenience functions -- @@ -521,7 +533,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'n_distinct', 0.6::real); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -540,7 +552,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'null_frac', 0.4::real); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -556,7 +568,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'nope', 0.5::real); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -573,7 +585,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -590,7 +602,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -608,7 +620,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -626,7 +638,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -643,7 +655,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -660,7 +672,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -676,7 +688,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -693,7 +705,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -710,7 +722,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -728,7 +740,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -745,7 +757,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -762,7 +774,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -779,7 +791,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -796,7 +808,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -812,7 +824,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -830,7 +842,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -848,7 +860,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -865,7 +877,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -882,7 +894,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -899,7 +911,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false @@ -916,7 +928,7 @@ SELECT pg_catalog.pg_restore_attribute_stats( ); SELECT * -FROM pg_stats +FROM stats_import.pg_stats_check WHERE schemaname = 'stats_import' AND tablename = 'test' AND inherited = false base-commit: a0b6ef29a51818a4073a5f390ed10ef6453d5c11 -- 2.53.0
From bee766997a938d115bcf97b0ffb0b181f9863b75 Mon Sep 17 00:00:00 2001 From: Corey Huinker <[email protected]> Date: Tue, 10 Mar 2026 13:51:00 -0400 Subject: [PATCH v6 2/3] Add tableid, statid, attnum, expr_attnum columns to pg_stats views. Add tableid and attnum columns to pg_stats. Add tableid, statid columns to pg_stats_ext. Add tableid, statid, expr_attnum columns to pg_stats_ext_exprs. 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. This patch makes simlilar changes to pg_stats_ext and pg_stats_ext_exprs, but there is no immediate need to utlize those changes in pg_dump. --- src/backend/catalog/system_views.sql | 171 +++++++++++---------- src/test/regress/expected/rules.out | 121 ++++++++------- src/test/regress/expected/stats_import.out | 150 +++++++++--------- src/test/regress/sql/stats_import.sql | 5 +- doc/src/sgml/system-views.sgml | 72 +++++++++ 5 files changed, 304 insertions(+), 215 deletions(-) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 339c016e510..1367bcbad4b 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, @@ -278,8 +280,10 @@ REVOKE ALL ON pg_statistic FROM public; CREATE VIEW pg_stats_ext WITH (security_barrier) AS SELECT cn.nspname AS schemaname, c.relname AS tablename, + s.stxrelid AS tableid, sn.nspname AS statistics_schemaname, s.stxname AS statistics_name, + s.oid AS statid, pg_get_userbyid(s.stxowner) AS statistics_owner, ( SELECT array_agg(a.attname ORDER BY a.attnum) FROM unnest(s.stxkeys) k @@ -312,92 +316,97 @@ CREATE VIEW pg_stats_ext WITH (security_barrier) AS CREATE VIEW pg_stats_ext_exprs WITH (security_barrier) AS SELECT cn.nspname AS schemaname, c.relname AS tablename, + s.stxrelid AS tableid, sn.nspname AS statistics_schemaname, s.stxname AS statistics_name, + s.oid AS statid, pg_get_userbyid(s.stxowner) AS statistics_owner, - stat.expr, + expr.expr, + 0 - expr.ordinality AS expr_attnum, sd.stxdinherit AS inherited, - (stat.a).stanullfrac AS null_frac, - (stat.a).stawidth AS avg_width, - (stat.a).stadistinct AS n_distinct, - (CASE - WHEN (stat.a).stakind1 = 1 THEN (stat.a).stavalues1 - WHEN (stat.a).stakind2 = 1 THEN (stat.a).stavalues2 - WHEN (stat.a).stakind3 = 1 THEN (stat.a).stavalues3 - WHEN (stat.a).stakind4 = 1 THEN (stat.a).stavalues4 - WHEN (stat.a).stakind5 = 1 THEN (stat.a).stavalues5 - END) AS most_common_vals, - (CASE - WHEN (stat.a).stakind1 = 1 THEN (stat.a).stanumbers1 - WHEN (stat.a).stakind2 = 1 THEN (stat.a).stanumbers2 - WHEN (stat.a).stakind3 = 1 THEN (stat.a).stanumbers3 - WHEN (stat.a).stakind4 = 1 THEN (stat.a).stanumbers4 - WHEN (stat.a).stakind5 = 1 THEN (stat.a).stanumbers5 - END) AS most_common_freqs, - (CASE - WHEN (stat.a).stakind1 = 2 THEN (stat.a).stavalues1 - WHEN (stat.a).stakind2 = 2 THEN (stat.a).stavalues2 - WHEN (stat.a).stakind3 = 2 THEN (stat.a).stavalues3 - WHEN (stat.a).stakind4 = 2 THEN (stat.a).stavalues4 - WHEN (stat.a).stakind5 = 2 THEN (stat.a).stavalues5 - END) AS histogram_bounds, - (CASE - WHEN (stat.a).stakind1 = 3 THEN (stat.a).stanumbers1[1] - WHEN (stat.a).stakind2 = 3 THEN (stat.a).stanumbers2[1] - WHEN (stat.a).stakind3 = 3 THEN (stat.a).stanumbers3[1] - WHEN (stat.a).stakind4 = 3 THEN (stat.a).stanumbers4[1] - WHEN (stat.a).stakind5 = 3 THEN (stat.a).stanumbers5[1] - END) correlation, - (CASE - WHEN (stat.a).stakind1 = 4 THEN (stat.a).stavalues1 - WHEN (stat.a).stakind2 = 4 THEN (stat.a).stavalues2 - WHEN (stat.a).stakind3 = 4 THEN (stat.a).stavalues3 - WHEN (stat.a).stakind4 = 4 THEN (stat.a).stavalues4 - WHEN (stat.a).stakind5 = 4 THEN (stat.a).stavalues5 - END) AS most_common_elems, - (CASE - WHEN (stat.a).stakind1 = 4 THEN (stat.a).stanumbers1 - WHEN (stat.a).stakind2 = 4 THEN (stat.a).stanumbers2 - WHEN (stat.a).stakind3 = 4 THEN (stat.a).stanumbers3 - WHEN (stat.a).stakind4 = 4 THEN (stat.a).stanumbers4 - WHEN (stat.a).stakind5 = 4 THEN (stat.a).stanumbers5 - END) AS most_common_elem_freqs, - (CASE - WHEN (stat.a).stakind1 = 5 THEN (stat.a).stanumbers1 - WHEN (stat.a).stakind2 = 5 THEN (stat.a).stanumbers2 - WHEN (stat.a).stakind3 = 5 THEN (stat.a).stanumbers3 - WHEN (stat.a).stakind4 = 5 THEN (stat.a).stanumbers4 - WHEN (stat.a).stakind5 = 5 THEN (stat.a).stanumbers5 - END) AS elem_count_histogram, - (CASE - WHEN (stat.a).stakind1 = 6 THEN (stat.a).stavalues1 - WHEN (stat.a).stakind2 = 6 THEN (stat.a).stavalues2 - WHEN (stat.a).stakind3 = 6 THEN (stat.a).stavalues3 - WHEN (stat.a).stakind4 = 6 THEN (stat.a).stavalues4 - WHEN (stat.a).stakind5 = 6 THEN (stat.a).stavalues5 - END) AS range_length_histogram, - (CASE - WHEN (stat.a).stakind1 = 6 THEN (stat.a).stanumbers1[1] - WHEN (stat.a).stakind2 = 6 THEN (stat.a).stanumbers2[1] - WHEN (stat.a).stakind3 = 6 THEN (stat.a).stanumbers3[1] - WHEN (stat.a).stakind4 = 6 THEN (stat.a).stanumbers4[1] - WHEN (stat.a).stakind5 = 6 THEN (stat.a).stanumbers5[1] - END) AS range_empty_frac, - (CASE - WHEN (stat.a).stakind1 = 7 THEN (stat.a).stavalues1 - WHEN (stat.a).stakind2 = 7 THEN (stat.a).stavalues2 - WHEN (stat.a).stakind3 = 7 THEN (stat.a).stavalues3 - WHEN (stat.a).stakind4 = 7 THEN (stat.a).stavalues4 - WHEN (stat.a).stakind5 = 7 THEN (stat.a).stavalues5 - END) AS range_bounds_histogram + a.stanullfrac AS null_frac, + a.stawidth AS avg_width, + a.stadistinct AS n_distinct, + CASE + WHEN a.stakind1 = 1 THEN a.stavalues1 + WHEN a.stakind2 = 1 THEN a.stavalues2 + WHEN a.stakind3 = 1 THEN a.stavalues3 + WHEN a.stakind4 = 1 THEN a.stavalues4 + WHEN a.stakind5 = 1 THEN a.stavalues5 + END AS most_common_vals, + CASE + WHEN a.stakind1 = 1 THEN a.stanumbers1 + WHEN a.stakind2 = 1 THEN a.stanumbers2 + WHEN a.stakind3 = 1 THEN a.stanumbers3 + WHEN a.stakind4 = 1 THEN a.stanumbers4 + WHEN a.stakind5 = 1 THEN a.stanumbers5 + END AS most_common_freqs, + CASE + WHEN a.stakind1 = 2 THEN a.stavalues1 + WHEN a.stakind2 = 2 THEN a.stavalues2 + WHEN a.stakind3 = 2 THEN a.stavalues3 + WHEN a.stakind4 = 2 THEN a.stavalues4 + WHEN a.stakind5 = 2 THEN a.stavalues5 + END AS histogram_bounds, + CASE + WHEN a.stakind1 = 3 THEN a.stanumbers1[1] + WHEN a.stakind2 = 3 THEN a.stanumbers2[1] + WHEN a.stakind3 = 3 THEN a.stanumbers3[1] + WHEN a.stakind4 = 3 THEN a.stanumbers4[1] + WHEN a.stakind5 = 3 THEN a.stanumbers5[1] + END AS correlation, + CASE + WHEN a.stakind1 = 4 THEN a.stavalues1 + WHEN a.stakind2 = 4 THEN a.stavalues2 + WHEN a.stakind3 = 4 THEN a.stavalues3 + WHEN a.stakind4 = 4 THEN a.stavalues4 + WHEN a.stakind5 = 4 THEN a.stavalues5 + END aS most_common_elems, + CASE + WHEN a.stakind1 = 4 THEN a.stanumbers1 + WHEN a.stakind2 = 4 THEN a.stanumbers2 + WHEN a.stakind3 = 4 THEN a.stanumbers3 + WHEN a.stakind4 = 4 THEN a.stanumbers4 + WHEN a.stakind5 = 4 THEN a.stanumbers5 + END aS most_common_elem_freqs, + CASE + WHEN a.stakind1 = 5 THEN a.stanumbers1 + WHEN a.stakind2 = 5 THEN a.stanumbers2 + WHEN a.stakind3 = 5 THEN a.stanumbers3 + WHEN a.stakind4 = 5 THEN a.stanumbers4 + WHEN a.stakind5 = 5 THEN a.stanumbers5 + END aS elem_count_histogram, + CASE + WHEN a.stakind1 = 6 THEN a.stavalues1 + WHEN a.stakind2 = 6 THEN a.stavalues2 + WHEN a.stakind3 = 6 THEN a.stavalues3 + WHEN a.stakind4 = 6 THEN a.stavalues4 + WHEN a.stakind5 = 6 THEN a.stavalues5 + END aS range_length_histogram, + CASE + WHEN a.stakind1 = 6 THEN a.stanumbers1[1] + WHEN a.stakind2 = 6 THEN a.stanumbers2[1] + WHEN a.stakind3 = 6 THEN a.stanumbers3[1] + WHEN a.stakind4 = 6 THEN a.stanumbers4[1] + WHEN a.stakind5 = 6 THEN a.stanumbers5[1] + END aS range_empty_frac, + CASE + WHEN a.stakind1 = 7 THEN a.stavalues1 + WHEN a.stakind2 = 7 THEN a.stavalues2 + WHEN a.stakind3 = 7 THEN a.stavalues3 + WHEN a.stakind4 = 7 THEN a.stavalues4 + WHEN a.stakind5 = 7 THEN a.stavalues5 + END AS range_bounds_histogram FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid) LEFT JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid) LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace) LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace) - JOIN LATERAL ( - SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr, - unnest(sd.stxdexpr)::pg_statistic AS a - ) stat ON (stat.expr IS NOT NULL) + JOIN LATERAL unnest(pg_get_statisticsobjdef_expressions(s.oid)) + WITH ORDINALITY AS expr(expr, ordinality) + ON s.stxexprs IS NOT NULL + LEFT JOIN LATERAL unnest(sd.stxdexpr) + WITH ORDINALITY AS a + ON a.ordinality = expr.ordinality WHERE pg_has_role(c.relowner, 'USAGE') AND (c.relrowsecurity = false OR NOT row_security_active(c.oid)); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index f373ad704b6..7e1af2edb32 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2542,7 +2542,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, @@ -2634,8 +2636,10 @@ pg_stats| SELECT n.nspname AS schemaname, WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid)))); pg_stats_ext| SELECT cn.nspname AS schemaname, c.relname AS tablename, + s.stxrelid AS tableid, sn.nspname AS statistics_schemaname, s.stxname AS statistics_name, + s.oid AS statid, pg_get_userbyid(s.stxowner) AS statistics_owner, ( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg FROM (unnest(s.stxkeys) k(k) @@ -2662,101 +2666,104 @@ pg_stats_ext| SELECT cn.nspname AS schemaname, WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid)))); pg_stats_ext_exprs| SELECT cn.nspname AS schemaname, c.relname AS tablename, + s.stxrelid AS tableid, sn.nspname AS statistics_schemaname, s.stxname AS statistics_name, + s.oid AS statid, pg_get_userbyid(s.stxowner) AS statistics_owner, - stat.expr, + expr.expr, + (0 - expr.ordinality) AS expr_attnum, sd.stxdinherit AS inherited, - (stat.a).stanullfrac AS null_frac, - (stat.a).stawidth AS avg_width, - (stat.a).stadistinct AS n_distinct, + a.stanullfrac AS null_frac, + a.stawidth AS avg_width, + a.stadistinct AS n_distinct, CASE - WHEN ((stat.a).stakind1 = 1) THEN (stat.a).stavalues1 - WHEN ((stat.a).stakind2 = 1) THEN (stat.a).stavalues2 - WHEN ((stat.a).stakind3 = 1) THEN (stat.a).stavalues3 - WHEN ((stat.a).stakind4 = 1) THEN (stat.a).stavalues4 - WHEN ((stat.a).stakind5 = 1) THEN (stat.a).stavalues5 + WHEN (a.stakind1 = 1) THEN a.stavalues1 + WHEN (a.stakind2 = 1) THEN a.stavalues2 + WHEN (a.stakind3 = 1) THEN a.stavalues3 + WHEN (a.stakind4 = 1) THEN a.stavalues4 + WHEN (a.stakind5 = 1) THEN a.stavalues5 ELSE NULL::anyarray END AS most_common_vals, CASE - WHEN ((stat.a).stakind1 = 1) THEN (stat.a).stanumbers1 - WHEN ((stat.a).stakind2 = 1) THEN (stat.a).stanumbers2 - WHEN ((stat.a).stakind3 = 1) THEN (stat.a).stanumbers3 - WHEN ((stat.a).stakind4 = 1) THEN (stat.a).stanumbers4 - WHEN ((stat.a).stakind5 = 1) THEN (stat.a).stanumbers5 + WHEN (a.stakind1 = 1) THEN a.stanumbers1 + WHEN (a.stakind2 = 1) THEN a.stanumbers2 + WHEN (a.stakind3 = 1) THEN a.stanumbers3 + WHEN (a.stakind4 = 1) THEN a.stanumbers4 + WHEN (a.stakind5 = 1) THEN a.stanumbers5 ELSE NULL::real[] END AS most_common_freqs, CASE - WHEN ((stat.a).stakind1 = 2) THEN (stat.a).stavalues1 - WHEN ((stat.a).stakind2 = 2) THEN (stat.a).stavalues2 - WHEN ((stat.a).stakind3 = 2) THEN (stat.a).stavalues3 - WHEN ((stat.a).stakind4 = 2) THEN (stat.a).stavalues4 - WHEN ((stat.a).stakind5 = 2) THEN (stat.a).stavalues5 + WHEN (a.stakind1 = 2) THEN a.stavalues1 + WHEN (a.stakind2 = 2) THEN a.stavalues2 + WHEN (a.stakind3 = 2) THEN a.stavalues3 + WHEN (a.stakind4 = 2) THEN a.stavalues4 + WHEN (a.stakind5 = 2) THEN a.stavalues5 ELSE NULL::anyarray END AS histogram_bounds, CASE - WHEN ((stat.a).stakind1 = 3) THEN (stat.a).stanumbers1[1] - WHEN ((stat.a).stakind2 = 3) THEN (stat.a).stanumbers2[1] - WHEN ((stat.a).stakind3 = 3) THEN (stat.a).stanumbers3[1] - WHEN ((stat.a).stakind4 = 3) THEN (stat.a).stanumbers4[1] - WHEN ((stat.a).stakind5 = 3) THEN (stat.a).stanumbers5[1] + WHEN (a.stakind1 = 3) THEN a.stanumbers1[1] + WHEN (a.stakind2 = 3) THEN a.stanumbers2[1] + WHEN (a.stakind3 = 3) THEN a.stanumbers3[1] + WHEN (a.stakind4 = 3) THEN a.stanumbers4[1] + WHEN (a.stakind5 = 3) THEN a.stanumbers5[1] ELSE NULL::real END AS correlation, CASE - WHEN ((stat.a).stakind1 = 4) THEN (stat.a).stavalues1 - WHEN ((stat.a).stakind2 = 4) THEN (stat.a).stavalues2 - WHEN ((stat.a).stakind3 = 4) THEN (stat.a).stavalues3 - WHEN ((stat.a).stakind4 = 4) THEN (stat.a).stavalues4 - WHEN ((stat.a).stakind5 = 4) THEN (stat.a).stavalues5 + WHEN (a.stakind1 = 4) THEN a.stavalues1 + WHEN (a.stakind2 = 4) THEN a.stavalues2 + WHEN (a.stakind3 = 4) THEN a.stavalues3 + WHEN (a.stakind4 = 4) THEN a.stavalues4 + WHEN (a.stakind5 = 4) THEN a.stavalues5 ELSE NULL::anyarray END AS most_common_elems, CASE - WHEN ((stat.a).stakind1 = 4) THEN (stat.a).stanumbers1 - WHEN ((stat.a).stakind2 = 4) THEN (stat.a).stanumbers2 - WHEN ((stat.a).stakind3 = 4) THEN (stat.a).stanumbers3 - WHEN ((stat.a).stakind4 = 4) THEN (stat.a).stanumbers4 - WHEN ((stat.a).stakind5 = 4) THEN (stat.a).stanumbers5 + WHEN (a.stakind1 = 4) THEN a.stanumbers1 + WHEN (a.stakind2 = 4) THEN a.stanumbers2 + WHEN (a.stakind3 = 4) THEN a.stanumbers3 + WHEN (a.stakind4 = 4) THEN a.stanumbers4 + WHEN (a.stakind5 = 4) THEN a.stanumbers5 ELSE NULL::real[] END AS most_common_elem_freqs, CASE - WHEN ((stat.a).stakind1 = 5) THEN (stat.a).stanumbers1 - WHEN ((stat.a).stakind2 = 5) THEN (stat.a).stanumbers2 - WHEN ((stat.a).stakind3 = 5) THEN (stat.a).stanumbers3 - WHEN ((stat.a).stakind4 = 5) THEN (stat.a).stanumbers4 - WHEN ((stat.a).stakind5 = 5) THEN (stat.a).stanumbers5 + WHEN (a.stakind1 = 5) THEN a.stanumbers1 + WHEN (a.stakind2 = 5) THEN a.stanumbers2 + WHEN (a.stakind3 = 5) THEN a.stanumbers3 + WHEN (a.stakind4 = 5) THEN a.stanumbers4 + WHEN (a.stakind5 = 5) THEN a.stanumbers5 ELSE NULL::real[] END AS elem_count_histogram, CASE - WHEN ((stat.a).stakind1 = 6) THEN (stat.a).stavalues1 - WHEN ((stat.a).stakind2 = 6) THEN (stat.a).stavalues2 - WHEN ((stat.a).stakind3 = 6) THEN (stat.a).stavalues3 - WHEN ((stat.a).stakind4 = 6) THEN (stat.a).stavalues4 - WHEN ((stat.a).stakind5 = 6) THEN (stat.a).stavalues5 + WHEN (a.stakind1 = 6) THEN a.stavalues1 + WHEN (a.stakind2 = 6) THEN a.stavalues2 + WHEN (a.stakind3 = 6) THEN a.stavalues3 + WHEN (a.stakind4 = 6) THEN a.stavalues4 + WHEN (a.stakind5 = 6) THEN a.stavalues5 ELSE NULL::anyarray END AS range_length_histogram, CASE - WHEN ((stat.a).stakind1 = 6) THEN (stat.a).stanumbers1[1] - WHEN ((stat.a).stakind2 = 6) THEN (stat.a).stanumbers2[1] - WHEN ((stat.a).stakind3 = 6) THEN (stat.a).stanumbers3[1] - WHEN ((stat.a).stakind4 = 6) THEN (stat.a).stanumbers4[1] - WHEN ((stat.a).stakind5 = 6) THEN (stat.a).stanumbers5[1] + WHEN (a.stakind1 = 6) THEN a.stanumbers1[1] + WHEN (a.stakind2 = 6) THEN a.stanumbers2[1] + WHEN (a.stakind3 = 6) THEN a.stanumbers3[1] + WHEN (a.stakind4 = 6) THEN a.stanumbers4[1] + WHEN (a.stakind5 = 6) THEN a.stanumbers5[1] ELSE NULL::real END AS range_empty_frac, CASE - WHEN ((stat.a).stakind1 = 7) THEN (stat.a).stavalues1 - WHEN ((stat.a).stakind2 = 7) THEN (stat.a).stavalues2 - WHEN ((stat.a).stakind3 = 7) THEN (stat.a).stavalues3 - WHEN ((stat.a).stakind4 = 7) THEN (stat.a).stavalues4 - WHEN ((stat.a).stakind5 = 7) THEN (stat.a).stavalues5 + WHEN (a.stakind1 = 7) THEN a.stavalues1 + WHEN (a.stakind2 = 7) THEN a.stavalues2 + WHEN (a.stakind3 = 7) THEN a.stavalues3 + WHEN (a.stakind4 = 7) THEN a.stavalues4 + WHEN (a.stakind5 = 7) THEN a.stavalues5 ELSE NULL::anyarray END AS range_bounds_histogram - FROM (((((pg_statistic_ext s + FROM ((((((pg_statistic_ext s JOIN pg_class c ON ((c.oid = s.stxrelid))) LEFT JOIN pg_statistic_ext_data sd ON ((s.oid = sd.stxoid))) LEFT JOIN pg_namespace cn ON ((cn.oid = c.relnamespace))) LEFT JOIN pg_namespace sn ON ((sn.oid = s.stxnamespace))) - JOIN LATERAL ( SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr, - unnest(sd.stxdexpr) AS a) stat ON ((stat.expr IS NOT NULL))) + JOIN LATERAL unnest(pg_get_statisticsobjdef_expressions(s.oid)) WITH ORDINALITY expr(expr, ordinality) ON ((s.stxexprs IS NOT NULL))) + LEFT JOIN LATERAL unnest(sd.stxdexpr) WITH ORDINALITY a(starelid, staattnum, stainherit, stanullfrac, stawidth, stadistinct, stakind1, stakind2, stakind3, stakind4, stakind5, staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, stavalues1, stavalues2, stavalues3, stavalues4, stavalues5, ordinality) ON ((a.ordinality = expr.ordinality))) WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid)))); pg_tables| SELECT n.nspname AS schemaname, c.relname AS tablename, diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index 12d70bc4723..4f0e846b005 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -3,7 +3,7 @@ CREATE SCHEMA stats_import; -- Set up convenience views -- CREATE VIEW stats_import.pg_stats_check AS -SELECT schemaname, tablename, attname, inherited, null_frac, avg_width, +SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width, n_distinct, most_common_vals::text AS most_common_vals, most_common_freqs, histogram_bounds::text AS histogram_bounds, correlation, most_common_elems::text AS most_common_elems, most_common_elem_freqs, @@ -77,7 +77,7 @@ SELECT COUNT(*) FROM pg_attribute attnum > 0; count ------- - 15 + 17 (1 row) -- Create a view that is used purely for the type based on pg_stats_ext. @@ -118,7 +118,7 @@ SELECT COUNT(*) FROM pg_attribute attnum > 0; count ------- - 20 + 23 (1 row) -- Create a view that is used purely for the type based on pg_stats_ext_exprs. @@ -659,9 +659,9 @@ 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) -- @@ -686,9 +686,9 @@ 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 @@ -711,9 +711,9 @@ 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 @@ -737,9 +737,9 @@ 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 @@ -763,9 +763,9 @@ 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 @@ -791,9 +791,9 @@ 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 @@ -818,9 +818,9 @@ 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 @@ -843,9 +843,9 @@ 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 @@ -869,9 +869,9 @@ 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 @@ -893,9 +893,9 @@ 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 @@ -919,9 +919,9 @@ 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 @@ -944,9 +944,9 @@ 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 @@ -972,9 +972,9 @@ 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 @@ -998,9 +998,9 @@ 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 @@ -1024,9 +1024,9 @@ 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 @@ -1049,9 +1049,9 @@ 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 @@ -1076,9 +1076,9 @@ 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 @@ -1100,9 +1100,9 @@ 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 @@ -1128,9 +1128,9 @@ 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 @@ -1156,9 +1156,9 @@ 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 @@ -1182,9 +1182,9 @@ 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 @@ -1208,9 +1208,9 @@ 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 @@ -1233,9 +1233,9 @@ 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 @@ -1260,9 +1260,9 @@ 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 fe54eb46f43..cb0d274fe4b 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -1,10 +1,11 @@ CREATE SCHEMA stats_import; -- --- Set up convenience views +-- Set up convenience views. Only stable values can be exposed by these +-- views. -- CREATE VIEW stats_import.pg_stats_check AS -SELECT schemaname, tablename, attname, inherited, null_frac, avg_width, +SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width, n_distinct, most_common_vals::text AS most_common_vals, most_common_freqs, histogram_bounds::text AS histogram_bounds, correlation, most_common_elems::text AS most_common_elems, most_common_elem_freqs, diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index e5fe423fc61..403a8d07400 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 </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>tableid</structfield> <type>oid</type> + (references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attrelid</structfield>) + </para> + <para> + ID of the table + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>attname</structfield> <type>name</type> @@ -4424,6 +4434,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>attnum</structfield> <type>int2</type> + (references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attnum</structfield>) + </para> + <para> + Position of column described by this row + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>inherited</structfield> <type>bool</type> @@ -4666,6 +4686,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>tableid</structfield> <type>oid</type> + (references <link linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link>.<structfield>stxrelid</structfield>) + </para> + <para> + ID of the table + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>statistics_schemaname</structfield> <type>name</type> @@ -4686,6 +4716,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>statid</structfield> <type>oid</type> + (references <link linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link>.<structfield>oid</structfield>) + </para> + <para> + ID of the extended statistics object + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>statistics_owner</structfield> <type>name</type> @@ -4877,6 +4917,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>tableid</structfield> <type>oid</type> + (references <link linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link>.<structfield>stxrelid</structfield>) + </para> + <para> + ID of the table + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>statistics_schemaname</structfield> <type>name</type> @@ -4897,6 +4947,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>statid</structfield> <type>oid</type> + (references <link linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link>.<structfield>oid</structfield>) + </para> + <para> + ID of the extended statistics object + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>statistics_owner</structfield> <type>name</type> @@ -4916,6 +4976,18 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>expr_attnum</structfield> <type>int2</type> + </para> + <para> + Synthetic attnum used to reference this expression in + <link linkend="catalog-pg-class"><structname>pg_statistic_ext</structname></link>.<structfield>stxdndistinct</structfield> + and + <link linkend="catalog-pg-class"><structname>pg_statistic_ext</structname></link>.<structfield>stxddependencies</structfield> + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>inherited</structfield> <type>bool</type> -- 2.53.0
From 2f6c80b5a09f3baddf056fcf5033004691c4890e Mon Sep 17 00:00:00 2001 From: Corey Huinker <[email protected]> Date: Wed, 25 Feb 2026 15:56:14 -0500 Subject: [PATCH v6 3/3] pg_dump: Use tableid in getAttributeStats The existing query for fetching attribute stats is clumsy for several reasons. One is that the volume of stats returned is unpredictable and could be very large, so stats must be fetched in medium-sized batches. The other is that the stats fetching query is on pg_stats, which historically does not expose tableid, requiring us to pass in an array of schemanames and an array of tablenames and unnest them in pairs. This results in a hash join which gives very poor performance, but adding an extra qual was able to trick the query into using an existing index. That trick always seems brittle because it is, and while it works on all past versions, there is no guarantee that it will continue to work on future versions. With that in mind, change the pg_dump query to instead use tableid on versions in which tableid is available in pg_stats. This virtually guarantees that pg_statistic will use index lookups, eliminates the "trick" qual mentioned above, and is just simpler. --- src/bin/pg_dump/pg_dump.c | 102 +++++++++++++++++++++++++++++++------- src/bin/pg_dump/pg_dump.h | 1 + 2 files changed, 84 insertions(+), 19 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 137161aa5e0..9cdacbc3467 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -7224,6 +7224,7 @@ getRelationStatistics(Archive *fout, DumpableObject *rel, int32 relpages, dobj->components |= DUMP_COMPONENT_STATISTICS; dobj->name = pg_strdup(rel->name); dobj->namespace = rel->namespace; + info->starelid = rel->catId.oid; info->relpages = relpages; info->reltuples = pstrdup(reltuples); info->relallvisible = relallvisible; @@ -11118,8 +11119,9 @@ static PGresult * fetchAttributeStats(Archive *fout) { ArchiveHandle *AH = (ArchiveHandle *) fout; - PQExpBuffer nspnames = createPQExpBuffer(); - PQExpBuffer relnames = createPQExpBuffer(); + PQExpBuffer nspnames = NULL; + PQExpBuffer relnames = NULL; + PQExpBuffer starelids = NULL; int count = 0; PGresult *res = NULL; static TocEntry *te; @@ -11153,8 +11155,18 @@ fetchAttributeStats(Archive *fout) restarted = true; } - appendPQExpBufferChar(nspnames, '{'); - appendPQExpBufferChar(relnames, '{'); + if (fout->remoteVersion >= 190000) + { + starelids = createPQExpBuffer(); + appendPQExpBufferChar(starelids, '{'); + } + else + { + nspnames = createPQExpBuffer(); + relnames = createPQExpBuffer(); + appendPQExpBufferChar(nspnames, '{'); + appendPQExpBufferChar(relnames, '{'); + } /* * Scan the TOC for the next set of relevant stats entries. We assume @@ -11167,14 +11179,35 @@ fetchAttributeStats(Archive *fout) if ((te->reqs & REQ_STATS) != 0 && strcmp(te->desc, "STATISTICS DATA") == 0) { - appendPGArray(nspnames, te->namespace); - appendPGArray(relnames, te->tag); + if (fout->remoteVersion >= 190000) + { + RelStatsInfo *rsinfo = (RelStatsInfo *) te->defnDumperArg; + + if (rsinfo == NULL) + pg_fatal("statistics table oid information missing for %s.%s", + te->namespace, te->tag); + + if (count > 0) + appendPQExpBufferChar(starelids, ','); + appendPQExpBuffer(starelids, "%u", rsinfo->starelid); + } + else + { + appendPGArray(nspnames, te->namespace); + appendPGArray(relnames, te->tag); + } + count++; } } - appendPQExpBufferChar(nspnames, '}'); - appendPQExpBufferChar(relnames, '}'); + if (fout->remoteVersion >= 190000) + appendPQExpBufferChar(starelids, '}'); + else + { + appendPQExpBufferChar(nspnames, '}'); + appendPQExpBufferChar(relnames, '}'); + } /* Execute the query for the next batch of relations. */ if (count > 0) @@ -11182,16 +11215,30 @@ fetchAttributeStats(Archive *fout) PQExpBuffer query = createPQExpBuffer(); appendPQExpBufferStr(query, "EXECUTE getAttributeStats("); - appendStringLiteralAH(query, nspnames->data, fout); - appendPQExpBufferStr(query, "::pg_catalog.name[],"); - appendStringLiteralAH(query, relnames->data, fout); - appendPQExpBufferStr(query, "::pg_catalog.name[])"); + if (fout->remoteVersion >= 190000) + { + appendStringLiteralAH(query, starelids->data, fout); + appendPQExpBufferStr(query, "::pg_catalog.oid[])"); + } + else + { + appendStringLiteralAH(query, nspnames->data, fout); + appendPQExpBufferStr(query, "::pg_catalog.name[],"); + appendStringLiteralAH(query, relnames->data, fout); + appendPQExpBufferStr(query, "::pg_catalog.name[])"); + } + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); destroyPQExpBuffer(query); } - destroyPQExpBuffer(nspnames); - destroyPQExpBuffer(relnames); + if (fout->remoteVersion >= 190000) + destroyPQExpBuffer(starelids); + else + { + destroyPQExpBuffer(nspnames); + destroyPQExpBuffer(relnames); + } return res; } @@ -11250,8 +11297,18 @@ dumpRelationStats_dumper(Archive *fout, const void *userArg, const TocEntry *te) query = createPQExpBuffer(); if (!fout->is_prepared[PREPQUERY_GETATTRIBUTESTATS]) { + /* + * Before v19, the starelid was not available in pg_stats. Prior to + * that we must identify tables with schemaname+relname. + */ + if (fout->remoteVersion >= 190000) + appendPQExpBufferStr(query, + "PREPARE getAttributeStats(pg_catalog.oid[]) AS\n"); + else + appendPQExpBufferStr(query, + "PREPARE getAttributeStats(pg_catalog.name[], pg_catalog.name[]) AS\n"); + appendPQExpBufferStr(query, - "PREPARE getAttributeStats(pg_catalog.name[], pg_catalog.name[]) AS\n" "SELECT s.schemaname, s.tablename, s.attname, s.inherited, " "s.null_frac, s.avg_width, s.n_distinct, " "s.most_common_vals, s.most_common_freqs, " @@ -11270,22 +11327,30 @@ dumpRelationStats_dumper(Archive *fout, const void *userArg, const TocEntry *te) "NULL AS range_empty_frac," "NULL AS range_bounds_histogram "); + appendPQExpBufferStr(query, "FROM pg_catalog.pg_stats s "); + /* * The results must be in the order of the relations supplied in the * parameters to ensure we remain in sync as we walk through the TOC. + * * The redundant filter clause on s.tablename = ANY(...) seems * sufficient to convince the planner to use * pg_class_relname_nsp_index, which avoids a full scan of pg_stats. - * This may not work for all versions. + * This seems to work for all version prior to v19, after which we + * will use the starelid, which is simpler. * * Our query for retrieving statistics for multiple relations uses * WITH ORDINALITY and multi-argument UNNEST(), both of which were * introduced in v9.4. For older versions, we resort to gathering * statistics for a single relation at a time. */ - if (fout->remoteVersion >= 90400) + if (fout->remoteVersion >= 190000) + appendPQExpBufferStr(query, + "JOIN unnest($1) WITH ORDINALITY AS u (tableid, ord) " + "ON s.tableid = u.tableid " + "ORDER BY u.ord, s.attname, s.inherited"); + else if (fout->remoteVersion >= 90400) appendPQExpBufferStr(query, - "FROM pg_catalog.pg_stats s " "JOIN unnest($1, $2) WITH ORDINALITY AS u (schemaname, tablename, ord) " "ON s.schemaname = u.schemaname " "AND s.tablename = u.tablename " @@ -11293,7 +11358,6 @@ dumpRelationStats_dumper(Archive *fout, const void *userArg, const TocEntry *te) "ORDER BY u.ord, s.attname, s.inherited"); else appendPQExpBufferStr(query, - "FROM pg_catalog.pg_stats s " "WHERE s.schemaname = $1[1] " "AND s.tablename = $2[1] " "ORDER BY s.attname, s.inherited"); diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 1c11a79083f..6fa248cc812 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -448,6 +448,7 @@ typedef struct _indexAttachInfo typedef struct _relStatsInfo { DumpableObject dobj; + Oid starelid; int32 relpages; char *reltuples; int32 relallvisible; -- 2.53.0
