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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
