-- drop and recreate tables to easily allow changing definitions to see how
-- perf changes.
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS addresses CASCADE;

CREATE TABLE users (
    id integer PRIMARY KEY
);

CREATE TABLE addresses (
    id integer PRIMARY KEY,
    user_id integer REFERENCES users(id)
);

CREATE INDEX ix_addresses_user_id ON addresses (user_id);

-- disable seqscan since we really want to see the query planner use indices if
-- at all possible.
SET enable_seqscan TO OFF;

-- simple primary key lookup
EXPLAIN (ANALYZE, BUFFERS)
SELECT addresses.id
FROM addresses
WHERE addresses.id = 1;

-- semi join lookup: find all addresses for a specific user
EXPLAIN (ANALYZE, BUFFERS)
SELECT addresses.id
FROM addresses
WHERE EXISTS (
    SELECT 1 FROM users
    WHERE (
        users.id = addresses.user_id AND
        users.id = 1
    )
);

-- doing both at the same time results in a seqscan on `addresses`.
EXPLAIN (ANALYZE, BUFFERS)
SELECT addresses.id
FROM addresses
WHERE (
    addresses.id = 1 OR
    EXISTS (
        SELECT 1 FROM users
        WHERE (
            users.id = addresses.user_id AND
            users.id = 1
        )
    )
);
