Postgres 11

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 earthquakes_ovr ("datetime" timestamp)
INSERT INTO earthquakes_ovr VALUES (NOW() - INTERVAL '6 DAYS')
1 rows affected

INSERT INTO earthquakes_ovr VALUES (now() - INTERVAL'4 days')
1 rows affected

CREATE UNLOGGED TABLE  earthquakes AS SELECT * FROM earthquakes_ovr
2 rows affected

    CREATE OR REPLACE FUNCTION earthquakes_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO earthquakes VALUES (NEW.*);
        DELETE FROM ONLY earthquakes WHERE datetime < (now() - '2 days'::interval);
        RETURN NEW;
    END;
    $$
    LANGUAGE plpgsql;
    
    CREATE TRIGGER earthquakes_trigger
            BEFORE INSERT ON earthquakes_ovr
            FOR EACH ROW EXECUTE PROCEDURE earthquakes_trigger();
INSERT INTO earthquakes_ovr VALUES (now())
1 rows affected

SELECT * FROM earthquakes_ovr


datetime


2021-04-18 00:11:15.191671

2021-04-20 00:11:15.203608

2021-04-24 00:11:15.267616


SELECT * FROM earthquakes


datetime


2021-04-24 00:11:15.267616