-- parallelism just makes the numbers more difficult to interpret
set max_parallel_workers_per_gather = 0;

-- Dean's example
create table mcv_test_1 (a int, b int);
insert into mcv_test_1 select 1,1 from generate_series(1,50000);
insert into mcv_test_1 select 1,2 from generate_series(1,40000);
insert into mcv_test_1 select 1,x/10 from generate_series(30,250000) g(x);
insert into mcv_test_1 select 2,1 from generate_series(1,30000);
insert into mcv_test_1 select 2,2 from generate_series(1,20000);
insert into mcv_test_1 select 2,x/10 from generate_series(30,500000) g(x);
insert into mcv_test_1 select 3,1 from generate_series(1,10000);
insert into mcv_test_1 select 3,2 from generate_series(1,5000);
insert into mcv_test_1 select 3,x from generate_series(3,600000) g(x);
insert into mcv_test_1 select x,x/10 from generate_series(4,750000) g(x);

analyse mcv_test_1;

explain analyse select * from mcv_test_1 where a=1 and b=1;

explain analyse select * from mcv_test_1 where a=1 and b=2;

explain analyse select * from mcv_test_1 where a=1 and (b=1 or b=2);

explain analyse select * from mcv_test_1 where (a=1 or a=2) and (b=1 or b=2);

create statistics mcv_test_mcv_ab (mcv) on a,b from mcv_test_1;
analyse mcv_test_1;

explain analyse select * from mcv_test_1 where a=1 and b=1;

explain analyse select * from mcv_test_1 where a=1 and b=2;

explain analyse select * from mcv_test_1 where a=1 and (b=1 or b=2);

explain analyse select * from mcv_test_1 where (a=1 or a=2) and (b=1 or b=2);

-- perfectly dependent columns with small number of distinct values
create table mcv_test_2(a int, b int, c int, d int);
insert into mcv_test_2 select mod(i,50), mod(i,50), mod(i,50), mod(i,50) from generate_series(1,50000) s(i);

analyse mcv_test_2;

explain analyse select * from mcv_test_2 where a=1 and b=1;

explain analyse select * from mcv_test_2 where a=1 and b=1 and c=1;

explain analyse select * from mcv_test_2 where a=1 and b=1 and c=1 and d=1;

explain analyse select * from mcv_test_2 where a=1 and b=2;

explain analyse select * from mcv_test_2 where a=1 and b=1 and c < 10;

explain analyse select * from mcv_test_2 where a=1 and b=1 and c < 10 and d < 10;

explain analyse select * from mcv_test_2 where a=1 and b=1 and c > 10;

explain analyse select * from mcv_test_2 where a=1 and b=1 and c > 10 and d > 10;

create statistics mcv_test_2_mcv_abcd (mcv) on a,b,c,d from mcv_test_2;
analyse mcv_test_2;

explain analyse select * from mcv_test_2 where a=1 and b=1;

explain analyse select * from mcv_test_2 where a=1 and b=1 and c=1;

explain analyse select * from mcv_test_2 where a=1 and b=1 and c=1 and d=1;

explain analyse select * from mcv_test_2 where a=1 and b=2;

explain analyse select * from mcv_test_2 where a=1 and b=1 and c < 10;

explain analyse select * from mcv_test_2 where a=1 and b=1 and c < 10 and d < 10;

explain analyse select * from mcv_test_2 where a=1 and b=1 and c > 10;

explain analyse select * from mcv_test_2 where a=1 and b=1 and c > 10 and d > 10;

-- perfectly dependent columns with small number of distinct values, but
-- skewed towards 0
create table mcv_test_3(a int, b int, c int, d int);
insert into mcv_test_3 select sqrt(mod(i,2500)), sqrt(mod(i,2500)), sqrt(mod(i,2500)), sqrt(mod(i,2500)) from generate_series(1,100000) s(i);

analyse mcv_test_3;

explain analyse select * from mcv_test_3 where a=1 and b=1;

explain analyse select * from mcv_test_3 where a=1 and b=1 and c=1;

explain analyse select * from mcv_test_3 where a=1 and b=1 and c=1 and d=1;

explain analyse select * from mcv_test_3 where a=1 and b=2;

explain analyse select * from mcv_test_3 where a=1 and b=1 and c < 10;

explain analyse select * from mcv_test_3 where a=1 and b=1 and c < 10 and d < 10;

explain analyse select * from mcv_test_3 where a=1 and b=1 and c > 10;

explain analyse select * from mcv_test_3 where a=1 and b=1 and c > 10 and d > 10;

create statistics mcv_test_3_mcv_abcd (mcv) on a,b,c,d from mcv_test_3;
analyse mcv_test_3;

explain analyse select * from mcv_test_3 where a=1 and b=1;

explain analyse select * from mcv_test_3 where a=1 and b=1 and c=1;

explain analyse select * from mcv_test_3 where a=1 and b=1 and c=1 and d=1;

explain analyse select * from mcv_test_3 where a=1 and b=2;

explain analyse select * from mcv_test_3 where a=1 and b=1 and c < 10;

explain analyse select * from mcv_test_3 where a=1 and b=1 and c < 10 and d < 10;

explain analyse select * from mcv_test_3 where a=1 and b=1 and c > 10;

explain analyse select * from mcv_test_3 where a=1 and b=1 and c > 10 and d > 10;

-- perfectly dependent values with large number of distinct values, so
-- more suitable for histograms

create table histogram_test_4(a int, b int, c int, d int);
insert into histogram_test_4 select x, x, x, x from (select (random() * 200)::int as x from generate_series(1,100000)) foo;

analyse histogram_test_4;

explain analyse select * from histogram_test_4 where a <= 10 and b <= 10;

explain analyse select * from histogram_test_4 where a <= 10 and b <= 10 and c <= 10;

explain analyse select * from histogram_test_4 where a <= 10 and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from histogram_test_4 where a = 1   and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from histogram_test_4 where a = 1   and b = 1   and c <= 10 and d <= 10;

explain analyse select * from histogram_test_4 where a = 1   and b = 1   and c >= 10 and d >= 10;

explain analyse select * from histogram_test_4 where a = 1   and b = 1   and c >= 90 and d >= 90;

explain analyse select * from histogram_test_4 where a = 1   and b = 2   and c <= 10 and d <= 10;

explain analyse select * from histogram_test_4 where a = 1   and b = 2   and c >= 10 and d >= 10;

explain analyse select * from histogram_test_4 where a = 1   and b = 2   and c >= 90 and d >= 90;

explain analyse select * from histogram_test_4 where a <= 10 and b >= 90 and c <= 10 and d <= 10;

explain analyse select * from histogram_test_4 where a <= 60 and b >= 40 and c <= 65 and d >= 45;

create statistics histogram_test_4_histogram_abcd (ndistinct, histogram) on a,b,c,d from histogram_test_4;
analyse histogram_test_4;

explain analyse select * from histogram_test_4 where a <= 10 and b <= 10;

explain analyse select * from histogram_test_4 where a <= 10 and b <= 10 and c <= 10;

explain analyse select * from histogram_test_4 where a <= 10 and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from histogram_test_4 where a = 1   and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from histogram_test_4 where a = 1   and b = 1   and c <= 10 and d <= 10;

explain analyse select * from histogram_test_4 where a = 1   and b = 1   and c >= 10 and d >= 10;

explain analyse select * from histogram_test_4 where a = 1   and b = 1   and c >= 90 and d >= 90;

explain analyse select * from histogram_test_4 where a = 1   and b = 2   and c <= 10 and d <= 10;

explain analyse select * from histogram_test_4 where a = 1   and b = 2   and c >= 10 and d >= 10;

explain analyse select * from histogram_test_4 where a = 1   and b = 2   and c >= 90 and d >= 90;

explain analyse select * from histogram_test_4 where a <= 10 and b >= 90 and c <= 10 and d <= 10;

explain analyse select * from histogram_test_4 where a <= 60 and b >= 40 and c <= 65 and d >= 45;

-- perfectly dependent values with large number of distinct values, so
-- more suitable for histograms, skewed towards 0

create table histogram_test_5(a int, b int, c int, d int);
insert into histogram_test_5 select x, x, x, x from (select (sqrt(random()) * 200)::int as x from generate_series(1,100000)) foo;

analyse histogram_test_5;

explain analyse select * from histogram_test_5 where a <= 10 and b <= 10;

explain analyse select * from histogram_test_5 where a <= 10 and b <= 10 and c <= 10;

explain analyse select * from histogram_test_5 where a <= 10 and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from histogram_test_5 where a = 1   and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from histogram_test_5 where a = 1   and b = 1   and c <= 10 and d <= 10;

explain analyse select * from histogram_test_5 where a = 1   and b = 1   and c >= 10 and d >= 10;

explain analyse select * from histogram_test_5 where a = 1   and b = 1   and c >= 90 and d >= 90;

explain analyse select * from histogram_test_5 where a = 1   and b = 2   and c <= 10 and d <= 10;

explain analyse select * from histogram_test_5 where a = 1   and b = 2   and c >= 10 and d >= 10;

explain analyse select * from histogram_test_5 where a = 1   and b = 2   and c >= 90 and d >= 90;

explain analyse select * from histogram_test_5 where a <= 10 and b >= 90 and c <= 10 and d <= 10;

explain analyse select * from histogram_test_5 where a <= 60 and b >= 40 and c <= 65 and d >= 45;

create statistics histogram_test_5_histogram_abcd (ndistinct, histogram) on a,b,c,d from histogram_test_5;
analyse histogram_test_5;

explain analyse select * from histogram_test_5 where a <= 10 and b <= 10;

explain analyse select * from histogram_test_5 where a <= 10 and b <= 10 and c <= 10;

explain analyse select * from histogram_test_5 where a <= 10 and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from histogram_test_5 where a = 1   and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from histogram_test_5 where a = 1   and b = 1   and c <= 10 and d <= 10;

explain analyse select * from histogram_test_5 where a = 1   and b = 1   and c >= 10 and d >= 10;

explain analyse select * from histogram_test_5 where a = 1   and b = 1   and c >= 90 and d >= 90;

explain analyse select * from histogram_test_5 where a = 1   and b = 2   and c <= 10 and d <= 10;

explain analyse select * from histogram_test_5 where a = 1   and b = 2   and c >= 10 and d >= 10;

explain analyse select * from histogram_test_5 where a = 1   and b = 2   and c >= 90 and d >= 90;

explain analyse select * from histogram_test_5 where a <= 10 and b >= 90 and c <= 10 and d <= 10;

explain analyse select * from histogram_test_5 where a <= 60 and b >= 40 and c <= 65 and d >= 45;


-- perfectly dependent values with large number of distinct values, so
-- more suitable for histograms, very few collisions (double)

create table histogram_test_6(a double precision, b double precision, c double precision, d double precision);
insert into histogram_test_6 select x, x, x, x from (select random() * 10000 as x from generate_series(1,100000)) foo;

analyse histogram_test_6;

explain analyse select * from histogram_test_6 where a <= 100 and b <= 100;

explain analyse select * from histogram_test_6 where a <= 100 and b <= 100 and c <= 100;

explain analyse select * from histogram_test_6 where a <= 100 and b <= 100 and c <= 100 and d <= 100;

explain analyse select * from histogram_test_6 where a <= 100 and b >= 900 and c <= 100 and d <= 100;

explain analyse select * from histogram_test_6 where a <= 600 and b >= 400 and c <= 650 and d >= 450;

create statistics histogram_test_6_histogram_abcd (ndistinct, histogram) on a,b,c,d from histogram_test_6;
analyse histogram_test_6;

explain analyse select * from histogram_test_6 where a <= 100 and b <= 100;

explain analyse select * from histogram_test_6 where a <= 100 and b <= 100 and c <= 100;

explain analyse select * from histogram_test_6 where a <= 100 and b <= 100 and c <= 100 and d <= 100;

explain analyse select * from histogram_test_6 where a <= 100 and b >= 900 and c <= 100 and d <= 100;

explain analyse select * from histogram_test_6 where a <= 600 and b >= 400 and c <= 650 and d >= 450;

-- perfectly dependent values combining a subset of very common combinations, and a subset
-- of large number of distinct values, so suitable for combination of mcv and histogram

create table mcv_histogram_test_7(a int, b int, c int, d int);
insert into mcv_histogram_test_7 select mod(i,50), mod(i,50), mod(i,50), mod(i,50) from generate_series(1,50000) s(i);
insert into mcv_histogram_test_7 select x, x, x, x from (select (random() * 200)::int as x from generate_series(1,50000)) foo;

analyse mcv_histogram_test_7;

explain analyse select * from mcv_histogram_test_7 where a <= 10 and b <= 10;

explain analyse select * from mcv_histogram_test_7 where a <= 10 and b <= 10 and c <= 10;

explain analyse select * from mcv_histogram_test_7 where a <= 10 and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b = 1   and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b = 1   and c >= 10 and d >= 10;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b = 1   and c >= 90 and d >= 90;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b = 2   and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b = 2   and c >= 10 and d >= 10;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b = 2   and c >= 90 and d >= 90;

explain analyse select * from mcv_histogram_test_7 where a <= 10 and b >= 90 and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_7 where a <= 60 and b >= 40 and c <= 65 and d >= 45;

create statistics mcv_histogram_test_7_histogram_abcd (ndistinct, mcv, histogram) on a,b,c,d from mcv_histogram_test_7;
analyse mcv_histogram_test_7;

explain analyse select * from mcv_histogram_test_7 where a <= 10 and b <= 10;

explain analyse select * from mcv_histogram_test_7 where a <= 10 and b <= 10 and c <= 10;

explain analyse select * from mcv_histogram_test_7 where a <= 10 and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b = 1   and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b = 1   and c >= 10 and d >= 10;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b = 1   and c >= 90 and d >= 90;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b = 2   and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b = 2   and c >= 10 and d >= 10;

explain analyse select * from mcv_histogram_test_7 where a = 1   and b = 2   and c >= 90 and d >= 90;

explain analyse select * from mcv_histogram_test_7 where a <= 10 and b >= 90 and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_7 where a <= 60 and b >= 40 and c <= 65 and d >= 45;

-- perfectly dependent values combining a subset of very common combinations, and a subset
-- of large number of distinct values, so suitable for combination of mcv and histogram
-- both subsets are skewed to 0

create table mcv_histogram_test_8(a int, b int, c int, d int);
insert into mcv_histogram_test_8 select sqrt(mod(i,2500)), sqrt(mod(i,2500)), sqrt(mod(i,2500)), sqrt(mod(i,2500)) from generate_series(1,100000) s(i);
insert into mcv_histogram_test_8 select x, x, x, x from (select (sqrt(random()) * 200)::int as x from generate_series(1,100000)) foo;

analyse mcv_histogram_test_8;

explain analyse select * from mcv_histogram_test_8 where a <= 10 and b <= 10;

explain analyse select * from mcv_histogram_test_8 where a <= 10 and b <= 10 and c <= 10;

explain analyse select * from mcv_histogram_test_8 where a <= 10 and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b = 1   and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b = 1   and c >= 10 and d >= 10;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b = 1   and c >= 90 and d >= 90;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b = 2   and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b = 2   and c >= 10 and d >= 10;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b = 2   and c >= 90 and d >= 90;

explain analyse select * from mcv_histogram_test_8 where a <= 10 and b >= 90 and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_8 where a <= 60 and b >= 40 and c <= 65 and d >= 45;

create statistics mcv_histogram_test_8_histogram_abcd (ndistinct, mcv, histogram) on a,b,c,d from mcv_histogram_test_8;
analyse mcv_histogram_test_8;

explain analyse select * from mcv_histogram_test_8 where a <= 10 and b <= 10;

explain analyse select * from mcv_histogram_test_8 where a <= 10 and b <= 10 and c <= 10;

explain analyse select * from mcv_histogram_test_8 where a <= 10 and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b <= 10 and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b = 1   and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b = 1   and c >= 10 and d >= 10;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b = 1   and c >= 90 and d >= 90;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b = 2   and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b = 2   and c >= 10 and d >= 10;

explain analyse select * from mcv_histogram_test_8 where a = 1   and b = 2   and c >= 90 and d >= 90;

explain analyse select * from mcv_histogram_test_8 where a <= 10 and b >= 90 and c <= 10 and d <= 10;

explain analyse select * from mcv_histogram_test_8 where a <= 60 and b >= 40 and c <= 65 and d >= 45;
