有时，我们为了直观的显示对象的空间占用及分布情况，我们可能会以图表的形式展示。
通常情况下，我们可以根据需求，以柱状图或者聚集图的形式来展示，从各个维度来了解数据空间占用的分布情况，例如：
1. bucket分布，就是按大小排序，选定要划分为几个bucket，每个bucket放同样数量的对象，输出bucket的边界，形式和pg_stats.histogram_bounds的输出类似。
方法举例，需要用到窗口函数ntile：
postgres=# select bucket,min(size),max(size),count(*) from (select relname,ntile(10) over( order by pg_relation_size(oid) ) bucket, pg_relation_size(oid) size from pg_class) t group by 1 order by 1;bucket | min | max | count
--------+-------+----------+-------1 | 0 | 0 | 312 | 0 | 0 | 303 | 0 | 0 | 304 | 0 | 0 | 305 | 0 | 8192 | 306 | 8192 | 8192 | 307 | 8192 | 16384 | 308 | 16384 | 16384 | 309 | 16384 | 32768 | 3010 | 32768 | 36249600 | 30
(10 rows)2. 按等间距线性分布，例如每100MB输出一组落在对应SIZE的对象。这种方式有点像systemtap的
@hist_linear分布
https://sourceware.org/systemtap/langref/Statistics_aggregates.html#SECTION00094100000000000000
方法举例：
postgres=# select pg_relation_size(oid)/1024/1024,count(*) from pg_class group by 1 order by 1;?column? | count
----------+-------0 | 29921 | 134 | 1
(3 rows)3. 按2^n间距指数分布，这种方式有点像systemtap的
@hist_log分布
https://sourceware.org/systemtap/langref/Statistics_aggregates.html#SECTION00094100000000000000
方法举例：
首先要将int转为二进制
http://blog.163.com/digoal@126/blog/static/16387704020132592725462/
create or replace function si32tob(i_num int) returns varbit as
$$declareo_bit text;o_len int;i_conv int;i_num_abs int;i_pos int;
beginif i_num = 0 then return varbit '0'; end if; o_len := 32;i_conv := 2;i_num_abs := abs(i_num);i_pos := trunc((dlog1(i_num_abs))/0.693147180559945);o_bit := mod(i_num_abs,i_conv)::text;if i_pos >= 1 thenfor i in 1..i_pos loopo_bit := mod(i_num_abs>>i, i_conv)||o_bit;end loop;end if;if i_num >=0 thennull;elseo_len := o_len - char_length(o_bit) - 1;o_bit := repeat('0', o_len)||o_bit;o_bit := '1'||o_bit;end if;return o_bit::varbit;
end;$$language plpgsql;
输出
postgres=# select 2^(bit_length(si32tob((pg_relation_size(oid))::int4))-1), count(*) from pg_class group by 1 order by 1;?column? | count
----------+-------1 | 1458192 | 6716384 | 6032768 | 1465536 | 6131072 | 4262144 | 7524288 | 116777216 | 133554432 | 1
(10 rows)4. 聚集分布，可以用k-mean分布插件
http://blog.163.com/digoal@126/blog/static/163877040201571745048121/
[图]
K_Means查询举例：
postgres=# select class,min(size),max(size),count(*) from (select kmeans(array[pg_relation_size(oid)],10) over() as class,pg_relation_size(oid) size from pg_class ) t group by 1 order by 1;class | min | max | count
-------+----------+----------+-------0 | 671744 | 671744 | 11 | 483328 | 483328 | 12 | 475136 | 475136 | 13 | 22487040 | 22487040 | 14 | 352256 | 352256 | 15 | 36249600 | 36249600 | 16 | 278528 | 319488 | 47 | 221184 | 221184 | 18 | 57344 | 139264 | 109 | 0 | 49152 | 285
(10 rows)
