random-bench.sh

#!/usr/bin/bash

echo type len rows randomlen prefix slice run time

for len in 10000 100000 1000000 10000000; do

	rows=$((1000000000/len))

	for a in 1 10 100 1000 10000 100000 1000000 10000000; do

		if [ "$a" -gt "$len" ]; then
			continue
		fi

		b=$((len - a))

		dropdb --if-exists test > /dev/null 2>&1
		createdb test > /dev/null 2>&1

		psql test -c "create unlogged table t (a text)" > /dev/null 2>&1

		psql test -c "create extension randomstring" > /dev/null 2>&1

		for p in 0 10 100 1000 10000 100000 1000000 10000000; do

			if [ "$p" -gt "$len" ]; then
				continue
			fi

			for l in 1 10 100 1000 10000 100000 1000000 10000000; do

				if [ "$l" -gt "$len" ]; then
					continue
				fi

				for r in `seq 1 5`; do

					psql test -c "truncate t" > /dev/null 2>&1

					psql test -c "insert into t select random_string($a) || repeat('a', $b) from generate_series(1,$rows) S(i)" > /dev/null 2>&1

					psql test -c "vacuum analyze t" > /dev/null 2>&1

					psql test -c "checkpoint" > /dev/null 2>&1

					psql test > tmp.log <<EOF
\timing on
select sum(length(substr(a,$p,$l))) from t
EOF

					t=`cat tmp.log | grep Time | awk '{print $2}'`

					echo random-first $len $rows $a $p $l $r $t

				done

			done

		done

	done

	for a in 1 10 100 1000 10000 100000 1000000 10000000; do

		if [ "$a" -gt "$len" ]; then
			continue
		fi

		b=$((len - a))

		dropdb --if-exists test > /dev/null 2>&1
		createdb test > /dev/null 2>&1

		psql test -c "create unlogged table t (a text)" > /dev/null 2>&1

		psql test -c "create extension randomstring" > /dev/null 2>&1

		for p in 0 10 100 1000 10000 100000 1000000 10000000; do

			if [ "$p" -gt "$len" ]; then
				continue
			fi

                        for l in 1 10 100 1000 10000 100000 1000000 10000000; do

				if [ "$l" -gt "$len" ]; then
					continue
				fi

				for r in `seq 1 5`; do

					psql test -c "truncate t" > /dev/null 2>&1

					psql test -c "insert into t select repeat('a', $b) || random_string($a) from generate_series(1,$rows) S(i)" > /dev/null 2>&1

					psql test -c "vacuum analyze t" > /dev/null 2>&1

					psql test -c "checkpoint" > /dev/null 2>&1

					psql test > tmp.log <<EOF
\timing on
select sum(length(substr(a,$p,$l))) from t
EOF

					t=`cat tmp.log | grep Time | awk '{print $2}'`

					echo random-last $len $rows $a $p $l $r $t

				done

			done

		done

	done

done