0

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.

3
  • Your queries won't run against the indicated table. Please show us a CREATE TABLE statement, then clean up your question so the commands you show run against that created table. Commented Sep 12, 2021 at 15:13
  • @jjanes I just edit clearly question. Please help me. I had investigate some logic and some way but i can't understand why index can't used Commented Sep 12, 2021 at 15:29
  • Your queries reference the non-existent table 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) Commented Sep 12, 2021 at 16:14

1 Answer 1

3

As far as I can tell, OVERLAPS only exists because it is in the SQL standard, and it doesn't support any indexing methods. The PostgreSQL-ish way to do this is with ranges, specifically tsrange or tstzrange.

tstzrange is the right one if you were starting from scratch, but given that your starting point is already broken with regards to time zones, that might be hard to accommodate. From your existing starting point, you could do:

create index on booking using gist (tsrange((start_date + start_time),(end_date + end_time)));

Which would support indexing on this WHERE clause:

tsrange('2021-01-09 11:30:00','2021-09-10 11:30:00') && tsrange((b.start_date + b.start_time),(b.end_date + b.end_time));

Although it would be better to rework your data so it only stored a tstzrange in the table, rather than 4 different columns. Or at least stored two timestamptz columns, for start and end, rather than breaking each into separate date and time.

Sign up to request clarification or add additional context in comments.

2 Comments

Thanks you so much. When i create index it now using index. But mysql using two time : '2021-01-09 11:30:00' and '2021-09-10 11:30:00'. Base on your answer, it only use time : '2021-01-09 11:30:00'::timestamp . How to work two time 2021-01-09 11:30:00 and 2021-09-10 11:30:00 on tsrange. Because i need two time and compare with start_date, start_time , end_date, end_time .
Sorry, I didn't notice that those two dates were different--they look so similar to one another I though it was just one moment in time. See update.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.