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 m (id bigserial, category int, object text, start int, "end" int);
insert into m (category, object, start, "end") values
(1, 'A', 10, 22),
(1, 'A', 26, 47),
(1, 'A', 18, 47),
(1, 'C', 17, 44),
(1, 'C', 30, 47),
(2, 'A', 4, 24),
(2, 'B', 5, 32),
(2, 'C', 5, 26),
(2, 'C', 25, 43),
(2, 'C', 12, 48),
(3, 'A', 16, 30),
(3, 'A', 4, 40),
(3, 'B', 7, 47),
(3, 'C', 28, 33),
(3, 'C', 13, 31);
with
-- Where do segments intersect each other?
cuts as
(
select category, object, start t from m
union -- Without "all" to deduplicate.
select category, object, "end" from m
),
-- Give those cuts an index, and compute the stop of the subsegment they start
-- (the last cut having no next cut, will get a null stop:
-- thus it's the only one that will not be considered a segment in the following joins)
icuts as
(
select
row_number() over () id,
category, object,
t ts, lead(t) over w te, lead(t) over w - t + 1 len
from cuts
CREATE TABLE
INSERT 0 15
| category | object | totaloverlap |
|---|---|---|
| 2 | C | 34 |
| 1 | A | 27 |
| 1 | C | 15 |
| 3 | A | 15 |
| 3 | C | 4 |
SELECT 5