BEGIN;

CREATE SCHEMA issuefup;


CREATE TABLE issues (
	id integer,
	dt_created timestamp(0) with time zone,
	dt_modified timestamp(0) with time zone,
	t_title varchar(100),
	t_description text
);

CREATE TABLE followups (
	id integer,
	dt_created timestamp(0) with time zone,
	dt_modified timestamp(0) with time zone,
	t_description text,
	n_issue integer NOT NULL
);

INSERT INTO issues
	(id, dt_created, dt_modified, t_title, t_description)
SELECT
	(d*100 + i),
	'2008-01-01'::date + (d * '1 day'::interval), 
	'2008-01-01'::date + (d * '1 day'::interval), 
	'issue title ' || d || '/' || i,
	'issue description ' || d || '/' || i
FROM
	generate_series(0,99) AS d,
	generate_series(0,99) AS i
;


INSERT INTO followups
	(id, dt_created, dt_modified, t_description, n_issue)
SELECT
	(i.id * 10) + d,
	'2008-01-01'::date + ((i.id + d) * '1 day'::interval), 
	'2008-01-01'::date + ((i.id + d) * '1 day'::interval),
	'followup description ' || ((i.id * 10) + d),
	i.id
FROM
	generate_series(0,9) AS d,
	issues AS i
;

ALTER TABLE issues ADD  PRIMARY KEY (id);
ALTER TABLE followups ADD PRIMARY KEY (id);
ALTER TABLE followups ADD CONSTRAINT n_issue_fkey FOREIGN KEY (n_issue) REFERENCES issues (id);
CREATE INDEX issues_dt_idx ON issues (dt_modified);
CREATE INDEX followups_nissue_dt_idx ON followups (n_issue, dt_modified);

ANALYSE ;

EXPLAIN ANALYSE
SELECT
	fu.*
FROM
	issues i,
	followups fu
WHERE
	i.dt_modified = '2008-01-07 00:00:00+00'
	AND fu.id = (
		SELECT f.id
		FROM followups f
		WHERE f.n_issue = i.id
		ORDER BY f.dt_modified DESC LIMIT 1
	)
;

ROLLBACK;
