Обсуждение: How do I index to speed up OVERLAPS?

I have a table shift with a start_time column and a stop_time column and
I do a lot of queries like:

    select * from shift
    where (start_time, stop_time) overlaps ($A, $B);

$A and $B are user-submitted values.

Anyhow, how do I put indexes on my table to make these queries run
faster?

Also, is there a better data type to use for ranges of time?

TIA

Matt

Matthew Wilson wrote:
> I have a table shift with a start_time column and a stop_time column and
> I do a lot of queries like:
>
>     select * from shift
>     where (start_time, stop_time) overlaps ($A, $B);
>
> $A and $B are user-submitted values.
>
> Anyhow, how do I put indexes on my table to make these queries run
> faster?
>
> Also, is there a better data type to use for ranges of time?

There's not really a good solution for this. A btree or hash index is
pretty much useless for ranges like this (although for certain simple
queries two btree searches can give you a useful subset).

If performance here is really important, you'll need to use some of PG's
 geometric functions. You can treat the range as a box with coordinates
(start,0,end, 1) and then use GiST indexing on the overlaps operation.

--
  Richard Huxton
  Archonet Ltd