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 account (
acct_id int PRIMARY KEY
, parent_id int REFERENCES account(acct_id)
, acct_name text
);
INSERT INTO account (acct_id, parent_id, acct_name) values
(1, 1, 'account 1')
, (2, 1, 'account 2')
, (3, 1, 'account 3')
, (4, 2, 'account 4')
, (5, 2, 'account 5')
, (6, 3, 'account 6')
, (7, 4, 'account 7')
, (8, 7, 'account 8')
, (9, 7, 'account 9')
;
CREATE TABLE
INSERT 0 9
WITH RECURSIVE search_graph AS (
SELECT parent_id, ARRAY[acct_id] AS path
FROM account
UNION ALL
SELECT g.parent_id, sg.path || g.acct_id
FROM search_graph sg
JOIN account g ON g.acct_id = sg.parent_id
WHERE g.acct_id <> ALL(sg.path)
)
SELECT path[1] AS child
, path[array_upper(path,1)] AS parent
, path
FROM search_graph
ORDER BY path;
| child | parent | path |
|---|---|---|
| 1 | 1 | {1} |
| 2 | 2 | {2} |
| 2 | 1 | {2,1} |
| 3 | 3 | {3} |
| 3 | 1 | {3,1} |
| 4 | 4 | {4} |
| 4 | 2 | {4,2} |
| 4 | 1 | {4,2,1} |
| 5 | 5 | {5} |
| 5 | 2 | {5,2} |
| 5 | 1 | {5,2,1} |
| 6 | 6 | {6} |
| 6 | 3 | {6,3} |
| 6 | 1 | {6,3,1} |
| 7 | 7 | {7} |
| 7 | 4 | {7,4} |
| 7 | 2 | {7,4,2} |
| 7 | 1 | {7,4,2,1} |
| 8 | 8 | {8} |
| 8 | 7 | {8,7} |
| 8 | 4 | {8,7,4} |
| 8 | 2 | {8,7,4,2} |
| 8 | 1 | {8,7,4,2,1} |
| 9 | 9 | {9} |
| 9 | 7 | {9,7} |
| 9 | 4 | {9,7,4} |
| 9 | 2 | {9,7,4,2} |
| 9 | 1 | {9,7,4,2,1} |
SELECT 28
-- Cheaper if we know the only circular path possible is a self-reference:
WITH RECURSIVE search_graph AS (
SELECT parent_id, ARRAY[acct_id] AS path, acct_id <> parent_id AS keep_going
FROM account
UNION ALL
SELECT g.parent_id, sg.path || g.acct_id, g.acct_id <> g.parent_id AS keep_going
FROM search_graph sg
JOIN account g ON g.acct_id = sg.parent_id
WHERE keep_going
)
SELECT path[1] AS child
, path[array_upper(path,1)] AS parent
, path
FROM search_graph
ORDER BY path;
| child | parent | path |
|---|---|---|
| 1 | 1 | {1} |
| 2 | 2 | {2} |
| 2 | 1 | {2,1} |
| 3 | 3 | {3} |
| 3 | 1 | {3,1} |
| 4 | 4 | {4} |
| 4 | 2 | {4,2} |
| 4 | 1 | {4,2,1} |
| 5 | 5 | {5} |
| 5 | 2 | {5,2} |
| 5 | 1 | {5,2,1} |
| 6 | 6 | {6} |
| 6 | 3 | {6,3} |
| 6 | 1 | {6,3,1} |
| 7 | 7 | {7} |
| 7 | 4 | {7,4} |
| 7 | 2 | {7,4,2} |
| 7 | 1 | {7,4,2,1} |
| 8 | 8 | {8} |
| 8 | 7 | {8,7} |
| 8 | 4 | {8,7,4} |
| 8 | 2 | {8,7,4,2} |
| 8 | 1 | {8,7,4,2,1} |
| 9 | 9 | {9} |
| 9 | 7 | {9,7} |
| 9 | 4 | {9,7,4} |
| 9 | 2 | {9,7,4,2} |
| 9 | 1 | {9,7,4,2,1} |
SELECT 28