I using postgresSql and using overlaps function find range time on table. My table look like
id room_id start_date start_time end_date end_time delete_at
1 123 2021-01-09 10:00:00 2021-01-10 10:30:00 null
2 456 2021-08-10 15:00:00 2021-01-10 15:30:00 null
///some record
3600 789 2021-09-10 00:00:00 2021-09-10 15:00:00 null
My table very large and have 38000 record. I write sql find logic overlap time look like :
select
*
from
booking as b
where
b.room_id in ('123', '456', '789')
and b.delete_flag = 0
and (cast('2021-01-09 11:30:00' as TIMESTAMP),
cast('2021-09-10 11:30:00' as TIMESTAMP)) overlaps ((b.start_date + bk.start_time),
(b.end_date + b.end_time));
I have create index start_date, start_time, end_date, end_time. But postgres cannot using my index . It only scan seq. I create new mutiple index look like :
CREATE INDEX start_date_time_idx ON booking ((start_date + start_time))
CREATE INDEX end_date_time_idx ON booking ((end_date + end_time))
But postgres cannot using start_date_time_idx and end_date_time_idx index. I can't understand why postgres cannot using index. I try change my code remove overlaps but no change
select
*
from
booking as b
where
b.room_id in ('123', '456', '789')
and b.delete_flag = 0
and (cast('2021-01-09 11:30:00' as TIMESTAMP) > (b.start_date + bk.start_time),
cast('2021-09-10 11:30:00' as TIMESTAMP))<= (b.end_date + b.end_time));
How to create index for function overlap or how to using multiple index in mycase. Why index of start_date, start_time, end_date , end_time , start_date_time_idx, end_date_time_idx cannot using mycase. Please help. Thank you.
bk, and the 2nd one has multiple other errors as well. I can try to guess what you meant, and when I do it does use an index (but not very efficiently)