Postgres 13

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 phone (
  client_id numeric (6) not null,
  active numeric(1) not null check (active IN (0, 1)),
  value varchar(15)
);

CREATE TABLE

insert into phone (client_id, active, value) values (10, 0, '1111');

INSERT 0 1

insert into phone (client_id, active, value) values (10, 1, '3333');

INSERT 0 1

insert into phone (client_id, active, value) values (15, 0, '5555');

INSERT 0 1

insert into phone (client_id, active, value) values (15, 1, '6666');

INSERT 0 1

insert into phone (client_id, active, value) values (15, 0, '7777');

INSERT 0 1

create unique index ix1 on phone (client_id, (case when active = 1 then active end));

CREATE INDEX

select * from phone

client_id
active
value

10
0
1111

10
1
3333

15
0
5555

15
1
6666

15
0
7777

SELECT 5