On 1/21/23 19:53, Egor Rogov wrote:
> 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.
>
OK, good to hear.
>
>> 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)?
>
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.
>
>> 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).
>
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 <tomas.von...@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><iteration count></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 <to...@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><iteration count></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
