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 orders (
"id" INTEGER,
"description" VARCHAR(18),
"parent_id" VARCHAR(4),
"cost" DECIMAL(8,2)
);
INSERT INTO orders
("id", "description", "parent_id", "cost")
VALUES
('1', 'Radiology', NULL, '0.00'),
('2', 'Lab Tests', NULL, '0.00'),
('3', 'Normal Radiology', '1', '0.00'),
('4', 'Resonance', '1', '100.00'),
('1100', 'Cerebral Resonance', '4', '200.00'),
('1900', 'Blood Tests', '2', '10.00'),
('2044', 'Calcium', '2', '50.00');
7 rows affected
WITH RECURSIVE hierarchy AS (
SELECT id, 1 AS rown, CAST(description AS TEXT) AS parent_list, id as parent
FROM orders
WHERE parent_id is null
UNION
SELECT c.id
,rown + 1 as rown
,CAST(repeat(' ', rown) || ' --> ' || c.description as text) as parent_list
,parent
FROM orders c
INNER JOIN hierarchy c2 ON CAST(c.parent_id AS INTEGER) = c2.id )
SELECT id,parent_list FROM hierarchy
ORDER BY parent DESC,rown
| id | parent_list |
|---|---|
| 2 | Lab Tests |
| 1900 | --> Blood Tests |
| 2044 | --> Calcium |
| 1 | Radiology |
| 4 | --> Resonance |
| 3 | --> Normal Radiology |
| 1100 | --> Cerebral Resonance |
WITH RECURSIVE hierarchy AS (
SELECT id, CAST(description AS TEXT) AS parent_list, 1 AS rown, id as parent
FROM orders
WHERE parent_id is null
UNION
SELECT c.id
,CAST(c2.parent_list || ' --> ' || c.description as text) as parent_list
,rown + 1 as rwon
,parent
FROM orders c
INNER JOIN hierarchy c2 ON CAST(c.parent_id AS INTEGER) = c2.id )
SELECT id, parent_list
FROM hierarchy
GROUP BY id, parent_list
ORDER BY parent_list;
| id | parent_list |
|---|---|
| 2 | Lab Tests |
| 1900 | Lab Tests --> Blood Tests |
| 2044 | Lab Tests --> Calcium |
| 1 | Radiology |
| 3 | Radiology --> Normal Radiology |
| 4 | Radiology --> Resonance |
| 1100 | Radiology --> Resonance --> Cerebral Resonance |