-- Note: Using default_statistics_target = 10 for readability


create table base_random as 
select
	i,
	random() as u
from generate_series(1,60000) as i;

-- simple way to avoid infinities
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
-- Note: The ndistinct estimator underestimates geometric by ~33% and cauchy by ~80%.

-- HEAD (stat target = 10)
-- uniform has 7 MCVs (bad).
-- geometric MCV list accounts for 88% of table.
-- geometric, power, and sometimes normal_s2 have redundant values in the histogram.

  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 mcv-stats patch (stat target = 10)
-- uniform has no MCVs (good).
-- normal has more or none, depending on how dispersed the data set is (probably good).
-- power has a full MCV list, but there are still redundant values in the histogram (good).
-- geometric MCV list accounts for 94% of table.
-- cauchy loses MCVs and gains redundant histogram boundaries (slight regression?).

  attname   |     most_common_vals      |           histogram_bounds           
------------+---------------------------+--------------------------------------
 cauchy     | {-1,0,-2,1,2,-3}          | {-1176,-18,-9,-6,-4,3,3,4,7,12,1598}
 geometric  | {0,1,2,3}                 | {4,4,4,4,5,5,6,11}
 normal_s2  | {49,50,48,51,47,52,46,53} | {43,45,45,54,54,56}
 normal_s20 |                           | {-31,24,33,39,44,50,55,60,67,76,118}
 power      | {1,2,3,4,5,6,7,8,9,10}    | {11,11,11,12,12,13,14,15,16,18,25}
 uniform    |                           | {1,11,21,31,40,49,60,71,81,90,100}
(6 rows)

-- With mcv-stats patch (stat target = 20)
-- Bumping stats target to 20 usually causes larger MCV lists, but uniform still has no MCVs (good).
-- Sometimes no values in geometric get sampled only once, resulting in both a full MCV list and an empty histogram (expected). Usually, MCV list accounts for 98% of table.

  attname   |                       most_common_vals                        |                            histogram_bounds
------------+---------------------------------------------------------------+------------------------------------------------------------------------
 cauchy     | {0,-1,-2,1,2,-3,-4,3,-5}                                      | {-200813,-55,-27,-18,-13,-11,-9,-8,-7,-6,4,4,5,5,6,8,10,14,22,41,4874}
 geometric  | {0,1,2,3,4,5}                                                 | {6,6,6,7,7,7,8,19}
 normal_s2  | {49,50,51,48,47,52,46,53,45}                                  | {40,44,44,54,54,54,55,57}
 normal_s20 | {49,51,50,41,62,46,40,53,54,42,59,52,38,60,43,48,45,56,35,47} | {-41,11,19,23,26,29,31,34,37,44,57,61,64,66,68,71,73,77,80,86,114}
 power      | {1,2,3,4,5,6,7,8,9,10,11,12,13,14}                            | {15,15,15,15,16,16,17,17,18,19,20,21,28}
 uniform    |                                                               | {1,5,10,16,20,25,30,35,40,45,50,55,60,65,70,75,80,85,89,95,100}
