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

create table test(id integer primary key) ;

insert into test values (1),(2),(3) ;

CREATE TABLE

INSERT 0 3

explain update test set id = 5 where id = 2 ;

QUERY PLAN

Update on test  (cost=0.15..8.17 rows=0 width=0)

  ->  Index Scan using test_pkey on test  (cost=0.15..8.17 rows=1 width=10)

        Index Cond: (id = 2)

EXPLAIN

select * from test order by id

id

1

2

3

SELECT 3

explain analyse update test set id = 5 where id = 2 ;

QUERY PLAN

Update on test  (cost=0.15..8.17 rows=0 width=0) (actual time=0.052..0.052 rows=0 loops=1)

  ->  Index Scan using test_pkey on test  (cost=0.15..8.17 rows=1 width=10) (actual time=0.008..0.009 rows=1 loops=1)

        Index Cond: (id = 2)

Planning Time: 0.057 ms

Execution Time: 0.078 ms

EXPLAIN

select * from test order by id

id

1

3

5

SELECT 3