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