run-local.sh

application/x-shellscript

Download

#!/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, view_text text, rows bigint, pct int, method text, mode 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 'v')"

psql test < explain.sql

id=0

function run_query {
	id=$((id+1))
	psql test -t -A <<EOF
drop view v;
$6;
insert into results values ($id, now(), NULL, pg_backend_pid(), '$1', '$6', $2, $3, '$4', '$5', NULL);
set auto_explain.log_min_duration = 0;
$1;
update results set end_time = now(), explain_text = get_explain('$1') 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 t"
		psql test -c "checkpoint"

		for m in full random system bernoulli; do

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

				run_query "select * from (select * from v offset $n) foo" $n 1 $m "local" "create view v as select * from t"
				run_query "select * from (select * from (select * from f offset 0) foo offset $n) bar" $n 1 $m "remote" "create view v as select * from t"

				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

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

			done

		done

	done

done