Postgres 14

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 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table test_table( id          integer generated always as identity
                       , balance     numeric
                       , last_block  text
                       ); 
                      
insert into test_table ( last_block, balance) 
     values ('14529343', 22), ('14529343', 2), ('162529343',22), ('14529343',4); 
4 rows affected

select ctid,  tt.* from test_table tt;


ctid
id
balance
last_block


(0,1)
1
22
14529343

(0,2)
2
2
14529343

(0,3)
3
22
162529343

(0,4)
4
4
14529343


update test_table
   set balance = 11 
 where last_block = '14529343'; 

select ctid, tt.* from test_table tt;
3 rows affected



ctid
id
balance
last_block


(0,3)
3
22
162529343

(0,5)
1
11
14529343

(0,6)
2
11
14529343

(0,7)
4
11
14529343


update test_table
   set balance = 11 
 where last_block = '14529343'; 

select ctid,  tt.* from test_table tt;
3 rows affected



ctid
id
balance
last_block


(0,3)
3
22
162529343

(0,8)
1
11
14529343

(0,9)
2
11
14529343

(0,10)
4
11
14529343


update test_table
   set balance = 11 
 where last_block = '14529343'
   and balance <> 11; 
Select count(*)
  from test_table
 where last_block = '14529343';


count


3


Select count(*)
  from test_table
 where last_block = '14529343'
   and balance <> 11; 


count


0

ctid	id	balance	last_block
(0,1)	1	22	14529343
(0,2)	2	2	14529343
(0,3)	3	22	162529343
(0,4)	4	4	14529343

ctid	id	balance	last_block
(0,3)	3	22	162529343
(0,5)	1	11	14529343
(0,6)	2	11	14529343
(0,7)	4	11	14529343

ctid	id	balance	last_block
(0,3)	3	22	162529343
(0,8)	1	11	14529343
(0,9)	2	11	14529343
(0,10)	4	11	14529343