#!/bin/bash

echo "create table jointest3_1 (keya int, infoa int);"
echo "create table jointest3_2 (keya int, keyb int);"
echo "create table jointest3_3 (keyb int, infob int);"
echo "BEGIN;"
hexdump -d </dev/urandom \
	| sed -e "s/^[0123456789abcdefABCDEF]\+ \+[^ ]\([0123456789]\+\) \+[^ ]\([0123456789]\+\) \+[^ ]\([0123456789]\+\) \+[^ ]\([0123456789]\+\) \+[^ ]\([0123456789]\+\) \+[^ ]\([0123456789]\+\) \+[^ ]\([0123456789]\+\) \+[^ ]\([0123456789]\+\).*$/\1\2\3\4\5\6\7\8/" \
	| sed -e "=" | sed -e "/^[0123456789]\{1,7\}$/h;/^[0123456789]\{1,7\}$/d;G;s/^\(.*\)\n\(.*\)/\2	\1/;s/^1000000/0/" \
	| sed -e "s/^\([0123456789]*\)	\([0123456789]\{4\}\)\([0123456789]\{4\}\)\([0123456789]\{6\}\)\([0123456789]\{6\}\)\([0123456789]\{6\}\)\([0123456789]\{6\}\).*$/insert into jointest3_1 values (\1, \2); insert into jointest3_3 values (\1, \3);insert into jointest3_2 values (\4, \5);insert into jointest3_2 values (\6, \7);/" \
	| head -n 1000000
echo "COMMIT;"
echo "create index indexjointest3_1_keya on jointest3_1 (keya);"
echo "cluster indexjointest3_1_keya on jointest3_1;"
echo "create index indexjointest3_1_infoa on jointest3_1 (infoa);"
echo "create index indexjointest3_2_keya on jointest3_2 (keya);"
echo "cluster indexjointest3_2_keya on jointest3_2;"
echo "create index indexjointest3_2_keyb on jointest3_2 (keyb);"
echo "create index indexjointest3_3_keyb on jointest3_3 (keyb);"
echo "cluster indexjointest3_3_keyb on jointest3_3;"
echo "create index indexjointest3_3_infob on jointest3_3 (infob);"
echo "vacuum analyse;"
echo "explain analyse select infoa, keya, keyb, infob from jointest3_1 natural join jointest3_2 natural join jointest3_3 where infoa = 1234;"
echo "explain analyse select infoa, keya, keyb, infob from jointest3_1 natural join jointest3_2 natural join jointest3_3 where infoa = 1234;"

echo "explain analyse select infoa, keya, keyb, infob from jointest3_1 natural join jointest3_2 natural join jointest3_3 where infob = 1234;"
echo "explain analyse select infoa, keya, keyb, infob from jointest3_1 natural join jointest3_2 natural join jointest3_3 where infob = 1234;"

echo "explain analyse select infoa, keya, keyb, infob from jointest3_1 natural join (select * from jointest3_2 natural join jointest3_3 where infob = 1234) as s;"
echo "explain analyse select infoa, keya, keyb, infob from jointest3_1 natural join (select * from jointest3_2 natural join jointest3_3 where infob = 1234) as s;"