run.sh

application/x-shellscript

Download

#!/usr/bin/env bash

LABEL=$1	# label for each rows in result file
ROWS=$2		# number of rows (millions)
DATADIR=$3	# where to initialize the instance

# timestamp to use for log/result file
TS=$(date +%Y%m%d-%H%M%S)

# kill the instance, initialize a new one
killall -9 postgres || true
rm -Rf $DATADIR

pg_ctl -D $DATADIR init

echo 'shared_buffers = 8GB' >> $DATADIR/postgresql.conf
echo 'max_wal_size = 64GB' >> $DATADIR/postgresql.conf
echo 'io_workers = 12' >> $DATADIR/postgresql.conf

pg_ctl -D $DATADIR -l pg.log start

createdb test

nrows=$((ROWS*1000000))

psql test <<EOF
set work_mem = '256MB';
set maintenance_work_mem = '256MB';
set max_parallel_maintenance_workers = 8;
drop table if exists test_table;
create table test_table (id bigint, val text) with (fillfactor=20);
insert into test_table select i, md5(i::text) from generate_series(1, $nrows) s(i) order by random();
create index on test_table (id);
vacuum freeze;
analyze;
checkpoint;
EOF

psql test -c "\d+"


for run in $(seq 1 2); do

	pg_ctl -D $DATADIR -l pg.log restart

	sudo ./drop-caches.sh

	t=$(psql test -t -A -c "select extract(epoch from now())")
	psql test > explain.log <<EOF
SET max_parallel_workers_per_gather = 0;
SET enable_sort = off;
SET enable_seqscan = on;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
EXPLAIN SELECT * FROM (SELECT * FROM test_table) OFFSET $nrows;
SELECT * FROM (SELECT * FROM test_table) OFFSET $nrows;
EOF
	t=$(psql test -t -A -c "select 1000 * (extract(epoch from now()) - $t)")

	cat explain.log >> explains-$TS.log

	cost=$(grep 'Scan' explain.log | head -n 1 | sed 's/.*cost=[^\s]\+\.\.\([^\s]\+\) .*/\1/')

	echo $LABEL $ROWS $run seqscan $t $cost >> results-$TS.csv

	pg_ctl -D $DATADIR -l pg.log restart

	sudo ./drop-caches.sh

	t=$(psql test -t -A -c "select extract(epoch from now())")
psql test > explain.log <<EOF
SET max_parallel_workers_per_gather = 0;
SET enable_sort = off;
SET enable_seqscan = off;
SET enable_indexscan = on;
SET enable_bitmapscan = off;
EXPLAIN SELECT * FROM (SELECT * FROM test_table ORDER BY id) OFFSET $nrows;
SELECT * FROM (SELECT * FROM test_table ORDER BY id) OFFSET $nrows;
EOF
	t=$(psql test -t -A -c "select 1000 * (extract(epoch from now()) - $t)")

	cat explain.log >> explains-$TS.log

	cost=$(grep 'Scan' explain.log | head -n 1 | sed 's/.*cost=[^\s]\+\.\.\([^\s]\+\) .*/\1/')

	echo $LABEL $ROWS $run indexscan $t $cost >> results-$TS.csv

done