#!/bin/bash

SCALES="1 32"
DBNAME="prefetch-test"
PREFETCH="0 1 2 3 4 8 16 32 64 128 256 512 1024"
FRACTIONS="1 5 10 25 50"
RUNS=1
DATADIR=/mnt/data/data-prefetch

dropdb --if-exists $DBNAME
createdb $DBNAME

rm results.log results.csv

for r in `seq 1 $RUNS`; do

        for s in $SCALES; do

                for m in random random-2 random-8 random-32 sequential; do

                        psql $DBNAME -c "drop table if exists prefetch_test"

                        psql $DBNAME -c "create table prefetch_test (a bigint, pad text)"

                        psql $DBNAME -c "alter table prefetch_test alter column pad set storage plain"

                        if [ "$m" == "random" ]; then
                                psql $DBNAME -c "with src as materialized (select mod((random() * 100000)::int, 1000) a from generate_series(1, $s*1024*1024/8) s(i)) insert into prefetch_test select a, repeat('x', 256) from (select a, generate_series(1,27) c from src) foo"
                        elif [ "$m" == "random-2" ]; then
                                psql $DBNAME -c "with src as materialized (select mod((random() * 100000)::int, 1000) a from generate_series(1, $s*1024*1024/8/2) s(i)) insert into prefetch_test select a, repeat('x', 256) from (select a, generate_series(1,27*2) c from src) foo"
                        elif [ "$m" == "random-8" ]; then
                                psql $DBNAME -c "with src as materialized (select mod((random() * 100000)::int, 1000) a from generate_series(1, $s*1024*1024/8/8) s(i)) insert into prefetch_test select a, repeat('x', 256) from (select a, generate_series(1,27*8) c from src) foo"
                        elif [ "$m" == "random-32" ]; then
                                psql $DBNAME -c "with src as materialized (select mod((random() * 100000)::int, 1000) a from generate_series(1, $s*1024*1024/8/32) s(i)) insert into prefetch_test select a, repeat('x', 256) from (select a, generate_series(1,27*32) c from src) foo"
                        elif [ "$m" == "sequential" ]; then
                                x=$(($s*27*1024*1024/8/1000))
                                psql $DBNAME -c "insert into prefetch_test select i/$x, repeat('x', 256) from generate_series(1, $s*27*1024*1024/8) s(i)"
                        fi

                        psql $DBNAME <<EOF
\d+
-- list first 100 pages
SELECT ctid, a FROM prefetch_test LIMIT 2700;
SELECT a, count(*) FROM prefetch_test GROUP BY 1 ORDER BY 1;
SELECT count(*) FROM prefetch_test;
EOF

                        psql $DBNAME -c "create index on prefetch_test(a)"

                        psql $DBNAME -c "vacuum analyze prefetch_test"

                        for f in $FRACTIONS; do

                                for p in $PREFETCH; do

                                        a=$((RANDOM % (1000 - f*10)))
                                        b=$((a+f*10))

                                        pg_ctl -D $DATADIR -l pg.log -w stop
                                        pg_ctl -D $DATADIR -l pg.log -w start
                                        sudo ./drop-caches.sh

                                        echo "====== scale $s prefetch $p fraction $f ($a, $b) ======" >> results.log 2>&1

                                        psql $DBNAME > timing.log 2>&1 <<EOF
SET enable_indexscan = off;
SET enable_seqscan = off;
SET max_parallel_workers_per_gather = 0;
SET effective_io_concurrency = $p;
-- SET work_mem = '64kB';
-- EXPLAIN SELECT count(*) FROM prefetch_test WHERE a >= $a AND a < $b;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT count(pad) FROM prefetch_test WHERE a >= $a AND a < $b;
EOF

                                        t=`grep -i 'Execution Time' timing.log | awk '{print $3}'`

                                        cat timing.log >> results.log 2>&1

                                        echo $s $p $f $m $r $a $b $t >> results.csv 2>&1

                                done

                        done

                done

        done

done