Postgres 13

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 "andaz-rkugf"(snapshot_timestamp timestamptz PRIMARY KEY);
INSERT INTO "andaz-rkugf" VALUES
  ('2018-10-30 10:11:23+0')  -- keep (1st in 10 min interval)
, ('2018-10-30 10:15:34+0')
, ('2018-10-30 10:16:00+0')
, ('2018-10-30 10:21:01+0')  -- keep (1st in 10 min interval)
, ('2018-10-30 10:22:01+0')
, ('2018-10-30 11:52:33+0')  -- keep (only 1 in 10 min interval)
, ('2021-11-30 10:00:00+0')  -- keep (later than cut-off)
;
7 rows affected

WITH del AS (
   DELETE FROM "andaz-rkugf" a
   WHERE  snapshot_timestamp <= '2018-10-31+0'
   RETURNING *
   )
INSERT INTO "andaz-rkugf"
SELECT min(snapshot_timestamp)
FROM   del
GROUP  BY extract(epoch FROM snapshot_timestamp)::bigint / 600
ORDER  BY 1;  -- optional
3 rows affected

TABLE "andaz-rkugf" ORDER BY snapshot_timestamp;


snapshot_timestamp


2018-10-30 10:11:23+00

2018-10-30 10:21:01+00

2018-10-30 11:52:33+00

2021-11-30 10:00:00+00