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)
);
insert into phone (client_id, active, value) values (10, 0, '1111');
1 rows affected

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

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

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

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

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


client_id
active
value


10
0
1111

10
1
3333

15
0
5555

15
1
6666

15
0
7777