use strict;
use warnings;

system "dropdb testjoin";
system "createdb testjoin";

open F, ">", "testjoin.sql";
print F <<MAIN;
CREATE TABLE table_a  ( updated    INTEGER,                   id SERIAL PRIMARY KEY              );
CREATE TABLE table_b  ( updated    INTEGER,                   id SERIAL PRIMARY KEY, a_id INTEGER);
CREATE TABLE archive_a( start_time INTEGER, end_time INTEGER, id INTEGER                         );
CREATE TABLE archive_b( start_time INTEGER, end_time INTEGER, id INTEGER,            a_id INTEGER);

CREATE INDEX aa_idx ON archive_a (start_time,end_time);
CREATE INDEX ab_idx ON archive_b (start_time,end_time);

CREATE VIEW a AS
	SELECT id FROM archive_a WHERE start_time <= 1000 AND end_time > 1000
	UNION 
	SELECT id FROM table_a WHERE updated <= 1000
	;

CREATE VIEW b AS
	SELECT id,a_id FROM archive_b WHERE start_time <= 1000 AND end_time > 1000
	UNION 
	SELECT id,a_id FROM table_b WHERE updated <= 1000
	;
MAIN
	
print F "INSERT INTO archive_a (start_time,end_time,id) VALUES (1,1,1200);\n";
print F "INSERT INTO archive_b (start_time,end_time,id,a_id) VALUES (1,1,1200,1200);\n";
print F "INSERT INTO table_a (id,updated) VALUES (DEFAULT,$_);\n" for 1 .. 1100;
print F "INSERT INTO table_b (id,updated,a_id) VALUES (DEFAULT,$_,$_);\n" for 1 .. 10, 1100;
print F "EXPLAIN ANALYZE SELECT a.id FROM a JOIN b ON a.id = b.a_id WHERE b.id = 1;\n";

system "psql testjoin < testjoin.sql";