DROP TABLE IF EXISTS child;
DROP TABLE IF EXISTS parent;

CREATE TABLE parent(parent_id BIGSERIAL PRIMARY KEY, name text);
CREATE TABLE child(child_id BIGSERIAL PRIMARY KEY, parent_id bigint references parent(parent_id), name text);

-- random name and sequential primary key for 100 thousand parents.
INSERT INTO parent
    SELECT
        nextval('parent_parent_id_seq'),
        md5(random()::text)
    FROM generate_series(1, 100000);

-- 1 million children.
-- set every odd id parent to have children. even id parent gets none.
INSERT INTO child
    SELECT
           nextval('child_child_id_seq'),
           ((generate_series/2*2) % 100000)::bigint + 1,
           md5(random()::text)
    FROM generate_series(1, 1000000);

CREATE INDEX ON child(parent_id);

VACUUM ANALYZE parent, child;

-- slow scans through entire child.
explain (analyze, buffers)
SELECT
    parent.*,
    EXISTS (SELECT * FROM child WHERE child.parent_id=parent.parent_id) AS child_exists
FROM parent
ORDER BY parent_id LIMIT 10;

-- fast only scans through children that have the parent.
explain (analyze, buffers)
SELECT
    parent.*,
    (SELECT EXISTS (SELECT * FROM child WHERE child.parent_id=parent.parent_id)) AS child_exists
FROM parent
ORDER BY parent_id LIMIT 10;

