-- Note: Using default_statistics_target = 10 for readability
drop table if exists base_random;
drop table if exists non_uniform_dist;
create table base_random as
select
i,
random() as u
from generate_series(1,100000) as i;
-- simple way to avoid infinites
delete from base_random
where u = 0;
create table non_uniform_dist as
select
width_bucket(u,0,1,100) as uniform,
floor( 50 + 20*sqrt(-2*ln(u))*cos(2*pi()*random()) ) as normal_s20, -- stddev = 20
floor( 50 + 2*sqrt(-2*ln(u))*cos(2*pi()*random()) ) as normal_s2, -- stddev = 2
floor( -log(u)/log(2) ) as geometric,
floor( tan(pi()*(u-0.5)) ) as cauchy,
width_bucket(power(u,0.2),1,0,30) as power
from base_random;
----------------------------------------
analyze;
select
attname,
most_common_vals,
histogram_bounds
from pg_stats
where tablename='non_uniform_dist'
order by attname;
-- typical results
-- HEAD
-- geometric, power, and sometimes normal_s2 have redundant values in the histogram boundary list.
attname | most_common_vals | histogram_bounds
------------+---------------------------------+-----------------------------------------
cauchy | {-1,0,-2,1,-3,2,3,-4,-5,4} | {-2032,-34,-17,-10,-8,-6,5,9,13,28,455}
geometric | {0,1,2} | {3,3,3,3,4,4,5,6,12}
normal_s2 | {49,50,48,51,47,52} | {42,45,46,46,53,53,53,54,56}
normal_s20 | {49,41,53,45,48,58,52,57,42,59} | {-18,21,28,34,39,47,55,63,70,78,129}
power | {1,2,3,4,5,6,7} | {8,8,9,9,10,11,11,13,14,16,25}
uniform | {15,1,7,91,5,16,71} | {2,13,24,33,42,51,60,70,80,89,100}
(6 rows)
-- with analyze_highly_skewed.patch
-- geometric and power no longer have redundant values. Sometimes the geometric histogram is null.
-- uniform also has more MCVs, which is not desirable.
attname | most_common_vals | histogram_bounds
------------+---------------------------------+----------------------------------------
cauchy | {-1,0,1,-2,2,-3,3,-4,4,-5} | {-997,-33,-14,-10,-7,-6,5,7,10,19,204}
geometric | {0,1,2,3,4,5,6,7,8,9} | {10,11}
normal_s2 | {49,50,51,48,47,52,53,46,54,45} | {44,44,55,57}
normal_s20 | {44,45,51,58,64,54,38,47,60,42} | {-19,22,30,35,41,50,56,62,70,79,118}
power | {1,2,3,4,5,6,7,8,9,10} | {11,11,12,13,14,15,17,19,22,26,41}
uniform | {76,18,70,40,71,50,69,86,1,37} | {2,11,21,31,41,51,60,72,82,91,100}
(6 rows)