Postgres 10

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?.
drop table if exists classes;
create table classes(id serial primary key, name text);
insert into classes (name) values
('Class A');

drop table if exists orders;
create table orders(id serial primary key, class_id int, name text);
insert into orders (class_id, name) values
(1, 'Order 1');

drop table if exists families;
create table families(id serial primary key, order_id int, name text);
insert into families (order_id, name) values
(1, 'Family 1');

drop table if exists species;
create table species(id serial primary key, family_id int, name text);
insert into species (family_id, name) values
(1, 'Species 1'),
(1, 'Species 2'),
(1, 'Species 3'),
(1, 'Species 4');

DROP TABLE



CREATE TABLE



INSERT 0 1



DROP TABLE



CREATE TABLE



INSERT 0 1



DROP TABLE



CREATE TABLE



INSERT 0 1



DROP TABLE



CREATE TABLE



INSERT 0 4


SELECT classes.id as class_id, orders.id as order_id, families.id as family_id, species.id as species_id
FROM species
JOIN families ON families.id = species.family_id
JOIN orders ON orders.id = families.order_id
JOIN classes ON classes.id = orders.class_id


class_id
order_id
family_id
species_id


1
1
1
1

1
1
1
2

1
1
1
3

1
1
1
4



SELECT 4


select jsonb_pretty(classes) from (

select
    jsonb_agg(jsonb_build_object(
        'id', id, 
        'class', name, 
        'orders', orders)
        order by id
    ) as classes
from classes
join (
    select
        class_id,
        jsonb_agg(jsonb_build_object(
            'id', id, 
            'order', name, 
            'families', families)
            order by id
        ) as orders
    from orders
    join (
        select 
            order_id, 
            jsonb_agg(jsonb_build_object(
                'id', id, 
                'family', name,
                'species', species)
                order by id
            ) as families
        from families
        join (
            select 
                family_id, 
                jsonb_agg(jsonb_build_object(
                    'id', id, 
                    'species', name)
                    order by id
                ) as species
            from species
            group by family_id
            ) s on id = family_id
        group by order_id
        ) f on id = order_id
    group by class_id
    ) o on id = class_id
    
) pretty_wrapper


jsonb_pretty


[
    {
        "id": 1,
        "class": "Class A",
        "orders": [
            {
                "id": 1,
                "order": "Order 1",
                "families": [
                    {
                        "id": 1,
                        "family": "Family 1",
                        "species": [
                            {
                                "id": 1,
                                "species": "Species 1"
                            },
                            {
                                "id": 2,
                                "species": "Species 2"
                            },
                            {
                                "id": 3,
                                "species": "Species 3"
                            },
                            {
                                "id": 4,
                                "species": "Species 4"
                            }
                        ]
                    }
                ]
            }
        ]
    }
]



SELECT 1