Postgres 15

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 agent_audit_log(id int, agent_id int , api text, at timestamp)

CREATE TABLE

INSERT INTO agent_audit_log VALUES (1,1,'A',now()), (2,1,'B',now()),(3,2,'A',now()), (4,2,'B',now())

INSERT 0 4

DELETE FROM agent_audit_log 
  WHERE id NOT IN (SELECT MIN(id) FROM agent_audit_log GROUP BY agent_id);

DELETE 2

SELECT * FROM agent_audit_log

id
agent_id
api
at

1
1
A
2023-06-16 22:11:16.919099

3
2
A
2023-06-16 22:11:16.919099

SELECT 2

INSERT INTO agent_audit_log VALUES (2,1,'A',now()), (4,2,'A',now()),(6,1,'A',now()), (6,2,'A',now())

INSERT 0 4

WITH CTE AS (
  SELECT id, ROW_NUMBER() OVER(PARTITION BY agent_id ORDER BY at) rn 
  FROM agent_audit_log)
DELETE FROM agent_audit_log 
  WHERE id NOT IN (SELECT id FROM CTE WHERE rn = 1);

DELETE 4

  SELECT *
  FROM agent_audit_log

id
agent_id
api
at

1
1
A
2023-06-16 22:11:16.919099

3
2
A
2023-06-16 22:11:16.919099

SELECT 2