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
Last activity: Jun 24, 2022, 01:55 AM