public inbox for [email protected]
help / color / mirror / Atom feedpg_stats and range statistics
6+ messages / 3 participants
[nested] [flat]
* pg_stats and range statistics
@ 2021-06-18 16:22 Egor Rogov <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Egor Rogov @ 2021-06-18 16:22 UTC (permalink / raw)
To: [email protected]
Hi,
Statistics for range types are not currently exposed in pg_stats view
(i.e. STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and
STATISTIC_KIND_BOUNDS_HISTOGRAM).
Shouldn't they? If so, here is a patch for adding them.
The following is a simple example of what it looks like:
CREATE TABLE test(r int4range);
INSERT INTO test
SELECT int4range((random()*10)::integer,(10+random()*10)::integer)
FROM generate_series(1,10000);
SET default_statistics_target = 10;
ANALYZE test;
SELECT range_length_histogram, range_length_empty_frac,
range_bounds_histogram
FROM pg_stats
WHERE tablename = 'test' \gx
-[ RECORD 1
]-----------+------------------------------------------------------------------------------------------------------
range_length_histogram | {1,4,6,8,9,10,11,12,14,16,20}
range_length_empty_frac | {0.0036666666}
range_bounds_histogram |
{"[0,10)","[1,11)","[2,12)","[3,13)","[4,14)","[5,15)","[6,16)","[7,17)","[8,18)","[9,19)","[10,20)"}
Regards,
Egor Rogov.
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index f517a7d4af..4d037b590e 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -12877,6 +12877,36 @@ 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 lengths of non-empty, non-null ranges.
+ (Null for non-range types.)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>range_length_empty_frac</structfield> <type>float4</type>
+ </para>
+ <para>
+ A fraction of 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>
+ Histograms of lower and upper bounds of non-empty, non-null ranges,
+ combined into a single array of 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 999d984068..93fadfff15 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_length_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 e5ab11275d..780aefdc26 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 range_length_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))))
Attachments:
[text/plain] pgstats.patch (4.6K, ../../[email protected]/2-pgstats.patch)
download | inline diff:
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index f517a7d4af..4d037b590e 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -12877,6 +12877,36 @@ 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 lengths of non-empty, non-null ranges.
+ (Null for non-range types.)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>range_length_empty_frac</structfield> <type>float4</type>
+ </para>
+ <para>
+ A fraction of 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>
+ Histograms of lower and upper bounds of non-empty, non-null ranges,
+ combined into a single array of 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 999d984068..93fadfff15 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_length_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 e5ab11275d..780aefdc26 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 range_length_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))))
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_stats and range statistics
@ 2021-06-18 20:31 Tomas Vondra <[email protected]>
parent: Egor Rogov <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Tomas Vondra @ 2021-06-18 20:31 UTC (permalink / raw)
To: Egor Rogov <[email protected]>; [email protected]
On 6/18/21 6:22 PM, Egor Rogov wrote:
> Hi,
>
> Statistics for range types are not currently exposed in pg_stats view
> (i.e. STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and
> STATISTIC_KIND_BOUNDS_HISTOGRAM).
>
> Shouldn't they? If so, here is a patch for adding them.
>
I think they should be exposed - I don't see why not to do that. I
noticed this when working on the count-min sketch experiment too, so
thanks for this patch.
FWIW I've added the patch to the next CF:
https://commitfest.postgresql.org/33/3184/
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_stats and range statistics
@ 2021-07-11 18:54 Soumyadeep Chakraborty <[email protected]>
parent: Tomas Vondra <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Soumyadeep Chakraborty @ 2021-07-11 18:54 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Egor Rogov <[email protected]>; PostgreSQL Hackers <[email protected]>
Hello,
This should have been added with [1].
Excerpt from the documentation:
"pg_stats is also designed to present the information in a more readable
format than the underlying catalog — at the cost that its schema must
be extended whenever new slot types are defined for pg_statistic." [2]
So, I added a reminder in pg_statistic.h.
Attached is v2 of this patch with some cosmetic changes. Renamed the columns a
bit and updated the docs to be a bit more descriptive.
(range_length_empty_frac -> empty_range_frac, range_bounds_histogram ->
range_bounds_histograms)
One question:
We do have the option of representing the histogram of lower bounds separately
from the histogram of upper bounds, as two separate view columns. Don't know if
there is much utility though and there is a fair bit of added complexity: see
below. Thoughts?
My attempts via SQL (unnest -> lower|upper -> array_agg) were futile given
unnest does not play nice with anyarray. For instance:
select unnest(stavalues1) from pg_statistic;
ERROR: cannot determine element type of "anyarray" argument
Maybe the only option is to write a UDF pg_get_{lower|upper}_bounds_histogram
which can do something similar to what calc_hist_selectivity does:
/*
* Convert histogram of ranges into histograms of its lower and upper
* bounds.
*/
nhist = hslot.nvalues;
hist_lower = (RangeBound *) palloc(sizeof(RangeBound) * nhist);
hist_upper = (RangeBound *) palloc(sizeof(RangeBound) * nhist);
for (i = 0; i < nhist; i++)
{
bool empty;
range_deserialize(rng_typcache, DatumGetRangeTypeP(hslot.values[i]),
&hist_lower[i], &hist_upper[i], &empty);
/* The histogram should not contain any empty ranges */
if (empty)
elog(ERROR, "bounds histogram contains an empty range");
}
This is looking good and ready.
[1] https://github.com/postgres/postgres/commit/918eee0c497c88260a2e107318843c9b1947bc6f
[2] https://www.postgresql.org/docs/devel/view-pg-stats.html
Regards,
Soumyadeep (VMware)
Attachments:
[text/x-patch] v2-pgstats.patch (6.2K, ../../CAE-ML+_69xE=hhw6ZU-BT5h9mR8u8p9BGrikToKPTGthK_VmZw@mail.gmail.com/2-v2-pgstats.patch)
download | inline diff:
From 1f2ed0911eb3f7a53b16047cefb499692daf5ef6 Mon Sep 17 00:00:00 2001
From: Egor Rogov <[email protected]>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_stats and range statistics
@ 2021-07-12 11:10 Egor Rogov <[email protected]>
parent: Soumyadeep Chakraborty <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Egor Rogov @ 2021-07-12 11:10 UTC (permalink / raw)
To: Soumyadeep Chakraborty <[email protected]>; Tomas Vondra <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hi,
thanks for the review and corrections.
On 11.07.2021 21:54, Soumyadeep Chakraborty wrote:
> Hello,
>
> This should have been added with [1].
>
> Excerpt from the documentation:
> "pg_stats is also designed to present the information in a more readable
> format than the underlying catalog — at the cost that its schema must
> be extended whenever new slot types are defined for pg_statistic." [2]
>
> So, I added a reminder in pg_statistic.h.
Good point.
> Attached is v2 of this patch with some cosmetic changes.
I wonder why "TODO: catalog version bump"? This patch doesn't change
catalog structure, or I miss something?
> Renamed the columns a
> bit and updated the docs to be a bit more descriptive.
> (range_length_empty_frac -> empty_range_frac, range_bounds_histogram ->
> range_bounds_histograms)
I intended to make the same prefix ("range_") for all columns concerned
with range types, although I'm fine with the proposed naming.
> One question:
>
> We do have the option of representing the histogram of lower bounds separately
> from the histogram of upper bounds, as two separate view columns. Don't know if
> there is much utility though and there is a fair bit of added complexity: see
> below. Thoughts?
I thought about it too, and decided not to transform the underlying data
structure. As far as I can see, pg_stats never employed such
transformations. For example, STATISTIC_KIND_DECHIST is an array
containing the histogram followed by the average in its last element. It
is shown in pg_stats.elem_count_histogram as is, although it arguably
may be splitted into two fields. All in all, I believe pg_stats's job is
to "unpack" stavalues and stanumbers into meaningful fields, and not to
try to go deeper than that.
>
> My attempts via SQL (unnest -> lower|upper -> array_agg) were futile given
> unnest does not play nice with anyarray. For instance:
>
> select unnest(stavalues1) from pg_statistic;
> ERROR: cannot determine element type of "anyarray" argument
>
> Maybe the only option is to write a UDF pg_get_{lower|upper}_bounds_histogram
> which can do something similar to what calc_hist_selectivity does:
>
> /*
> * Convert histogram of ranges into histograms of its lower and upper
> * bounds.
> */
> nhist = hslot.nvalues;
> hist_lower = (RangeBound *) palloc(sizeof(RangeBound) * nhist);
> hist_upper = (RangeBound *) palloc(sizeof(RangeBound) * nhist);
> for (i = 0; i < nhist; i++)
> {
> bool empty;
>
> range_deserialize(rng_typcache, DatumGetRangeTypeP(hslot.values[i]),
> &hist_lower[i], &hist_upper[i], &empty);
> /* The histogram should not contain any empty ranges */
> if (empty)
> elog(ERROR, "bounds histogram contains an empty range");
> }
>
> This is looking good and ready.
>
> [1] https://github.com/postgres/postgres/commit/918eee0c497c88260a2e107318843c9b1947bc6f
> [2] https://www.postgresql.org/docs/devel/view-pg-stats.html
>
> Regards,
> Soumyadeep (VMware)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_stats and range statistics
@ 2021-07-12 13:04 Tomas Vondra <[email protected]>
parent: Egor Rogov <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Tomas Vondra @ 2021-07-12 13:04 UTC (permalink / raw)
To: Egor Rogov <[email protected]>; Soumyadeep Chakraborty <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On 7/12/21 1:10 PM, Egor Rogov wrote:
> Hi,
>
> thanks for the review and corrections.
>
> On 11.07.2021 21:54, Soumyadeep Chakraborty wrote:
>> Hello,
>>
>> This should have been added with [1].
>>
>> Excerpt from the documentation:
>> "pg_stats is also designed to present the information in a more readable
>> format than the underlying catalog — at the cost that its schema must
>> be extended whenever new slot types are defined for pg_statistic." [2]
>>
>> So, I added a reminder in pg_statistic.h.
>
> Good point.
>
>
>> Attached is v2 of this patch with some cosmetic changes.
>
> I wonder why "TODO: catalog version bump"? This patch doesn't change
> catalog structure, or I miss something?
>
It changes system_views.sql, which is catalog change, as it redefines
the pg_stats system view (it adds 3 more columns). So it changes what
you get after initdb, hence catversion has to be bumped.
>
>> Renamed the columns a
>> bit and updated the docs to be a bit more descriptive.
>> (range_length_empty_frac -> empty_range_frac, range_bounds_histogram ->
>> range_bounds_histograms)
>
> I intended to make the same prefix ("range_") for all columns concerned
> with range types, although I'm fine with the proposed naming.
>
Yeah, I'd vote to change empty_range_frac -> range_empty_frac.
>
>> One question:
>>
>> We do have the option of representing the histogram of lower bounds
>> separately
>> from the histogram of upper bounds, as two separate view columns.
>> Don't know if
>> there is much utility though and there is a fair bit of added
>> complexity: see
>> below. Thoughts?
>
> I thought about it too, and decided not to transform the underlying data
> structure. As far as I can see, pg_stats never employed such
> transformations. For example, STATISTIC_KIND_DECHIST is an array
> containing the histogram followed by the average in its last element. It
> is shown in pg_stats.elem_count_histogram as is, although it arguably
> may be splitted into two fields. All in all, I believe pg_stats's job is
> to "unpack" stavalues and stanumbers into meaningful fields, and not to
> try to go deeper than that.
>
Not firm opinion, but the pg_stats is meant to be easier to
read/understand for humans. So far the transformation were simple
because all the data was fairly simple, but the range stuff may need
more complex transformation.
For example we do quite a bit more in pg_stats_ext views, because it
deals with multi-column stats.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_stats and range statistics
@ 2021-07-23 18:05 Egor Rogov <[email protected]>
parent: Tomas Vondra <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Egor Rogov @ 2021-07-23 18:05 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; Soumyadeep Chakraborty <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hi Tomas,
On 12.07.2021 16:04, Tomas Vondra wrote:
> On 7/12/21 1:10 PM, Egor Rogov wrote:
>> Hi,
>>
>> thanks for the review and corrections.
>>
>> On 11.07.2021 21:54, Soumyadeep Chakraborty wrote:
>>> Hello,
>>>
>>> This should have been added with [1].
>>>
>>> Excerpt from the documentation:
>>> "pg_stats is also designed to present the information in a more readable
>>> format than the underlying catalog — at the cost that its schema must
>>> be extended whenever new slot types are defined for pg_statistic." [2]
>>>
>>> So, I added a reminder in pg_statistic.h.
>> Good point.
>>
>>
>>> Attached is v2 of this patch with some cosmetic changes.
>> I wonder why "TODO: catalog version bump"? This patch doesn't change
>> catalog structure, or I miss something?
>>
> It changes system_views.sql, which is catalog change, as it redefines
> the pg_stats system view (it adds 3 more columns). So it changes what
> you get after initdb, hence catversion has to be bumped.
>
>>> Renamed the columns a
>>> bit and updated the docs to be a bit more descriptive.
>>> (range_length_empty_frac -> empty_range_frac, range_bounds_histogram ->
>>> range_bounds_histograms)
>> I intended to make the same prefix ("range_") for all columns concerned
>> with range types, although I'm fine with the proposed naming.
>>
> Yeah, I'd vote to change empty_range_frac -> range_empty_frac.
>
>>> One question:
>>>
>>> We do have the option of representing the histogram of lower bounds
>>> separately
>>> from the histogram of upper bounds, as two separate view columns.
>>> Don't know if
>>> there is much utility though and there is a fair bit of added
>>> complexity: see
>>> below. Thoughts?
>> I thought about it too, and decided not to transform the underlying data
>> structure. As far as I can see, pg_stats never employed such
>> transformations. For example, STATISTIC_KIND_DECHIST is an array
>> containing the histogram followed by the average in its last element. It
>> is shown in pg_stats.elem_count_histogram as is, although it arguably
>> may be splitted into two fields. All in all, I believe pg_stats's job is
>> to "unpack" stavalues and stanumbers into meaningful fields, and not to
>> try to go deeper than that.
>>
> Not firm opinion, but the pg_stats is meant to be easier to
> read/understand for humans. So far the transformation were simple
> because all the data was fairly simple, but the range stuff may need
> more complex transformation.
>
> For example we do quite a bit more in pg_stats_ext views, because it
> deals with multi-column stats.
In pg_stats_ext, yes, but not in pg_stats (at least until now).
Since no one has expressed a strong desire for a more complex
transformation, should we proceed with the proposed approach (with
further renaming empty_range_frac -> range_empty_frac as you suggested)?
Or should we wait more for someone to weigh in?
>
>
> regards
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-07-23 18:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-18 16:22 pg_stats and range statistics Egor Rogov <[email protected]>
2021-06-18 20:31 ` Tomas Vondra <[email protected]>
2021-07-11 18:54 ` Soumyadeep Chakraborty <[email protected]>
2021-07-12 11:10 ` Egor Rogov <[email protected]>
2021-07-12 13:04 ` Tomas Vondra <[email protected]>
2021-07-23 18:05 ` Egor Rogov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox