From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Matthew Wilson <matt(at)tplus1(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How do I index to speed up OVERLAPS? |
Date: | 2008-12-02 11:37:21 |
Message-ID: | 49351DF1.6060505@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2008-12-02 11:40:08 | Re: psql verbose mode |
Previous Message | Richard Huxton | 2008-12-02 11:32:43 | Re: pg_xlog content |