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 edge (
parent_id int not null,
child_id int not null,
value float not null,
span daterange not null
);
CREATE TABLE
create index edge_span_idx on edge using gist(span);
CREATE INDEX
insert into edge(parent_id, child_id, value, span) values
(1,2,0, daterange('2023-01-01','2023-01-10', '[]')),
(1,2,0, daterange('2023-01-11','2023-01-20', '[]')),
(1,2,0, daterange('2023-01-21','infinity', '[]')),
(1,2,0, daterange('2022-01-01', '2022-02-01', '[]')),
(1,3,0, daterange('2023-01-01','2023-01-10', '[]')),
(1,3,0, daterange('2023-01-11','2023-01-20', '[]')),
(1,3,1, daterange('2023-01-21','infinity', '[]'))
INSERT 0 7
select * from edge;
| parent_id | child_id | value | span |
|---|---|---|---|
| 1 | 2 | 0 | [2023-01-01,2023-01-11) |
| 1 | 2 | 0 | [2023-01-11,2023-01-21) |
| 1 | 2 | 0 | [2023-01-21,infinity] |
| 1 | 2 | 0 | [2022-01-01,2022-02-02) |
| 1 | 3 | 0 | [2023-01-01,2023-01-11) |
| 1 | 3 | 0 | [2023-01-11,2023-01-21) |
| 1 | 3 | 1 | [2023-01-21,infinity] |
SELECT 7
select parent_id, child_id, value, range_agg(span)
from edge
group by parent_id, child_id, value
| parent_id | child_id | value | range_agg |
|---|---|---|---|
| 1 | 3 | 0 | {[2023-01-01,2023-01-21)} |
| 1 | 2 | 0 | {[2022-01-01,2022-02-02),[2023-01-01,infinity]} |
| 1 | 3 | 1 | {[2023-01-21,infinity]} |
SELECT 3
select parent_id, child_id, value, unnest(range_agg(span)) as span
from edge
group by parent_id, child_id, value
| parent_id | child_id | value | span |
|---|---|---|---|
| 1 | 3 | 0 | [2023-01-01,2023-01-21) |
| 1 | 2 | 0 | [2022-01-01,2022-02-02) |
| 1 | 2 | 0 | [2023-01-01,infinity] |
| 1 | 3 | 1 | [2023-01-21,infinity] |
SELECT 4