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
| id | root_id | parent_id | status | ts | comment |
|---|---|---|---|---|---|
| 1 | null | null | COMPLETE | 2023-08-09 11:50:27.644319 | >90 days old, complete, standalone |
| 2 | null | null | COMPLETE | 2023-08-09 11:50:27.644319 | >90 days old, complete, root of 3,4 |
| 3 | 2 | null | COMPLETE | 2023-08-09 11:50:27.644319 | >90 days old, complete, child of 2, parent of 4 |
| 4 | 2 | 3 | OPEN | 2023-08-09 11:50:27.644319 | >90 days old, open, child of 2,3 |
| 5 | null | null | COMPLETE | 2023-08-09 11:50:27.644319 | >90 days old, complete, root of 6,7 |
| 6 | 5 | null | COMPLETE | 2023-08-09 11:50:27.644319 | >90 days old, complete, child of 5, parent of 4 |
| 7 | 5 | 6 | COMPLETE | 2023-10-30 11:50:27.644319 | <=90 days old, complete, child of 5,6 |
INSERT 0 7