Subject: Re: pg_stats and range statistics

From: Egor Rogov <[email-masked]>
Date: Sat, 21 Jan 2023 21:53:20 +0300

Hi Tomas,

On 21.01.2023 00:50, Tomas Vondra wrote:
> Hi Egor,
>
> While reviewing a patch improving join estimates for ranges [1] I
> realized we don't show stats for ranges in pg_stats, and I recalled we
> had this patch.
>
> I rebased the v2, and I decided to took a stab at showing separate
> histograms for lower/upper histogram bounds. I believe it makes it way
> more readable, which is what pg_stats is about IMHO.

Thanks for looking into this.

I have to admit it looks much better this way, so +1.

> This simply adds two functions, accepting/producing anyarray - one for
> lower bounds, one for upper bounds. I don't think it can be done with a
> plain subquery (or at least I don't know how).

Anyarray is an alien to SQL, so functions are well justified here. What 
makes me a bit uneasy is two almost identical functions. Should we 
consider other options like a function with an additional parameter or a 
function returning an array of bounds arrays (which is somewhat 
wasteful, but probably it doesn't matter much here)?

> Finally, it renames the empty_range_frac to start with range_, per the
> earlier discussion. I wonder if the new column names for lower/upper
> bounds (range_lower_bounds_histograms/range_upper_bounds_histograms) are
> too long ...

It seems so. The ending -s should be left out since it's a single 
histogram now. And I think that 
range_lower_histogram/range_upper_histogram are descriptive enough.

I'm adding one more patch to shorten the column names, refresh the docs, 
and make 'make check' happy (unfortunately, we have to edit 
src/regress/expected/rules.out every time pg_stats definition changes).

>
> regards
>
> [1] https://commitfest.postgresql.org/41/3821/
>

From b339c0eab5616cec61e9d9e85398034861608d30 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email-masked]>
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 <[email-masked]>
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 <[email-masked]>
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: Tomas Vondra <[email-masked]>
Date: Sun, 22 Jan 2023 19:19:41 +0100

> I have to admit it looks much better this way, so +1.

OK, good to hear.

> wasteful, but probably it doesn't matter much here)?

I thought about that, but I think the alternatives (e.g. a single
function with a parameter determining which boundary to return). But I
don't think it's better.

Moreover, I think this is pretty similar to lower/upper, which already
work on range values. So if we have separate functions for that, we
should do the same thing here.

I renamed the functions to ranges_lower/ranges_upper, but maybe why not
to even call the functions lower/upper too?

The main trouble with the function I can think of is that we only have
anyarray type, not anyrangearray. So the functions will get called for
arbitrary array, and the check that it's array of ranges happens inside.
I'm not sure if that's a good or bad idea, or what would it take to add
a new polymorphic type ...

For now I at least kept "ranges_" to make it less likely.

> src/regress/expected/rules.out every time pg_stats definition changes).

Thanks. I noticed the docs were added to pg_user_mapping by mistake, not
to pg_stats. So I fixed that, and I also added the new functions.

Finally, I reordered the fields a bit - moved range_empty_frac to keep
the histogram fields together.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

From 8049d2b7e5d1636d5fb2b7d421d6b29a39389fb3 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email-masked]>
Date: Fri, 20 Jan 2023 20:50:41 +0100
Subject: [PATCH 1/2] Display length and bounds histograms in 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(-)

index c1e4048054..e0851c52b4 100644
@@ -9634,6 +9634,46 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
index 8608e3fa5b..b3b9e64ae2 100644
@@ -243,7 +243,35 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
index 86810a1a6e..a0097282fc 100644
 
index 86eb8e8c58..161557e4cb 100644
 
index 8770c5b4c6..10401dece0 100644
 
 /*
index e7a2f5856a..26087dfdf5 100644
@@ -2436,7 +2436,39 @@ pg_stats| SELECT n.nspname AS schemaname,

From 9e95cf8025f37b819a3396fa24e0ccac92f18ce0 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email-masked]>
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