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 product (p_id varchar PRIMARY KEY);
CREATE TABLE category (c_id varchar PRIMARY KEY, parent_c_id varchar);
CREATE TABLE product_category (
    p_id varchar,
    c_id varchar,
    PRIMARY KEY (p_id, c_id),
    FOREIGN KEY (p_id) REFERENCES product (p_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (c_id) REFERENCES category (c_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);
                                                                       
INSERT INTO product (p_id) VALUES
('p_01'),
('p_02'),
('p_03'),
('p_04'),
('p_05');

SELECT * FROM product;
5 rows affected



p_id


p_01

p_02

p_03

p_04

p_05


INSERT INTO category (c_id, parent_c_id) VALUES
('c_0_1', NULL),
-- L1
('c_1_1', 'c_0_1'),
('c_1_2', 'c_0_1'),
('c_1_3', 'c_0_1'),
-- L2
('c_2_1', 'c_1_1'),
('c_2_2', 'c_1_1'),
('c_2_3', 'c_1_2'),
('c_2_4', 'c_1_3'),
-- L3
('c_3_1', 'c_2_1'),
('c_3_2', 'c_2_2'),
('c_3_3', 'c_2_3'),
('c_3_4', 'c_2_4'),
-- L4
('c_4_1', 'c_3_1'),
('c_4_2', 'c_3_2'),
('c_4_3', 'c_3_3'),
('c_4_4', 'c_3_4');

SELECT * FROM category;
16 rows affected



c_id
parent_c_id


c_0_1
null

c_1_1
c_0_1

c_1_2
c_0_1

c_1_3
c_0_1

c_2_1
c_1_1

c_2_2
c_1_1

c_2_3
c_1_2

c_2_4
c_1_3

c_3_1
c_2_1

c_3_2
c_2_2

c_3_3
c_2_3

c_3_4
c_2_4

c_4_1
c_3_1

c_4_2
c_3_2

c_4_3
c_3_3

c_4_4
c_3_4


INSERT INTO product_category (p_id, c_id) VALUES
-- p_01 explicitly attached to every level in path 1; include.
('p_01', 'c_0_1'),
('p_01', 'c_2_1'),
('p_01', 'c_3_1'),
('p_01', 'c_4_1'),
-- p_02 explicitly attached to desired level in paths 1 and 3; include both.
('p_02', 'c_3_3'),
('p_02', 'c_3_4'),
-- p_03 explicitly attached to super-level in path 3; exclude.
('p_03', 'c_2_4'),
-- p_04 explicitly attached to sub-level in path 1,
-- transitively to desired level in path 1; include.
('p_04', 'c_4_2');
-- p_05 not attached at all.

SELECT * FROM product_category;
8 rows affected



p_id
c_id


p_01
c_0_1

p_01
c_2_1

p_01
c_3_1

p_01
c_4_1

p_02
c_3_3

p_02
c_3_4

p_03
c_2_4

p_04
c_4_2


WITH RECURSIVE cte AS (
    SELECT c_id, parent_c_id, 0 as level
    FROM category
    WHERE parent_c_id IS NULL
    
    UNION
    
    SELECT
        c.c_id,
        cte.parent_c_id,
        cte.level + 1
    FROM
        category c
    JOIN 
        cte
    ON c.parent_c_id = cte.c_id
)
SELECT * FROM cte


c_id
parent_c_id
level


c_0_1
null
0

c_1_1
null
1

c_1_2
null
1

c_1_3
null
1

c_2_1
null
2

c_2_2
null
2

c_2_3
null
2

c_2_4
null
2

c_3_1
null
3

c_3_2
null
3

c_3_3
null
3

c_3_4
null
3

c_4_1
null
4

c_4_2
null
4

c_4_3
null
4

c_4_4
null
4


WITH RECURSIVE cte AS (
    SELECT c_id, parent_c_id, 0 as level, NULL AS level3_category
    FROM category
    WHERE parent_c_id IS NULL
    
    UNION
    
    SELECT
        c.c_id,
        cte.parent_c_id,
        cte.level + 1,
        CASE 
            WHEN cte.level + 1 = 3 THEN c.c_id
            ELSE cte.level3_category
        END
    FROM
        category c
    JOIN 
        cte
    ON c.parent_c_id = cte.c_id
)
SELECT * FROM cte


c_id
parent_c_id
level
level3_category


c_0_1
null
0
null

c_1_1
null
1
null

c_1_2
null
1
null

c_1_3
null
1
null

c_2_1
null
2
null

c_2_2
null
2
null

c_2_3
null
2
null

c_2_4
null
2
null

c_3_1
null
3
c_3_1

c_3_2
null
3
c_3_2

c_3_3
null
3
c_3_3

c_3_4
null
3
c_3_4

c_4_1
null
4
c_3_1

c_4_2
null
4
c_3_2

c_4_3
null
4
c_3_3

c_4_4
null
4
c_3_4


WITH RECURSIVE cte AS (
    SELECT c_id, parent_c_id, 0 as level, NULL AS level3_category
    FROM category
    WHERE parent_c_id IS NULL
    
    UNION
    
    SELECT
        c.c_id,
        cte.parent_c_id,
        cte.level + 1,
        CASE 
            WHEN cte.level + 1 = 3 THEN c.c_id
            ELSE cte.level3_category
        END
    FROM
        category c
    JOIN 
        cte
    ON c.parent_c_id = cte.c_id
)
SELECT 
    * 
FROM 
    cte
JOIN 
    product_category pc
ON cte.c_id = pc.c_id AND cte.level3_category IS NOT NULL


c_id
parent_c_id
level
level3_category
p_id
c_id


c_3_1
null
3
c_3_1
p_01
c_3_1

c_3_3
null
3
c_3_3
p_02
c_3_3

c_3_4
null
3
c_3_4
p_02
c_3_4

c_4_1
null
4
c_3_1
p_01
c_4_1

c_4_2
null
4
c_3_2
p_04
c_4_2


WITH RECURSIVE cte AS (
    SELECT c_id, parent_c_id, 0 as level, NULL AS level3_category
    FROM category
    WHERE parent_c_id IS NULL
    
    UNION
    
    SELECT
        c.c_id,
        cte.parent_c_id,
        cte.level + 1,
        CASE 
            WHEN cte.level + 1 = 3 THEN c.c_id
            ELSE cte.level3_category
        END
    FROM
        category c
    JOIN 
        cte
    ON c.parent_c_id = cte.c_id
)
SELECT 
    p_id,
    ARRAY_AGG(DISTINCT level3_category) as c_id
FROM 
    cte
JOIN 
    product_category pc
ON cte.c_id = pc.c_id AND cte.level3_category IS NOT NULL
GROUP BY p_id


p_id
c_id


p_01
{c_3_1}

p_02
{c_3_3,c_3_4}

p_04
{c_3_2}
c_id	parent_c_id
c_0_1	null
c_1_1	c_0_1
c_1_2	c_0_1
c_1_3	c_0_1
c_2_1	c_1_1
c_2_2	c_1_1
c_2_3	c_1_2
c_2_4	c_1_3
c_3_1	c_2_1
c_3_2	c_2_2
c_3_3	c_2_3
c_3_4	c_2_4
c_4_1	c_3_1
c_4_2	c_3_2
c_4_3	c_3_3
c_4_4	c_3_4
p_id	c_id
p_01	c_0_1
p_01	c_2_1
p_01	c_3_1
p_01	c_4_1
p_02	c_3_3
p_02	c_3_4
p_03	c_2_4
p_04	c_4_2