I have a table which is storing reservation data using the columns
ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the following queries:
SELECT * FROM reservations WHERE starts_at < '2014-01-03 00:00:00' AND ends_at >='2014-01-01 00:00:00';
SELECT * FROM reservations WHERE tsrange(starts_at, ends_at) && ('2014-01-01 00:00:00', '2014-01-03 00:00:00')
I have regular B-Tree indices on the
ends_at columns, therefore the first query is always using them. However, unless I define a functional GiST index on the tsrange, the second query does a complete scan.
create index tsrange_idx on reservations using gist(tsrange(starts_at, ends_at));
My question is, as the table grows, which index is going to be faster? Probably the answer is obvious looking at the query execution plan, but I'm not well-versed with reading
EXPLAIN ANALYZE output.