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 the_table (attribute_id int, created_at timestamp, product_id int);

CREATE TABLE


insert into the_table 
values
(1, timestamp '2020-04-28 15:31:11', 112235),
(4, timestamp '2020-04-28 15:30:25', 112235),
(1, timestamp '2020-04-29 15:30:25', 112236),
(4, timestamp '2020-04-29 15:30:25', 112236);

INSERT 0 4


delete from the_table t1
where exists (select *
              from the_table t2
              where t2.created_at > t1.created_at
                and t2.attribute_id = t1.attribute_id);

DELETE 2


select *
from the_table


attribute_id
created_at
product_id


1
2020-04-29 15:30:25
112236

4
2020-04-29 15:30:25
112236



SELECT 2