Re: Any "guide to indexes" exists?

On May 7, 2007, at 11:01 AM, Karsten Hilbert wrote:
> On Mon, May 07, 2007 at 10:47:24AM -0500, Jim Nasby wrote:
>> GiST can also be useful if you have to query in multiple dimensions,
>> which can occur outside the normal case of geometry. Best example I
>> know of is a table containing duration information in the form of
>> start_time and end_time. Trying to query for what events happened on
>> 5/28/2005 will generally be much cheaper with a GiST index than a b-
>> tree.
> Are you referring to queries with
>
> ... where some_timestamp between some_start and some_end ...
>
> or
>
> ... where some_timestamp > some_start and some_timestamp <
> some_end ...

Yes. :)

Say the table spans 10 years of records. You're looking for a
specific set of records that affect a day 5 years ago. Your where
clause will look something like this:

WHERE start_time < now() - 5 years AND end_time > now() - 5 years 1 day

B-tree indexes will be nearly useless in this case, because each one
is going to match on over half the table. But there are operators
that would let you treat this as a 2 dimensional problem and then use
GiST (or r-tree, but that's no longer recommended). For example, if
you create an index on:

box(point(start_time, start_time), point(end_time, end_time))

Then you just need to query for all rows that overlap the box defined
by:

box(point(now() - 5 years 1 day, now() - 5 years 1 day), point(now()
- 5 years, now() - 5 years))

You'd have to double-check the exact logic and see which overlaps
operator you want, but the basic idea is the same: you're converting
2 one-dimensional timelines (start_time and end_time) into a two-
dimensional timeline, and then using geometric functions to index that.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)