\o
\timing off
\set outer_size 20000000
\set inner_size 10000000

drop table if exists outer_table;
drop table if exists inner_table;

-- create the testing tables
create table outer_table (id int primary key, val int);
create table inner_table (id int primary key, val_a int, val_b int, val_c int, val_txt char(32));

insert into outer_table select i, i from generate_series(1,:outer_size) s(i);
insert into inner_table select i, mod(i,1000), mod(i,1000), mod(i,1000), md5(i::text) from generate_series(1,:inner_size) s(i);

analyze outer_table;
analyze inner_table;

-- force hashjoin
set enable_mergejoin = off;
set enable_nestloop = off;
set enable_sort = off;

\timing on
\o /dev/null

-------------------------------------------------------------------------------------------------------------------
-- SMALL WORK_MEM (2MB)
-------------------------------------------------------------------------------------------------------------------

\echo "===== SMALL WORK MEM (2MB) / no dynamic buckets ====="
set work_mem = '2MB';
set enable_hashjoin_bucket = off;

\echo "query A"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;

\echo "query B"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;

\echo "query C"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;

\echo "query D"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;

\echo "===== SMALL WORK MEM (2MB) / dynamic buckets ====="
set work_mem = '2MB';
set enable_hashjoin_bucket = on;

\echo "query A"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;

\echo "query B"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;

\echo "query C"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;

\echo "query D"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;

-------------------------------------------------------------------------------------------------------------------
-- MEDIUM WORK MEM (16MB)
-------------------------------------------------------------------------------------------------------------------

\echo "===== MEDIUM WORK MEM (16MB) / no dynamic buckets ====="
set work_mem = '16MB';
set enable_hashjoin_bucket = off;

\echo "query A"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;

\echo "query B"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;

\echo "query C"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;

\echo "query D"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;

\echo "===== MEDIUM WORK MEM (16MB) / dynamic buckets ====="
set work_mem = '16MB';
set enable_hashjoin_bucket = on;

\echo "query A"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;

\echo "query B"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;

\echo "query C"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;

\echo "query D"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;

-------------------------------------------------------------------------------------------------------------------
-- LARGE WORK MEM (64MB)
-------------------------------------------------------------------------------------------------------------------

\echo "===== LARGE WORK MEM (64MB) / no dynamic buckets ====="
set work_mem = '64MB';
set enable_hashjoin_bucket = off;

\echo "query A"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;

\echo "query B"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;

\echo "query C"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;

\echo "query D"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;

\echo "===== LARGE WORK MEM (64MB) / dynamic buckets ====="
set work_mem = '64MB';
set enable_hashjoin_bucket = on;

\echo "query A"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100;

\echo "query B"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100;

\echo "query C"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 200 and val_b < 200;

\echo "query D"

select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;
select count(i.val_txt) from outer_table o join inner_table i on (o.id = i.id) where val_a < 100 and val_b < 100 and val_c < 100;