BM-perf-test-misc-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=5
xacts=500

###for records in 100
for records in 1000000
do
for pattern in 0_1 null_1 s95 p5 asc desc organ
	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
			0_1      ) $PSQL "select setseed(0.935349); insert into tsort select random(0, 1000) % 2 from generate_series(1,$records) i" ;;
			null_1   ) $PSQL "select setseed(0.935349); insert into tsort select case when random() < 0.5 then NULL else 1 end from generate_series(1,$records) i" ;;
			s95      ) $PSQL "insert into tsort select i from generate_series(1,$records / 20 * 19) i; select setseed(0.935349); insert into tsort select random(1, $records) from generate_series(1,$records / 20) i" ;;
			p5       ) $PSQL "select setseed(0.935349); with r (rand, i) as (select random(-1000, 1000), i from generate_series(1,$records,1) i ) insert into tsort select case when r.rand < -950 or r.rand > 950 then r.rand else 0 end from r;" ;;
			asc      ) $PSQL "insert into tsort select i from generate_series(1,$records) i" ;;
			desc     ) $PSQL "insert into tsort select i from generate_series($records, 1, -1) i" ;;
			organ    ) $PSQL "insert into tsort select i from generate_series(1,$records / 2) i; insert into tsort select i from generate_series($records / 2, 1, -1) i;" ;;
			* ) echo "Error: unknown pattern"; exit 2;;
		esac

###debug				$PSQL "select * from tsort" >> results.txt
		sql="select x from tsort order by x offset 1_000_000_000;"
#				$PSQL "alter system set wip_radix_sort = 'on';" #XXX
#				$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