pushdown.sh

application/x-sh

Download

#!/bin/bash

RESULTS=$1

echo '' > $RESULTS.csv
echo '' > $RESULTS-explains.log

dropdb test > /dev/null 2>&1
createdb test > /dev/null 2>&1

psql test -c "create table fact (dim_a_id int, dim_b_id int, f1 double precision, f2 double precision)" > /dev/null 2>&1
psql test -c "create table dim_a (id int primary key, val double precision)" > /dev/null 2>&1
psql test -c "create table dim_b (id int primary key, val double precision)" > /dev/null 2>&1

for d in 100 1000 10000; do

	for n in 1000000 10000000; do

		psql test -c 'truncate dim_a' > /dev/null 2>&1;
		psql test -c 'truncate dim_b' > /dev/null 2>&1;
		psql test -c 'truncate fact' > /dev/null 2>&1;

		psql test -c "insert into dim_a select i, random() from generate_series(1,$d) s(i)" > /dev/null 2>&1
		psql test -c "insert into dim_b select i, random() from generate_series(1,$d) s(i)" > /dev/null 2>&1

		psql test -c "insert into fact select 1 + mod((random()*100000)::int,$d), 1 + mod((random()*100000)::int,$d), random(), random() from generate_series(1,$n) s(i)" > /dev/null 2>&1

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

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

		for w in 0 4; do

			for p in off on; do

				for q in "select sum(f1) from fact f join dim_a d1 on (d1.id = dim_a_id)" \
					"select d1.id, sum(f1) from fact f join dim_a d1 on (d1.id = dim_a_id) group by d1.id" \
					"select d1.val, sum(f1) from fact f join dim_a d1 on (d1.id = dim_a_id) group by d1.val" \
					"select sum(f1) from fact f join dim_a d1 on (d1.id = dim_a_id) join dim_b d2 on (d2.id = dim_b_id)" \
					"select d1.id, sum(f1) from fact f join dim_a d1 on (d1.id = dim_a_id) join dim_b d2 on (d2.id = dim_b_id) group by d1.id" \
					"select d1.val, sum(f1) from fact f join dim_a d1 on (d1.id = dim_a_id) join dim_b d2 on (d2.id = dim_b_id) group by d1.val" \
					"select d1.id, d2.id, sum(f1) from fact f join dim_a d1 on (d1.id = dim_a_id) join dim_b d2 on (d2.id = dim_b_id) group by d1.id, d2.id" \
					"select d1.val, d2.val, sum(f1) from fact f join dim_a d1 on (d1.id = dim_a_id) join dim_b d2 on (d2.id = dim_b_id) group by d1.val, d2.val"; do

					echo "====== d=$d n=$n w=$w p=$p q='$q' =====" >> $RESULTS-explains.log 2>&1

					psql test >> $RESULTS-explains.log 2>&1 <<EOF
set work_mem = '1GB';
set max_parallel_workers_per_gather = $w;
set enable_agg_pushdown = $p;
\timing on
EXPLAIN ANALYZE SELECT * FROM ($q) foo OFFSET 1000000000;
EOF

					for r in `seq 1 5`; do

						psql test > timing.log 2>&1 <<EOF
set work_mem = '1GB';
set max_parallel_workers_per_gather = $w;
set enable_agg_pushdown = $p;
\timing on
SELECT * FROM ($q) foo OFFSET 1000000000;
EOF

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

						echo $d $n $w $p "'$q'" $r $t >> $RESULTS.csv

					done

				done

			done

		done

	done

done