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

set enable_bitmapscan=off

SET

create table demo (A int, B int);

CREATE TABLE

insert into demo select generate_series(1,1000),generate_series(1,1000)

INSERT 0 1000

create index demo_ab on demo (A ASC, B ASC);

CREATE INDEX

--create index demo_ba on demo (B ASC, A ASC);
vacuum analyze demo;

VACUUM

explain select * from demo where A=999

QUERY PLAN

Index Only Scan using demo_ab on demo  (cost=0.28..4.29 rows=1 width=8)

  Index Cond: (a = 999)

EXPLAIN

explain select * from demo where B=999

QUERY PLAN

Seq Scan on demo  (cost=0.00..17.50 rows=1 width=8)

  Filter: (b = 999)

EXPLAIN

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

QUERY PLAN

Index Only Scan using demo_ab on demo  (cost=0.28..4.29 rows=1 width=8)

  Index Cond: ((a = 999) AND (b = 999))

EXPLAIN

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

QUERY PLAN

Index Only Scan using demo_ab on demo  (cost=0.28..4.29 rows=1 width=8)

  Index Cond: ((a = 999) AND (b > 999))

EXPLAIN

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

QUERY PLAN

Index Only Scan using demo_ab on demo  (cost=0.28..4.29 rows=1 width=8)

  Index Cond: ((a > 999) AND (b = 999))

EXPLAIN

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

QUERY PLAN

Index Only Scan using demo_ab on demo  (cost=0.28..4.29 rows=1 width=8)

  Index Cond: ((a > 999) AND (b > 999))

EXPLAIN