explain-analyze.sh

#!/usr/bin/env bash

function ea {

	for r in $(seq 1 5); do

		q=$1

		rm pg.log; pg_ctl -D tmp/data -l pg.log restart > /dev/null 2>&1
		sudo ./drop-caches.sh

		t=$(psql test -c "$q" | grep 'Execution Time' | awk '{print $3}')

		stats=$(grep 'heapam_index_fetch_tuple block' pg.log | awk '{print $11}' | ./stats.py 24891 74823)

		echo "'$q'" $t $stats
	done
}


ea "EXPLAIN (ANALYZE, VERBOSE, COSTS OFF) SELECT * FROM t WHERE a BETWEEN 16336 AND 49103 ORDER BY a"
ea "EXPLAIN (ANALYZE, VERBOSE, COSTS OFF) SELECT * FROM t WHERE a BETWEEN 16336 AND 49103 ORDER BY a DESC"

ea "EXPLAIN (ANALYZE, VERBOSE, COSTS OFF) SELECT * FROM t2 WHERE a BETWEEN -49103 AND -16336 ORDER BY a"
ea "EXPLAIN (ANALYZE, VERBOSE, COSTS OFF) SELECT * FROM t2 WHERE a BETWEEN -49103 AND -16336 ORDER BY a DESC"