run.sh

application/x-shellscript

Download

#!/bin/bash

patch=$1
nrows=$2

function query_duration() {
	scan=$1
	query=$2

	seqscan=off
	bitmapscan=off
	indexscan=off
	indexonlyscan=off

	if [ "$scan" == "seqscan" ]; then
		seqscan=on
	elif [ "$scan" == "bitmapscan" ]; then
		bitmapscan=on
	elif [ "$scan" == "indexscan" ]; then
		indexscan=on
	elif [ "$scan" == "indexonlyscan" ]; then
		indexscan=on
		indexonlyscan=on
	fi

	psql test > test.log 2>&1 <<EOF
SET enable_seqscan=$seqscan;
SET enable_bitmapscan=$bitmapscan;
SET enable_indexscan=$indexscan;
SET enable_indexonlyscan=$indexonlyscan;
SET max_parallel_workers_per_gather=0;
EXPLAIN SELECT * FROM ($query OFFSET 0) foo OFFSET 1000000000;
SELECT extract(epoch from now()), 'start';
SELECT * FROM ($query OFFSET 0) foo OFFSET 1000000000;
SELECT extract(epoch from now()), 'end';
EOF

	cat test.log >> debug.log

	start=`grep 'start' test.log | awk '{print $1}'`
	end=`grep 'end' test.log | awk '{print $1}'`

	echo "($end - $start) * 1000" | bc
}

ndistinct=1

#for ndistinct in 100 10000 1000000 100000000; do
while /bin/true; do

	ndistinct=$((ndistinct * 100))

	if [[ $ndistinct -gt $nrows ]]; then
		break
	fi

	for type in int bigint; do

		for data in random sequential cycle correlated; do

			echo "===== NDISTINCT $ndistinct TYPE $type DATA $data ====="

			psql test -c "drop table if exists test_table";

			psql test -c "create table test_table (v $type)";

			if [ "$data" == "random" ]; then
				echo  "insert into test_table select $ndistinct * random() from generate_series(1, $nrows) s(i)"
				psql test -c "insert into test_table select $ndistinct * random() from generate_series(1, $nrows) s(i)"
			elif [ "$data" == "sequential" ]; then
				echo "insert into test_table select i::float * $ndistinct / $nrows from generate_series(1, $nrows) s(i)"
				psql test -c "insert into test_table select i::float * $ndistinct / $nrows from generate_series(1, $nrows) s(i)"
			elif [ "$data" == "cycle" ]; then
				echo "insert into test_table select mod(i,$ndistinct) from generate_series(1, $nrows) s(i)"
				psql test -c "insert into test_table select mod(i,$ndistinct) from generate_series(1, $nrows) s(i)"
			elif [ "$data" == "correlated" ]; then
				echo "insert into test_table select i::float * $ndistinct / $nrows + random() * sqrt($ndistinct) from generate_series(1, $nrows) s(i)"
				psql test -c "insert into test_table select i::float * $ndistinct / $nrows + random() * sqrt($ndistinct) from generate_series(1, $nrows) s(i)"
			fi

			psql test -c "create index on test_table (v)";

			psql test -c "vacuum analyze test_table";

			for run in `seq 1 10`; do

				for nvalues in 1 10 100 1000; do

					for step in 1 5 10; do

						if [[ $nvalues -eq 1 && $step -gt 1 ]]; then
							continue
						fi

						range=$((nvalues * step))

						if [[ $range -gt $ndistinct ]]; then
							continue
						fi

						if [[ $ndistinct -gt $range ]]; then
							value=$((RANDOM % (ndistinct - range)))
						else
							value=0
						fi

						values="$value"

						for v in `seq 1 $((nvalues-1))`; do
							value=$((value + step))
							values="$values, $value"
						done

						query="select * from test_table where v in ($values)"

						echo "----- nvalues $nvalues step $step run $run query $query -----"

						#for scan in indexscan indexonlyscan seqscan bitmapscan; do
						for scan in indexscan indexonlyscan bitmapscan; do
							t=$(query_duration $scan "$query")
							echo $patch $nrows $ndistinct $type $data $nvalues $step $run $scan $t >> results.csv
						done

					done

				done

			done

		done

	done

done