Re: How to create a multi-column index with 2 dates using 'gist'?

On Tue, Aug 25, 2009 at 07:39:26PM +0800, Fred Janon wrote:
> Basically I have an events table representing events with a duration
> (startdate, enddate). I was wondering if it would improve the performance if
> I was creating a separate table (indexed as you suggested) with the date
> ranges (startdate, enddate) and point to that from my events table. That
> would eliminate the duplicate ranges, costing a join to find the events
> within a date range, but maybe improving the search performance for events
> that overlap a certain date range. Any feedback on that?

It depends on the sorts of queries you're going to be doing most often.

Not sure how is best to explain when GiST is going to win, but if you
think of a rectangle with the start dates going along the top edge and
the end dates going down the side. If you sort the values by the start
date will you end up with most of them on a diagonal or will they be
scattered randomly around. I.e the less correlation between the start
and end date the better GiST will do, relative to a btree index. I
think that's right anyway!

--
Sam http://samason.me.uk/