sortbenchall.sh.txt

#!/bin/bash

dbname=postgres
mod=100
sec=5

psql -c "alter system set max_parallel_workers_per_gather TO 0;" $dbname
psql -c "select pg_reload_conf();" $dbname

for mem in "4MB" "4GB"
do

        psql -c "alter system set work_mem TO '$mem';" $dbname
        psql -c "select pg_reload_conf();" $dbname
        psql -c "show work_mem" $dbname

	sec=10

	for sql in "select * from tenk1 order by two offset 1000000" "select * from tenk1 order by tenthous offset 1000000" "select * from tenk1 order by stringu1 offset 1000000" "select * from tenk1 order by stringu2 offset 1000000" "select * from tenk1 order by twenty offset 1000000" "select twenty,sum(unique1 order by unique1) from tenk1 group by twenty" "select unique1,sum(twenty order by twenty) from tenk1 group by unique1"
	do
		echo "$sql" > bench.sql
		
		for i in {1..3}
		do
			pgbench -n -f bench.sql -T $sec regression | grep tps
		done
	done
	
	sec=5

	for sql in "select * from t order by a offset 1000000000"
	do
		for records in 10000 1000000
		do
                        psql -c "drop table if exists t" $dbname
                        psql -c "create table t (a bigint not null)" $dbname
                        psql -c "insert into t select x % $mod from generate_series(1,$records) x" $dbname
                        psql -c "vacuum freeze t" $dbname
                        for i in {1..32}
                        do
                                echo "Running $sql with $records rows and $i column(s) for $sec with work_mem $mem"
                                psql -c "explain (analyze, timing off) $sql" $dbname | grep -E "Memory|Disk"
                                echo "$sql" > bench.sql
                                for loops in {1..3}
                                do
                                       pgbench -n -M prepared -T $sec -f bench.sql $dbname | grep tps
				       #pgbench -n -M prepared -t 1 -f bench.sql $dbname | grep -E "tps|blockSize"
                                done
                                psql -c "alter table t add column c$i bigint" $dbname
                                psql -c "update t set c$i = a" $dbname
                                psql -c "vacuum full t" $dbname
                                psql -c "vacuum freeze t" $dbname
                        done
                done
       done
done