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:59:05.826174 | >90 days old, complete, standalone |
2 | null | null | COMPLETE | 2023-08-09 11:59:05.826174 | >90 days old, complete, root of 3,4 |
3 | 2 | null | COMPLETE | 2023-08-09 11:59:05.826174 | >90 days old, complete, child of 2, parent of 4 |
4 | 2 | 3 | OPEN | 2023-08-09 11:59:05.826174 | >90 days old, open, child of 2,3 |
5 | null | null | COMPLETE | 2023-08-09 11:59:05.826174 | >90 days old, complete, root of 6,7 |
6 | 5 | null | COMPLETE | 2023-08-09 11:59:05.826174 | >90 days old, complete, child of 5, parent of 4 |
7 | 5 | 6 | COMPLETE | 2023-10-30 11:59:05.826174 | <=90 days old, complete, child of 5,6 |
INSERT 0 7
BEGIN
id | root_id | parent_id | status | ts | comment |
---|---|---|---|---|---|
1 | null | null | COMPLETE | 2023-08-09 11:59:05.826174 | >90 days old, complete, standalone |
DELETE 1
ROLLBACK
INSERT 0 10
id | root_id | parent_id | status | ts | comment |
---|---|---|---|---|---|
1 | null | null | COMPLETE | 2023-08-09 11:59:05.826174 | >90 days old, complete, standalone |
2 | null | null | COMPLETE | 2023-08-09 11:59:05.826174 | >90 days old, complete, root of 3,4 |
3 | 2 | null | COMPLETE | 2023-08-09 11:59:05.826174 | >90 days old, complete, child of 2, parent of 4 |
4 | 2 | 3 | OPEN | 2023-08-09 11:59:05.826174 | >90 days old, open, child of 2,3 |
5 | null | null | COMPLETE | 2023-08-09 11:59:05.826174 | >90 days old, complete, root of 6,7 |
6 | 5 | null | COMPLETE | 2023-08-09 11:59:05.826174 | >90 days old, complete, child of 5, parent of 4 |
7 | 5 | 6 | COMPLETE | 2023-10-30 11:59:05.826174 | <=90 days old, complete, child of 5,6 |
8 | null | null | COMPLETE | 2023-10-30 11:59:05.846666 | <=90 days old, complete, standalone |
9 | null | null | COMPLETE | 2023-10-30 11:59:05.846666 | <=90 days old, complete, root of 10 |
10 | 9 | null | COMPLETE | 2023-08-09 11:59:05.846666 | >90 days old, complete, child of 9 |
11 | 11 | null | COMPLETE | 2023-08-09 11:59:05.846666 | >90 days old, complete, parent/child of self |
12 | null | 12 | COMPLETE | 2023-08-09 11:59:05.846666 | >90 days old, complete, parent/child of self |
13 | 14 | null | COMPLETE | 2023-08-09 11:59:05.846666 | >90 days old, complete, cross-parent/child of 14 |
14 | 13 | null | COMPLETE | 2023-08-09 11:59:05.846666 | >90 days old, complete, cross-parent/child of 13 |
15 | null | null | COMPLETE | 2023-08-09 11:59:05.846666 | >90 days old, complete, parent of 16,17 |
16 | null | 15 | COMPLETE | 2023-08-09 11:59:05.846666 | >90 days old, complete, child of 15 |
17 | null | 15 | OPEN | 2023-10-30 11:59:05.846666 | <=90 days old, open, child of 15 |
SELECT 17
BEGIN
id | root_id | parent_id | status | ts | comment |
---|---|---|---|---|---|
1 | null | null | COMPLETE | 2023-08-09 11:59:05.826174 | >90 days old, complete, standalone |
11 | 11 | null | COMPLETE | 2023-08-09 11:59:05.846666 | >90 days old, complete, parent/child of self |
12 | null | 12 | COMPLETE | 2023-08-09 11:59:05.846666 | >90 days old, complete, parent/child of self |
13 | 14 | null | COMPLETE | 2023-08-09 11:59:05.846666 | >90 days old, complete, cross-parent/child of 14 |
14 | 13 | null | COMPLETE | 2023-08-09 11:59:05.846666 | >90 days old, complete, cross-parent/child of 13 |
16 | null | 15 | COMPLETE | 2023-08-09 11:59:05.846666 | >90 days old, complete, child of 15 |
DELETE 6
ROLLBACK