0002-tweaks-20230122.patch
text/x-patch
Filename: 0002-tweaks-20230122.patch
Type: text/x-patch
Part: 1
Message:
Re: pg_stats and range statistics
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch 0002
Subject: tweaks
| File | + | − |
|---|---|---|
| doc/src/sgml/catalogs.sgml | 0 | 40 |
| doc/src/sgml/func.sgml | 36 | 0 |
| doc/src/sgml/system-views.sgml | 40 | 0 |
| src/backend/catalog/system_views.sql | 17 | 17 |
| src/backend/utils/adt/rangetypes_typanalyze.c | 6 | 0 |
| src/include/catalog/pg_proc.dat | 2 | 2 |
| src/include/catalog/pg_statistic.h | 0 | 3 |
| src/test/regress/expected/rules.out | 18 | 18 |
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><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