Postgres 14

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

with my_table (id, start_date, end_date) as
(
values
    (1, '2022-05-23'::date, '2022-05-26'::date),
    (2, '2022-05-25', '2022-05-30'),
    (3, '2022-05-01', '2022-05-20')
)
select *
from my_table
where daterange(start_date, end_date) && daterange('2022-05-19', '2022-05-24')


id
start_date
end_date


1
2022-05-23
2022-05-26

3
2022-05-01
2022-05-20



SELECT 2


with my_table (id, start_date, end_date) as
(
values
    (1, '2022-05-23'::date, '2022-05-26'::date),
    (2, '2022-05-25', '2022-05-30'),
    (3, '2022-05-01', '2022-05-20')
)
select *
from my_table
where (start_date, end_date) overlaps ('2022-05-19', '2022-05-24')


id
start_date
end_date


1
2022-05-23
2022-05-26

3
2022-05-01
2022-05-20



SELECT 2


with my_table (id, start_date, end_date) as
(
values
    (1, '2022-05-23'::date, '2022-05-26'::date),
    (2, '2022-05-25', '2022-05-30'),
    (3, '2022-05-01', '2022-05-20')
)
select bool_or((start_date, end_date) overlaps ('2022-05-19', '2022-05-24')) as found
from my_table


found


t



SELECT 1