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 table_name (ID, Start_Date, End_Date) AS
SELECT '001', DATE '2005-01-01', DATE '2006-01-01' UNION ALL
SELECT '001', DATE '2005-01-01', DATE '2007-01-01' UNION ALL
SELECT '001', DATE '2008-01-01', DATE '2008-06-01' UNION ALL
SELECT '001', DATE '2008-04-01', DATE '2008-12-01' UNION ALL
SELECT '001', DATE '2010-01-01', DATE '2010-05-01' UNION ALL
SELECT '001', DATE '2010-04-01', DATE '2010-12-01' UNION ALL
SELECT '001', DATE '2010-11-01', DATE '2012-01-01' UNION ALL
SELECT '002', DATE '2010-11-01', DATE '2010-11-30' UNION ALL
SELECT '002', DATE '2010-11-02', DATE '2010-11-09' UNION ALL
SELECT '002', DATE '2010-11-11', DATE '2010-11-20' UNION ALL
SELECT '002', DATE '2010-11-24', DATE '2010-12-02' UNION ALL
SELECT '002', DATE '2010-12-05', DATE '2010-12-06' UNION ALL
SELECT '002', DATE '2010-12-06', DATE '2010-12-08';
13 rows affected
SELECT id,
MAX(CASE cnt WHEN 1 THEN dt END) AS start_date,
MAX(CASE cnt WHEN 0 THEN dt END) AS end_date
FROM (
SELECT id,
dt,
SUM(cnt) OVER (PARTITION BY id ORDER BY dt) AS grp,
cnt
FROM (
SELECT ID,
dt,
SUM(type) OVER (PARTITION BY id ORDER BY dt, rn) * type AS cnt
FROM (
SELECT r.*,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt) AS rn
FROM (
SELECT id, 1 AS type, start_date AS dt FROM table_name
UNION ALL
SELECT id, -1 AS type, end_date AS dt FROM table_name
) r
) p
) s
WHERE cnt IN (1,0)
) t
GROUP BY id, grp
| id | start_date | end_date |
|---|---|---|
| 001 | 2005-01-01 | 2007-01-01 |
| 001 | 2008-01-01 | 2008-12-01 |
| 001 | 2010-01-01 | 2012-01-01 |
| 002 | 2010-11-01 | 2010-12-02 |
| 002 | 2010-12-05 | 2010-12-08 |
SELECT id,
MIN(start_date) AS start_date,
MAX(end_Date) AS end_date
FROM (
SELECT t.*,
SUM(CASE WHEN start_date <= prev_max THEN 0 ELSE 1 END)
OVER (PARTITION BY id ORDER BY start_date) AS grp
FROM (
SELECT t.*,
MAX(end_date) OVER (
PARTITION BY id ORDER BY start_date
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS prev_max
FROM table_name t
) t
) t
GROUP BY id, grp
| id | start_date | end_date |
|---|---|---|
| 002 | 2010-12-05 | 2010-12-08 |
| 002 | 2010-11-01 | 2010-12-02 |
| 001 | 2005-01-01 | 2007-01-01 |
| 001 | 2008-01-01 | 2008-12-01 |
| 001 | 2010-01-01 | 2012-01-01 |