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 names_categories (
    id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    created_at TIMESTAMPTZ DEFAULT now(),
    thing_name TEXT NOT NULL, 
    thing_category TEXT NOT NULL
);

CREATE TABLE relator (
    id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    created_at TIMESTAMPTZ DEFAULT now(),
    parent_name TEXT NOT NULL, 
    child_name TEXT NOT NULL,
    child_quantity INTEGER NOT NULL 
);

CREATE TABLE sales (
    id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    created_at TIMESTAMPTZ DEFAULT now(),    
    sold_name TEXT NOT NULL,
    sold_quantity INTEGER NOT NULL
);

INSERT INTO names_categories (thing_name, thing_category)
VALUES ('parent1', 'bundle'), ('child1', 'assembly'), ('subChild1', 'component'), ('subChild2', 'component');

INSERT INTO relator (parent_name, child_name, child_quantity)
VALUES ('parent1', 'child1', 1),('child1', 'subChild1', 10), ('child1', 'subChild2', 2);

INSERT INTO sales (sold_name, sold_quantity)
VALUES ('parent1', 1), ('parent1', 2);

SELECT * FROM names_categories;
SELECT * FROM relator;
SELECT * FROM sales;
4 rows affected

3 rows affected

2 rows affected



id
created_at
thing_name
thing_category


1
2021-03-05 08:02:35.955324+00
parent1
bundle

2
2021-03-05 08:02:35.955324+00
child1
assembly

3
2021-03-05 08:02:35.955324+00
subChild1
component

4
2021-03-05 08:02:35.955324+00
subChild2
component




id
created_at
parent_name
child_name
child_quantity


1
2021-03-05 08:02:35.955324+00
parent1
child1
1

2
2021-03-05 08:02:35.955324+00
child1
subChild1
10

3
2021-03-05 08:02:35.955324+00
child1
subChild2
2




id
created_at
sold_name
sold_quantity


1
2021-03-05 08:02:35.955324+00
parent1
1

2
2021-03-05 08:02:35.955324+00
parent1
2


CREATE VIEW relationships_with_child_catetgory AS (
    SELECT 
    r.parent_name,
    r.child_name, 
    r.child_quantity,
    n.thing_category AS child_category
    FROM 
    relator r 
    INNER JOIN 
    names_categories n 
    ON r.child_name = n.thing_name 
);

SELECT * FROM relationships_with_child_catetgory


parent_name
child_name
child_quantity
child_category


parent1
child1
1
assembly

child1
subChild1
10
component

child1
subChild2
2
component


WITH RECURSIVE cte AS (
    SELECT 
        s.sold_name,
        s.sold_quantity,
        r.child_name,
        r.child_quantity,
        nc.thing_category as category
    FROM 
        sales s
    JOIN relator r
    ON s.sold_name = r.parent_name
    JOIN names_categories nc
    ON r.child_name = nc.thing_name
    
    UNION ALL
    
    SELECT
        cte.sold_name,
        cte.sold_quantity,
        r.child_name,
        r.child_quantity,
        nc.thing_category
    FROM cte
    JOIN relator r ON cte.child_name = r.parent_name
    JOIN names_categories nc
    ON r.child_name = nc.thing_name
)
SELECT
    child_name,
    SUM(sold_quantity * child_quantity)
FROM cte
WHERE category = 'component'
GROUP BY child_name


child_name
sum


subChild2
6

subChild1
30

id	created_at	thing_name	thing_category
1	2021-03-05 08:02:35.955324+00	parent1	bundle
2	2021-03-05 08:02:35.955324+00	child1	assembly
3	2021-03-05 08:02:35.955324+00	subChild1	component
4	2021-03-05 08:02:35.955324+00	subChild2	component