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} |