test_update.sh

text/x-sh

Download

test_time=60
dropdb --if-exists test_update
createdb test_update
for ncols in 10 20 40 80; do
	echo "$ncols columns"
	echo "select 'drop table if exists test_$ncols, foo; create table foo (b int primary key, a int); create table test_$ncols (a int primary key, b int default null, c int default 0);' union all select 'alter table test_$ncols add column f' || i || ' text default ''xxxxx'';' from generate_series(3, $ncols) i;" > /tmp/test_table.sql
	echo "\gexec" >> /tmp/test_table.sql
	psql test_update -f /tmp/test_table.sql > /dev/null 2>&1
	psql test_update -c "insert into foo (b) select generate_series(1, 1000000)" > /dev/null 2>&1

	echo "\set a random(1, 1000000)" > /tmp/nojoin.sql
	echo "update test_$ncols t set b = :a where a = :a;" >> /tmp/nojoin.sql
	echo "\set a random(1, 1000000)" > /tmp/join.sql
	echo "update test_$ncols t set b = foo.b from foo where t.a = foo.a and foo.b = :a;" >> /tmp/join.sql
	for test_file in nojoin join; do
		for proto in "simple" "prepared"; do
			echo $test_file $proto
			for i in 1 2 3; do
				psql test_update -c \
				"truncate test_$ncols; \
				 insert into test_$ncols (a) select generate_series(1, 1000000)" > /dev/null 2>&1;
				pgbench test_update -n -T $test_time -M$proto -f /tmp/"$test_file".sql | grep tps
			done
		done
	done
	echo ""
done