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 events ( id int
,season_start date
,season_end date
,event_date date);
INSERT INTO events (id, season_start, season_end, event_date) VALUES
(1,'2022-01-01', '2022-01-31', '2022-01-01')
,(2,'2022-01-01', '2022-01-31', '2022-01-02')
,(3,'2022-01-01', '2022-01-31', '2022-01-10')
,(4,'2022-01-01', '2022-01-31', '2022-01-16')
,(5,'2022-01-01', '2022-01-31', '2022-01-18')
,(6,'2022-01-01', '2022-01-31', '2022-01-20');
SELECT * FROM events;
CREATE TABLE
INSERT 0 6
| id | season_start | season_end | event_date |
|---|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 | 2022-01-01 |
| 2 | 2022-01-01 | 2022-01-31 | 2022-01-02 |
| 3 | 2022-01-01 | 2022-01-31 | 2022-01-10 |
| 4 | 2022-01-01 | 2022-01-31 | 2022-01-16 |
| 5 | 2022-01-01 | 2022-01-31 | 2022-01-18 |
| 6 | 2022-01-01 | 2022-01-31 | 2022-01-20 |
SELECT 6
WITH RECURSIVE
ranges AS (
(
SELECT id,
season_start,
season_end,
event_date,
season_start range_start,
(event_date + INTERVAL '6 day') :: DATE range_end
FROM events
ORDER BY event_date LIMIT 1
)
UNION ALL
(
SELECT events.id,
events.season_start,
events.season_end,
events.event_date,
(ranges.range_end + INTERVAL '1 day') :: DATE,
(events.event_date + INTERVAL '6 day') :: DATE
FROM events
JOIN ranges ON ranges.range_end < events.event_date
ORDER BY event_date LIMIT 1
)
)
SELECT events.id,
events.season_start,
events.season_end,
events.event_date,
ranges.range_start,
ranges.range_end
FROM events
JOIN ranges ON events.event_date BETWEEN range_start AND range_end;
| id | season_start | season_end | event_date | range_start | range_end |
|---|---|---|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 | 2022-01-01 | 2022-01-01 | 2022-01-07 |
| 2 | 2022-01-01 | 2022-01-31 | 2022-01-02 | 2022-01-01 | 2022-01-07 |
| 3 | 2022-01-01 | 2022-01-31 | 2022-01-10 | 2022-01-08 | 2022-01-16 |
| 4 | 2022-01-01 | 2022-01-31 | 2022-01-16 | 2022-01-08 | 2022-01-16 |
| 5 | 2022-01-01 | 2022-01-31 | 2022-01-18 | 2022-01-17 | 2022-01-24 |
| 6 | 2022-01-01 | 2022-01-31 | 2022-01-20 | 2022-01-17 | 2022-01-24 |
SELECT 6
DELETE FROM events WHERE id = 3;
SELECT * FROM events;
DELETE 1
| id | season_start | season_end | event_date |
|---|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 | 2022-01-01 |
| 2 | 2022-01-01 | 2022-01-31 | 2022-01-02 |
| 4 | 2022-01-01 | 2022-01-31 | 2022-01-16 |
| 5 | 2022-01-01 | 2022-01-31 | 2022-01-18 |
| 6 | 2022-01-01 | 2022-01-31 | 2022-01-20 |
SELECT 5
WITH RECURSIVE
ranges AS (
(
SELECT id,
season_start,
season_end,
event_date,
season_start range_start,
(event_date + INTERVAL '6 day') :: DATE range_end
FROM events
ORDER BY event_date LIMIT 1
)
UNION ALL
(
SELECT events.id,
events.season_start,
events.season_end,
events.event_date,
(ranges.range_end + INTERVAL '1 day') :: DATE,
(events.event_date + INTERVAL '6 day') :: DATE
FROM events
JOIN ranges ON ranges.range_end < events.event_date
ORDER BY event_date LIMIT 1
)
)
SELECT events.id,
events.season_start,
events.season_end,
events.event_date,
ranges.range_start,
ranges.range_end
FROM events
JOIN ranges ON events.event_date BETWEEN range_start AND range_end;
| id | season_start | season_end | event_date | range_start | range_end |
|---|---|---|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 | 2022-01-01 | 2022-01-01 | 2022-01-07 |
| 2 | 2022-01-01 | 2022-01-31 | 2022-01-02 | 2022-01-01 | 2022-01-07 |
| 4 | 2022-01-01 | 2022-01-31 | 2022-01-16 | 2022-01-08 | 2022-01-22 |
| 5 | 2022-01-01 | 2022-01-31 | 2022-01-18 | 2022-01-08 | 2022-01-22 |
| 6 | 2022-01-01 | 2022-01-31 | 2022-01-20 | 2022-01-08 | 2022-01-22 |
SELECT 5