hashagg_bench.sh.txt

#!/bin/bash

dbname=postgres
secs=10
psql -c "alter system set max_parallel_workers_per_gather = 0;" $dbname > /dev/null
psql -c "alter system set jit = 0;" $dbname > /dev/null
psql -c "select pg_reload_conf();" $dbname > /dev/null
psql -c "create extension if not exists pg_prewarm;" $dbname > /dev/null
psql -c "drop table if exists hashagg;" $dbname > /dev/null
psql -c "create table hashagg (a bigint);" $dbname > /dev/null

for rows in 10000 100000 1000000 10000000
#for rows in 10000000
do
	psql -c "truncate table hashagg;" $dbname > /dev/null
	psql -c "insert into hashagg select a from generate_series(1, $rows) a;" $dbname > /dev/null
	psql -c "vacuum freeze analyze hashagg;" $dbname > /dev/null
	psql -c "select pg_prewarm('hashagg');" $dbname > /dev/null

	for work_mem in '512kB' '1MB' '2MB' '4MB' '8MB' '16MB' '32MB' '64MB' '128MB' '256MB'
	do
		psql -c "alter system set work_mem = '$work_mem';" $dbname > /dev/null
		psql -c "select pg_reload_conf();" $dbname > /dev/null
		echo "select a,count(*) from hashagg group by a;" > bench.sql
		psql -c "explain analyze select a,count(*) from hashagg group by a;" $dbname | grep "Batches" | tr '\n' ' '
		echo -n "$rows $work_mem "
		for i in {1..3}
		do
			pgbench -n -f bench.sql -M prepared -T $secs $dbname | grep latency | sed 's/[^0-9.]//g' | tr '\n' ' '
		done
		echo "ms"
	done
done