
-- Average out results of the ANALYZE runs
create table mcv_results_master_03172112 as 
select 
	title, 
	count, 
	dcount, 
	cast(avg(num_mcvs) as numeric(5)) as avg_num_mcvs, 
	cast(avg(ratio_least_common_mcv) as numeric(5,2)) as "avg_ratio_LCM", 
	cast(avg(ratio_most_common_non_mcv) as numeric(5,2)) as "avg_ratio_MCNM" 
from mcv_results_03172112 
where branch = 'master' group by 1,2,3;

create table mcv_results_rse_03172112 as 
select
	title, 
	count, 
	dcount, 
	cast(avg(num_mcvs) as numeric(5)) as avg_num_mcvs, 
	cast(avg(ratio_least_common_mcv) as numeric(5,2)) as "avg_ratio_LCM", 
	cast(avg(ratio_most_common_non_mcv) as numeric(5,2)) as "avg_ratio_MCNM" 
from mcv_results_03172112 
where branch = 'RSE' group by 1,2,3;

-- V2, using 2, 2.5, and 3.0 as cutoffs
create table mcv_results_v2_20_03172112 as 
select
	title, 
	count, 
	dcount, 
	cast(avg(num_mcvs) as numeric(5)) as avg_num_mcvs, 
	cast(avg(ratio_least_common_mcv) as numeric(5,2)) as "avg_ratio_LCM", 
	cast(avg(ratio_most_common_non_mcv) as numeric(5,2)) as "avg_ratio_MCNM" 
from mcv_results_03172112 
where branch = 'V2_20' group by 1,2,3;

create table mcv_results_v2_25_03172112 as 
select
	title, 
	count, 
	dcount, 
	cast(avg(num_mcvs) as numeric(5)) as avg_num_mcvs, 
	cast(avg(ratio_least_common_mcv) as numeric(5,2)) as "avg_ratio_LCM", 
	cast(avg(ratio_most_common_non_mcv) as numeric(5,2)) as "avg_ratio_MCNM" 
from mcv_results_03172112 
where branch = 'V2_25' group by 1,2,3;

create table mcv_results_v2_30_03172112 as 
select
	title, 
	count, 
	dcount, 
	cast(avg(num_mcvs) as numeric(5)) as avg_num_mcvs, 
	cast(avg(ratio_least_common_mcv) as numeric(5,2)) as "avg_ratio_LCM", 
	cast(avg(ratio_most_common_non_mcv) as numeric(5,2)) as "avg_ratio_MCNM" 
from mcv_results_03172112 
where branch = 'V2_30' group by 1,2,3;


-- compare number of MCVs
select 
	m.title,
	m.avg_num_mcvs as master_num_mcvs,
	r.avg_num_mcvs as "RSE_num_mcvs",
	v20.avg_num_mcvs as "V20_num_mcvs",
	v25.avg_num_mcvs as "V25_num_mcvs",
	v30.avg_num_mcvs as "V30_num_mcvs"
from mcv_results_master_03172112 m
join mcv_results_RSE_03172112 r on m.title=r.title
join mcv_results_V2_20_03172112 v20 on m.title=v20.title
join mcv_results_V2_25_03172112 v25 on m.title=v25.title
join mcv_results_V2_30_03172112 v30 on m.title=v30.title
-- ~ where m.title like '%uniform%'
where m.title not like '%uniform%'
order by m.title;

-- compare ratio of actual to estimated cardinality of least common MCV
-- Note: For the uniform distributions, the ratio will be zero if there
-- are no MCVs.
select 
	--m.title,
	m."avg_ratio_LCM" as master_ratio_LCM,
	r."avg_ratio_LCM" as "RSE_ratio_LCM",
	v20."avg_ratio_LCM" as "V20_ratio_LCM",
	v25."avg_ratio_LCM" as "V25_ratio_LCM",
	v30."avg_ratio_LCM" as "V30_ratio_LCM"
from mcv_results_master_03172112 m
join mcv_results_RSE_03172112 r on m.title=r.title
join mcv_results_V2_20_03172112 v20 on m.title=v20.title
join mcv_results_V2_25_03172112 v25 on m.title=v25.title
join mcv_results_V2_30_03172112 v30 on m.title=v30.title
where m.title like '%uniform%'
-- ~ where m.title not like '%uniform%'
order by m.title;

-- compare ratio of actual to estimated cardinality of most common non-MCV
-- Note: For the uniform distributions, the ratio will be zero if all
-- values are accounted for in the MCV list.
select 
	--m.title,
	m."avg_ratio_MCNM" as master_ratio_MCNM,
	r."avg_ratio_MCNM" as "RSE_ratio_MCNM",
	v20."avg_ratio_MCNM" as "V20_ratio_MCNM",
	v25."avg_ratio_MCNM" as "V25_ratio_MCNM",
	v30."avg_ratio_MCNM" as "V30_ratio_MCNM"
from mcv_results_master_03172112 m
join mcv_results_RSE_03172112 r on m.title=r.title
join mcv_results_V2_20_03172112 v20 on m.title=v20.title
join mcv_results_V2_25_03172112 v25 on m.title=v25.title
join mcv_results_V2_30_03172112 v30 on m.title=v30.title
where m.title like '%uniform%'
-- ~ where m.title not like '%uniform%'
order by m.title;

