hashjoin-test.sh

#!/usr/bin/bash

psql test -c "create table small (id bigint, val text)"
psql test -c "create table large (id bigint, val text)"

psql test -c "insert into large select 1000000000 * random(), md5(i::text) from generate_series(1, 10000000) s(i)"
psql test -c "copy large to '/tmp/large.dump'"

psql test -c "insert into small select 1000000000 * random(), md5(i::text) from generate_series(1, 10000) s(i)"
psql test -c "vacuum analyze small"

psql test -c 'drop table if exists temp_files_stats'
psql test -c 'create table temp_files_stats (temp_files bigint, temp_bytes bigint)'

for i in 1 5 10 25 50; do

        echo "=============== SIZE $i ===================="

        psql test -c 'truncate large'

        for i in `seq 1 $i`; do

                psql test -c "copy large from '/tmp/large.dump'"

        done

	psql test -c "vacuum analyze large"

        psql test -c "checkpoint"

        psql test -c "\d+"

        psql test -c "update pg_class set (relpages, reltuples) = (1000000, 1) where relname = 'large'";

        psql test -c "delete from temp_files_stats"

        psql test -c "insert into temp_files_stats select temp_files, temp_bytes from pg_stat_database where datname = 'test'"

        psql test -c "explain analyze select * from small join large using (id)"

        psql test -c "select (d.temp_files - s.temp_files) as temp_files, (d.temp_bytes - s.temp_bytes) as temp_bytes from pg_stat_database d, temp_files_stats s where datname = 'test'"

        for r in `seq 1 2`; do

                echo "========= run $r ==========="

                psql test <<EOF
\timing on
delete from temp_files_stats;
insert into temp_files_stats select temp_files, temp_bytes from pg_stat_database where datname = 'test';
select * from (select * from small join large using (id) offset 1000000000) foo;
select (d.temp_files - s.temp_files) as temp_files, (d.temp_bytes - s.temp_bytes) as temp_bytes from pg_stat_database d, temp_files_stats s where datname = 'test';
EOF

   	done

done