Postgres 12

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 servers(ID int, Name varchar(50));

create table virtual_machines (ID int, Name varchar(50), serverName varchar(50));
create table Services (ID int, Name varchar(50), vmName varchar(50));
insert into servers values( 1 , 'Server1');
insert into virtual_machines values(1,'vm1','Server1');
insert into virtual_machines values(2,'vm2','Server1');

insert into Services values(1,'service1','vm1');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

with vm_services as
(
    SELECT vm.id,vm.name, vm.servername
         ,jsonb_agg    ( json_build_object('id', s.id, 'name', s.name, 'vmName', s.vmname))Services
    FROM virtual_machines vm left join services s on vm.name = s.vmname
    group by vm.id,vm.name,vm.servername
    order by vm.id
)
SELECT srv.id,srv.name
     ,jsonb_agg    ( json_build_object('id', vm.id, 'name', vm.name, 'serverName', vm.serverName,'Services',vm.services))virtual_machines
FROM servers srv inner join vm_services vm on vm.serverName = srv.Name
group by srv.id,srv.name


id
name
virtual_machines


1
Server1
[{"id": 2, "name": "vm2", "Services": [{"id": null, "name": null, "vmName": null}], "serverName": "Server1"}, {"id": 1, "name": "vm1", "Services": [{"id": 1, "name": "service1", "vmName": "vm1"}], "serverName": "Server1"}]