BM-perf-test-rand-stag-sawtooth-20251120.sh

set -e

#==========================
dbname=
#PSQL=echo
PSQL="psql $dbname -c"
#==========================
sleep="sleep 5"
###sleep=

$PSQL "alter system set max_parallel_workers_per_gather TO 0;"
$PSQL "alter system set work_mem TO '1GB';"
$PSQL "select pg_reload_conf();"

$PSQL "create extension if not exists pg_prewarm;"
$PSQL "show shared_buffers;"

offset="offset 1_000_000_000"
###
xacts=500

###for records in 100
for records in 1000000
do
		# todo: asc/desc, z1, p5, s95

		for pattern in sawtooth rand stagger
#		for pattern in rand
		do
#			for mod in 10 $((records+1))
			for mod in 2 3 10 32 100 316 1000 3162 10000 31622 100000 316228 $((records/2)) $((records+1)) # last one keeps the original number
			do
				echo "Records: $records" >> results.txt
				echo "  Pattern: $pattern" >> results.txt
				echo "    Mod: $mod" >> results.txt

				$sleep
				$PSQL "drop table if exists tsort; create unlogged table tsort (x bigint);"
				case $pattern in
					sawtooth ) $PSQL "insert into tsort select i % $mod from generate_series(0,$records - 1) i" ;;
					rand     ) $PSQL "select setseed(0.935349); insert into tsort select random(0, 100_000_000) % $mod from generate_series(0,$records - 1) i" ;;
					stagger  ) $PSQL "insert into tsort select (i::numeric * $mod + i) % $records from generate_series(0,$records - 1) i" ;;
					* ) echo "Error: unknown pattern"; exit 2;;
				esac

#				$PSQL "select * from tsort" >> results.txt #XXX
				sql="select x from tsort order by x offset 1_000_000_000;"
#				$PSQL "explain (analyze, timing off) $sql;" | grep -E "Memory|Disk" #XXX
#				continue # XXX

				#todo: put into function

				$PSQL "vacuum freeze tsort"
				$PSQL "select pg_prewarm('tsort')"

				echo $sql > bench.sql

				echo "standard" >> results.txt
				$sleep
				$PSQL "alter system set wip_radix_sort = 'off';"
				$PSQL "select pg_reload_conf();"
				for i in {1..3}
				do
					pgbench -n -t $xacts -f bench.sql | grep latency >> results.txt
				done

				echo "patch" >> results.txt
				$sleep
				$PSQL "alter system set wip_radix_sort = 'on';"
				$PSQL "select pg_reload_conf();"
				for i in {1..3}
				do
					pgbench -n -t $xacts -f bench.sql | grep latency >> results.txt
				done
			done
		done
done