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 demo (A int, B int);
insert into demo select generate_series(1,1000),generate_series(1,1000)
1000 rows affected

create index demo_a on demo (A ASC);
create index demo_b on demo (B ASC);
vacuum analyze demo;
explain select * from demo where A=999

QUERY PLAN

Index Scan using demo_a on demo  (cost=0.28..8.29 rows=1 width=8)

  Index Cond: (a = 999)

explain select * from demo where B=999

QUERY PLAN

Index Scan using demo_b on demo  (cost=0.28..8.29 rows=1 width=8)

  Index Cond: (b = 999)

explain select * from demo where A=999 and B=999

QUERY PLAN

Index Scan using demo_b on demo  (cost=0.28..8.29 rows=1 width=8)

  Index Cond: (b = 999)

  Filter: (a = 999)

explain select * from demo where A=999 and B>999

QUERY PLAN

Index Scan using demo_b on demo  (cost=0.28..8.29 rows=1 width=8)

  Index Cond: (b > 999)

  Filter: (a = 999)

explain select * from demo where A>999 and B=999

QUERY PLAN

Index Scan using demo_b on demo  (cost=0.28..8.29 rows=1 width=8)

  Index Cond: (b = 999)

  Filter: (a > 999)

explain select * from demo where A>999 and B>999

QUERY PLAN

Index Scan using demo_b on demo  (cost=0.28..8.29 rows=1 width=8)

  Index Cond: (b > 999)

  Filter: (a > 999)