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?.

--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

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