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 (name TEXT, email TEXT, last_opened INTEGER);

CREATE TABLE

INSERT INTO users VALUES ('Bob Smith', 'bobsmith@example.com', 65615);
INSERT INTO users VALUES ('Rolf Smith', 'rolfsmith@example.com', 8587597);
INSERT INTO users VALUES ('Susan Williams', 'susanwilliams@example.com', 56);
INSERT INTO users VALUES ('Anne Pun', 'annepun@example.com', 2419200);

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

CREATE FUNCTION delete_inactive(seconds NUMERIC) RETURNS BIGINT AS $$
    WITH deleted AS (DELETE FROM users WHERE last_opened > seconds RETURNING *)
    SELECT COUNT(*) FROM deleted;
$$ LANGUAGE SQL;

CREATE FUNCTION

SELECT delete_inactive(86400);

delete_inactive

2

SELECT 1

SELECT * FROM users;

name
email
last_opened

Bob Smith
bobsmith@example.com
65615

Susan Williams
susanwilliams@example.com
56

SELECT 2

name	email	last_opened
Bob Smith	bobsmith@example.com	65615
Susan Williams	susanwilliams@example.com	56