kmoppel/analytical_queries.sh

## analytical_queries.sh
#!/bin/bash

# Assuming that testclusters are on localhost and $USER can start / stop clusters
# Results DB used for storing pg_stat_statement snapshots from test instances should be running already
export PGHOST=/var/run/postgresql
export PGUSER=postgres
export PGDATABASE=postgres

PGBENCH=/usr/lib/postgresql/12/bin/pgbench
PSQL=/usr/lib/postgresql/12/bin/psql

#  Add/remove hosts as needed here !!!
RESULTS_STORE="-p 6432"


connection_str[0]="-p 5432"
connection_name[0]="11.5"
connection_str[1]='-p 5433'
connection_name[1]="12.0"
restart_cmd[0]='pg_ctlcluster 11 main restart'
restart_cmd[1]='pg_ctlcluster 12 main restart'
stop_cmd[0]='pg_ctlcluster 11 main stop'
stop_cmd[1]='pg_ctlcluster 12 main stop'

TEST_MODE="analytics"
SCALES="100 1000"	# 100 = 10mio pgbench_accounts rows
PGBENCH_CLIENTS="1 2"
TEST_DURATION=7200 # seconds
DO_PGBENCH_INIT=1
DROP_TABLES_AFTER_TESTING=0	# when enough disk only for 1 instance

TEST_QUERIES=$(cat <<-HERE
SELECT avg(abalance) FROM pgbench_accounts JOIN pgbench_branches USING (bid) WHERE bid % 2 = 0;\n
SELECT COUNT(DISTINCT aid) FROM pgbench_accounts;\n
SELECT count(*) FROM pgbench_accounts JOIN pgbench_accounts_copy USING (aid) WHERE aid % 2 = 0;\n
SELECT sum(a.abalance) FROM pgbench_accounts a JOIN pgbench_accounts_copy USING (aid) WHERE a.bid % 10 = 0;\n
HERE
)


# Test start / stop cmd and conn str + pg_stat_statement setup
i=0
for conn_str in "${connection_str[@]}" ; do
	${restart_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not restart ${connection_name[i]}"
		exit 1
	fi

	$PSQL $conn_str -qXc "select 1" &>/dev/null
	if [ "$?" -ne 0 ] ; then
		echo "could not connect to $conn_str, check connection params. exiting..."
		exit 1
	fi

	$PSQL $conn_str -qXc "create extension if not exists pg_stat_statements"
	if [ $? -ne 0 ] ; then
		echo "could not create pg_stat_statements. check shared_preload_libraries"
		exit 1
	fi

	${stop_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not stop ${connection_name[i]}"
		exit 1
	fi
i=$((i + 1))
done

echo "*** creating TEST RESULTS table my_pg_stat_statements if not existing ***"
$PSQL $RESULTS_STORE -qc "create table if not exists my_pg_stat_statements as select ''::text as testset, ''::text as mode, 0 as scale, 0 as clients, now() as created_on, * from pg_stat_statements where false"
echo "deleting possible old results for test: $TEST_MODE"
$PSQL $RESULTS_STORE -qc "delete from my_pg_stat_statements where mode = '$TEST_MODE'"


i=0
for conn_str in "${connection_str[@]}" ; do

	echo ""
	echo "*** TESTING ${connection_name[i]} (conn_str $conn_str) ***"
	echo ""

	echo "restarting..."
	${restart_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not stop ${connection_name[i]}"
		exit 1
	fi

	# Drop old pgbench accounts tables if any
	# $PSQL $conn_str -qXc "drop table if exists pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers;"

	for SCALE in $SCALES ; do

		echo ""
		echo "*** SCALE $SCALE ***"
		echo ""

		if [ "$DO_PGBENCH_INIT" -gt 0 ] ; then

			echo "init scale $SCALE ..."
			$PGBENCH "$conn_str" -i -q --foreign-keys --unlogged-tables -s $SCALE
			$PSQL $conn_str -qXc "drop table if exists pgbench_accounts_copy"
			$PSQL $conn_str -qXc "create unlogged table if not exists pgbench_accounts_copy as select * from pgbench_accounts"
			$PSQL $conn_str -qXc "create unique index if not exists pgbench_accounts_copy_aid_idx ON pgbench_accounts_copy (aid)"

		fi

		$PSQL $conn_str -qXc "vacuum analyze"
		$PSQL $conn_str -qXc "checkpoint"
		echo "sleeping 10..."
		sleep 10


		for CLIENTS in $PGBENCH_CLIENTS ; do

			echo ""
			echo "*** CLIENTS $CLIENTS ***"
			echo ""

			echo "testing ${connection_name[i]} - scale $SCALE, duration $TEST_DURATION s ..."
			$PSQL $conn_str -qXc "select pg_stat_statements_reset()"

			echo -e $TEST_QUERIES |  $PGBENCH $conn_str -c $CLIENTS --random-seed=666 -T $TEST_DURATION -f-


			# Store results for this run
			echo "storing pg_stat_statements results for ${connection_name[i]}..."
			$PSQL $conn_str -qXc "copy (select '${connection_name[$i]}', '$TEST_MODE', ${SCALE:-NULL}, ${CLIENTS:-1}, now(), * from pg_stat_statements where calls > 1 and query ~* '^(INSERT|UPDATE|SELECT).*pgbench_') to stdout" | $PSQL $RESULTS_STORE -qXc "copy my_pg_stat_statements from stdin"

		done # clients

	done # scale

	if [ $DROP_TABLES_AFTER_TESTING -gt 0 ]; then
		# drop all created tables
		$PSQL $conn_str -qXc "drop table if exists pgbench_accounts, pgbench_accounts_copy, pgbench_branches, pgbench_tellers, pgbench_history"
	fi

	echo "stopping..."
	${stop_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not stop ${connection_name[i]}"
		exit 1
	fi

i=$((i + 1))


done # conn str


echo "done"

## analyze_results.sql
select
*,
avg(testset_mean_diff) over() as avg_testset_mean_diff,
avg(testset_stddev_diff) over() as testset_stddev_diff
from (

select
  *,
  case when lag(testset_mean) over w2 = 0 then 0 else round((100 * (testset_mean - lag(testset_mean) over w2) / lag(testset_mean) over w2)::numeric, 1) end as testset_mean_diff,
  case when lag(testset_stddev) over w2 = 0 then 0 else round((100 * (testset_stddev - lag(testset_stddev) over w2) / lag(testset_stddev) over w2)::numeric, 1) end as testset_stddev_diff
from (

select
*,
make_interval(secs := (total_time / 1000)::int) as runtime,
case when lag(mean_time) over w = 0 then 0 else round((100 * (mean_time - lag(mean_time) over w) / lag(mean_time) over w)::numeric, 1) end as mean_diff_pct,
case when lag(stddev_time) over w = 0 then 0 else round((100 * (stddev_time - lag(stddev_time) over w) / lag(stddev_time) over w)::numeric, 1) end as stddev_diff_pct,
round(avg(mean_time) over(partition by testset, query), 4) as testset_mean,
round(avg(stddev_time) over(partition by testset, query), 4) as testset_stddev

from (

select
  testset,
  mode,
  scale,
  clients,
  mean_time::numeric,
  stddev_time::numeric,
  total_time::int8,
  calls,
  case when shared_blks_hit + shared_blks_read > 0 then round((shared_blks_hit / (shared_blks_hit + shared_blks_read)::numeric) * 100, 1) else 0 end as hit_rate, created_on,
  query::varchar(80)

from
  my_pg_stat_statements
where
  query ~ 'pgbench'
) x window w as (partition by mode, query, scale, clients order by testset) order by mode, query, scale, clients, testset

) y
window w2 as (partition by mode, query, scale, clients order by testset)
) z
order by mode, query, scale, clients, testset;

## pgbench_default.sh
#!/bin/bash

# Assuming that testclusters are on localhost and $USER can start / stop clusters
# Results DB used for storing pg_stat_statement snapshots from test instances should be running already
export PGHOST=/var/run/postgresql
export PGUSER=postgres
export PGDATABASE=postgres

PGBENCH=/usr/lib/postgresql/12/bin/pgbench
PSQL=/usr/lib/postgresql/12/bin/psql

#  Add/remove hosts as needed here !!!
RESULTS_STORE="-p 6432"


connection_str[0]="-p 5432"
connection_name[0]="11.5"
connection_str[1]='-p 5433'
connection_name[1]="12.0"
restart_cmd[0]='pg_ctlcluster 11 main restart'
restart_cmd[1]='pg_ctlcluster 12 main restart'
stop_cmd[0]='pg_ctlcluster 11 main stop'
stop_cmd[1]='pg_ctlcluster 12 main stop'

TEST_MODE="pgbench_plain"
SCALES="100 1000"	# 100 = 10mio pgbench_accounts rows
PGBENCH_CLIENTS="1 2 4"
TEST_DURATION=7200	# seconds
DO_PGBENCH_INIT=1
DROP_TABLES_AFTER_TESTING=0	# when enough disk only for 1 instance


# Test start / stop cmd and conn str + pg_stat_statement setup
i=0
for conn_str in "${connection_str[@]}" ; do
	${restart_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not restart ${connection_name[i]}"
		exit 1
	fi

	$PSQL $conn_str -qXc "select 1" &>/dev/null
	if [ "$?" -ne 0 ] ; then
		echo "could not connect to $conn_str, check connection params. exiting..."
		exit 1
	fi

	$PSQL $conn_str -qXc "create extension if not exists pg_stat_statements"
	if [ $? -ne 0 ] ; then
		echo "could not create pg_stat_statements. check shared_preload_libraries"
		exit 1
	fi

	${stop_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not stop ${connection_name[i]}"
		exit 1
	fi
i=$((i + 1))
done

echo "*** creating TEST RESULTS table my_pg_stat_statements if not existing ***"
$PSQL $RESULTS_STORE -qc "create table if not exists my_pg_stat_statements as select ''::text as testset, ''::text as mode, 0 as scale, 0 as clients, now() as created_on, * from pg_stat_statements where false"
echo "deleting possible old results for test: $TEST_MODE"
$PSQL $RESULTS_STORE -qc "delete from my_pg_stat_statements where mode = '$TEST_MODE'"


i=0
for conn_str in "${connection_str[@]}" ; do


	echo ""
	echo "*** TESTING ${connection_name[i]} (conn_str $conn_str) ***"
	echo ""

	echo "restarting..."
	${restart_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not stop ${connection_name[i]}"
		exit 1
	fi

	# Drop old pgbench accounts tables if any
	# $PSQL $conn_str -qXc "drop table if exists pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers;"

	for SCALE in $SCALES ; do

		echo ""
		echo "*** SCALE $SCALE ***"
		echo ""

		if [ "$DO_PGBENCH_INIT" -gt 0 ] ; then

			echo "init scale $SCALE ..."
			$PGBENCH "$conn_str" -i -q --foreign-keys -s $SCALE

		fi

		$PSQL $conn_str -qXc "vacuum analyze"
		$PSQL $conn_str -qXc "checkpoint"
		echo "sleeping 10..."
		sleep 10


		for CLIENTS in $PGBENCH_CLIENTS ; do

			echo ""
			echo "*** CLIENTS $CLIENTS ***"
			echo ""

			echo "testing ${connection_name[i]} - scale $SCALE, duration $TEST_DURATION s ..."
			$PSQL $conn_str -qXc "select pg_stat_statements_reset()"

			$PGBENCH $conn_str -c $CLIENTS --random-seed=666 -T $TEST_DURATION

			# Store results for this run
			echo "storing pg_stat_statements results for ${connection_name[i]}..."
			$PSQL $conn_str -qXc "copy (select '${connection_name[$i]}', '$TEST_MODE', ${SCALE:-NULL}, ${CLIENTS:-1}, now(), * from pg_stat_statements where calls > 1 and query ~* '^(INSERT|UPDATE|SELECT).*pgbench_') to stdout" | $PSQL $RESULTS_STORE -qXc "copy my_pg_stat_statements from stdin"

		done # clients

	done # scale

	if [ $DROP_TABLES_AFTER_TESTING -gt 0 ]; then
		# drop all created tables
		$PSQL $conn_str -qXc "drop table if exists pgbench_accounts, pgbench_branches, pgbench_tellers, pgbench_history"
	fi

	echo "stopping..."
	${stop_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not stop ${connection_name[i]}"
		exit 1
	fi

i=$((i + 1))


done # conn str


echo "done"

## pgbench_extra_indexes.sh
#!/bin/bash

# Assuming that testclusters are on localhost and $USER can start / stop clusters
# Results DB used for storing pg_stat_statement snapshots from test instances should be running already
export PGHOST=/var/run/postgresql
export PGUSER=postgres
export PGDATABASE=postgres

PGBENCH=/usr/lib/postgresql/12/bin/pgbench
PSQL=/usr/lib/postgresql/12/bin/psql

#  Add/remove hosts as needed here !!!
RESULTS_STORE="-p 6432"

connection_str[0]='-p 5433'
connection_name[0]="12.0"
connection_str[1]="-p 5432"
connection_name[1]="11.5"
restart_cmd[0]='pg_ctlcluster 12 main restart'
restart_cmd[1]='pg_ctlcluster 11 main restart'
stop_cmd[0]='pg_ctlcluster 12 main stop'
stop_cmd[1]='pg_ctlcluster 11 main stop'

TEST_MODE="pgbench_extra"
SCALES="100 1000"
PGBENCH_CLIENTS="1 2 4"
TEST_DURATION=7200	# seconds
DO_PGBENCH_INIT=1
DROP_TABLES_AFTER_TESTING=0	# when enough disk only for 1 instance


# Test start / stop cmd and conn str + pg_stat_statement setup
i=0
for conn_str in "${connection_str[@]}" ; do
	${restart_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not restart ${connection_name[i]}"
		exit 1
	fi

	$PSQL $conn_str -qXc "select 1" &>/dev/null
	if [ "$?" -ne 0 ] ; then
		echo "could not connect to $conn_str, check connection params. exiting..."
		exit 1
	fi

	$PSQL $conn_str -qXc "create extension if not exists pg_stat_statements"
	if [ $? -ne 0 ] ; then
		echo "could not create pg_stat_statements. check shared_preload_libraries"
		exit 1
	fi

	${stop_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not stop ${connection_name[i]}"
		exit 1
	fi
i=$((i + 1))
done

echo "*** creating TEST RESULTS table my_pg_stat_statements if not existing ***"
$PSQL $RESULTS_STORE -qc "create table if not exists my_pg_stat_statements as select ''::text as testset, ''::text as mode, 0 as scale, 0 as clients, now() as created_on, * from pg_stat_statements where false"
echo "deleting possible old results for test: $TEST_MODE"
$PSQL $RESULTS_STORE -qc "delete from my_pg_stat_statements where mode = '$TEST_MODE'"


i=0
for conn_str in "${connection_str[@]}" ; do


	echo ""
	echo "*** TESTING ${connection_name[i]} (conn_str $conn_str) ***"
	echo ""

	echo "restarting..."
	${restart_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not stop ${connection_name[i]}"
		exit 1
	fi

	for SCALE in $SCALES ; do

		echo ""
		echo "*** SCALE $SCALE ***"
		echo ""

		if [ "$DO_PGBENCH_INIT" -gt 0 ] ; then

			echo "init scale $SCALE ..."
			$PGBENCH "$conn_str" -i -q --foreign-keys -s $SCALE

			$PSQL $conn_str -qXc "create index on pgbench_accounts(bid)"
			$PSQL $conn_str -qXc "create index on pgbench_history(tid)"
			$PSQL $conn_str -qXc "create index on pgbench_history(bid, aid)"
			$PSQL $conn_str -qXc "create index on pgbench_history(mtime)"

		fi

		$PSQL $conn_str -qXc "vacuum analyze"
		$PSQL $conn_str -qXc "checkpoint"
		echo "sleeping 10..."
		sleep 10


		for CLIENTS in $PGBENCH_CLIENTS ; do

			echo ""
			echo "*** CLIENTS $CLIENTS ***"
			echo ""

			### TEST
			# echo 3 | sudo tee /proc/sys/vm/drop_caches	# drop file system cache

			echo "testing ${connection_name[i]} - scale $SCALE, duration $TEST_DURATION s ..."
			$PSQL $conn_str -qXc "select pg_stat_statements_reset()"

			$PGBENCH $conn_str -c $CLIENTS --random-seed=666 -T $TEST_DURATION


			# Store results for this run
			echo "storing pg_stat_statements results for ${connection_name[i]}..."
			$PSQL $conn_str -qXc "copy (select '${connection_name[$i]}', '$TEST_MODE', ${SCALE:-NULL}, ${CLIENTS:-1}, now(), * from pg_stat_statements where calls > 1 and query ~* '^(INSERT|UPDATE|SELECT).*pgbench_') to stdout" | $PSQL $RESULTS_STORE -qXc "copy my_pg_stat_statements from stdin"

		done # clients

	done # scale

	if [ $DROP_TABLES_AFTER_TESTING -gt 0 ]; then
		# drop all created tables
		$PSQL $conn_str -qXc "drop table if exists pgbench_accounts, pgbench_branches, pgbench_tellers, pgbench_history"
	fi

	echo "stopping..."
	${stop_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not stop ${connection_name[i]}"
		exit 1
	fi

i=$((i + 1))


done # conn str


echo "done"

## readonly.sh
#!/bin/bash

# Assuming that testclusters are on localhost and $USER can start / stop clusters
# Results DB used for storing pg_stat_statement snapshots from test instances should be running already
export PGHOST=/var/run/postgresql
export PGUSER=postgres
export PGDATABASE=postgres

PGBENCH=/usr/lib/postgresql/12/bin/pgbench
PSQL=/usr/lib/postgresql/12/bin/psql

#  Add/remove hosts as needed here !!!
RESULTS_STORE="-p 6432"

connection_str[0]="-p 5432"
connection_name[0]="11.5"
connection_str[1]='-p 5433'
connection_name[1]="12.0"
restart_cmd[0]='pg_ctlcluster 11 main restart'
restart_cmd[1]='pg_ctlcluster 12 main restart'
stop_cmd[0]='pg_ctlcluster 11 main stop'
stop_cmd[1]='pg_ctlcluster 12 main stop'

TEST_MODE="readonly"
SCALES="100 1000 5000"	# 100 = 10mio pgbench_accounts rows
PGBENCH_CLIENTS="1 2 4"
TEST_DURATION=7200	# seconds
DO_PGBENCH_INIT=1
DROP_TABLES_AFTER_TESTING=0	# when enough disk only for 1 instance


# Test start / stop cmd and conn str + pg_stat_statement setup
i=0
for conn_str in "${connection_str[@]}" ; do
	${restart_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not restart ${connection_name[i]}"
		exit 1
	fi

	$PSQL $conn_str -qXc "select 1" &>/dev/null
	if [ "$?" -ne 0 ] ; then
		echo "could not connect to $conn_str, check connection params. exiting..."
		exit 1
	fi

	$PSQL $conn_str -qXc "create extension if not exists pg_stat_statements"
	if [ $? -ne 0 ] ; then
		echo "could not create pg_stat_statements. check shared_preload_libraries"
		exit 1
	fi

	${stop_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not stop ${connection_name[i]}"
		exit 1
	fi
i=$((i + 1))
done

echo "*** creating TEST RESULTS table my_pg_stat_statements if not existing ***"
$PSQL $RESULTS_STORE -qc "create table if not exists my_pg_stat_statements as select ''::text as testset, ''::text as mode, 0 as scale, 0 as clients, now() as created_on, * from pg_stat_statements where false"
echo "deleting possible old results for test: $TEST_MODE"
$PSQL $RESULTS_STORE -qc "delete from my_pg_stat_statements where mode = '$TEST_MODE'"


i=0
for conn_str in "${connection_str[@]}" ; do


	echo ""
	echo "*** TESTING ${connection_name[i]} (conn_str $conn_str) ***"
	echo ""

	echo "restarting..."
	${restart_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not stop ${connection_name[i]}"
		exit 1
	fi


	for SCALE in $SCALES ; do

		echo ""
		echo "*** SCALE $SCALE ***"
		echo ""

		if [ "$DO_PGBENCH_INIT" -gt 0 ] ; then

			echo "init scale $SCALE ..."
			$PGBENCH "$conn_str" -i -q --foreign-keys --unlogged-tables -s $SCALE

		fi

		$PSQL $conn_str -qXc "vacuum analyze"
		$PSQL $conn_str -qXc "checkpoint"
		echo "sleeping 10..."
		sleep 10


		for CLIENTS in $PGBENCH_CLIENTS ; do

			echo ""
			echo "*** CLIENTS $CLIENTS ***"
			echo ""

			echo "testing ${connection_name[i]} - scale $SCALE, duration $TEST_DURATION s ..."
			$PSQL $conn_str -qXc "select pg_stat_statements_reset()"

			$PGBENCH $conn_str -c $CLIENTS --random-seed=666 -T $TEST_DURATION -S


			# Store results for this run
			echo "storing pg_stat_statements results for ${connection_name[i]}..."
			$PSQL $conn_str -qXc "copy (select '${connection_name[$i]}', '$TEST_MODE', ${SCALE:-NULL}, ${CLIENTS:-1}, now(), * from pg_stat_statements where calls > 1 and query ~* '^(INSERT|UPDATE|SELECT).*pgbench_') to stdout" | $PSQL $RESULTS_STORE -qXc "copy my_pg_stat_statements from stdin"

		done # clients

	done # scale

	if [ $DROP_TABLES_AFTER_TESTING -gt 0 ]; then
		# drop all created tables
		$PSQL $conn_str -qXc "drop table if exists pgbench_accounts, pgbench_branches, pgbench_tellers, pgbench_history"
	fi

	echo "stopping..."
	${stop_cmd[i]}
	if [ $? -ne 0 ] ; then
		echo "could not stop ${connection_name[i]}"
		exit 1
	fi

i=$((i + 1))


done # conn str


echo "done"
	#!/bin/bash

	# Assuming that testclusters are on localhost and $USER can start / stop clusters
	# Results DB used for storing pg_stat_statement snapshots from test instances should be running already
	export PGHOST=/var/run/postgresql
	export PGUSER=postgres
	export PGDATABASE=postgres

	PGBENCH=/usr/lib/postgresql/12/bin/pgbench
	PSQL=/usr/lib/postgresql/12/bin/psql

	# Add/remove hosts as needed here !!!
	RESULTS_STORE="-p 6432"


	connection_str[0]="-p 5432"
	connection_name[0]="11.5"
	connection_str[1]='-p 5433'
	connection_name[1]="12.0"
	restart_cmd[0]='pg_ctlcluster 11 main restart'
	restart_cmd[1]='pg_ctlcluster 12 main restart'
	stop_cmd[0]='pg_ctlcluster 11 main stop'
	stop_cmd[1]='pg_ctlcluster 12 main stop'

	TEST_MODE="analytics"
	SCALES="100 1000" # 100 = 10mio pgbench_accounts rows
	PGBENCH_CLIENTS="1 2"
	TEST_DURATION=7200 # seconds
	DO_PGBENCH_INIT=1
	DROP_TABLES_AFTER_TESTING=0 # when enough disk only for 1 instance

	TEST_QUERIES=$(cat <<-HERE
	SELECT avg(abalance) FROM pgbench_accounts JOIN pgbench_branches USING (bid) WHERE bid % 2 = 0;\n
	SELECT COUNT(DISTINCT aid) FROM pgbench_accounts;\n
	SELECT count(*) FROM pgbench_accounts JOIN pgbench_accounts_copy USING (aid) WHERE aid % 2 = 0;\n
	SELECT sum(a.abalance) FROM pgbench_accounts a JOIN pgbench_accounts_copy USING (aid) WHERE a.bid % 10 = 0;\n
	HERE
	)


	# Test start / stop cmd and conn str + pg_stat_statement setup
	i=0
	for conn_str in "${connection_str[@]}" ; do
	${restart_cmd[i]}
	if [ $? -ne 0 ] ; then
	echo "could not restart ${connection_name[i]}"
	exit 1
	fi

	$PSQL $conn_str -qXc "select 1" &>/dev/null
	if [ "$?" -ne 0 ] ; then
	echo "could not connect to $conn_str, check connection params. exiting..."
	exit 1
	fi

	$PSQL $conn_str -qXc "create extension if not exists pg_stat_statements"
	if [ $? -ne 0 ] ; then
	echo "could not create pg_stat_statements. check shared_preload_libraries"
	exit 1
	fi

	${stop_cmd[i]}
	if [ $? -ne 0 ] ; then
	echo "could not stop ${connection_name[i]}"
	exit 1
	fi
	i=$((i + 1))
	done

	echo "* creating TEST RESULTS table my_pg_stat_statements if not existing *"
	$PSQL $RESULTS_STORE -qc "create table if not exists my_pg_stat_statements as select ''::text as testset, ''::text as mode, 0 as scale, 0 as clients, now() as created_on, * from pg_stat_statements where false"
	echo "deleting possible old results for test: $TEST_MODE"
	$PSQL $RESULTS_STORE -qc "delete from my_pg_stat_statements where mode = '$TEST_MODE'"


	i=0
	for conn_str in "${connection_str[@]}" ; do

	echo ""
	echo "* TESTING ${connection_name[i]} (conn_str $conn_str) *"
	echo ""

	echo "restarting..."
	${restart_cmd[i]}
	if [ $? -ne 0 ] ; then
	echo "could not stop ${connection_name[i]}"
	exit 1
	fi

	# Drop old pgbench accounts tables if any
	# $PSQL $conn_str -qXc "drop table if exists pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers;"

	for SCALE in $SCALES ; do

	echo ""
	echo "* SCALE $SCALE *"
	echo ""

	if [ "$DO_PGBENCH_INIT" -gt 0 ] ; then

	echo "init scale $SCALE ..."
	$PGBENCH "$conn_str" -i -q --foreign-keys --unlogged-tables -s $SCALE
	$PSQL $conn_str -qXc "drop table if exists pgbench_accounts_copy"
	$PSQL $conn_str -qXc "create unlogged table if not exists pgbench_accounts_copy as select * from pgbench_accounts"
	$PSQL $conn_str -qXc "create unique index if not exists pgbench_accounts_copy_aid_idx ON pgbench_accounts_copy (aid)"

	fi

	$PSQL $conn_str -qXc "vacuum analyze"
	$PSQL $conn_str -qXc "checkpoint"
	echo "sleeping 10..."
	sleep 10


	for CLIENTS in $PGBENCH_CLIENTS ; do

	echo ""
	echo "* CLIENTS $CLIENTS *"
	echo ""

	echo "testing ${connection_name[i]} - scale $SCALE, duration $TEST_DURATION s ..."
	$PSQL $conn_str -qXc "select pg_stat_statements_reset()"

	echo -e $TEST_QUERIES \| $PGBENCH $conn_str -c $CLIENTS --random-seed=666 -T $TEST_DURATION -f-


	# Store results for this run
	echo "storing pg_stat_statements results for ${connection_name[i]}..."
	$PSQL $conn_str -qXc "copy (select '${connection_name[$i]}', '$TEST_MODE', ${SCALE:-NULL}, ${CLIENTS:-1}, now(), * from pg_stat_statements where calls > 1 and query ~* '^(INSERT\|UPDATE\|SELECT).*pgbench_') to stdout" \| $PSQL $RESULTS_STORE -qXc "copy my_pg_stat_statements from stdin"

	done # clients

	done # scale

	if [ $DROP_TABLES_AFTER_TESTING -gt 0 ]; then
	# drop all created tables
	$PSQL $conn_str -qXc "drop table if exists pgbench_accounts, pgbench_accounts_copy, pgbench_branches, pgbench_tellers, pgbench_history"
	fi

	echo "stopping..."
	${stop_cmd[i]}
	if [ $? -ne 0 ] ; then
	echo "could not stop ${connection_name[i]}"
	exit 1
	fi

	i=$((i + 1))


	done # conn str


	echo "done"
	select
	*,
	avg(testset_mean_diff) over() as avg_testset_mean_diff,
	avg(testset_stddev_diff) over() as testset_stddev_diff
	from (

	select
	*,
	case when lag(testset_mean) over w2 = 0 then 0 else round((100 * (testset_mean - lag(testset_mean) over w2) / lag(testset_mean) over w2)::numeric, 1) end as testset_mean_diff,
	case when lag(testset_stddev) over w2 = 0 then 0 else round((100 * (testset_stddev - lag(testset_stddev) over w2) / lag(testset_stddev) over w2)::numeric, 1) end as testset_stddev_diff
	from (

	select
	*,
	make_interval(secs := (total_time / 1000)::int) as runtime,
	case when lag(mean_time) over w = 0 then 0 else round((100 * (mean_time - lag(mean_time) over w) / lag(mean_time) over w)::numeric, 1) end as mean_diff_pct,
	case when lag(stddev_time) over w = 0 then 0 else round((100 * (stddev_time - lag(stddev_time) over w) / lag(stddev_time) over w)::numeric, 1) end as stddev_diff_pct,
	round(avg(mean_time) over(partition by testset, query), 4) as testset_mean,
	round(avg(stddev_time) over(partition by testset, query), 4) as testset_stddev

	from (

	select
	testset,
	mode,
	scale,
	clients,
	mean_time::numeric,
	stddev_time::numeric,
	total_time::int8,
	calls,
	case when shared_blks_hit + shared_blks_read > 0 then round((shared_blks_hit / (shared_blks_hit + shared_blks_read)::numeric) * 100, 1) else 0 end as hit_rate, created_on,
	query::varchar(80)

	from
	my_pg_stat_statements
	where
	query ~ 'pgbench'
	) x window w as (partition by mode, query, scale, clients order by testset) order by mode, query, scale, clients, testset

	) y
	window w2 as (partition by mode, query, scale, clients order by testset)
	) z
	order by mode, query, scale, clients, testset;