#!/bin/bash

REMOTE=192.168.0.164
LOCAL=127.0.0.1

dropdb --if-exists test
createdb test

psql test -c "create table results (query_id int primary key, start_time timestamptz not null, end_time timestamptz, pid int not null, query_text text, view_text text, rows bigint, pct int, method text, mode text, explain_text text)"
psql -h $REMOTE test -c "create table results (query_id int primary key, start_time timestamptz not null, end_time timestamptz, pid int not null, query_text text, view_text text, rows bigint, pct int, method text, mode text, explain_text text)"

psql -h $REMOTE test -c "create table t (c1 bigint, c2 bigint, c3 bigint, c4 bigint, c5 bigint, c6 bigint, c7 bigint, c8 bigint, c9 bigint, c10 bigint) with (fillfactor = 50)"

psql test -c "create extension postgres_fdw"

psql test -c "create server s foreign data wrapper postgres_fdw options (host '$REMOTE', dbname 'test')"
psql test -c "create user mapping for postgres server s options (user 'postgres')"
psql test -c "create foreign table f (c1 bigint, c2 bigint, c3 bigint, c4 bigint, c5 bigint, c6 bigint, c7 bigint, c8 bigint, c9 bigint, c10 bigint) server s options (table_name 'v')"

psql -h $REMOTE test < explain.sql
psql test < explain.sql

id=0

function run_query {
	id=$((id+1))
	psql test -h $1 -t -A <<EOF
insert into results values ($id, now(), NULL, pg_backend_pid(), '$2', '$7', $3, $4, '$5', '$6', NULL);
set auto_explain.log_min_duration = 0;
$2;
update results set end_time = now(), explain_text = get_explain('$2') where query_id = $id;
EOF
}

for r in `seq 1 10`; do

	for n in 10000 100000 1000000 10000000 100000000; do

		psql -h $REMOTE test -c "truncate t"
		psql -h $REMOTE test -c "insert into t select 1000000 * random(), 1000000 * random(), 1000000 * random(), 1000000 * random(), 1000000 * random(), 1000000 * random(), 1000000 * random(), 1000000 * random(), 1000000 * random(), 1000000 * random() from generate_series(1,$n)"

		sleep 1

		psql -h $REMOTE test -c "vacuum t"
		psql -h $REMOTE test -c "checkpoint"

		for m in full random system bernoulli; do

			if [ "$m" == "full" ]; then

				v="create view v as select * from t"

				psql -h $REMOTE test -c "drop view if exists v"
				psql -h $REMOTE test -c "$v"

				run_query $REMOTE "select * from (select * from v offset $n) foo" $n 1 $m "local" "$v"
				run_query $LOCAL "select * from (select * from (select * from f offset 0) foo offset $n) bar" $n 1 $m "remote" "$v"

				continue

			fi

			for p in 1 10 20 30 40 50 60 70 80 90 100; do

				if [ "$m" == "random" ]; then
					v="create view v as select * from t where 100 * random() < $p"
				else
					v="create view v as select * from t tablesample $m ($p)"
				fi

				psql -h $REMOTE test -c "drop view if exists v"
				psql -h $REMOTE test -c "$v"

				run_query $REMOTE "select * from (select * from v offset $n) foo" $n $p $m "local" "$v"
				run_query $LOCAL "select * from (select * from (select * from f offset 0) foo offset $n) bar" $n $p $m "remote" "$v"

			done

		done

	done

done