#!/bin/bash

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, rows bigint, pct double precision, target int, method text, mode text, x text, explain_text text)"

psql 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 '127.0.0.1', 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 't', analyze_sampling 'off')"

psql test < explain.sql

id=0

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

for r in `seq 1 10`; do

	for n in 10000 100000 1000000 10000000 100000000; do

		psql test -c "truncate t"
		psql 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 test -c "vacuum analyze t"
		psql test -c "checkpoint"

		for m in off auto random system bernoulli; do

			psql test -c "alter foreign table f options (set analyze_sampling '$m')"

			psql test -c "\d+ f"

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

				t=$((n*p/100/300))

				echo "PERCENT: rows $n pct $p target $t"

				if [ "$t" -lt 1 ]; then
					echo "target $t, skipping"
					continue
				fi

				if [ "$t" -gt 10000 ]; then
					echo "target $t, skipping"
					continue
				fi

				if [ "$m" == "off" ]; then
					run_query "analyze t" $n $p $t "local" "local" "PERCENT"
				fi

				run_query "analyze f" $n $p $t $m "remote" "PERCENT"

			done

			# loop targets
			for t in 1 10 50 100 250 500 750 1000 2500 5000 7500 10000; do

				p=$((t*300*100/$n))

				echo "TARGET: rows $n pct $p target $t"

				if [ "$p" -gt 100 ]; then
					echo "percent $p, skipping"
					continue
				fi

				if [ "$m" == "off" ]; then
					run_query "analyze t" $n $p $t "local" "local" "TARGET"
				fi

				run_query "analyze f" $n $p $t $m "remote" "TARGET"

			done

		done

	done

done