pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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) download 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))))

view thread (25+ messages)  latest in thread

Message-ID: <b67d8b57-9357-7e82-a2e7-f6ce6eaeec67@postgrespro.ru>
Permalink:  ../b67d8b57-9357-7e82-a2e7-f6ce6eaeec67@postgrespro.ru/
Also on:    postgresql.org/message-id/b67d8b57-9357-7e82-a2e7-f6ce6eaeec67@postgrespro.ru

 ·  · 

reply

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