BEGIN;

CREATE SCHEMA mightlike;


CREATE TABLE items (
	objectid  integer NOT NULL,
	objname   text NOT NULL
);

CREATE TABLE might_like (
	item       integer NOT NULL,
	created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
	child      integer NOT NULL
);

INSERT INTO items SELECT i, 'item number ' || i 
FROM generate_series(1, 100000) i;

INSERT INTO might_like SELECT i, (now() - j * '1 day'::interval), i+j
FROM generate_series(1, 99990) i, generate_series(1, 10) j;

ALTER TABLE items ADD PRIMARY KEY (objectid);
ALTER TABLE might_like ADD PRIMARY KEY (item, child);
ALTER TABLE might_like ADD CONSTRAINT valid_child FOREIGN KEY (child) REFERENCES items;
CREATE INDEX might_like_idx1 ON might_like (item, created_at);

-- EXPLAIN ANALYSE
SELECT
	objectid, objname
FROM
	items i
	JOIN might_like m ON (i.objectid = m.child)
WHERE
	m.created_at > (now() - '1 week'::interval)
	AND m.item = 125
ORDER BY
	objectid
LIMIT
	16
;

ROLLBACK;
