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?.
--https://stackoverflow.com/q/78046805/5298879
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');
--stabilise random sample set between re-runs
select setseed(.42);
--adding 420'000 random samples
INSERT INTO time_interval (start_time, end_time)
select start_ts, start_ts+random()*interval '24h'
from generate_series(1,42e4) as multiplicator(g),
lateral (select now()-random()*interval '1 year' as start_ts, g);
--inspecting samples
select * from time_interval tablesample bernoulli(.42)repeatable(.42)limit 14;
CREATE TABLE
INSERT 0 9
| setseed |
|---|
SELECT 1
INSERT 0 420000
| id | start_time | end_time |
|---|---|---|
| 109 | 2024-02-13 19:11:16.932586 | 2024-02-13 23:21:19.459637 |
| 532 | 2023-08-14 03:29:31.850986 | 2023-08-14 23:02:38.318462 |
| 764 | 2024-02-16 18:59:49.274986 | 2024-02-17 09:49:41.507871 |
| 1120 | 2023-12-08 15:34:15.934186 | 2023-12-09 10:11:29.016729 |
| 1823 | 2024-01-03 02:28:05.681386 | 2024-01-03 22:33:53.993674 |
| 1833 | 2023-07-25 03:03:21.185386 | 2023-07-25 16:19:16.143746 |
| 2367 | 2023-04-26 18:20:07.054186 | 2023-04-27 03:11:52.386872 |
| 2832 | 2023-03-06 08:20:54.631786 | 2023-03-07 04:33:21.163002 |
| 3069 | 2024-01-13 05:51:23.892586 | 2024-01-13 11:23:46.139657 |
| 3085 | 2023-09-21 04:22:12.103786 | 2023-09-21 07:38:05.960571 |
| 4479 | 2023-03-29 21:26:04.145386 | 2023-03-30 03:28:54.530147 |
| 4537 | 2023-11-19 11:33:02.551786 | 2023-11-20 07:43:05.853259 |
| 4596 | 2024-01-29 11:18:14.791786 | 2024-01-29 16:19:13.644006 |
| 4639 | 2023-11-09 07:07:09.396586 | 2023-11-09 22:58:39.120375 |
SELECT 14
create index gist_idx on time_interval
using gist(tsrange(coalesce(start_time,'infinity'),
coalesce(end_time, 'infinity')));
CREATE INDEX
vacuum analyze time_interval;
VACUUM
--https://stackoverflow.com/a/78047761/5298879
--Zegarek
--around 1.5ms exec time thanks to the index
prepare tsrange_overlap as
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]';
explain analyze verbose execute tsrange_overlap;
PREPARE
| QUERY PLAN |
|---|
| Bitmap Heap Scan on public.time_interval (cost=43.54..1940.71 rows=936 width=20) (actual time=0.527..1.289 rows=901 loops=1) |
| Output: id, start_time, end_time |
| Recheck Cond: (tsrange(COALESCE(time_interval.start_time, 'infinity'::timestamp without time zone), COALESCE(time_interval.end_time, 'infinity'::timestamp without time zone)) && '["2024-02-10 10:00:00","2024-02-10 17:00:00"]'::tsrange) |
| Heap Blocks: exact=765 |
| -> Bitmap Index Scan on gist_idx (cost=0.00..43.30 rows=936 width=0) (actual time=0.434..0.434 rows=901 loops=1) |
| Index Cond: (tsrange(COALESCE(time_interval.start_time, 'infinity'::timestamp without time zone), COALESCE(time_interval.end_time, 'infinity'::timestamp without time zone)) && '["2024-02-10 10:00:00","2024-02-10 17:00:00"]'::tsrange) |
| Planning Time: 0.245 ms |
| Execution Time: 1.356 ms |
EXPLAIN
--https://stackoverflow.com/a/78046875/5298879
--SelVazi
--around 35ms
prepare individual_field_comparison as
SELECT *
FROM time_interval
WHERE (start_time <= '2024-02-10 17:00:00' OR start_time IS NULL)
AND ('2024-02-10 10:00:00' <= end_time OR end_time IS NULL);
explain analyze verbose execute individual_field_comparison;
PREPARE
| QUERY PLAN |
|---|
| Seq Scan on public.time_interval (cost=0.00..8976.14 rows=15455 width=20) (actual time=0.007..35.399 rows=901 loops=1) |
| Output: id, start_time, end_time |
| Filter: (((time_interval.start_time <= '2024-02-10 17:00:00'::timestamp without time zone) OR (time_interval.start_time IS NULL)) AND (('2024-02-10 10:00:00'::timestamp without time zone <= time_interval.end_time) OR (time_interval.end_time IS NULL))) |
| Rows Removed by Filter: 419108 |
| Planning Time: 0.082 ms |
| Execution Time: 35.446 ms |
EXPLAIN