bench_windowsort.sh.txt

#!/bin/bash

psql -c "create table if not exists ab (a int, b int);" postgres

for rows in 1000000 10000000
do
	for mod in 1 10 100 1000 10000 100000 1000000
	do
		for selectrows in "select x%$mod,x from generate_Series(1,$rows) x;" "select x / ($rows / $mod) ,x from generate_Series(1,$rows) x;" "select x % $mod * random(),x from generate_Series(1,$rows) x;"
		do
			psql -c "truncate table ab;" postgres > /dev/null
			psql -c "insert into ab $selectrows" postgres > /dev/null
			psql -c "vacuum freeze ab;" postgres > /dev/null
			psql -c "select pg_prewarm('ab');" postgres > /dev/null
			echo "select a,b,row_number() over (order by a) from ab order by a,b" > bench.sql
			
			psql -c "alter system set enable_sort_pushdown = off;" postgres > /dev/null
			psql -c "select pg_reload_conf();" postgres > /dev/null
			echo -n "$rows $mod Master: "
			pgbench -n -f bench.sql -T 10 -M prepared postgres | grep latency

			psql -c "alter system set enable_sort_pushdown = on;" postgres > /dev/null
			psql -c "select pg_reload_conf();" postgres > /dev/null
			echo -n "$rows $mod Patched: "
			pgbench -n -f bench.sql -T 10 -M prepared postgres | grep latency
		done
	done
done