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 tbl ("EmployeePositionId" int, "Subdivision" int, "Parent" int, level int);
INSERT INTO tbl VALUES
(4718, 485, 42, 1)
, (4719, 5064, 485, 2)
, (4720, 5065, 5064, 3)
, (4721, 5065, 5064, 3)
, (4722, 3000, NULL, 0)
, (6718, 8485, 43, 1)
, (6719, 8064, 8485, 2)
, (6720, 8065, 8064, 3)
, (6721, 8065, 8064, 3)
, (6722, 7000, NULL, 0)
;
10 rows affected
-- your example:
WITH RECURSIVE init AS (
SELECT "Subdivision", "Parent", level
FROM tbl
WHERE "EmployeePositionId" IN (4719, 4720, 4721) -- input
)
, cte AS (
TABLE init
UNION
SELECT c."Parent", t."Parent", c.level - 1
FROM cte c
LEFT JOIN tbl t ON t."Subdivision" = c."Parent" -- terminate recursion with NULL row
WHERE c."Parent" IS NOT NULL
)
, agg AS (
SELECT level, min("Subdivision") AS "Subdivision", count(*) AS ct
FROM cte
GROUP BY level
)
SELECT "Subdivision"
FROM agg a
WHERE ct = 1 -- no other live branch
AND level < (SELECT max(level) FROM cte WHERE "Parent" IS NULL) IS NOT TRUE -- no earler dead end
AND level <= (SELECT min(level) FROM init) -- include highest (least) level
ORDER BY level DESC -- pick earliest (greatest) qualifying level
LIMIT 1;
| Subdivision |
|---|
| 5064 |
-- no common parent
-- your example:
WITH RECURSIVE init AS (
SELECT "Subdivision", "Parent", level
FROM tbl
WHERE "EmployeePositionId" IN (4719, 4720, 4721, 4722) -- input
)
, cte AS (
TABLE init
UNION
SELECT c."Parent", t."Parent", c.level - 1
FROM cte c
LEFT JOIN tbl t ON t."Subdivision" = c."Parent" -- terminate recursion with NULL row
WHERE c."Parent" IS NOT NULL
)
, agg AS (
SELECT level, min("Subdivision") AS "Subdivision", count(*) AS ct
FROM cte
GROUP BY level
)
SELECT "Subdivision"
FROM agg a
WHERE ct = 1 -- no other live branch
AND level < (SELECT max(level) FROM cte WHERE "Parent" IS NULL) IS NOT TRUE -- no earler dead end
AND level <= (SELECT min(level) FROM init) -- include highest (least) level
ORDER BY level DESC -- pick earliest (greatest) qualifying level
LIMIT 1;