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 tree (
dependent text NOT NULL,
prereq text NOT NULL,
PRIMARY KEY (dependent, prereq),
CHECK (dependent != prereq)
);
INSERT INTO tree VALUES
('B', 'A'),
('C', 'B'),
('F', 'D'),
('F', 'E'),
('G', 'E'),
('H', 'F'),
('H', 'G'),
('J', 'I'),
('K', 'I'),
('K', 'L'),
('N', 'J'),
('N', 'M'),
('P', 'O'),
('Q', 'P');
SELECT * FROM tree;
| dependent | prereq |
|---|---|
| B | A |
| C | B |
| F | D |
| F | E |
| G | E |
| H | F |
| H | G |
| J | I |
| K | I |
| K | L |
| N | J |
| N | M |
| P | O |
| Q | P |
SELECT 14
WITH RECURSIVE dependents AS (
SELECT
dependent,
array_agg(prereq) as prereqs
FROM
tree
GROUP BY dependent
), deletions AS (
SELECT array_cat(ARRAY['A', 'D', 'E', 'I'], array_agg(dependent))
FROM dependents
WHERE prereqs <@ ARRAY['A', 'D', 'E', 'I']
UNION
SELECT DISTINCT array_cat(del.array_cat, array_agg(dep.dependent) OVER ())
FROM dependents dep
JOIN deletions del
ON NOT(dep.dependent = ANY(del.array_cat)) AND dep.prereqs <@ del.array_cat
)
SELECT * FROM deletions
| array_cat |
|---|
| {A,D,E,I,J,F,B,G} |
| {A,D,E,I,J,F,B,G,H,C} |
SELECT 2