From 307447e6dbc035c0e6e7a8ce9cee704a3b710ee9 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Tue, 10 Feb 2026 12:36:57 +0900 Subject: [PATCH] Add information about range type stats to pg_stats_ext_exprs This commit adds three attributes to the system view pg_stats_ext_exprs, whose data can exist when involving a range type in an expression: range_length_histogram range_empty_frac range_bounds_histogram These statistics fields exist since 918eee0c497c, and have become viewable in pg_stats later in bc3c8db8ae2f. This puts the definition of pg_stats_ext_exprs on par with pg_stats. This issue has showed up during the discussion about the restore of extended statistics for expressions, so as it becomes possible to query the stats data to restore from the catalogs. Having access to this data is useful on its own, without the restore part. Some documentation and some tests are added, written by me. Corey has authored the part in system_views.sql. Bump catalog version. Author: Corey Huinker Co-authored-by: Michael Paquier Discussion: https://postgr.es/m/aYmCUx9VvrKiZQLL@paquier.xyz --- doc/src/sgml/system-views.sgml | 39 +++++++++++++++++++++++++ src/backend/catalog/system_views.sql | 23 ++++++++++++++- src/include/catalog/catversion.h | 2 +- src/test/regress/expected/rules.out | 26 ++++++++++++++++- src/test/regress/expected/stats_ext.out | 27 +++++++++++++++++ src/test/regress/sql/stats_ext.sql | 18 ++++++++++++ 6 files changed, 132 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index 8b4abef8c68..e5fe423fc61 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -5045,6 +5045,45 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx non-null elements. (Null for scalar types.) + + + + range_length_histogram anyarray + + + A histogram of the lengths of non-empty and non-null range values of an + expression. (Null for non-range types.) + + + This histogram is calculated using the subtype_diff + range function regardless of whether range bounds are inclusive. + + + + + + range_empty_frac float4 + + + Fraction of expression entries whose values are empty ranges. + (Null for non-range types.) + + + + + + range_bounds_histogram anyarray + + + A histogram of lower and upper bounds of non-empty and non-null range + values. (Null for non-range types.) + + + These two histograms are represented as a single array of ranges, whose + lower bounds represent the histogram of lower bounds, and upper bounds + represent the histogram of upper bounds. + + diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 7553f31fef0..1ea8f1faa9e 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -363,7 +363,28 @@ CREATE VIEW pg_stats_ext_exprs WITH (security_barrier) AS 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 + 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 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) diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index a09d8a6c645..a910b3d04e6 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202602051 +#define CATALOG_VERSION_NO 202602101 #endif diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index f4ee2bd7459..f9bc213e5a1 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2696,7 +2696,31 @@ pg_stats_ext_exprs| SELECT cn.nspname AS schemaname, WHEN ((stat.a).stakind4 = 5) THEN (stat.a).stanumbers4 WHEN ((stat.a).stakind5 = 5) THEN (stat.a).stanumbers5 ELSE NULL::real[] - END AS elem_count_histogram + 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 + 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] + 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 + ELSE NULL::anyarray + 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))) diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index b2a06579135..cb8856ac50f 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -3628,3 +3628,30 @@ SELECT * FROM check_estimated_rows('SELECT * FROM sb_2 WHERE numeric_lt(y, 1.0)' -- Tidy up DROP TABLE sb_1, sb_2 CASCADE; +-- Check statistics generated for range type and expressions. +CREATE TABLE stats_ext_tbl_range(name text, irange int4range); +INSERT INTO stats_ext_tbl_range VALUES + ('red', '[1,7)'::int4range), + ('blue', '[2,8]'::int4range), + ('green', '[3,9)'::int4range); +CREATE STATISTICS stats_ext_range (mcv) + ON irange, (irange + '[4,10)'::int4range) + FROM stats_ext_tbl_range; +ANALYZE stats_ext_tbl_range; +SELECT attnames, most_common_vals + FROM pg_stats_ext + WHERE statistics_name = 'stats_ext_range'; + attnames | most_common_vals +----------+------------------------------------------------------------ + {irange} | {{"[1,7)","[1,10)"},{"[2,9)","[2,10)"},{"[3,9)","[3,10)"}} +(1 row) + +SELECT range_length_histogram, range_empty_frac, range_bounds_histogram + FROM pg_stats_ext_exprs + WHERE statistics_name = 'stats_ext_range'; + range_length_histogram | range_empty_frac | range_bounds_histogram +------------------------+------------------+------------------------------ + {7,8,9} | 0 | {"[1,10)","[2,10)","[3,10)"} +(1 row) + +DROP TABLE stats_ext_tbl_range; diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 76ee9d29c08..9dcce3440c8 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -1866,3 +1866,21 @@ SELECT * FROM check_estimated_rows('SELECT * FROM sb_2 WHERE numeric_lt(y, 1.0)' -- Tidy up DROP TABLE sb_1, sb_2 CASCADE; + +-- Check statistics generated for range type and expressions. +CREATE TABLE stats_ext_tbl_range(name text, irange int4range); +INSERT INTO stats_ext_tbl_range VALUES + ('red', '[1,7)'::int4range), + ('blue', '[2,8]'::int4range), + ('green', '[3,9)'::int4range); +CREATE STATISTICS stats_ext_range (mcv) + ON irange, (irange + '[4,10)'::int4range) + FROM stats_ext_tbl_range; +ANALYZE stats_ext_tbl_range; +SELECT attnames, most_common_vals + FROM pg_stats_ext + WHERE statistics_name = 'stats_ext_range'; +SELECT range_length_histogram, range_empty_frac, range_bounds_histogram + FROM pg_stats_ext_exprs + WHERE statistics_name = 'stats_ext_range'; +DROP TABLE stats_ext_tbl_range; -- 2.47.3