Postgres 16

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 time_interval (
    id SERIAL PRIMARY KEY,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP
);

INSERT INTO time_interval (start_time, end_time) VALUES
    ('2024-02-10 01:30:00',                  null), -- pending    
    ('2024-02-10 03:00:00',                  null), -- pending
    ('2024-02-10 07:00:00', '2024-02-10 10:30:00'),
    ('2024-02-10 09:00:00', '2024-02-10 12:00:00'),
    ('2024-02-10 11:30:00', '2024-02-10 15:00:00'),
    ('2024-02-10 13:30:00', '2024-02-10 17:30:00'),
    ('2024-02-10 16:00:00', '2024-02-10 20:00:00'),
    ('2024-02-10 18:30:00', '2024-02-10 22:00:00'),
    ('2024-02-10 21:00:00', '2024-02-10 23:30:00');

alter table time_interval 
  add column time_range tsrange 
  generated always as (tsrange(coalesce(start_time,'infinity'), 
                               coalesce(end_time,  'infinity'))) stored;

select * from time_interval;

CREATE TABLE



INSERT 0 9



ALTER TABLE




id
start_time
end_time
time_range


1
2024-02-10 01:30:00
null
["2024-02-10 01:30:00",infinity)

2
2024-02-10 03:00:00
null
["2024-02-10 03:00:00",infinity)

3
2024-02-10 07:00:00
2024-02-10 10:30:00
["2024-02-10 07:00:00","2024-02-10 10:30:00")

4
2024-02-10 09:00:00
2024-02-10 12:00:00
["2024-02-10 09:00:00","2024-02-10 12:00:00")

5
2024-02-10 11:30:00
2024-02-10 15:00:00
["2024-02-10 11:30:00","2024-02-10 15:00:00")

6
2024-02-10 13:30:00
2024-02-10 17:30:00
["2024-02-10 13:30:00","2024-02-10 17:30:00")

7
2024-02-10 16:00:00
2024-02-10 20:00:00
["2024-02-10 16:00:00","2024-02-10 20:00:00")

8
2024-02-10 18:30:00
2024-02-10 22:00:00
["2024-02-10 18:30:00","2024-02-10 22:00:00")

9
2024-02-10 21:00:00
2024-02-10 23:30:00
["2024-02-10 21:00:00","2024-02-10 23:30:00")



SELECT 9


select * from time_interval
where time_range && '[2024-02-10 10:00:00,2024-02-10 17:00:00]';


id
start_time
end_time
time_range


1
2024-02-10 01:30:00
null
["2024-02-10 01:30:00",infinity)

2
2024-02-10 03:00:00
null
["2024-02-10 03:00:00",infinity)

3
2024-02-10 07:00:00
2024-02-10 10:30:00
["2024-02-10 07:00:00","2024-02-10 10:30:00")

4
2024-02-10 09:00:00
2024-02-10 12:00:00
["2024-02-10 09:00:00","2024-02-10 12:00:00")

5
2024-02-10 11:30:00
2024-02-10 15:00:00
["2024-02-10 11:30:00","2024-02-10 15:00:00")

6
2024-02-10 13:30:00
2024-02-10 17:30:00
["2024-02-10 13:30:00","2024-02-10 17:30:00")

7
2024-02-10 16:00:00
2024-02-10 20:00:00
["2024-02-10 16:00:00","2024-02-10 20:00:00")



SELECT 7


select * from time_interval
where tsrange(coalesce(start_time,'infinity'), 
              coalesce(end_time,  'infinity')) 
    && '[2024-02-10 10:00:00,2024-02-10 17:00:00]';


id
start_time
end_time
time_range


1
2024-02-10 01:30:00
null
["2024-02-10 01:30:00",infinity)

2
2024-02-10 03:00:00
null
["2024-02-10 03:00:00",infinity)

3
2024-02-10 07:00:00
2024-02-10 10:30:00
["2024-02-10 07:00:00","2024-02-10 10:30:00")

4
2024-02-10 09:00:00
2024-02-10 12:00:00
["2024-02-10 09:00:00","2024-02-10 12:00:00")

5
2024-02-10 11:30:00
2024-02-10 15:00:00
["2024-02-10 11:30:00","2024-02-10 15:00:00")

6
2024-02-10 13:30:00
2024-02-10 17:30:00
["2024-02-10 13:30:00","2024-02-10 17:30:00")

7
2024-02-10 16:00:00
2024-02-10 20:00:00
["2024-02-10 16:00:00","2024-02-10 20:00:00")



SELECT 7


alter table time_interval
  add column if not exists start_end_time tsrange,
  drop column if exists time_range;
update time_interval 
  set start_end_time=tsrange(coalesce(start_time,'infinity'), 
                             coalesce(end_time,  'infinity'),
                             '[]');
alter table time_interval
  drop column if exists start_time,
  drop column if exists end_time;
select * from time_interval;

ALTER TABLE



UPDATE 9



ALTER TABLE




id
start_end_time


1
["2024-02-10 01:30:00",infinity]

2
["2024-02-10 03:00:00",infinity]

3
["2024-02-10 07:00:00","2024-02-10 10:30:00"]

4
["2024-02-10 09:00:00","2024-02-10 12:00:00"]

5
["2024-02-10 11:30:00","2024-02-10 15:00:00"]

6
["2024-02-10 13:30:00","2024-02-10 17:30:00"]

7
["2024-02-10 16:00:00","2024-02-10 20:00:00"]

8
["2024-02-10 18:30:00","2024-02-10 22:00:00"]

9
["2024-02-10 21:00:00","2024-02-10 23:30:00"]



SELECT 9


select * from time_interval
where start_end_time && '[2024-02-10 10:00:00,2024-02-10 17:00:00]';


id
start_end_time


1
["2024-02-10 01:30:00",infinity]

2
["2024-02-10 03:00:00",infinity]

3
["2024-02-10 07:00:00","2024-02-10 10:30:00"]

4
["2024-02-10 09:00:00","2024-02-10 12:00:00"]

5
["2024-02-10 11:30:00","2024-02-10 15:00:00"]

6
["2024-02-10 13:30:00","2024-02-10 17:30:00"]

7
["2024-02-10 16:00:00","2024-02-10 20:00:00"]



SELECT 7

id	start_time	end_time	time_range
1	2024-02-10 01:30:00	null	["2024-02-10 01:30:00",infinity)
2	2024-02-10 03:00:00	null	["2024-02-10 03:00:00",infinity)
3	2024-02-10 07:00:00	2024-02-10 10:30:00	["2024-02-10 07:00:00","2024-02-10 10:30:00")
4	2024-02-10 09:00:00	2024-02-10 12:00:00	["2024-02-10 09:00:00","2024-02-10 12:00:00")
5	2024-02-10 11:30:00	2024-02-10 15:00:00	["2024-02-10 11:30:00","2024-02-10 15:00:00")
6	2024-02-10 13:30:00	2024-02-10 17:30:00	["2024-02-10 13:30:00","2024-02-10 17:30:00")
7	2024-02-10 16:00:00	2024-02-10 20:00:00	["2024-02-10 16:00:00","2024-02-10 20:00:00")
8	2024-02-10 18:30:00	2024-02-10 22:00:00	["2024-02-10 18:30:00","2024-02-10 22:00:00")
9	2024-02-10 21:00:00	2024-02-10 23:30:00	["2024-02-10 21:00:00","2024-02-10 23:30:00")

id	start_end_time
1	["2024-02-10 01:30:00",infinity]
2	["2024-02-10 03:00:00",infinity]
3	["2024-02-10 07:00:00","2024-02-10 10:30:00"]
4	["2024-02-10 09:00:00","2024-02-10 12:00:00"]
5	["2024-02-10 11:30:00","2024-02-10 15:00:00"]
6	["2024-02-10 13:30:00","2024-02-10 17:30:00"]
7	["2024-02-10 16:00:00","2024-02-10 20:00:00"]
8	["2024-02-10 18:30:00","2024-02-10 22:00:00"]
9	["2024-02-10 21:00:00","2024-02-10 23:30:00"]