bench_cache.sh

application/x-sh

Download

#!/bin/sh
# settings
export PGDATABASE=bench
BENCHOPTS='-T 15'

set -e

# initialization
psql -1 <<EOF
drop table if exists ts;
create table ts as select generate_series(timestamptz '2002-01-01', '2012-01-01', '5min') ts;
vacuum analyze ts;

drop table if exists one;
create table one as select timestamptz '2012-01-01 00:00:00' ts;

vacuum analyze one;
select pg_sleep(1);
EOF

runbench() {
	cat pgbench_script.tmp
	for i in `seq 1 3`; do
		pgbench -n -f pgbench_script.tmp $BENCHOPTS |grep 'excluding connections'
	done
}

cat >pgbench_script.tmp <<EOF
select * from ts where ts between to_timestamp('2005-01-01', 'YYYY-MM-DD') and to_timestamp('2005-01-01', 'YYYY-MM-DD');
EOF
runbench

cat >pgbench_script.tmp <<EOF
select * from ts where ts>now();
EOF
runbench

# test expression overhead for small tables
cat >pgbench_script.tmp <<EOF
/*uncachable*/ select * from one where ts >= to_date(clock_timestamp()::date::text, 'YYYY-MM-DD') and ts < (to_date(clock_timestamp()::date::text, 'YYYY-MM-DD') + interval '1 year')
EOF
runbench

cat >pgbench_script.tmp <<EOF
/*cachable*/ select * from one where ts >= to_date(now()::date::text, 'YYYY-MM-DD') and ts < (to_date(now()::date::text, 'YYYY-MM-DD') + interval '1 year')
EOF
runbench

# cleanup
rm pgbench_script.tmp