-- Note: Using default_statistics_target = 10 for readability


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)


