run.sh

application/x-shellscript

Download

#!/usr/bin/bash

for nr in 1000000 10000 10000000; do

	for nc in 1 5 10 20; do

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

		create_sql='CREATE TABLE t (id int primary key';
		insert_sql="INSERT INTO t (id";

		for c in `seq 1 $nc`; do
			create_sql="$create_sql, c$c text";
			insert_sql="$insert_sql, c$c";
		done

		create_sql="$create_sql)";
		insert_sql="$insert_sql) select i";

		for c in `seq 1 $nc`; do
			insert_sql="$insert_sql, md5(random()::text)";
		done

		insert_sql="$insert_sql FROM generate_series(1,$nr) s(i)"

		echo $create_sql
		echo $insert_sql

		psql test -c "$create_sql"
		psql test -c "$insert_sql"

		psql test -c 'vacuum (freeze,analyze) t'

		psql test -c 'checkpoint'

		for r in `seq 1 10`; do

			for qs in 8 16 64 256 1024; do

				for nw in 4 0 8 2 16; do

					psql test > timing.log <<EOF
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
set max_parallel_workers_per_gather=$nw;
set min_parallel_index_scan_size='1kB';
set min_parallel_table_scan_size='1kB';
set parallel_tuple_queue_size = $qs;
EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM t OFFSET 1000000000) foo;
\timing
SELECT * FROM (SELECT * FROM t OFFSET 1000000000) foo;
EOF

					t=`grep '^Time' timing.log | awk '{print $2}'`
					w=`grep 'Workers Launched' timing.log | awk '{print $3}'`

					if [ "$w" == "" ]; then
						w="-1"
					fi

					echo $nr $nc $qs $nw $w $r $t >> results.csv

					echo "===== rows $nr columns $qs queue $nc workers $nw ($w) run $r timing $t =====" >> explain.log 2>&1
					cat timing.log >> explain.log 2>&1

				done

			done

		done

	done

done