Sample Header Ad - 728x90

2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?

12 votes
1 answer
7336 views
I have a table which is storing reservation data using the columns starts_at & 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-01 00:00:00'; Or 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 starts_at and 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.
Asked by Saurabh Nanda (333 rep)
Feb 18, 2014, 01:32 AM
Last activity: Jun 24, 2022, 01:55 AM