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 example
(
node text,
connections text[],
nodes_in_chain text[]
);
INSERT INTO example VALUES
('a', ARRAY['a','b'], null),
('b', ARRAY['a','b','c','d'], null),
('c', ARRAY['b','c'], null),
('d', ARRAY['b','d'], null),
('e', ARRAY['e','f'], null),
('f', ARRAY['e','f'], null);
SELECT * FROM example;
6 rows affected
| node | connections | nodes_in_chain |
|---|---|---|
| a | {a,b} | null |
| b | {a,b,c,d} | null |
| c | {b,c} | null |
| d | {b,d} | null |
| e | {e,f} | null |
| f | {e,f} | null |
SELECT e1.node,
e1.connections,
COALESCE(e2.connections, e1.connections) nodes_in_chain
FROM example e1
LEFT JOIN example e2
ON e2.node <> e1.node
AND (e1.connections <@ e2.connections);
| node | connections | nodes_in_chain |
|---|---|---|
| a | {a,b} | {a,b,c,d} |
| b | {a,b,c,d} | {a,b,c,d} |
| c | {b,c} | {a,b,c,d} |
| d | {b,d} | {a,b,c,d} |
| e | {e,f} | {e,f} |
| f | {e,f} | {e,f} |
DELETE FROM example WHERE node = 'f';
INSERT INTO example VALUES
('f', ARRAY['e','f','g'], null),
('g', ARRAY['f','g','h'], null),
('h', ARRAY['g','h'], null);
1 rows affected
3 rows affected
WITH RECURSIVE al (dst, src) AS
(
SELECT e1.node, e2.node
FROM example e1
JOIN example e2
ON e1.node = any(e2.connections)
), tc (dst, src) AS
(
SELECT dst, src FROM al
UNION
SELECT a1.dst, a2.src
FROM al as a1
JOIN tc as a2
ON a1.src = a2.dst
)
SELECT src, array_agg(DISTINCT dst ORDER BY dst) AS nodes_in_chain
FROM tc
GROUP BY src;
| src | nodes_in_chain |
|---|---|
| a | {a,b,c,d} |
| b | {a,b,c,d} |
| c | {a,b,c,d} |
| d | {a,b,c,d} |
| e | {e,f,g,h} |
| f | {e,f,g,h} |
| g | {e,f,g,h} |
| h | {e,f,g,h} |
SELECT e1.node,
e1.connections,
COALESCE(e2.connections, e1.connections) nodes_in_chain
FROM example e1
LEFT JOIN example e2
ON e2.node <> e1.node
AND (e1.connections <@ e2.connections);
| node | connections | nodes_in_chain |
|---|---|---|
| a | {a,b} | {a,b,c,d} |
| b | {a,b,c,d} | {a,b,c,d} |
| c | {b,c} | {a,b,c,d} |
| d | {b,d} | {a,b,c,d} |
| e | {e,f} | {e,f,g} |
| f | {e,f,g} | {e,f,g} |
| g | {f,g,h} | {f,g,h} |
| h | {g,h} | {f,g,h} |