#!/bin/bash

# average number of rows per page with the given schema
ROWS_PER_PAGE=107

# number of rows per 100 ranges (assuming it's 128 pages, i.e. 1MB)
MODULO=$((ROWS_PER_PAGE*128*100))

echo 'create or replace function generate_data (p_pages bigint, p_rows bigint) returns void as $$
declare
    v_rec record;
begin
    for v_rec in (select i from generate_series(1, p_pages) s(i) order by random()) loop
	insert into t select v_rec.i, md5(random()::text) from generate_series(1, p_rows) s(x);
    end loop;
end;
$$ language plpgsql;' | psql test

for s in 1 10 200; do

	psql test -c "drop table if exists t"
	psql test -c "create table t (a bigint, b text)"

	psql test -c "insert into t select mod(i, $MODULO), md5(i::text) from generate_series(1,$MODULO * $s) s(i)"

	psql test -c "create index t_a_idx on t (a)"

	psql test -c "vacuum analyze t"

	index_size=`psql -t -A -c "select relpages from pg_class where relname ='t_a_idx'" test`

	for wm in 1MB 4MB 16MB 64MB 256MB; do

		for eic in 0 5 10 50 100; do

			for w in 0 2 4; do

				for p in 1 2 5 10 15; do

					RANGE=$(((MODULO*p)/100))

					for r in `seq 1 10`; do

						START=`psql test -t -A -c "select (($MODULO - $RANGE) * random())::int"`
						END=$((START+RANGE))

						psql test > tmp.log 2>&1 <<EOF
set enable_seqscan=off;
set enable_indexscan = off;
set max_parallel_workers_per_gather = $w;
set effective_io_concurrency = $eic;
set min_parallel_table_scan_size = '8kB';
set min_parallel_index_scan_size = '8kB';
set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set work_mem = '$wm';
explain select count(b) from t where a between $START and $END;
\timing
select count(b)from t where a between $START and $END;
EOF

						psql test > tmp2.log 2>&1 <<EOF
set enable_seqscan=off;
set enable_indexscan=off;
set max_parallel_workers_per_gather = $w;
set effective_io_concurrency = $eic;
set min_parallel_table_scan_size = '8kB';
set min_parallel_index_scan_size = '8kB';
set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set work_mem = '$wm';
explain (analyze, costs off, timing off) select count(b) from t where a between $START and $END;
EOF

						echo "===== btree scale $s workers $w percent $p run $r eic $eic wm $rm sequential =====" >> plans.log 2>&1

						cat tmp.log >> plans.log 2>&1
						cat tmp2.log >> plans.log 2>&1

						time=`cat tmp.log | grep Time | awk '{print $2}'`
						planned=`cat tmp2.log | grep 'Workers Planned' | awk '{print $3}'`
						launched=`cat tmp2.log | grep 'Workers Launched' | awk '{print $3}'`
						removed=`cat tmp2.log | grep 'Rows Removed by Index Recheck' | awk '{print $6}'`

						pbhs=`cat tmp2.log | grep 'Parallel Bitmap Heap Scan' | wc -l`
						pbis=`cat tmp2.log | grep 'Parallel Bitmap Index Scan' | wc -l`

						if [ "$pbhs" == "0" ]; then
							rows=`cat tmp2.log | grep 'Bitmap Heap Scan' | awk '{print $8}' | sed 's/rows=//'`
						else
							rows=`cat tmp2.log | grep 'Parallel Bitmap Heap Scan' | awk '{print $9}' | sed 's/rows=//'`
						fi

						if [ "$planned" == "" ]; then
							planned=0
							launched=0
						fi

						echo btree sequential $s $index_size $wm $eic $w $p $r $START $END $time $planned $launched $pbhs $pbis $removed $rows >> results-btree.csv

					done

				done

			done

		done

	done

	psql test -c "drop table if exists t"
	psql test -c "create table t (a bigint, b text)"

	# scale is a multiple of 100MB
	psql test -c "select generate_data($s * 128 * 100, $ROWS_PER_PAGE)"

	psql test -c "create index t_a_idx on t (a)"

	psql test -c "vacuum analyze t"

	index_size=`psql -t -A -c "select relpages from pg_class where relname ='t_a_idx'" test`

	for wm in 1MB 4MB 16MB 64MB 256MB; do

		for eic in 0 5 10 50 100; do

			for w in 0 2 4; do

				for p in 1 2 5 10 15; do

					MODULO=$((s*100*128))
					RANGE=$(((MODULO*p)/100))

					for r in `seq 1 10`; do

						START=`psql test -t -A -c "select (($MODULO - $RANGE) * random())::int"`
						END=$((START+RANGE))

						psql test > tmp.log 2>&1 <<EOF
set enable_seqscan=off;
set enable_indexscan = off;
set max_parallel_workers_per_gather = $w;
set effective_io_concurrency = $eic;
set min_parallel_table_scan_size = '8kB';
set min_parallel_index_scan_size = '8kB';
set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set work_mem = '$wm';
explain select count(b) from t where a between $START and $END;
\timing
select count(b)from t where a between $START and $END;
EOF

						psql test > tmp2.log 2>&1 <<EOF
set enable_seqscan=off;
set enable_indexscan=off;
set max_parallel_workers_per_gather = $w;
set effective_io_concurrency = $eic;
set min_parallel_table_scan_size = '8kB';
set min_parallel_index_scan_size = '8kB';
set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set work_mem = '$wm';
explain (analyze, costs off, timing off) select count(b) from t where a between $START and $END;
EOF

						echo "===== btree scale $s workers $w percent $p run $r eic $eic wm $rm random =====" >> plans.log 2>&1

						cat tmp.log >> plans.log 2>&1
						cat tmp2.log >> plans.log 2>&1

						time=`cat tmp.log | grep Time | awk '{print $2}'`
						planned=`cat tmp2.log | grep 'Workers Planned' | awk '{print $3}'`
						launched=`cat tmp2.log | grep 'Workers Launched' | awk '{print $3}'`
						removed=`cat tmp2.log | grep 'Rows Removed by Index Recheck' | awk '{print $6}'`

						pbhs=`cat tmp2.log | grep 'Parallel Bitmap Heap Scan' | wc -l`
						pbis=`cat tmp2.log | grep 'Parallel Bitmap Index Scan' | wc -l`

						if [ "$pbhs" == "0" ]; then
							rows=`cat tmp2.log | grep 'Bitmap Heap Scan' | awk '{print $8}' | sed 's/rows=//'`
						else
							rows=`cat tmp2.log | grep 'Parallel Bitmap Heap Scan' | awk '{print $9}' | sed 's/rows=//'`
						fi

						if [ "$planned" == "" ]; then
							planned=0
							launched=0
						fi

						echo btree random $s $index_size $wm $eic $w $p $r $START $END $time $planned $launched $pbhs $pbis $removed $rows >> results-btree.csv

					done

				done

			done

		done

	done

done