From: Egor Rogov <e.rogov@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
Subject: pg_stats and range statistics
Date: Fri, 18 Jun 2021 19:22:51 +0300
Message-ID: <b67d8b57-9357-7e82-a2e7-f6ce6eaeec67@postgrespro.ru> (raw)
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.
Attachments:pgstats.patch (text/plain, 4.6K) downloaddiff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgmlindex 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.sqlindex 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.outindex 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))))
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-hackers@postgresql.org
Cc: e.rogov@postgrespro.ru
Subject: Re: pg_stats and range statistics
In-Reply-To: <b67d8b57-9357-7e82-a2e7-f6ce6eaeec67@postgrespro.ru>
References: <b67d8b57-9357-7e82-a2e7-f6ce6eaeec67@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox