pg_stats and range statistics

Egor Rogov <e.rogov@postgrespro.ru>

From: Egor Rogov <e.rogov@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
Date: 2021-06-18T16:22:51Z
Lists: pgsql-hackers

Attachments

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.

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →
  1. Collect and use histograms of lower and upper bounds for range types.