Postgres 9.6

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 widgets (id int, name text);
create table connections(id int, widget_id int);
create table modes_connections(connection_id int, mode_id int);
create table modes (id int, name text);
insert into widgets values
(1, 'widget1'), (2, 'widget2'),(3, 'widget3'),(4, 'widget4');

insert into connections values
(1, 1),(2, 1),(3, 1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),
(11, 2),(12, 2),(13, 3),(14, 3),(15, 4);

insert into modes_connections values
(1, 1),(1, 2),(1, 3),(1, 4),(1, 5),
(2, 1),(2, 2),(2, 3),(2, 4),(2, 5),
(2, 6),(2, 7),(2, 8),(2, 9),(2, 10),
(3, 5),(3, 6),(3, 7),(3, 8),(3, 9);

insert into modes values 
(1, 'A'),(2, 'B'),(3, 'C'),(4, 'D');
4 rows affected

15 rows affected

20 rows affected

4 rows affected

select w.id, w.name, m.id, m.name, count(c.id) connections
from   widgets w
join   connections c
on     c.widget_id = w.id
join   modes_connections mc
on     mc.connection_id = c.id
join   modes m
on     m.id = mc.mode_id
group by w.id, w.name, m.id, m.name 
;


id
name
id
name
connections


1
widget1
1
A
2

1
widget1
2
B
2

1
widget1
3
C
2

1
widget1
4
D
2


;with x as
(
select w.id, w.name, m.id mode_id, m.name mode_name, count(c.id) connections
from   widgets w
join   connections c
on     c.widget_id = w.id
join   modes_connections mc
on     mc.connection_id = c.id
join   modes m
on     m.id = mc.mode_id
group by w.id, w.name, m.id, m.name 
)
select id, name
from   widgets wdg
where  exists (select 1 from x where id=wdg.id and mode_id = 1 and connections = 2)
and    exists (select 1 from x where id=wdg.id and mode_id = 2 and connections = 2)


id
name


1
widget1