#!/bin/bash

N=${N:-10}

psql -X -q <<'EOF'
set client_min_messages = warning;
create table if not exists t3m as select g as id, g % 1000 as a from generate_series(1, 3000000) g;
create table if not exists t3m_wide as select g as id, g % 1000 as a, g % 997 as b, g * 2 as c, repeat(md5(g::text), 3) as pad from generate_series(1, 3000000) g;
create table if not exists t300k as select g as id, g % 1000 as a from generate_series(1, 300000) g;
create table if not exists t60k as select g as id, g % 1000 as a from generate_series(1, 60000) g;
create table if not exists t3m_dup as select -(g % 10) - 1 as id, g % 1000 as a from generate_series(1, 3000000) g;
create table if not exists t4m_outer as select g as id, g % 1000 as b, case when g % 4000 = 0 then -1 else g % 1000 end as c from generate_series(1, 4000000) g;
create or replace function f() returns setof int rows 100000000 language sql as 'select g * 3 from generate_series(1, 1000) g';
vacuum analyze t3m, t3m_wide, t300k, t60k, t3m_dup, t4m_outer;
EOF

# run <name> <query> [parallel workers]
run() {
    {
        echo "set jit = off; set max_parallel_workers_per_gather = ${3:-0};"
        echo "set parallel_setup_cost = 0; set parallel_tuple_cost = 0;"
        echo "set enable_mergejoin = off; set enable_nestloop = off; set work_mem = '1GB';"
        echo "$2;"
        echo '\timing on'
        for i in $(seq $N); do echo "$2;"; done
    } | psql -X -q | awk '/^Time:/ {print $2}' |
        sort -n | awk -v name="$1" '{a[NR] = $1} END {printf "%-14s %8.1f ms\n", name, a[int((NR + 1) / 2)]}'
}

run build_3m   "select sum(i.a) from f() o(id) join t3m i on o.id = i.id"
run build_wide "select sum(i.a + i.b + i.c + length(i.pad)) from f() o(id) join t3m_wide i on o.id = i.id"
run build_300k "select sum(i.a) from f() o(id) join t300k i on o.id = i.id"
run rebuild    "select sum(i.a) from f() o(id) join (select * from t3m i where (i.id % 1) = 0) i on o.id = i.id"
run build_60k  "select sum(i.a) from f() o(id) join t60k i on o.id = i.id"
run dup_keys   "select sum(i.a) from f() o(id) join t3m_dup i on o.id = i.id"
run parallel   "select sum(i.a) from t4m_outer o join t3m i on o.id = i.id where o.b <> o.c" 2