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?.

select version();


version


PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create table table_a ( id integer, a text, b text);
insert into table_a ( id, a, b) 
   values (1,'a1', 'b1')
        , (1,'a2', 'b1')
        , (1,'a2', 'b2')
        , (2,'a1', 'b1')
        , (2,'a2', 'b1'); 
select * from table_a; 
5 rows affected



id
a
b


1
a1
b1

1
a2
b1

1
a2
b2

2
a1
b1

2
a2
b1


delete 
  from table_a
 where a = 'a1';
select * from table_a;
2 rows affected



id
a
b


1
a2
b1

1
a2
b2

2
a2
b1


truncate table_a; 
insert into table_a ( id, a, b) 
   values (1,'a1', 'b1')
        , (1,'a2', 'b1')
        , (1,'a2', 'b2')
        , (2,'a1', 'b1')
        , (2,'a2', 'b1'); 
select * from table_a; 
5 rows affected



id
a
b


1
a1
b1

1
a2
b1

1
a2
b2

2
a1
b1

2
a2
b1


delete 
  from table_a
 where id in( select id
                from table_a 
               where a = 'a1' 
            ) ;
           

select * from table_a;
5 rows affected