pg_stats and range statistics

From 1f2ed0911eb3f7a53b16047cefb499692daf5ef6 Mon Sep 17 00:00:00 2001
From: Egor Rogov <e.rogov@postgrespro.ru>
Date: Sun, 11 Jul 2021 11:09:45 -0700
Subject: [PATCH v2 1/1] Display length and bounds histograms in pg_stats

Values corresponding to STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and
STATISTIC_KIND_BOUNDS_HISTOGRAM were not exposed to pg_stats when these
slot kinds were introduced in 918eee0c49.

This commit adds the missing fields to pg_stats.

TODO: catalog version bump
---
 doc/src/sgml/catalogs.sgml           | 32 ++++++++++++++++++++++++++++
 src/backend/catalog/system_views.sql | 23 +++++++++++++++++++-
 src/include/catalog/pg_statistic.h   |  3 +++
 src/test/regress/expected/rules.out  | 26 +++++++++++++++++++++-
 4 files changed, 82 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index f517a7d4af..7168ff9a13 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -12877,6 +12877,38 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        non-null elements.  (Null for scalar types.)
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_length_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of the lengths of non-empty and non-null range values of a
+       range type column. (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>empty_range_frac</structfield> <type>float4</type>
+      </para>
+      <para>
+       Fraction of column entries whose values are empty ranges.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_bounds_histograms</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       Histograms of lower and upper bounds of non-empty, non-null ranges,
+       combined into a single array of range values. The lower and upper bounds
+       of each value correspond to the histograms of lower and upper bounds
+       respectively. (Null for non-range types.)
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 999d984068..d8bc622ad5 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -243,7 +243,28 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind3 = 5 THEN stanumbers3
             WHEN stakind4 = 5 THEN stanumbers4
             WHEN stakind5 = 5 THEN stanumbers5
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stavalues1
+            WHEN stakind2 = 6 THEN stavalues2
+            WHEN stakind3 = 6 THEN stavalues3
+            WHEN stakind4 = 6 THEN stavalues4
+            WHEN stakind5 = 6 THEN stavalues5
+        END AS range_length_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stanumbers1[1]
+            WHEN stakind2 = 6 THEN stanumbers2[1]
+            WHEN stakind3 = 6 THEN stanumbers3[1]
+            WHEN stakind4 = 6 THEN stanumbers4[1]
+            WHEN stakind5 = 6 THEN stanumbers5[1]
+        END AS empty_range_frac,
+        CASE
+            WHEN stakind1 = 7 THEN stavalues1
+            WHEN stakind2 = 7 THEN stavalues2
+            WHEN stakind3 = 7 THEN stavalues3
+            WHEN stakind4 = 7 THEN stavalues4
+            WHEN stakind5 = 7 THEN stavalues5
+            END AS range_bounds_histograms
     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h
index 4f95d7ade4..d0b4923424 100644
--- a/src/include/catalog/pg_statistic.h
+++ b/src/include/catalog/pg_statistic.h
@@ -152,6 +152,9 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum));
  * data "kind" will appear in any particular slot.  Instead, search the
  * stakind fields to see if the desired data is available.  (The standard
  * function get_attstatsslot() may be used for this.)
+ *
+ * Note: The pg_stats view needs to be modified whenever a new slot kind is
+ * added to core.
  */
 
 /*
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index e5ab11275d..1ce26aa717 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2409,7 +2409,31 @@ pg_stats| SELECT n.nspname AS schemaname,
             WHEN (s.stakind4 = 5) THEN s.stanumbers4
             WHEN (s.stakind5 = 5) THEN s.stanumbers5
             ELSE NULL::real[]
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stavalues1
+            WHEN (s.stakind2 = 6) THEN s.stavalues2
+            WHEN (s.stakind3 = 6) THEN s.stavalues3
+            WHEN (s.stakind4 = 6) THEN s.stavalues4
+            WHEN (s.stakind5 = 6) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_length_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stanumbers1[1]
+            WHEN (s.stakind2 = 6) THEN s.stanumbers2[1]
+            WHEN (s.stakind3 = 6) THEN s.stanumbers3[1]
+            WHEN (s.stakind4 = 6) THEN s.stanumbers4[1]
+            WHEN (s.stakind5 = 6) THEN s.stanumbers5[1]
+            ELSE NULL::real
+        END AS empty_range_frac,
+        CASE
+            WHEN (s.stakind1 = 7) THEN s.stavalues1
+            WHEN (s.stakind2 = 7) THEN s.stavalues2
+            WHEN (s.stakind3 = 7) THEN s.stavalues3
+            WHEN (s.stakind4 = 7) THEN s.stavalues4
+            WHEN (s.stakind5 = 7) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_bounds_histograms
    FROM (((pg_statistic s
      JOIN pg_class c ON ((c.oid = s.starelid)))
      JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
-- 
2.25.1

From b339c0eab5616cec61e9d9e85398034861608d30 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.vondra@postgresql.org>
Date: Fri, 20 Jan 2023 20:50:41 +0100
Subject: [PATCH 1/3] Display length and bounds histograms in pg_stats

Values corresponding to STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and
STATISTIC_KIND_BOUNDS_HISTOGRAM were not exposed to pg_stats when these
slot kinds were introduced in 918eee0c49.

This commit adds the missing fields to pg_stats.

TODO: catalog version bump
---
 doc/src/sgml/catalogs.sgml           | 32 ++++++++++++++++++++++++++++
 src/backend/catalog/system_views.sql | 23 +++++++++++++++++++-
 src/include/catalog/pg_statistic.h   |  3 +++
 src/test/regress/expected/rules.out  | 26 +++++++++++++++++++++-
 4 files changed, 82 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c1e4048054e..c8bd84c56eb 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9634,6 +9634,38 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
        User mapping specific options, as <quote>keyword=value</quote> strings
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_length_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of the lengths of non-empty and non-null range values of a
+       range type column. (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>empty_range_frac</structfield> <type>float4</type>
+      </para>
+      <para>
+       Fraction of column entries whose values are empty ranges.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_bounds_histograms</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       Histograms of lower and upper bounds of non-empty, non-null ranges,
+       combined into a single array of range values. The lower and upper bounds
+       of each value correspond to the histograms of lower and upper bounds
+       respectively. (Null for non-range types.)
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 8608e3fa5b1..ccd6c7ffdb7 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -243,7 +243,28 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind3 = 5 THEN stanumbers3
             WHEN stakind4 = 5 THEN stanumbers4
             WHEN stakind5 = 5 THEN stanumbers5
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stavalues1
+            WHEN stakind2 = 6 THEN stavalues2
+            WHEN stakind3 = 6 THEN stavalues3
+            WHEN stakind4 = 6 THEN stavalues4
+            WHEN stakind5 = 6 THEN stavalues5
+        END AS range_length_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stanumbers1[1]
+            WHEN stakind2 = 6 THEN stanumbers2[1]
+            WHEN stakind3 = 6 THEN stanumbers3[1]
+            WHEN stakind4 = 6 THEN stanumbers4[1]
+            WHEN stakind5 = 6 THEN stanumbers5[1]
+        END AS empty_range_frac,
+        CASE
+            WHEN stakind1 = 7 THEN stavalues1
+            WHEN stakind2 = 7 THEN stavalues2
+            WHEN stakind3 = 7 THEN stavalues3
+            WHEN stakind4 = 7 THEN stavalues4
+            WHEN stakind5 = 7 THEN stavalues5
+            END AS range_bounds_histograms
     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h
index 8770c5b4c60..10401dece0d 100644
--- a/src/include/catalog/pg_statistic.h
+++ b/src/include/catalog/pg_statistic.h
@@ -152,6 +152,9 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum));
  * data "kind" will appear in any particular slot.  Instead, search the
  * stakind fields to see if the desired data is available.  (The standard
  * function get_attstatsslot() may be used for this.)
+ *
+ * Note: The pg_stats view needs to be modified whenever a new slot kind is
+ * added to core.
  */
 
 /*
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index e7a2f5856aa..ced5933271c 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2436,7 +2436,31 @@ pg_stats| SELECT n.nspname AS schemaname,
             WHEN (s.stakind4 = 5) THEN s.stanumbers4
             WHEN (s.stakind5 = 5) THEN s.stanumbers5
             ELSE NULL::real[]
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stavalues1
+            WHEN (s.stakind2 = 6) THEN s.stavalues2
+            WHEN (s.stakind3 = 6) THEN s.stavalues3
+            WHEN (s.stakind4 = 6) THEN s.stavalues4
+            WHEN (s.stakind5 = 6) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_length_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stanumbers1[1]
+            WHEN (s.stakind2 = 6) THEN s.stanumbers2[1]
+            WHEN (s.stakind3 = 6) THEN s.stanumbers3[1]
+            WHEN (s.stakind4 = 6) THEN s.stanumbers4[1]
+            WHEN (s.stakind5 = 6) THEN s.stanumbers5[1]
+            ELSE NULL::real
+        END AS empty_range_frac,
+        CASE
+            WHEN (s.stakind1 = 7) THEN s.stavalues1
+            WHEN (s.stakind2 = 7) THEN s.stavalues2
+            WHEN (s.stakind3 = 7) THEN s.stavalues3
+            WHEN (s.stakind4 = 7) THEN s.stavalues4
+            WHEN (s.stakind5 = 7) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_bounds_histograms
    FROM (((pg_statistic s
      JOIN pg_class c ON ((c.oid = s.starelid)))
      JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
-- 
2.39.0

From 9bd2bbef9dc092ab5fdace74e26f3afeaa62830d Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.vondra@postgresql.org>
Date: Fri, 20 Jan 2023 22:29:53 +0100
Subject: [PATCH 2/3] add functions to extract lower/upper ramge bounds

---
 src/backend/catalog/system_views.sql          |  19 ++-
 src/backend/utils/adt/rangetypes_typanalyze.c | 118 ++++++++++++++++++
 src/include/catalog/pg_proc.dat               |  10 ++
 3 files changed, 141 insertions(+), 6 deletions(-)

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index ccd6c7ffdb7..e3314550e32 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -259,12 +259,19 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind5 = 6 THEN stanumbers5[1]
         END AS empty_range_frac,
         CASE
-            WHEN stakind1 = 7 THEN stavalues1
-            WHEN stakind2 = 7 THEN stavalues2
-            WHEN stakind3 = 7 THEN stavalues3
-            WHEN stakind4 = 7 THEN stavalues4
-            WHEN stakind5 = 7 THEN stavalues5
-            END AS range_bounds_histograms
+            WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1)
+            WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2)
+            WHEN stakind3 = 7 THEN ranges_lower_bounds(stavalues3)
+            WHEN stakind4 = 7 THEN ranges_lower_bounds(stavalues4)
+            WHEN stakind5 = 7 THEN ranges_lower_bounds(stavalues5)
+            END AS range_lower_bounds_histograms,
+        CASE
+            WHEN stakind1 = 7 THEN ranges_upper_bounds(stavalues1)
+            WHEN stakind2 = 7 THEN ranges_upper_bounds(stavalues2)
+            WHEN stakind3 = 7 THEN ranges_upper_bounds(stavalues3)
+            WHEN stakind4 = 7 THEN ranges_upper_bounds(stavalues4)
+            WHEN stakind5 = 7 THEN ranges_upper_bounds(stavalues5)
+            END AS range_upper_bounds_histograms
     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
diff --git a/src/backend/utils/adt/rangetypes_typanalyze.c b/src/backend/utils/adt/rangetypes_typanalyze.c
index 86810a1a6e6..a0097282fc0 100644
--- a/src/backend/utils/adt/rangetypes_typanalyze.c
+++ b/src/backend/utils/adt/rangetypes_typanalyze.c
@@ -26,6 +26,8 @@
 
 #include "catalog/pg_operator.h"
 #include "commands/vacuum.h"
+#include "utils/array.h"
+#include "utils/arrayaccess.h"
 #include "utils/float.h"
 #include "utils/fmgrprotos.h"
 #include "utils/lsyscache.h"
@@ -427,3 +429,119 @@ compute_range_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc,
 	 * hashtable should also go away, as it used a child memory context.
 	 */
 }
+
+Datum
+ranges_lower_bounds(PG_FUNCTION_ARGS)
+{
+	AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0);
+	int			ndims = AARR_NDIM(array);
+	int		   *dims = AARR_DIMS(array);
+	Oid			element_type = AARR_ELEMTYPE(array);
+	int			i;
+	array_iter	iter;
+	int			nelems;
+	Datum	   *elems;
+	TypeCacheEntry *typentry;
+	TypeCacheEntry *typentry_element;
+
+	/* Get information about range type; note column might be a domain */
+	typentry = range_get_typcache(fcinfo, getBaseType(element_type));
+
+	if (typentry->typtype != TYPTYPE_RANGE)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("expected array of ranges")));
+
+	ndims = AARR_NDIM(array);
+	dims = AARR_DIMS(array);
+	nelems = ArrayGetNItems(ndims, dims);
+
+	elems = (Datum *) palloc(nelems * sizeof(Datum));
+
+	array_iter_setup(&iter, array);
+
+	for (i = 0; i < nelems; i++)
+	{
+		Datum		itemvalue;
+		bool		isnull;
+		RangeBound	lower;
+		RangeBound	upper;
+		bool		empty;
+
+		/* Get source element, checking for NULL */
+		itemvalue = array_iter_next(&iter, &isnull, i,
+									typentry->typlen, typentry->typbyval,
+									typentry->typalign);
+
+		Assert(!isnull);
+
+		range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty);
+		elems[i] = lower.val;
+	}
+
+	typentry_element = typentry->rngelemtype;
+
+	PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems,
+						  typentry_element->type_id,
+						  typentry_element->typlen,
+						  typentry_element->typbyval,
+						  typentry_element->typalign));
+}
+
+Datum
+ranges_upper_bounds(PG_FUNCTION_ARGS)
+{
+	AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0);
+	int			ndims = AARR_NDIM(array);
+	int		   *dims = AARR_DIMS(array);
+	Oid			element_type = AARR_ELEMTYPE(array);
+	int			i;
+	array_iter	iter;
+	int			nelems;
+	Datum	   *elems;
+	TypeCacheEntry *typentry;
+	TypeCacheEntry *typentry_element;
+
+	/* Get information about range type; note column might be a domain */
+	typentry = range_get_typcache(fcinfo, getBaseType(element_type));
+
+	if (typentry->typtype != TYPTYPE_RANGE)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("expected array of ranges")));
+
+	ndims = AARR_NDIM(array);
+	dims = AARR_DIMS(array);
+	nelems = ArrayGetNItems(ndims, dims);
+
+	elems = (Datum *) palloc(nelems * sizeof(Datum));
+
+	array_iter_setup(&iter, array);
+
+	for (i = 0; i < nelems; i++)
+	{
+		Datum		itemvalue;
+		bool		isnull;
+		RangeBound	lower;
+		RangeBound	upper;
+		bool		empty;
+
+		/* Get source element, checking for NULL */
+		itemvalue = array_iter_next(&iter, &isnull, i,
+									typentry->typlen, typentry->typbyval,
+									typentry->typalign);
+
+		Assert(!isnull);
+
+		range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty);
+		elems[i] = upper.val;
+	}
+
+	typentry_element = typentry->rngelemtype;
+
+	PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems,
+						  typentry_element->type_id,
+						  typentry_element->typlen,
+						  typentry_element->typbyval,
+						  typentry_element->typalign));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 86eb8e8c58a..161557e4cb2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11891,4 +11891,14 @@
   prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary',
   prosrc => 'brin_minmax_multi_summary_send' },
 
+{ oid => '9693', descr => 'lower bounds of ranges',
+  proname => 'ranges_lower_bounds', provolatile => 's',
+  prorettype => 'anyarray', proargtypes => 'anyarray',
+  prosrc => 'ranges_lower_bounds' },
+
+{ oid => '9694', descr => 'upper bounds of ranges',
+  proname => 'ranges_upper_bounds', provolatile => 's',
+  prorettype => 'anyarray', proargtypes => 'anyarray',
+  prosrc => 'ranges_upper_bounds' },
+
 ]
-- 
2.39.0

From 600fed1faae369b8291ec3a7fc64f6ad8099c124 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.vondra@postgresql.org>
Date: Fri, 20 Jan 2023 22:40:28 +0100
Subject: [PATCH 3/3] rename empty_range_frac

---
 src/backend/catalog/system_views.sql | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index e3314550e32..ac99d41f808 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -257,7 +257,7 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind3 = 6 THEN stanumbers3[1]
             WHEN stakind4 = 6 THEN stanumbers4[1]
             WHEN stakind5 = 6 THEN stanumbers5[1]
-        END AS empty_range_frac,
+        END AS range_empty_frac,
         CASE
             WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1)
             WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2)
-- 
2.39.0

From b339c0eab5616cec61e9d9e85398034861608d30 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.vondra@postgresql.org>
Date: Fri, 20 Jan 2023 20:50:41 +0100
Subject: [PATCH 1/3] Display length and bounds histograms in pg_stats

Values corresponding to STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and
STATISTIC_KIND_BOUNDS_HISTOGRAM were not exposed to pg_stats when these
slot kinds were introduced in 918eee0c49.

This commit adds the missing fields to pg_stats.

TODO: catalog version bump
---
 doc/src/sgml/catalogs.sgml           | 32 ++++++++++++++++++++++++++++
 src/backend/catalog/system_views.sql | 23 +++++++++++++++++++-
 src/include/catalog/pg_statistic.h   |  3 +++
 src/test/regress/expected/rules.out  | 26 +++++++++++++++++++++-
 4 files changed, 82 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c1e4048054e..c8bd84c56eb 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9634,6 +9634,38 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
        User mapping specific options, as <quote>keyword=value</quote> strings
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_length_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of the lengths of non-empty and non-null range values of a
+       range type column. (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>empty_range_frac</structfield> <type>float4</type>
+      </para>
+      <para>
+       Fraction of column entries whose values are empty ranges.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_bounds_histograms</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       Histograms of lower and upper bounds of non-empty, non-null ranges,
+       combined into a single array of range values. The lower and upper bounds
+       of each value correspond to the histograms of lower and upper bounds
+       respectively. (Null for non-range types.)
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 8608e3fa5b1..ccd6c7ffdb7 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -243,7 +243,28 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind3 = 5 THEN stanumbers3
             WHEN stakind4 = 5 THEN stanumbers4
             WHEN stakind5 = 5 THEN stanumbers5
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stavalues1
+            WHEN stakind2 = 6 THEN stavalues2
+            WHEN stakind3 = 6 THEN stavalues3
+            WHEN stakind4 = 6 THEN stavalues4
+            WHEN stakind5 = 6 THEN stavalues5
+        END AS range_length_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stanumbers1[1]
+            WHEN stakind2 = 6 THEN stanumbers2[1]
+            WHEN stakind3 = 6 THEN stanumbers3[1]
+            WHEN stakind4 = 6 THEN stanumbers4[1]
+            WHEN stakind5 = 6 THEN stanumbers5[1]
+        END AS empty_range_frac,
+        CASE
+            WHEN stakind1 = 7 THEN stavalues1
+            WHEN stakind2 = 7 THEN stavalues2
+            WHEN stakind3 = 7 THEN stavalues3
+            WHEN stakind4 = 7 THEN stavalues4
+            WHEN stakind5 = 7 THEN stavalues5
+            END AS range_bounds_histograms
     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h
index 8770c5b4c60..10401dece0d 100644
--- a/src/include/catalog/pg_statistic.h
+++ b/src/include/catalog/pg_statistic.h
@@ -152,6 +152,9 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum));
  * data "kind" will appear in any particular slot.  Instead, search the
  * stakind fields to see if the desired data is available.  (The standard
  * function get_attstatsslot() may be used for this.)
+ *
+ * Note: The pg_stats view needs to be modified whenever a new slot kind is
+ * added to core.
  */
 
 /*
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index e7a2f5856aa..ced5933271c 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2436,7 +2436,31 @@ pg_stats| SELECT n.nspname AS schemaname,
             WHEN (s.stakind4 = 5) THEN s.stanumbers4
             WHEN (s.stakind5 = 5) THEN s.stanumbers5
             ELSE NULL::real[]
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stavalues1
+            WHEN (s.stakind2 = 6) THEN s.stavalues2
+            WHEN (s.stakind3 = 6) THEN s.stavalues3
+            WHEN (s.stakind4 = 6) THEN s.stavalues4
+            WHEN (s.stakind5 = 6) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_length_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stanumbers1[1]
+            WHEN (s.stakind2 = 6) THEN s.stanumbers2[1]
+            WHEN (s.stakind3 = 6) THEN s.stanumbers3[1]
+            WHEN (s.stakind4 = 6) THEN s.stanumbers4[1]
+            WHEN (s.stakind5 = 6) THEN s.stanumbers5[1]
+            ELSE NULL::real
+        END AS empty_range_frac,
+        CASE
+            WHEN (s.stakind1 = 7) THEN s.stavalues1
+            WHEN (s.stakind2 = 7) THEN s.stavalues2
+            WHEN (s.stakind3 = 7) THEN s.stavalues3
+            WHEN (s.stakind4 = 7) THEN s.stavalues4
+            WHEN (s.stakind5 = 7) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_bounds_histograms
    FROM (((pg_statistic s
      JOIN pg_class c ON ((c.oid = s.starelid)))
      JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
-- 
2.39.0

From 9bd2bbef9dc092ab5fdace74e26f3afeaa62830d Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.vondra@postgresql.org>
Date: Fri, 20 Jan 2023 22:29:53 +0100
Subject: [PATCH 2/3] add functions to extract lower/upper ramge bounds

---
 src/backend/catalog/system_views.sql          |  19 ++-
 src/backend/utils/adt/rangetypes_typanalyze.c | 118 ++++++++++++++++++
 src/include/catalog/pg_proc.dat               |  10 ++
 3 files changed, 141 insertions(+), 6 deletions(-)

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index ccd6c7ffdb7..e3314550e32 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -259,12 +259,19 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind5 = 6 THEN stanumbers5[1]
         END AS empty_range_frac,
         CASE
-            WHEN stakind1 = 7 THEN stavalues1
-            WHEN stakind2 = 7 THEN stavalues2
-            WHEN stakind3 = 7 THEN stavalues3
-            WHEN stakind4 = 7 THEN stavalues4
-            WHEN stakind5 = 7 THEN stavalues5
-            END AS range_bounds_histograms
+            WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1)
+            WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2)
+            WHEN stakind3 = 7 THEN ranges_lower_bounds(stavalues3)
+            WHEN stakind4 = 7 THEN ranges_lower_bounds(stavalues4)
+            WHEN stakind5 = 7 THEN ranges_lower_bounds(stavalues5)
+            END AS range_lower_bounds_histograms,
+        CASE
+            WHEN stakind1 = 7 THEN ranges_upper_bounds(stavalues1)
+            WHEN stakind2 = 7 THEN ranges_upper_bounds(stavalues2)
+            WHEN stakind3 = 7 THEN ranges_upper_bounds(stavalues3)
+            WHEN stakind4 = 7 THEN ranges_upper_bounds(stavalues4)
+            WHEN stakind5 = 7 THEN ranges_upper_bounds(stavalues5)
+            END AS range_upper_bounds_histograms
     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
diff --git a/src/backend/utils/adt/rangetypes_typanalyze.c b/src/backend/utils/adt/rangetypes_typanalyze.c
index 86810a1a6e6..a0097282fc0 100644
--- a/src/backend/utils/adt/rangetypes_typanalyze.c
+++ b/src/backend/utils/adt/rangetypes_typanalyze.c
@@ -26,6 +26,8 @@
 
 #include "catalog/pg_operator.h"
 #include "commands/vacuum.h"
+#include "utils/array.h"
+#include "utils/arrayaccess.h"
 #include "utils/float.h"
 #include "utils/fmgrprotos.h"
 #include "utils/lsyscache.h"
@@ -427,3 +429,119 @@ compute_range_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc,
 	 * hashtable should also go away, as it used a child memory context.
 	 */
 }
+
+Datum
+ranges_lower_bounds(PG_FUNCTION_ARGS)
+{
+	AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0);
+	int			ndims = AARR_NDIM(array);
+	int		   *dims = AARR_DIMS(array);
+	Oid			element_type = AARR_ELEMTYPE(array);
+	int			i;
+	array_iter	iter;
+	int			nelems;
+	Datum	   *elems;
+	TypeCacheEntry *typentry;
+	TypeCacheEntry *typentry_element;
+
+	/* Get information about range type; note column might be a domain */
+	typentry = range_get_typcache(fcinfo, getBaseType(element_type));
+
+	if (typentry->typtype != TYPTYPE_RANGE)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("expected array of ranges")));
+
+	ndims = AARR_NDIM(array);
+	dims = AARR_DIMS(array);
+	nelems = ArrayGetNItems(ndims, dims);
+
+	elems = (Datum *) palloc(nelems * sizeof(Datum));
+
+	array_iter_setup(&iter, array);
+
+	for (i = 0; i < nelems; i++)
+	{
+		Datum		itemvalue;
+		bool		isnull;
+		RangeBound	lower;
+		RangeBound	upper;
+		bool		empty;
+
+		/* Get source element, checking for NULL */
+		itemvalue = array_iter_next(&iter, &isnull, i,
+									typentry->typlen, typentry->typbyval,
+									typentry->typalign);
+
+		Assert(!isnull);
+
+		range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty);
+		elems[i] = lower.val;
+	}
+
+	typentry_element = typentry->rngelemtype;
+
+	PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems,
+						  typentry_element->type_id,
+						  typentry_element->typlen,
+						  typentry_element->typbyval,
+						  typentry_element->typalign));
+}
+
+Datum
+ranges_upper_bounds(PG_FUNCTION_ARGS)
+{
+	AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0);
+	int			ndims = AARR_NDIM(array);
+	int		   *dims = AARR_DIMS(array);
+	Oid			element_type = AARR_ELEMTYPE(array);
+	int			i;
+	array_iter	iter;
+	int			nelems;
+	Datum	   *elems;
+	TypeCacheEntry *typentry;
+	TypeCacheEntry *typentry_element;
+
+	/* Get information about range type; note column might be a domain */
+	typentry = range_get_typcache(fcinfo, getBaseType(element_type));
+
+	if (typentry->typtype != TYPTYPE_RANGE)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("expected array of ranges")));
+
+	ndims = AARR_NDIM(array);
+	dims = AARR_DIMS(array);
+	nelems = ArrayGetNItems(ndims, dims);
+
+	elems = (Datum *) palloc(nelems * sizeof(Datum));
+
+	array_iter_setup(&iter, array);
+
+	for (i = 0; i < nelems; i++)
+	{
+		Datum		itemvalue;
+		bool		isnull;
+		RangeBound	lower;
+		RangeBound	upper;
+		bool		empty;
+
+		/* Get source element, checking for NULL */
+		itemvalue = array_iter_next(&iter, &isnull, i,
+									typentry->typlen, typentry->typbyval,
+									typentry->typalign);
+
+		Assert(!isnull);
+
+		range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty);
+		elems[i] = upper.val;
+	}
+
+	typentry_element = typentry->rngelemtype;
+
+	PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems,
+						  typentry_element->type_id,
+						  typentry_element->typlen,
+						  typentry_element->typbyval,
+						  typentry_element->typalign));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 86eb8e8c58a..161557e4cb2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11891,4 +11891,14 @@
   prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary',
   prosrc => 'brin_minmax_multi_summary_send' },
 
+{ oid => '9693', descr => 'lower bounds of ranges',
+  proname => 'ranges_lower_bounds', provolatile => 's',
+  prorettype => 'anyarray', proargtypes => 'anyarray',
+  prosrc => 'ranges_lower_bounds' },
+
+{ oid => '9694', descr => 'upper bounds of ranges',
+  proname => 'ranges_upper_bounds', provolatile => 's',
+  prorettype => 'anyarray', proargtypes => 'anyarray',
+  prosrc => 'ranges_upper_bounds' },
+
 ]
-- 
2.39.0

From 600fed1faae369b8291ec3a7fc64f6ad8099c124 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.vondra@postgresql.org>
Date: Fri, 20 Jan 2023 22:40:28 +0100
Subject: [PATCH 3/3] rename empty_range_frac

---
 src/backend/catalog/system_views.sql | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index e3314550e32..ac99d41f808 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -257,7 +257,7 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind3 = 6 THEN stanumbers3[1]
             WHEN stakind4 = 6 THEN stanumbers4[1]
             WHEN stakind5 = 6 THEN stanumbers5[1]
-        END AS empty_range_frac,
+        END AS range_empty_frac,
         CASE
             WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1)
             WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2)
-- 
2.39.0

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c8bd84c56e..e0851c52b4 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9647,7 +9647,7 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
 
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
-       <structfield>empty_range_frac</structfield> <type>float4</type>
+       <structfield>range_empty_frac</structfield> <type>float4</type>
       </para>
       <para>
        Fraction of column entries whose values are empty ranges.
@@ -9657,13 +9657,21 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
 
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
-       <structfield>range_bounds_histograms</structfield> <type>anyarray</type>
+       <structfield>range_lower_histogram</structfield> <type>anyarray</type>
       </para>
       <para>
-       Histograms of lower and upper bounds of non-empty, non-null ranges,
-       combined into a single array of range values. The lower and upper bounds
-       of each value correspond to the histograms of lower and upper bounds
-       respectively. (Null for non-range types.)
+       A histogram of lower bounds of non-empty and non-null range values.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_upper_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of upper bounds of non-empty and non-null range values.
+       (Null for non-range types.)
       </para></entry>
      </row>
     </tbody>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index ac99d41f80..b3b9e64ae2 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -264,14 +264,14 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind3 = 7 THEN ranges_lower_bounds(stavalues3)
             WHEN stakind4 = 7 THEN ranges_lower_bounds(stavalues4)
             WHEN stakind5 = 7 THEN ranges_lower_bounds(stavalues5)
-            END AS range_lower_bounds_histograms,
+            END AS range_lower_histogram,
         CASE
             WHEN stakind1 = 7 THEN ranges_upper_bounds(stavalues1)
             WHEN stakind2 = 7 THEN ranges_upper_bounds(stavalues2)
             WHEN stakind3 = 7 THEN ranges_upper_bounds(stavalues3)
             WHEN stakind4 = 7 THEN ranges_upper_bounds(stavalues4)
             WHEN stakind5 = 7 THEN ranges_upper_bounds(stavalues5)
-            END AS range_upper_bounds_histograms
+            END AS range_upper_histogram
     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index ced5933271..26087dfdf5 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2452,15 +2452,23 @@ pg_stats| SELECT n.nspname AS schemaname,
             WHEN (s.stakind4 = 6) THEN s.stanumbers4[1]
             WHEN (s.stakind5 = 6) THEN s.stanumbers5[1]
             ELSE NULL::real
-        END AS empty_range_frac,
+        END AS range_empty_frac,
         CASE
-            WHEN (s.stakind1 = 7) THEN s.stavalues1
-            WHEN (s.stakind2 = 7) THEN s.stavalues2
-            WHEN (s.stakind3 = 7) THEN s.stavalues3
-            WHEN (s.stakind4 = 7) THEN s.stavalues4
-            WHEN (s.stakind5 = 7) THEN s.stavalues5
+            WHEN (s.stakind1 = 7) THEN ranges_lower_bounds(s.stavalues1)
+            WHEN (s.stakind2 = 7) THEN ranges_lower_bounds(s.stavalues2)
+            WHEN (s.stakind3 = 7) THEN ranges_lower_bounds(s.stavalues3)
+            WHEN (s.stakind4 = 7) THEN ranges_lower_bounds(s.stavalues4)
+            WHEN (s.stakind5 = 7) THEN ranges_lower_bounds(s.stavalues5)
             ELSE NULL::anyarray
-        END AS range_bounds_histograms
+        END AS range_lower_histogram,
+        CASE
+            WHEN (s.stakind1 = 7) THEN ranges_upper_bounds(s.stavalues1)
+            WHEN (s.stakind2 = 7) THEN ranges_upper_bounds(s.stavalues2)
+            WHEN (s.stakind3 = 7) THEN ranges_upper_bounds(s.stavalues3)
+            WHEN (s.stakind4 = 7) THEN ranges_upper_bounds(s.stavalues4)
+            WHEN (s.stakind5 = 7) THEN ranges_upper_bounds(s.stavalues5)
+            ELSE NULL::anyarray
+        END AS range_upper_histogram
    FROM (((pg_statistic s
      JOIN pg_class c ON ((c.oid = s.starelid)))
      JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))

From 8049d2b7e5d1636d5fb2b7d421d6b29a39389fb3 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.vondra@postgresql.org>
Date: Fri, 20 Jan 2023 20:50:41 +0100
Subject: [PATCH 1/2] Display length and bounds histograms in pg_stats

Values corresponding to STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and
STATISTIC_KIND_BOUNDS_HISTOGRAM were not exposed to pg_stats when these
slot kinds were introduced in 918eee0c49.

This commit adds the missing fields to pg_stats.

TODO: catalog version bump
---
 doc/src/sgml/catalogs.sgml                    |  40 ++++++
 src/backend/catalog/system_views.sql          |  30 ++++-
 src/backend/utils/adt/rangetypes_typanalyze.c | 118 ++++++++++++++++++
 src/include/catalog/pg_proc.dat               |  10 ++
 src/include/catalog/pg_statistic.h            |   3 +
 src/test/regress/expected/rules.out           |  34 ++++-
 6 files changed, 233 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c1e4048054..e0851c52b4 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9634,6 +9634,46 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
        User mapping specific options, as <quote>keyword=value</quote> strings
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_length_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of the lengths of non-empty and non-null range values of a
+       range type column. (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_empty_frac</structfield> <type>float4</type>
+      </para>
+      <para>
+       Fraction of column entries whose values are empty ranges.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_lower_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of lower bounds of non-empty and non-null range values.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_upper_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of upper bounds of non-empty and non-null range values.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 8608e3fa5b..b3b9e64ae2 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -243,7 +243,35 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind3 = 5 THEN stanumbers3
             WHEN stakind4 = 5 THEN stanumbers4
             WHEN stakind5 = 5 THEN stanumbers5
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stavalues1
+            WHEN stakind2 = 6 THEN stavalues2
+            WHEN stakind3 = 6 THEN stavalues3
+            WHEN stakind4 = 6 THEN stavalues4
+            WHEN stakind5 = 6 THEN stavalues5
+        END AS range_length_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stanumbers1[1]
+            WHEN stakind2 = 6 THEN stanumbers2[1]
+            WHEN stakind3 = 6 THEN stanumbers3[1]
+            WHEN stakind4 = 6 THEN stanumbers4[1]
+            WHEN stakind5 = 6 THEN stanumbers5[1]
+        END AS range_empty_frac,
+        CASE
+            WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1)
+            WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2)
+            WHEN stakind3 = 7 THEN ranges_lower_bounds(stavalues3)
+            WHEN stakind4 = 7 THEN ranges_lower_bounds(stavalues4)
+            WHEN stakind5 = 7 THEN ranges_lower_bounds(stavalues5)
+            END AS range_lower_histogram,
+        CASE
+            WHEN stakind1 = 7 THEN ranges_upper_bounds(stavalues1)
+            WHEN stakind2 = 7 THEN ranges_upper_bounds(stavalues2)
+            WHEN stakind3 = 7 THEN ranges_upper_bounds(stavalues3)
+            WHEN stakind4 = 7 THEN ranges_upper_bounds(stavalues4)
+            WHEN stakind5 = 7 THEN ranges_upper_bounds(stavalues5)
+            END AS range_upper_histogram
     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
diff --git a/src/backend/utils/adt/rangetypes_typanalyze.c b/src/backend/utils/adt/rangetypes_typanalyze.c
index 86810a1a6e..a0097282fc 100644
--- a/src/backend/utils/adt/rangetypes_typanalyze.c
+++ b/src/backend/utils/adt/rangetypes_typanalyze.c
@@ -26,6 +26,8 @@
 
 #include "catalog/pg_operator.h"
 #include "commands/vacuum.h"
+#include "utils/array.h"
+#include "utils/arrayaccess.h"
 #include "utils/float.h"
 #include "utils/fmgrprotos.h"
 #include "utils/lsyscache.h"
@@ -427,3 +429,119 @@ compute_range_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc,
 	 * hashtable should also go away, as it used a child memory context.
 	 */
 }
+
+Datum
+ranges_lower_bounds(PG_FUNCTION_ARGS)
+{
+	AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0);
+	int			ndims = AARR_NDIM(array);
+	int		   *dims = AARR_DIMS(array);
+	Oid			element_type = AARR_ELEMTYPE(array);
+	int			i;
+	array_iter	iter;
+	int			nelems;
+	Datum	   *elems;
+	TypeCacheEntry *typentry;
+	TypeCacheEntry *typentry_element;
+
+	/* Get information about range type; note column might be a domain */
+	typentry = range_get_typcache(fcinfo, getBaseType(element_type));
+
+	if (typentry->typtype != TYPTYPE_RANGE)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("expected array of ranges")));
+
+	ndims = AARR_NDIM(array);
+	dims = AARR_DIMS(array);
+	nelems = ArrayGetNItems(ndims, dims);
+
+	elems = (Datum *) palloc(nelems * sizeof(Datum));
+
+	array_iter_setup(&iter, array);
+
+	for (i = 0; i < nelems; i++)
+	{
+		Datum		itemvalue;
+		bool		isnull;
+		RangeBound	lower;
+		RangeBound	upper;
+		bool		empty;
+
+		/* Get source element, checking for NULL */
+		itemvalue = array_iter_next(&iter, &isnull, i,
+									typentry->typlen, typentry->typbyval,
+									typentry->typalign);
+
+		Assert(!isnull);
+
+		range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty);
+		elems[i] = lower.val;
+	}
+
+	typentry_element = typentry->rngelemtype;
+
+	PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems,
+						  typentry_element->type_id,
+						  typentry_element->typlen,
+						  typentry_element->typbyval,
+						  typentry_element->typalign));
+}
+
+Datum
+ranges_upper_bounds(PG_FUNCTION_ARGS)
+{
+	AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0);
+	int			ndims = AARR_NDIM(array);
+	int		   *dims = AARR_DIMS(array);
+	Oid			element_type = AARR_ELEMTYPE(array);
+	int			i;
+	array_iter	iter;
+	int			nelems;
+	Datum	   *elems;
+	TypeCacheEntry *typentry;
+	TypeCacheEntry *typentry_element;
+
+	/* Get information about range type; note column might be a domain */
+	typentry = range_get_typcache(fcinfo, getBaseType(element_type));
+
+	if (typentry->typtype != TYPTYPE_RANGE)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("expected array of ranges")));
+
+	ndims = AARR_NDIM(array);
+	dims = AARR_DIMS(array);
+	nelems = ArrayGetNItems(ndims, dims);
+
+	elems = (Datum *) palloc(nelems * sizeof(Datum));
+
+	array_iter_setup(&iter, array);
+
+	for (i = 0; i < nelems; i++)
+	{
+		Datum		itemvalue;
+		bool		isnull;
+		RangeBound	lower;
+		RangeBound	upper;
+		bool		empty;
+
+		/* Get source element, checking for NULL */
+		itemvalue = array_iter_next(&iter, &isnull, i,
+									typentry->typlen, typentry->typbyval,
+									typentry->typalign);
+
+		Assert(!isnull);
+
+		range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty);
+		elems[i] = upper.val;
+	}
+
+	typentry_element = typentry->rngelemtype;
+
+	PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems,
+						  typentry_element->type_id,
+						  typentry_element->typlen,
+						  typentry_element->typbyval,
+						  typentry_element->typalign));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 86eb8e8c58..161557e4cb 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11891,4 +11891,14 @@
   prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary',
   prosrc => 'brin_minmax_multi_summary_send' },
 
+{ oid => '9693', descr => 'lower bounds of ranges',
+  proname => 'ranges_lower_bounds', provolatile => 's',
+  prorettype => 'anyarray', proargtypes => 'anyarray',
+  prosrc => 'ranges_lower_bounds' },
+
+{ oid => '9694', descr => 'upper bounds of ranges',
+  proname => 'ranges_upper_bounds', provolatile => 's',
+  prorettype => 'anyarray', proargtypes => 'anyarray',
+  prosrc => 'ranges_upper_bounds' },
+
 ]
diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h
index 8770c5b4c6..10401dece0 100644
--- a/src/include/catalog/pg_statistic.h
+++ b/src/include/catalog/pg_statistic.h
@@ -152,6 +152,9 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum));
  * data "kind" will appear in any particular slot.  Instead, search the
  * stakind fields to see if the desired data is available.  (The standard
  * function get_attstatsslot() may be used for this.)
+ *
+ * Note: The pg_stats view needs to be modified whenever a new slot kind is
+ * added to core.
  */
 
 /*
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index e7a2f5856a..26087dfdf5 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2436,7 +2436,39 @@ pg_stats| SELECT n.nspname AS schemaname,
             WHEN (s.stakind4 = 5) THEN s.stanumbers4
             WHEN (s.stakind5 = 5) THEN s.stanumbers5
             ELSE NULL::real[]
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stavalues1
+            WHEN (s.stakind2 = 6) THEN s.stavalues2
+            WHEN (s.stakind3 = 6) THEN s.stavalues3
+            WHEN (s.stakind4 = 6) THEN s.stavalues4
+            WHEN (s.stakind5 = 6) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_length_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stanumbers1[1]
+            WHEN (s.stakind2 = 6) THEN s.stanumbers2[1]
+            WHEN (s.stakind3 = 6) THEN s.stanumbers3[1]
+            WHEN (s.stakind4 = 6) THEN s.stanumbers4[1]
+            WHEN (s.stakind5 = 6) THEN s.stanumbers5[1]
+            ELSE NULL::real
+        END AS range_empty_frac,
+        CASE
+            WHEN (s.stakind1 = 7) THEN ranges_lower_bounds(s.stavalues1)
+            WHEN (s.stakind2 = 7) THEN ranges_lower_bounds(s.stavalues2)
+            WHEN (s.stakind3 = 7) THEN ranges_lower_bounds(s.stavalues3)
+            WHEN (s.stakind4 = 7) THEN ranges_lower_bounds(s.stavalues4)
+            WHEN (s.stakind5 = 7) THEN ranges_lower_bounds(s.stavalues5)
+            ELSE NULL::anyarray
+        END AS range_lower_histogram,
+        CASE
+            WHEN (s.stakind1 = 7) THEN ranges_upper_bounds(s.stavalues1)
+            WHEN (s.stakind2 = 7) THEN ranges_upper_bounds(s.stavalues2)
+            WHEN (s.stakind3 = 7) THEN ranges_upper_bounds(s.stavalues3)
+            WHEN (s.stakind4 = 7) THEN ranges_upper_bounds(s.stavalues4)
+            WHEN (s.stakind5 = 7) THEN ranges_upper_bounds(s.stavalues5)
+            ELSE NULL::anyarray
+        END AS range_upper_histogram
    FROM (((pg_statistic s
      JOIN pg_class c ON ((c.oid = s.starelid)))
      JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
-- 
2.39.0

From 9e95cf8025f37b819a3396fa24e0ccac92f18ce0 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas@2ndquadrant.com>
Date: Sun, 22 Jan 2023 17:28:30 +0100
Subject: [PATCH 2/2] tweaks

---
 doc/src/sgml/catalogs.sgml                    | 40 -------------------
 doc/src/sgml/func.sgml                        | 36 +++++++++++++++++
 doc/src/sgml/system-views.sgml                | 40 +++++++++++++++++++
 src/backend/catalog/system_views.sql          | 34 ++++++++--------
 src/backend/utils/adt/rangetypes_typanalyze.c |  6 +++
 src/include/catalog/pg_proc.dat               |  4 +-
 src/include/catalog/pg_statistic.h            |  3 --
 src/test/regress/expected/rules.out           | 36 ++++++++---------
 8 files changed, 119 insertions(+), 80 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index e0851c52b4..c1e4048054 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9634,46 +9634,6 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
        User mapping specific options, as <quote>keyword=value</quote> strings
       </para></entry>
      </row>
-
-     <row>
-      <entry role="catalog_table_entry"><para role="column_definition">
-       <structfield>range_length_histogram</structfield> <type>anyarray</type>
-      </para>
-      <para>
-       A histogram of the lengths of non-empty and non-null range values of a
-       range type column. (Null for non-range types.)
-      </para></entry>
-     </row>
-
-     <row>
-      <entry role="catalog_table_entry"><para role="column_definition">
-       <structfield>range_empty_frac</structfield> <type>float4</type>
-      </para>
-      <para>
-       Fraction of column entries whose values are empty ranges.
-       (Null for non-range types.)
-      </para></entry>
-     </row>
-
-     <row>
-      <entry role="catalog_table_entry"><para role="column_definition">
-       <structfield>range_lower_histogram</structfield> <type>anyarray</type>
-      </para>
-      <para>
-       A histogram of lower bounds of non-empty and non-null range values.
-       (Null for non-range types.)
-      </para></entry>
-     </row>
-
-     <row>
-      <entry role="catalog_table_entry"><para role="column_definition">
-       <structfield>range_upper_histogram</structfield> <type>anyarray</type>
-      </para>
-      <para>
-       A histogram of upper bounds of non-empty and non-null range values.
-       (Null for non-range types.)
-      </para></entry>
-     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index b8dac9ef46..f8eb488b2c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19357,6 +19357,24 @@ SELECT NULLIF(value, '(none)') ...
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>ranges_lower</primary>
+        </indexterm>
+        <function>ranges_lower</function> ( <type>anyarray</type> )
+        <returnvalue>anyarray</returnvalue>
+       </para>
+       <para>
+        Extracts lower bounds of ranges in the array (<literal>NULL</literal> if
+        the range is empty or the lower bound is infinite).
+       </para>
+       <para>
+        <literal>lower(numrange(1.1,2.2))</literal>
+        <returnvalue>1.1</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -19375,6 +19393,24 @@ SELECT NULLIF(value, '(none)') ...
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>ranges_upper</primary>
+        </indexterm>
+        <function>ranges_upper</function> ( <type>anyarray</type> )
+        <returnvalue>anyarray</returnvalue>
+       </para>
+       <para>
+        Extracts upper bounds of ranges (<literal>NULL</literal> if the
+        range is empty or the upper bound is infinite).
+       </para>
+       <para>
+        <literal>upper(numrange(1.1,2.2))</literal>
+        <returnvalue>2.2</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 7c8fc3f654..a48d775467 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -3783,6 +3783,46 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        non-null elements.  (Null for scalar types.)
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_length_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of the lengths of non-empty and non-null range values of a
+       range type column. (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_empty_frac</structfield> <type>float4</type>
+      </para>
+      <para>
+       Fraction of column entries whose values are empty ranges.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_lower_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of lower bounds of non-empty and non-null range values.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_upper_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of upper bounds of non-empty and non-null range values.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b3b9e64ae2..00e91e5d93 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -244,13 +244,6 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind4 = 5 THEN stanumbers4
             WHEN stakind5 = 5 THEN stanumbers5
         END AS elem_count_histogram,
-        CASE
-            WHEN stakind1 = 6 THEN stavalues1
-            WHEN stakind2 = 6 THEN stavalues2
-            WHEN stakind3 = 6 THEN stavalues3
-            WHEN stakind4 = 6 THEN stavalues4
-            WHEN stakind5 = 6 THEN stavalues5
-        END AS range_length_histogram,
         CASE
             WHEN stakind1 = 6 THEN stanumbers1[1]
             WHEN stakind2 = 6 THEN stanumbers2[1]
@@ -259,18 +252,25 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind5 = 6 THEN stanumbers5[1]
         END AS range_empty_frac,
         CASE
-            WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1)
-            WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2)
-            WHEN stakind3 = 7 THEN ranges_lower_bounds(stavalues3)
-            WHEN stakind4 = 7 THEN ranges_lower_bounds(stavalues4)
-            WHEN stakind5 = 7 THEN ranges_lower_bounds(stavalues5)
+            WHEN stakind1 = 6 THEN stavalues1
+            WHEN stakind2 = 6 THEN stavalues2
+            WHEN stakind3 = 6 THEN stavalues3
+            WHEN stakind4 = 6 THEN stavalues4
+            WHEN stakind5 = 6 THEN stavalues5
+        END AS range_length_histogram,
+        CASE
+            WHEN stakind1 = 7 THEN ranges_lower(stavalues1)
+            WHEN stakind2 = 7 THEN ranges_lower(stavalues2)
+            WHEN stakind3 = 7 THEN ranges_lower(stavalues3)
+            WHEN stakind4 = 7 THEN ranges_lower(stavalues4)
+            WHEN stakind5 = 7 THEN ranges_lower(stavalues5)
             END AS range_lower_histogram,
         CASE
-            WHEN stakind1 = 7 THEN ranges_upper_bounds(stavalues1)
-            WHEN stakind2 = 7 THEN ranges_upper_bounds(stavalues2)
-            WHEN stakind3 = 7 THEN ranges_upper_bounds(stavalues3)
-            WHEN stakind4 = 7 THEN ranges_upper_bounds(stavalues4)
-            WHEN stakind5 = 7 THEN ranges_upper_bounds(stavalues5)
+            WHEN stakind1 = 7 THEN ranges_upper(stavalues1)
+            WHEN stakind2 = 7 THEN ranges_upper(stavalues2)
+            WHEN stakind3 = 7 THEN ranges_upper(stavalues3)
+            WHEN stakind4 = 7 THEN ranges_upper(stavalues4)
+            WHEN stakind5 = 7 THEN ranges_upper(stavalues5)
             END AS range_upper_histogram
     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
diff --git a/src/backend/utils/adt/rangetypes_typanalyze.c b/src/backend/utils/adt/rangetypes_typanalyze.c
index a0097282fc..982f23ae22 100644
--- a/src/backend/utils/adt/rangetypes_typanalyze.c
+++ b/src/backend/utils/adt/rangetypes_typanalyze.c
@@ -430,6 +430,9 @@ compute_range_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc,
 	 */
 }
 
+/*
+ * ranges_lower_bounds() -- return array of lower bounds for ranges
+ */
 Datum
 ranges_lower_bounds(PG_FUNCTION_ARGS)
 {
@@ -488,6 +491,9 @@ ranges_lower_bounds(PG_FUNCTION_ARGS)
 						  typentry_element->typalign));
 }
 
+/*
+ * ranges_upper_bounds() -- return array of upper bounds for ranges
+ */
 Datum
 ranges_upper_bounds(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 161557e4cb..c53e9fc669 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11892,12 +11892,12 @@
   prosrc => 'brin_minmax_multi_summary_send' },
 
 { oid => '9693', descr => 'lower bounds of ranges',
-  proname => 'ranges_lower_bounds', provolatile => 's',
+  proname => 'ranges_lower', provolatile => 's',
   prorettype => 'anyarray', proargtypes => 'anyarray',
   prosrc => 'ranges_lower_bounds' },
 
 { oid => '9694', descr => 'upper bounds of ranges',
-  proname => 'ranges_upper_bounds', provolatile => 's',
+  proname => 'ranges_upper', provolatile => 's',
   prorettype => 'anyarray', proargtypes => 'anyarray',
   prosrc => 'ranges_upper_bounds' },
 
diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h
index 10401dece0..8770c5b4c6 100644
--- a/src/include/catalog/pg_statistic.h
+++ b/src/include/catalog/pg_statistic.h
@@ -152,9 +152,6 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum));
  * data "kind" will appear in any particular slot.  Instead, search the
  * stakind fields to see if the desired data is available.  (The standard
  * function get_attstatsslot() may be used for this.)
- *
- * Note: The pg_stats view needs to be modified whenever a new slot kind is
- * added to core.
  */
 
 /*
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 26087dfdf5..ea4d721e98 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2437,14 +2437,6 @@ pg_stats| SELECT n.nspname AS schemaname,
             WHEN (s.stakind5 = 5) THEN s.stanumbers5
             ELSE NULL::real[]
         END AS elem_count_histogram,
-        CASE
-            WHEN (s.stakind1 = 6) THEN s.stavalues1
-            WHEN (s.stakind2 = 6) THEN s.stavalues2
-            WHEN (s.stakind3 = 6) THEN s.stavalues3
-            WHEN (s.stakind4 = 6) THEN s.stavalues4
-            WHEN (s.stakind5 = 6) THEN s.stavalues5
-            ELSE NULL::anyarray
-        END AS range_length_histogram,
         CASE
             WHEN (s.stakind1 = 6) THEN s.stanumbers1[1]
             WHEN (s.stakind2 = 6) THEN s.stanumbers2[1]
@@ -2454,19 +2446,27 @@ pg_stats| SELECT n.nspname AS schemaname,
             ELSE NULL::real
         END AS range_empty_frac,
         CASE
-            WHEN (s.stakind1 = 7) THEN ranges_lower_bounds(s.stavalues1)
-            WHEN (s.stakind2 = 7) THEN ranges_lower_bounds(s.stavalues2)
-            WHEN (s.stakind3 = 7) THEN ranges_lower_bounds(s.stavalues3)
-            WHEN (s.stakind4 = 7) THEN ranges_lower_bounds(s.stavalues4)
-            WHEN (s.stakind5 = 7) THEN ranges_lower_bounds(s.stavalues5)
+            WHEN (s.stakind1 = 6) THEN s.stavalues1
+            WHEN (s.stakind2 = 6) THEN s.stavalues2
+            WHEN (s.stakind3 = 6) THEN s.stavalues3
+            WHEN (s.stakind4 = 6) THEN s.stavalues4
+            WHEN (s.stakind5 = 6) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_length_histogram,
+        CASE
+            WHEN (s.stakind1 = 7) THEN ranges_lower(s.stavalues1)
+            WHEN (s.stakind2 = 7) THEN ranges_lower(s.stavalues2)
+            WHEN (s.stakind3 = 7) THEN ranges_lower(s.stavalues3)
+            WHEN (s.stakind4 = 7) THEN ranges_lower(s.stavalues4)
+            WHEN (s.stakind5 = 7) THEN ranges_lower(s.stavalues5)
             ELSE NULL::anyarray
         END AS range_lower_histogram,
         CASE
-            WHEN (s.stakind1 = 7) THEN ranges_upper_bounds(s.stavalues1)
-            WHEN (s.stakind2 = 7) THEN ranges_upper_bounds(s.stavalues2)
-            WHEN (s.stakind3 = 7) THEN ranges_upper_bounds(s.stavalues3)
-            WHEN (s.stakind4 = 7) THEN ranges_upper_bounds(s.stavalues4)
-            WHEN (s.stakind5 = 7) THEN ranges_upper_bounds(s.stavalues5)
+            WHEN (s.stakind1 = 7) THEN ranges_upper(s.stavalues1)
+            WHEN (s.stakind2 = 7) THEN ranges_upper(s.stavalues2)
+            WHEN (s.stakind3 = 7) THEN ranges_upper(s.stavalues3)
+            WHEN (s.stakind4 = 7) THEN ranges_upper(s.stavalues4)
+            WHEN (s.stakind5 = 7) THEN ranges_upper(s.stavalues5)
             ELSE NULL::anyarray
         END AS range_upper_histogram
    FROM (((pg_statistic s
-- 
2.39.0

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0987eb805a..c4d86e1679 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19446,6 +19446,24 @@ SELECT NULLIF(value, '(none)') ...
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>ranges_lower</primary>
+        </indexterm>
+        <function>ranges_lower</function> ( <type>anyarray</type> )
+        <returnvalue>anyarray</returnvalue>
+       </para>
+       <para>
+        Extracts lower bounds of ranges in the array (<literal>NULL</literal> if
+        the range is empty or the lower bound is infinite).
+       </para>
+       <para>
+        <literal>lower(ARRAY[numrange(1.1,2.2),numrange(3.3,4.4)])</literal>
+        <returnvalue>{1.1,3.3}</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -19464,6 +19482,24 @@ SELECT NULLIF(value, '(none)') ...
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>ranges_upper</primary>
+        </indexterm>
+        <function>ranges_upper</function> ( <type>anyarray</type> )
+        <returnvalue>anyarray</returnvalue>
+       </para>
+       <para>
+        Extracts upper bounds of ranges (<literal>NULL</literal> if the
+        range is empty or the upper bound is infinite).
+       </para>
+       <para>
+        <literal>upper(ARRAY[numrange(1.1,2.2),numrange(3.3,4.4)])</literal>
+        <returnvalue>{2.2,4.4}</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 7c8fc3f654..a48d775467 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -3783,6 +3783,46 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        non-null elements.  (Null for scalar types.)
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_length_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of the lengths of non-empty and non-null range values of a
+       range type column. (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_empty_frac</structfield> <type>float4</type>
+      </para>
+      <para>
+       Fraction of column entries whose values are empty ranges.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_lower_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of lower bounds of non-empty and non-null range values.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_upper_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of upper bounds of non-empty and non-null range values.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 8ea159dbde..882344a2d7 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -243,7 +243,35 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind3 = 5 THEN stanumbers3
             WHEN stakind4 = 5 THEN stanumbers4
             WHEN stakind5 = 5 THEN stanumbers5
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stanumbers1[1]
+            WHEN stakind2 = 6 THEN stanumbers2[1]
+            WHEN stakind3 = 6 THEN stanumbers3[1]
+            WHEN stakind4 = 6 THEN stanumbers4[1]
+            WHEN stakind5 = 6 THEN stanumbers5[1]
+        END AS range_empty_frac,
+        CASE
+            WHEN stakind1 = 6 THEN stavalues1
+            WHEN stakind2 = 6 THEN stavalues2
+            WHEN stakind3 = 6 THEN stavalues3
+            WHEN stakind4 = 6 THEN stavalues4
+            WHEN stakind5 = 6 THEN stavalues5
+        END AS range_length_histogram,
+        CASE
+            WHEN stakind1 = 7 THEN ranges_lower(stavalues1)
+            WHEN stakind2 = 7 THEN ranges_lower(stavalues2)
+            WHEN stakind3 = 7 THEN ranges_lower(stavalues3)
+            WHEN stakind4 = 7 THEN ranges_lower(stavalues4)
+            WHEN stakind5 = 7 THEN ranges_lower(stavalues5)
+            END AS range_lower_histogram,
+        CASE
+            WHEN stakind1 = 7 THEN ranges_upper(stavalues1)
+            WHEN stakind2 = 7 THEN ranges_upper(stavalues2)
+            WHEN stakind3 = 7 THEN ranges_upper(stavalues3)
+            WHEN stakind4 = 7 THEN ranges_upper(stavalues4)
+            WHEN stakind5 = 7 THEN ranges_upper(stavalues5)
+            END AS range_upper_histogram
     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
diff --git a/src/backend/utils/adt/rangetypes_typanalyze.c b/src/backend/utils/adt/rangetypes_typanalyze.c
index 86810a1a6e..a288ee3ebc 100644
--- a/src/backend/utils/adt/rangetypes_typanalyze.c
+++ b/src/backend/utils/adt/rangetypes_typanalyze.c
@@ -26,6 +26,8 @@
 
 #include "catalog/pg_operator.h"
 #include "commands/vacuum.h"
+#include "utils/array.h"
+#include "utils/arrayaccess.h"
 #include "utils/float.h"
 #include "utils/fmgrprotos.h"
 #include "utils/lsyscache.h"
@@ -38,6 +40,7 @@ static int	range_bound_qsort_cmp(const void *a1, const void *a2, void *arg);
 static void compute_range_stats(VacAttrStats *stats,
 								AnalyzeAttrFetchFunc fetchfunc, int samplerows,
 								double totalrows);
+static Datum ranges_bounds_common(TypeCacheEntry *typentry, AnyArrayType *array, bool need_lower);
 
 /*
  * range_typanalyze -- typanalyze function for range columns
@@ -427,3 +430,107 @@ compute_range_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc,
 	 * hashtable should also go away, as it used a child memory context.
 	 */
 }
+
+/*
+ * ranges_bounds_common() -- for ranges_upper_bounds() and ranges_lower_bounds()
+ */
+Datum
+ranges_bounds_common(TypeCacheEntry *typentry, AnyArrayType *array, bool need_lower)
+{
+	int			ndims = AARR_NDIM(array);
+	int		   *dims = AARR_DIMS(array);
+	int		   *lbs = AARR_LBOUND(array);
+	int			i;
+	array_iter	iter;
+	int			nelems;
+	Datum	   *elems;
+	bool	   *nulls;
+	TypeCacheEntry *typentry_element;
+
+	if (typentry->typtype != TYPTYPE_RANGE)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("expected array of ranges")));
+
+	ndims = AARR_NDIM(array);
+	dims = AARR_DIMS(array);
+	nelems = ArrayGetNItems(ndims, dims);
+
+	elems = (Datum *) palloc(nelems * sizeof(Datum));
+	nulls = (bool *) palloc(nelems * sizeof(bool));
+
+	array_iter_setup(&iter, array);
+
+	for (i = 0; i < nelems; i++)
+	{
+		Datum		itemvalue;
+		bool		isnull;
+		RangeBound	lower;
+		RangeBound	upper;
+		RangeBound *bound;
+		bool		empty;
+
+		/* Get source element, checking for NULL */
+		itemvalue = array_iter_next(&iter, &isnull, i,
+									typentry->typlen, typentry->typbyval,
+									typentry->typalign);
+
+		Assert(!isnull);
+
+		range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty);
+		bound = need_lower ? &lower : &upper;
+	    /* NULL if there's no finite bound - for consistency with range_lower/range_upper */
+		if (empty || bound->infinite)
+		{
+			nulls[i] = true;
+			elems[i] = (Datum) 0;
+		}
+		else
+		{
+			nulls[i] = false;
+			elems[i] = bound->val;
+		}
+	}
+
+	typentry_element = typentry->rngelemtype;
+
+	PG_RETURN_ARRAYTYPE_P(construct_md_array(elems, nulls,
+						  ndims, dims, lbs,
+						  typentry_element->type_id,
+						  typentry_element->typlen,
+						  typentry_element->typbyval,
+						  typentry_element->typalign));
+}
+
+/*
+ * ranges_lower_bounds() -- return array of lower bounds for ranges
+ */
+Datum
+ranges_lower_bounds(PG_FUNCTION_ARGS)
+{
+	AnyArrayType   *array = PG_GETARG_ANY_ARRAY_P(0);
+	Oid				element_type = AARR_ELEMTYPE(array);
+	TypeCacheEntry *typentry;
+
+	/* Get information about range type; note column might be a domain */
+	typentry = range_get_typcache(fcinfo, getBaseType(element_type));
+
+	return ranges_bounds_common(typentry, array, true);
+}
+
+/*
+ * ranges_upper_bounds() -- return array of upper bounds for ranges
+ */
+Datum
+ranges_upper_bounds(PG_FUNCTION_ARGS)
+{
+	AnyArrayType   *array = PG_GETARG_ANY_ARRAY_P(0);
+	Oid				element_type = AARR_ELEMTYPE(array);
+	TypeCacheEntry *typentry;
+
+	/* Get information about range type; note column might be a domain */
+	typentry = range_get_typcache(fcinfo, getBaseType(element_type));
+
+	return ranges_bounds_common(typentry, array, false);
+}
+
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7c358cff16..b6bfd32111 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11977,4 +11977,14 @@
   proname => 'any_value_transfn', prorettype => 'anyelement',
   proargtypes => 'anyelement anyelement', prosrc => 'any_value_transfn' },
 
+{ oid => '9693', descr => 'lower bounds of ranges',
+  proname => 'ranges_lower', provolatile => 's',
+  prorettype => 'anyarray', proargtypes => 'anyarray',
+  prosrc => 'ranges_lower_bounds' },
+
+{ oid => '9694', descr => 'upper bounds of ranges',
+  proname => 'ranges_upper', provolatile => 's',
+  prorettype => 'anyarray', proargtypes => 'anyarray',
+  prosrc => 'ranges_upper_bounds' },
+
 ]
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index ee02ff0163..6d7461179f 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -597,6 +597,22 @@ select range_intersect_agg(nr) from numrange_test where nr @> 4.0;
  [3,5)
 (1 row)
 
+select ranges_lower(ARRAY[1,2,3]); -- should fail
+ERROR:  type 23 is not a range type
+select ranges_lower(array_agg(nr order by nr)), ranges_upper(array_agg(nr order by nr)) from numrange_test;
+        ranges_lower        |        ranges_upper        
+----------------------------+----------------------------
+ {NULL,NULL,NULL,1.1,1.7,3} | {NULL,5,NULL,2.2,1.7,NULL}
+(1 row)
+
+select ranges_lower(a), ranges_upper(a) from (
+  select ARRAY[ array_agg(nr order by nr), array_agg(nr order by nr desc) ] from numrange_test
+) t(a);
+                      ranges_lower                       |                      ranges_upper                       
+---------------------------------------------------------+---------------------------------------------------------
+ {{NULL,NULL,NULL,1.1,1.7,3},{3,1.7,1.1,NULL,NULL,NULL}} | {{NULL,5,NULL,2.2,1.7,NULL},{NULL,1.7,2.2,NULL,5,NULL}}
+(1 row)
+
 analyze numrange_test;
 create table numrange_test2(nr numrange);
 create index numrange_test2_hash_idx on numrange_test2 using hash (nr);
@@ -751,6 +767,12 @@ select * from textrange_test where tr < 'empty';
 ----
 (0 rows)
 
+select ranges_lower(array_agg(tr order by tr)), ranges_upper(array_agg(tr order by tr)) from textrange_test;
+      ranges_lower      |      ranges_upper      
+------------------------+------------------------
+ {NULL,NULL,NULL,a,b,d} | {NULL,q,NULL,NULL,g,d}
+(1 row)
+
 -- test canonical form for int4range
 select int4range(1, 10, '[]');
  int4range 
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 996d22b7dd..41f0310922 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2454,7 +2454,39 @@ pg_stats| SELECT n.nspname AS schemaname,
             WHEN (s.stakind4 = 5) THEN s.stanumbers4
             WHEN (s.stakind5 = 5) THEN s.stanumbers5
             ELSE NULL::real[]
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stanumbers1[1]
+            WHEN (s.stakind2 = 6) THEN s.stanumbers2[1]
+            WHEN (s.stakind3 = 6) THEN s.stanumbers3[1]
+            WHEN (s.stakind4 = 6) THEN s.stanumbers4[1]
+            WHEN (s.stakind5 = 6) THEN s.stanumbers5[1]
+            ELSE NULL::real
+        END AS range_empty_frac,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stavalues1
+            WHEN (s.stakind2 = 6) THEN s.stavalues2
+            WHEN (s.stakind3 = 6) THEN s.stavalues3
+            WHEN (s.stakind4 = 6) THEN s.stavalues4
+            WHEN (s.stakind5 = 6) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_length_histogram,
+        CASE
+            WHEN (s.stakind1 = 7) THEN ranges_lower(s.stavalues1)
+            WHEN (s.stakind2 = 7) THEN ranges_lower(s.stavalues2)
+            WHEN (s.stakind3 = 7) THEN ranges_lower(s.stavalues3)
+            WHEN (s.stakind4 = 7) THEN ranges_lower(s.stavalues4)
+            WHEN (s.stakind5 = 7) THEN ranges_lower(s.stavalues5)
+            ELSE NULL::anyarray
+        END AS range_lower_histogram,
+        CASE
+            WHEN (s.stakind1 = 7) THEN ranges_upper(s.stavalues1)
+            WHEN (s.stakind2 = 7) THEN ranges_upper(s.stavalues2)
+            WHEN (s.stakind3 = 7) THEN ranges_upper(s.stavalues3)
+            WHEN (s.stakind4 = 7) THEN ranges_upper(s.stavalues4)
+            WHEN (s.stakind5 = 7) THEN ranges_upper(s.stavalues5)
+            ELSE NULL::anyarray
+        END AS range_upper_histogram
    FROM (((pg_statistic s
      JOIN pg_class c ON ((c.oid = s.starelid)))
      JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index c23be928c3..8948ad3e58 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -133,6 +133,12 @@ select range_intersect_agg(nr) from numrange_test;
 select range_intersect_agg(nr) from numrange_test where false;
 select range_intersect_agg(nr) from numrange_test where nr @> 4.0;
 
+select ranges_lower(ARRAY[1,2,3]); -- should fail
+select ranges_lower(array_agg(nr order by nr)), ranges_upper(array_agg(nr order by nr)) from numrange_test;
+select ranges_lower(a), ranges_upper(a) from (
+  select ARRAY[ array_agg(nr order by nr), array_agg(nr order by nr desc) ] from numrange_test
+) t(a);
+
 analyze numrange_test;
 
 create table numrange_test2(nr numrange);
@@ -195,6 +201,7 @@ select * from textrange_test where tr = '("b","g")';
 select * from textrange_test where tr = '["b","g")';
 select * from textrange_test where tr < 'empty';
 
+select ranges_lower(array_agg(tr order by tr)), ranges_upper(array_agg(tr order by tr)) from textrange_test;
 
 -- test canonical form for int4range
 select int4range(1, 10, '[]');

From 87424880f1a970448979681684e6916f33567eeb Mon Sep 17 00:00:00 2001
From: Greg Stark <stark@mit.edu>
Date: Mon, 3 Apr 2023 17:04:11 -0400
Subject: [PATCH] pg_stats and range statistics

---
 doc/src/sgml/func.sgml                        |  36 ++++++
 doc/src/sgml/system-views.sgml                |  40 +++++++
 src/backend/catalog/system_views.sql          |  30 ++++-
 src/backend/utils/adt/rangetypes_typanalyze.c | 107 ++++++++++++++++++
 src/include/catalog/pg_proc.dat               |  10 ++
 src/test/regress/expected/rangetypes.out      |  22 ++++
 src/test/regress/expected/rules.out           |  34 +++++-
 src/test/regress/sql/rangetypes.sql           |   7 ++
 8 files changed, 284 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 918a492234..548078a12e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19643,6 +19643,24 @@ SELECT NULLIF(value, '(none)') ...
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>ranges_lower</primary>
+        </indexterm>
+        <function>ranges_lower</function> ( <type>anyarray</type> )
+        <returnvalue>anyarray</returnvalue>
+       </para>
+       <para>
+        Extracts lower bounds of ranges in the array (<literal>NULL</literal> if
+        the range is empty or the lower bound is infinite).
+       </para>
+       <para>
+        <literal>lower(ARRAY[numrange(1.1,2.2),numrange(3.3,4.4)])</literal>
+        <returnvalue>{1.1,3.3}</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -19661,6 +19679,24 @@ SELECT NULLIF(value, '(none)') ...
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>ranges_upper</primary>
+        </indexterm>
+        <function>ranges_upper</function> ( <type>anyarray</type> )
+        <returnvalue>anyarray</returnvalue>
+       </para>
+       <para>
+        Extracts upper bounds of ranges (<literal>NULL</literal> if the
+        range is empty or the upper bound is infinite).
+       </para>
+       <para>
+        <literal>upper(ARRAY[numrange(1.1,2.2),numrange(3.3,4.4)])</literal>
+        <returnvalue>{2.2,4.4}</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index bb1a418450..d7760838ae 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -3784,6 +3784,46 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        non-null elements.  (Null for scalar types.)
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_length_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of the lengths of non-empty and non-null range values of a
+       range type column. (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_empty_frac</structfield> <type>float4</type>
+      </para>
+      <para>
+       Fraction of column entries whose values are empty ranges.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_lower_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of lower bounds of non-empty and non-null range values.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_upper_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of upper bounds of non-empty and non-null range values.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 574cbc2e44..3fb7ee448f 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -243,7 +243,35 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind3 = 5 THEN stanumbers3
             WHEN stakind4 = 5 THEN stanumbers4
             WHEN stakind5 = 5 THEN stanumbers5
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stanumbers1[1]
+            WHEN stakind2 = 6 THEN stanumbers2[1]
+            WHEN stakind3 = 6 THEN stanumbers3[1]
+            WHEN stakind4 = 6 THEN stanumbers4[1]
+            WHEN stakind5 = 6 THEN stanumbers5[1]
+        END AS range_empty_frac,
+        CASE
+            WHEN stakind1 = 6 THEN stavalues1
+            WHEN stakind2 = 6 THEN stavalues2
+            WHEN stakind3 = 6 THEN stavalues3
+            WHEN stakind4 = 6 THEN stavalues4
+            WHEN stakind5 = 6 THEN stavalues5
+        END AS range_length_histogram,
+        CASE
+            WHEN stakind1 = 7 THEN ranges_lower(stavalues1)
+            WHEN stakind2 = 7 THEN ranges_lower(stavalues2)
+            WHEN stakind3 = 7 THEN ranges_lower(stavalues3)
+            WHEN stakind4 = 7 THEN ranges_lower(stavalues4)
+            WHEN stakind5 = 7 THEN ranges_lower(stavalues5)
+            END AS range_lower_histogram,
+        CASE
+            WHEN stakind1 = 7 THEN ranges_upper(stavalues1)
+            WHEN stakind2 = 7 THEN ranges_upper(stavalues2)
+            WHEN stakind3 = 7 THEN ranges_upper(stavalues3)
+            WHEN stakind4 = 7 THEN ranges_upper(stavalues4)
+            WHEN stakind5 = 7 THEN ranges_upper(stavalues5)
+            END AS range_upper_histogram
     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
diff --git a/src/backend/utils/adt/rangetypes_typanalyze.c b/src/backend/utils/adt/rangetypes_typanalyze.c
index 86810a1a6e..a288ee3ebc 100644
--- a/src/backend/utils/adt/rangetypes_typanalyze.c
+++ b/src/backend/utils/adt/rangetypes_typanalyze.c
@@ -26,6 +26,8 @@
 
 #include "catalog/pg_operator.h"
 #include "commands/vacuum.h"
+#include "utils/array.h"
+#include "utils/arrayaccess.h"
 #include "utils/float.h"
 #include "utils/fmgrprotos.h"
 #include "utils/lsyscache.h"
@@ -38,6 +40,7 @@ static int	range_bound_qsort_cmp(const void *a1, const void *a2, void *arg);
 static void compute_range_stats(VacAttrStats *stats,
 								AnalyzeAttrFetchFunc fetchfunc, int samplerows,
 								double totalrows);
+static Datum ranges_bounds_common(TypeCacheEntry *typentry, AnyArrayType *array, bool need_lower);
 
 /*
  * range_typanalyze -- typanalyze function for range columns
@@ -427,3 +430,107 @@ compute_range_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc,
 	 * hashtable should also go away, as it used a child memory context.
 	 */
 }
+
+/*
+ * ranges_bounds_common() -- for ranges_upper_bounds() and ranges_lower_bounds()
+ */
+Datum
+ranges_bounds_common(TypeCacheEntry *typentry, AnyArrayType *array, bool need_lower)
+{
+	int			ndims = AARR_NDIM(array);
+	int		   *dims = AARR_DIMS(array);
+	int		   *lbs = AARR_LBOUND(array);
+	int			i;
+	array_iter	iter;
+	int			nelems;
+	Datum	   *elems;
+	bool	   *nulls;
+	TypeCacheEntry *typentry_element;
+
+	if (typentry->typtype != TYPTYPE_RANGE)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("expected array of ranges")));
+
+	ndims = AARR_NDIM(array);
+	dims = AARR_DIMS(array);
+	nelems = ArrayGetNItems(ndims, dims);
+
+	elems = (Datum *) palloc(nelems * sizeof(Datum));
+	nulls = (bool *) palloc(nelems * sizeof(bool));
+
+	array_iter_setup(&iter, array);
+
+	for (i = 0; i < nelems; i++)
+	{
+		Datum		itemvalue;
+		bool		isnull;
+		RangeBound	lower;
+		RangeBound	upper;
+		RangeBound *bound;
+		bool		empty;
+
+		/* Get source element, checking for NULL */
+		itemvalue = array_iter_next(&iter, &isnull, i,
+									typentry->typlen, typentry->typbyval,
+									typentry->typalign);
+
+		Assert(!isnull);
+
+		range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty);
+		bound = need_lower ? &lower : &upper;
+	    /* NULL if there's no finite bound - for consistency with range_lower/range_upper */
+		if (empty || bound->infinite)
+		{
+			nulls[i] = true;
+			elems[i] = (Datum) 0;
+		}
+		else
+		{
+			nulls[i] = false;
+			elems[i] = bound->val;
+		}
+	}
+
+	typentry_element = typentry->rngelemtype;
+
+	PG_RETURN_ARRAYTYPE_P(construct_md_array(elems, nulls,
+						  ndims, dims, lbs,
+						  typentry_element->type_id,
+						  typentry_element->typlen,
+						  typentry_element->typbyval,
+						  typentry_element->typalign));
+}
+
+/*
+ * ranges_lower_bounds() -- return array of lower bounds for ranges
+ */
+Datum
+ranges_lower_bounds(PG_FUNCTION_ARGS)
+{
+	AnyArrayType   *array = PG_GETARG_ANY_ARRAY_P(0);
+	Oid				element_type = AARR_ELEMTYPE(array);
+	TypeCacheEntry *typentry;
+
+	/* Get information about range type; note column might be a domain */
+	typentry = range_get_typcache(fcinfo, getBaseType(element_type));
+
+	return ranges_bounds_common(typentry, array, true);
+}
+
+/*
+ * ranges_upper_bounds() -- return array of upper bounds for ranges
+ */
+Datum
+ranges_upper_bounds(PG_FUNCTION_ARGS)
+{
+	AnyArrayType   *array = PG_GETARG_ANY_ARRAY_P(0);
+	Oid				element_type = AARR_ELEMTYPE(array);
+	TypeCacheEntry *typentry;
+
+	/* Get information about range type; note column might be a domain */
+	typentry = range_get_typcache(fcinfo, getBaseType(element_type));
+
+	return ranges_bounds_common(typentry, array, false);
+}
+
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f9f2642201..b986610f09 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12053,4 +12053,14 @@
   proname => 'any_value_transfn', prorettype => 'anyelement',
   proargtypes => 'anyelement anyelement', prosrc => 'any_value_transfn' },
 
+{ oid => '9693', descr => 'lower bounds of ranges',
+  proname => 'ranges_lower', provolatile => 's',
+  prorettype => 'anyarray', proargtypes => 'anyarray',
+  prosrc => 'ranges_lower_bounds' },
+
+{ oid => '9694', descr => 'upper bounds of ranges',
+  proname => 'ranges_upper', provolatile => 's',
+  prorettype => 'anyarray', proargtypes => 'anyarray',
+  prosrc => 'ranges_upper_bounds' },
+
 ]
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index ee02ff0163..6d7461179f 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -597,6 +597,22 @@ select range_intersect_agg(nr) from numrange_test where nr @> 4.0;
  [3,5)
 (1 row)
 
+select ranges_lower(ARRAY[1,2,3]); -- should fail
+ERROR:  type 23 is not a range type
+select ranges_lower(array_agg(nr order by nr)), ranges_upper(array_agg(nr order by nr)) from numrange_test;
+        ranges_lower        |        ranges_upper        
+----------------------------+----------------------------
+ {NULL,NULL,NULL,1.1,1.7,3} | {NULL,5,NULL,2.2,1.7,NULL}
+(1 row)
+
+select ranges_lower(a), ranges_upper(a) from (
+  select ARRAY[ array_agg(nr order by nr), array_agg(nr order by nr desc) ] from numrange_test
+) t(a);
+                      ranges_lower                       |                      ranges_upper                       
+---------------------------------------------------------+---------------------------------------------------------
+ {{NULL,NULL,NULL,1.1,1.7,3},{3,1.7,1.1,NULL,NULL,NULL}} | {{NULL,5,NULL,2.2,1.7,NULL},{NULL,1.7,2.2,NULL,5,NULL}}
+(1 row)
+
 analyze numrange_test;
 create table numrange_test2(nr numrange);
 create index numrange_test2_hash_idx on numrange_test2 using hash (nr);
@@ -751,6 +767,12 @@ select * from textrange_test where tr < 'empty';
 ----
 (0 rows)
 
+select ranges_lower(array_agg(tr order by tr)), ranges_upper(array_agg(tr order by tr)) from textrange_test;
+      ranges_lower      |      ranges_upper      
+------------------------+------------------------
+ {NULL,NULL,NULL,a,b,d} | {NULL,q,NULL,NULL,g,d}
+(1 row)
+
 -- test canonical form for int4range
 select int4range(1, 10, '[]');
  int4range 
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index ab1aebfde4..7c783ce47a 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2455,7 +2455,39 @@ pg_stats| SELECT n.nspname AS schemaname,
             WHEN (s.stakind4 = 5) THEN s.stanumbers4
             WHEN (s.stakind5 = 5) THEN s.stanumbers5
             ELSE NULL::real[]
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stanumbers1[1]
+            WHEN (s.stakind2 = 6) THEN s.stanumbers2[1]
+            WHEN (s.stakind3 = 6) THEN s.stanumbers3[1]
+            WHEN (s.stakind4 = 6) THEN s.stanumbers4[1]
+            WHEN (s.stakind5 = 6) THEN s.stanumbers5[1]
+            ELSE NULL::real
+        END AS range_empty_frac,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stavalues1
+            WHEN (s.stakind2 = 6) THEN s.stavalues2
+            WHEN (s.stakind3 = 6) THEN s.stavalues3
+            WHEN (s.stakind4 = 6) THEN s.stavalues4
+            WHEN (s.stakind5 = 6) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_length_histogram,
+        CASE
+            WHEN (s.stakind1 = 7) THEN ranges_lower(s.stavalues1)
+            WHEN (s.stakind2 = 7) THEN ranges_lower(s.stavalues2)
+            WHEN (s.stakind3 = 7) THEN ranges_lower(s.stavalues3)
+            WHEN (s.stakind4 = 7) THEN ranges_lower(s.stavalues4)
+            WHEN (s.stakind5 = 7) THEN ranges_lower(s.stavalues5)
+            ELSE NULL::anyarray
+        END AS range_lower_histogram,
+        CASE
+            WHEN (s.stakind1 = 7) THEN ranges_upper(s.stavalues1)
+            WHEN (s.stakind2 = 7) THEN ranges_upper(s.stavalues2)
+            WHEN (s.stakind3 = 7) THEN ranges_upper(s.stavalues3)
+            WHEN (s.stakind4 = 7) THEN ranges_upper(s.stavalues4)
+            WHEN (s.stakind5 = 7) THEN ranges_upper(s.stavalues5)
+            ELSE NULL::anyarray
+        END AS range_upper_histogram
    FROM (((pg_statistic s
      JOIN pg_class c ON ((c.oid = s.starelid)))
      JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index c23be928c3..8948ad3e58 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -133,6 +133,12 @@ select range_intersect_agg(nr) from numrange_test;
 select range_intersect_agg(nr) from numrange_test where false;
 select range_intersect_agg(nr) from numrange_test where nr @> 4.0;
 
+select ranges_lower(ARRAY[1,2,3]); -- should fail
+select ranges_lower(array_agg(nr order by nr)), ranges_upper(array_agg(nr order by nr)) from numrange_test;
+select ranges_lower(a), ranges_upper(a) from (
+  select ARRAY[ array_agg(nr order by nr), array_agg(nr order by nr desc) ] from numrange_test
+) t(a);
+
 analyze numrange_test;
 
 create table numrange_test2(nr numrange);
@@ -195,6 +201,7 @@ select * from textrange_test where tr = '("b","g")';
 select * from textrange_test where tr = '["b","g")';
 select * from textrange_test where tr < 'empty';
 
+select ranges_lower(array_agg(tr order by tr)), ranges_upper(array_agg(tr order by tr)) from textrange_test;
 
 -- test canonical form for int4range
 select int4range(1, 10, '[]');
-- 
2.40.0

From ae3461fd57564200b9fdc1216116bef512d3089c Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Sat, 25 Nov 2023 00:38:05 +0200
Subject: [PATCH 1/2] Add comment to pg_statistic catalog table

Make a reminder that pg_stats view needs to be modified whenever a new slot
kind is added.  To prevent situations like 918eee0c49 when pg_stats was
forgotten to be updated.

Discussion: https://postgr.es/m/flat/b67d8b57-9357-7e82-a2e7-f6ce6eaeec67@postgrespro.ru
Author: Egor Rogov, Soumyadeep Chakraborty
Reviewed-by: Tomas Vondra, Justin Pryzby, Jian He
---
 src/include/catalog/pg_statistic.h | 3 +++
 1 file changed, 3 insertions(+)

diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h
index 0808c5fc893..95878422752 100644
--- a/src/include/catalog/pg_statistic.h
+++ b/src/include/catalog/pg_statistic.h
@@ -152,6 +152,9 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum));
  * data "kind" will appear in any particular slot.  Instead, search the
  * stakind fields to see if the desired data is available.  (The standard
  * function get_attstatsslot() may be used for this.)
+ *
+ * Note: The pg_stats view needs to be modified whenever a new slot kind is
+ * added to core.
  */
 
 /*
-- 
2.39.3 (Apple Git-145)

From a92bd68d5f47137426b6007bf357ea95693eba2f Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Sat, 25 Nov 2023 00:38:37 +0200
Subject: [PATCH 2/2] Display length and bounds histograms in pg_stats

Values corresponding to STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and
STATISTIC_KIND_BOUNDS_HISTOGRAM were not exposed to pg_stats when these
slot kinds were introduced in 918eee0c49.

This commit adds the missing fields to pg_stats.

Catversion is bumped.

Discussion: https://postgr.es/m/flat/b67d8b57-9357-7e82-a2e7-f6ce6eaeec67@postgrespro.ru
Author: Egor Rogov, Soumyadeep Chakraborty
Reviewed-by: Tomas Vondra, Justin Pryzby, Jian He
---
 doc/src/sgml/system-views.sgml       | 35 ++++++++++++++++++++++++++++
 src/backend/catalog/system_views.sql | 23 +++++++++++++++++-
 src/test/regress/expected/rules.out  | 26 ++++++++++++++++++++-
 3 files changed, 82 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 7078491c4c0..73da3850742 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -3799,6 +3799,41 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        non-null elements.  (Null for scalar types.)
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_length_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of the lengths of non-empty and non-null range values of a
+       range type column. (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_empty_frac</structfield> <type>float4</type>
+      </para>
+      <para>
+       Fraction of column entries whose values are empty ranges.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_bounds_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of lower and upper bounds of non-empty and non-null range
+       values. (Null for non-range types.)
+      </para>
+      <para>
+       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.
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b65f6b52490..11d18ed9dd6 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -243,7 +243,28 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind3 = 5 THEN stanumbers3
             WHEN stakind4 = 5 THEN stanumbers4
             WHEN stakind5 = 5 THEN stanumbers5
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stavalues1
+            WHEN stakind2 = 6 THEN stavalues2
+            WHEN stakind3 = 6 THEN stavalues3
+            WHEN stakind4 = 6 THEN stavalues4
+            WHEN stakind5 = 6 THEN stavalues5
+        END AS range_length_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stanumbers1[1]
+            WHEN stakind2 = 6 THEN stanumbers2[1]
+            WHEN stakind3 = 6 THEN stanumbers3[1]
+            WHEN stakind4 = 6 THEN stanumbers4[1]
+            WHEN stakind5 = 6 THEN stanumbers5[1]
+        END AS range_empty_frac,
+        CASE
+            WHEN stakind1 = 7 THEN stavalues1
+            WHEN stakind2 = 7 THEN stavalues2
+            WHEN stakind3 = 7 THEN stavalues3
+            WHEN stakind4 = 7 THEN stavalues4
+            WHEN stakind5 = 7 THEN stavalues5
+            END AS range_bounds_histogram
     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 1442c43d9c5..05070393b99 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2466,7 +2466,31 @@ pg_stats| SELECT n.nspname AS schemaname,
             WHEN (s.stakind4 = 5) THEN s.stanumbers4
             WHEN (s.stakind5 = 5) THEN s.stanumbers5
             ELSE NULL::real[]
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stavalues1
+            WHEN (s.stakind2 = 6) THEN s.stavalues2
+            WHEN (s.stakind3 = 6) THEN s.stavalues3
+            WHEN (s.stakind4 = 6) THEN s.stavalues4
+            WHEN (s.stakind5 = 6) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_length_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stanumbers1[1]
+            WHEN (s.stakind2 = 6) THEN s.stanumbers2[1]
+            WHEN (s.stakind3 = 6) THEN s.stanumbers3[1]
+            WHEN (s.stakind4 = 6) THEN s.stanumbers4[1]
+            WHEN (s.stakind5 = 6) THEN s.stanumbers5[1]
+            ELSE NULL::real
+        END AS range_empty_frac,
+        CASE
+            WHEN (s.stakind1 = 7) THEN s.stavalues1
+            WHEN (s.stakind2 = 7) THEN s.stavalues2
+            WHEN (s.stakind3 = 7) THEN s.stavalues3
+            WHEN (s.stakind4 = 7) THEN s.stavalues4
+            WHEN (s.stakind5 = 7) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_bounds_histogram
    FROM (((pg_statistic s
      JOIN pg_class c ON ((c.oid = s.starelid)))
      JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
-- 
2.39.3 (Apple Git-145)

From 1887f2ed8cc16a00c60bf72533a8bb195c040faf Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Sat, 25 Nov 2023 00:38:05 +0200
Subject: [PATCH 1/2] Update comments for pg_statistic catalog table

Make a reminder that pg_stats view needs to be modified whenever a new slot
kind is added.  To prevent situations like 918eee0c49 when pg_stats was
forgotten to be updated.

Also, revise the comment that only non-null, non-empty rows are considered
for the range length histogram.

Discussion: https://postgr.es/m/flat/b67d8b57-9357-7e82-a2e7-f6ce6eaeec67@postgrespro.ru
Author: Egor Rogov, Soumyadeep Chakraborty
Reviewed-by: Tomas Vondra, Justin Pryzby, Jian He
---
 src/include/catalog/pg_statistic.h | 6 +++++-
 1 file changed, 5 insertions(+), 1 deletion(-)

diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h
index 0808c5fc893..87f48dc6894 100644
--- a/src/include/catalog/pg_statistic.h
+++ b/src/include/catalog/pg_statistic.h
@@ -152,6 +152,9 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum));
  * data "kind" will appear in any particular slot.  Instead, search the
  * stakind fields to see if the desired data is available.  (The standard
  * function get_attstatsslot() may be used for this.)
+ *
+ * Note: The pg_stats view needs to be modified whenever a new slot kind is
+ * added to core.
  */
 
 /*
@@ -262,7 +265,8 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum));
  * a format similar to STATISTIC_KIND_HISTOGRAM: it contains M (>=2) range
  * values that divide the column data values into M-1 bins of approximately
  * equal population. The lengths are stored as float8s, as measured by the
- * range type's subdiff function. Only non-null rows are considered.
+ * range type's subdiff function. Only non-null, non-empty rows are
+ * considered.
  */
 #define STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM  6
 
-- 
2.39.3 (Apple Git-145)

From 235b2f3de5fff7ae07b0499831435d7b536d6306 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Sat, 25 Nov 2023 00:38:37 +0200
Subject: [PATCH 2/2] Display length and bounds histograms in pg_stats

Values corresponding to STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and
STATISTIC_KIND_BOUNDS_HISTOGRAM were not exposed to pg_stats when these
slot kinds were introduced in 918eee0c49.

This commit adds the missing fields to pg_stats.

Catversion is bumped.

Discussion: https://postgr.es/m/flat/b67d8b57-9357-7e82-a2e7-f6ce6eaeec67@postgrespro.ru
Author: Egor Rogov, Soumyadeep Chakraborty
Reviewed-by: Tomas Vondra, Justin Pryzby, Jian He
---
 doc/src/sgml/system-views.sgml       | 39 ++++++++++++++++++++++++++++
 src/backend/catalog/system_views.sql | 23 +++++++++++++++-
 src/test/regress/expected/rules.out  | 26 ++++++++++++++++++-
 3 files changed, 86 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 7078491c4c0..0ef17456318 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -3799,6 +3799,45 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        non-null elements.  (Null for scalar types.)
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_length_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of the lengths of non-empty and non-null range values of a
+       range type column. (Null for non-range types.)
+      </para>
+      <para>
+       This histogram is calculated using the <literal>subtype_diff</literal>
+       range function regardless of whether range bounds are inclusive.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_empty_frac</structfield> <type>float4</type>
+      </para>
+      <para>
+       Fraction of column entries whose values are empty ranges.
+       (Null for non-range types.)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>range_bounds_histogram</structfield> <type>anyarray</type>
+      </para>
+      <para>
+       A histogram of lower and upper bounds of non-empty and non-null range
+       values. (Null for non-range types.)
+      </para>
+      <para>
+       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.
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b65f6b52490..11d18ed9dd6 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -243,7 +243,28 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
             WHEN stakind3 = 5 THEN stanumbers3
             WHEN stakind4 = 5 THEN stanumbers4
             WHEN stakind5 = 5 THEN stanumbers5
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stavalues1
+            WHEN stakind2 = 6 THEN stavalues2
+            WHEN stakind3 = 6 THEN stavalues3
+            WHEN stakind4 = 6 THEN stavalues4
+            WHEN stakind5 = 6 THEN stavalues5
+        END AS range_length_histogram,
+        CASE
+            WHEN stakind1 = 6 THEN stanumbers1[1]
+            WHEN stakind2 = 6 THEN stanumbers2[1]
+            WHEN stakind3 = 6 THEN stanumbers3[1]
+            WHEN stakind4 = 6 THEN stanumbers4[1]
+            WHEN stakind5 = 6 THEN stanumbers5[1]
+        END AS range_empty_frac,
+        CASE
+            WHEN stakind1 = 7 THEN stavalues1
+            WHEN stakind2 = 7 THEN stavalues2
+            WHEN stakind3 = 7 THEN stavalues3
+            WHEN stakind4 = 7 THEN stavalues4
+            WHEN stakind5 = 7 THEN stavalues5
+            END AS range_bounds_histogram
     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 1442c43d9c5..05070393b99 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2466,7 +2466,31 @@ pg_stats| SELECT n.nspname AS schemaname,
             WHEN (s.stakind4 = 5) THEN s.stanumbers4
             WHEN (s.stakind5 = 5) THEN s.stanumbers5
             ELSE NULL::real[]
-        END AS elem_count_histogram
+        END AS elem_count_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stavalues1
+            WHEN (s.stakind2 = 6) THEN s.stavalues2
+            WHEN (s.stakind3 = 6) THEN s.stavalues3
+            WHEN (s.stakind4 = 6) THEN s.stavalues4
+            WHEN (s.stakind5 = 6) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_length_histogram,
+        CASE
+            WHEN (s.stakind1 = 6) THEN s.stanumbers1[1]
+            WHEN (s.stakind2 = 6) THEN s.stanumbers2[1]
+            WHEN (s.stakind3 = 6) THEN s.stanumbers3[1]
+            WHEN (s.stakind4 = 6) THEN s.stanumbers4[1]
+            WHEN (s.stakind5 = 6) THEN s.stanumbers5[1]
+            ELSE NULL::real
+        END AS range_empty_frac,
+        CASE
+            WHEN (s.stakind1 = 7) THEN s.stavalues1
+            WHEN (s.stakind2 = 7) THEN s.stavalues2
+            WHEN (s.stakind3 = 7) THEN s.stavalues3
+            WHEN (s.stakind4 = 7) THEN s.stavalues4
+            WHEN (s.stakind5 = 7) THEN s.stavalues5
+            ELSE NULL::anyarray
+        END AS range_bounds_histogram
    FROM (((pg_statistic s
      JOIN pg_class c ON ((c.oid = s.starelid)))
      JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
-- 
2.39.3 (Apple Git-145)