#! /bin/sh

dbname="postgres"
function insert_result() {
	psql ${dbname} -f - > /dev/null <<EOF
insert into result select $1, $2, $3, c.relpages, s.n_live_tup, c.reltuples, (select count(*) from t), reltuples::float / (select count(*) from t) from pg_stat_user_tables s, pg_class c where s.relname = 't' and c.relname = 't';
EOF
}

function vac_with_bufs() {
	psql ${dbname} -c "vacuum verbose t" |&  egrep "INFO: *\"t\": found " | sed -e 's/^.* versions in \([0-9]*\) .*$/\1/'
}

function update_result_bufs() {
	local test_no=$1
	local delratio=$2
	local vac_no=$3
	local bufs=$4

	psql ${dbname} -c "update result set bufs=${bufs} where \"#\"=$test_no and \"del%\"=$delratio and \"##\"=$vac_no" >/dev/null
}

function store_result() {
	local test_no=$1
	local delratio=$2
	scanned_bufs=`vac_with_bufs`
	insert_result $test_no $delratio 1
	update_result_bufs $test_no $delratio 1 $scanned_bufs
#	scanned_bufs=`vac_with_bufs`
#	insert_result $test_no $delratio 2
#	update_result_bufs $test_no $delratio 2 $scanned_bufs
}


function test1() {
	local delratio=$1

	echo "test1 ratio = $delratio"
	psql ${dbname} -f - > /dev/null <<EOF
drop table if exists t;
create table t (a int, b int);
insert into t (select a, (random() * 100000)::int from generate_series((select count(*) from t) + 1, $nrows) a);
update t set b = b + 1 where a <  (select count(*) from t) * 0.7;
vacuum t;
delete from t where a < (select count(*) from t) * $delratio;
EOF

	store_result 1 $delratio
}

function test2() {
	local delratio=$1

	echo "test2 ratio = $delratio"
	psql ${dbname} -f - > /dev/null <<EOF
drop table if exists t;
create table t (a int, b text);
insert into t (select a, 'abcdefg' from generate_series((select count(*) from t) + 1, $nrows) a);
update t set b = repeat('abcdefghij', 250) where a <  (select count(*) from t) * 0.7;
vacuum t;
delete from t where a < (select count(*) from t) * $delratio;
EOF
	store_result 2 $delratio
}

psql ${dbname} -f - > /dev/null <<EOF
drop table if exists result;
create table result ("#" int, "del%" float, "##" int, pages int, n_live_tup int, "tups est" int, "tups real" int, "est/real" numeric(10, 3), bufs int default 0);
EOF
nrows=1000000
test1 0.1
test1 0.2
test1 0.3
test1 0.4
test1 0.5
test1 0.6
test1 0.7
test1 0.8
test1 0.9
test1 0.95
test1 0.99

nrows=100000
test2 0.1
test2 0.2
test2 0.3
test2 0.4
test2 0.5
test2 0.6
test2 0.7
test2 0.8
test2 0.9
test2 0.95
test2 0.99

psql ${dbname} -c 'select * from result order by "#", "del%", "##"'