Postgres 12

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

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