Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
CREATE TABLE emails (id INTEGER PRIMARY KEY, content TEXT);
CREATE TABLE email_opens (
    email_id INTEGER,
    user_id INTEGER,
    opened_time INTEGER,
    PRIMARY KEY(email_id, user_id),
    FOREIGN KEY (email_id) REFERENCES emails(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT INTO users VALUES (1, 'Bob Smith', 'bobsmith@example.com');
INSERT INTO users VALUES (2, 'Rolf Smith', 'rolfsmith@example.com');
INSERT INTO users VALUES (3, 'Susan Williams', 'susanwilliams@example.com');
INSERT INTO users VALUES (4, 'Anne Pun', 'annepun@example.com');

INSERT INTO emails VALUES (1, 'This is a test e-mail');
INSERT INTO emails VALUES (2, 'Another test e-mail.');
INSERT INTO emails VALUES (3, 'We should really write longer e-mails!');

INSERT INTO email_opens VALUES (3, 2, 1572393600);
INSERT INTO email_opens VALUES (2, 2, 1572220800);
INSERT INTO email_opens VALUES (1, 3, 1572393600);
INSERT INTO email_opens VALUES (2, 3, 1572480000);
INSERT INTO email_opens VALUES (1, 4, 1572480000);
INSERT INTO email_opens VALUES (1, 1, 1572393600);

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

CREATE OR REPLACE FUNCTION opened_ago(email_open_row email_opens) RETURNS INTEGER AS $$
    SELECT cast(extract(epoch FROM CURRENT_TIMESTAMP) AS INTEGER) - email_open_row.opened_time AS email_opened_ago;
$$ LANGUAGE SQL;

CREATE FUNCTION

SELECT *, opened_ago(email_opens) FROM users
JOIN email_opens ON users.id = email_opens.user_id
WHERE opened_ago(email_opens) < 17509903;

id
name
email
email_id
user_id
opened_time
opened_ago

SELECT 0