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 tableName (
    id  int  ,
    address_id int,
    state varchar(5)
   );
    
    
INSERT INTO tableName values 
(12,1,'A'),
(94,1,'A'),
(991,1,'A'),
(992,2,'A'),
(993,2,'A'),
(37,4,'A');




CREATE TABLE



INSERT 0 6


select * from tableName;


id
address_id
state


12
1
A

94
1
A

991
1
A

992
2
A

993
2
A

37
4
A



SELECT 6


select tn.id,tn.address_id,tn.state
from tableName tn
inner join (select max(id) as id ,count(address_id) as nr_count
            from tableName
            where state='A'
            group by address_id
            ) as t1 on tn.id=t1.id
where   t1.nr_count >1;          




id
address_id
state


991
1
A

993
2
A



SELECT 2