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');

insert into connections values
(1,1),
(2,1),
(3,1),
(4,1),
(5,2),
(6,2),
(7,2),
(8,2);

-- widget 1 has port_ids 1-4
  -- ports 1,2 support modes A,B,C
  -- ports 3,4 support mode B
-- widget 2 has port_ids 5-8:
  -- ports 5,6 support mode B
  -- ports 7,8 support modes A,B,C
insert into modes_connections values
(1,1),(1,2),(1,3),
(2,1),(2,2),(2,3),
(3,2),
(4,2),
(5,2),
(6,2),
(7,1),(7,2),(7,3),
(8,1),(8,2),(8,3);

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

8 rows affected

16 rows affected

3 rows affected

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 
;


id
name
mode_id
mode_name
connections


1
widget1
1
A
2

1
widget1
2
B
4

1
widget1
3
C
2

2
widget2
1
A
2

2
widget2
2
B
4

2
widget2
3
C
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 = 3 and connections >= 1)

-- here I'd expect widget1 and widget2, since both can support 2 ports of mode B and 2 ports of mode A



id
name


1
widget1

2
widget2

id	name	mode_id	mode_name	connections
1	widget1	1	A	2
1	widget1	2	B	4
1	widget1	3	C	2
2	widget2	1	A	2
2	widget2	2	B	4
2	widget2	3	C	2