bench.sh.txt

text/plain

Download

#!/bin/bash

db=postgres

psql -c "drop table if exists t1;" $db
psql -c "create table t1 (a int, b text);" $db
psql -c "insert into t1 select x,repeat('a',1024) from generate_series(1,10000)x;" $db
psql -c "create index on t1(a);" $db
psql -c "vacuum freeze analyze t1;" $db
psql -c "select pg_prewarm('t1');" $db

for r in 1 2 4 8 16 32 64 128 256 512 1024 2048 4096 8192
do
	echo -n "$r rows "
	psql -c "explain (analyze) select count(t1.b) from (values(1),(2)) t2(x) left join (select * from t1 where a <= $r) t1 on true;" $db | grep "Maximum Storage"
done

for r in 1 2 4 8 16 32 64 128 256 512 1024 2048 4096 8192
do
		echo -n "Testing with $r rows "
		echo "select count(t1.b) from (values(1),(2)) t2(x) left join (select * from t1 where a <= $r) t1 on true;" > bench.sql
		pgbench -n -T 10 -f bench.sql -M prepared $db | grep latency
done