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

On Tue, 25 Aug 2009, Fred Janon wrote:
> Asking the Performance people as well, since I didn't get any answer from General...
>
> I have been unable to create a multi column index with 2 integers as well, same error as
> the one I get with 2 dates.

> ERROR: data type date has no default operator class for access method "gist"
> HINT: You must specify an operator class for the index or define a default operator class
> for the data type.

You need to install the contrib package btree_gist, which contains default
operators for various data types, including (at least) integer, and
probably date as well. However, there seems to be very little point in
doing so, as the standard Postgres btree will handle these many times
better than GiST.

> I search the pdf docs and online without finding what an "operator class" for DATE would
> be. Would a multi-column index help in that case (OVERLAPS and dates comparison) anyway?
> Or should I just define an index for each of the dates?

Here we have a clue as to why you are wanting GiST. You want to say "Find
me the rows that overlap in date with this range". That requires more than
just a standard index, and creating a two-column GiST date index will not
solve your problem.

Your query will look something like:

SELECT blah FROM blah
WHERE start_date <= range_end AND end_date >= range_start

And for that, you need an R-Tree index. Now, I am not aware of one in
Postgres which indexes dates, however the "seg" package in contrib will
index floating point values, and "bioseg" (available from
http://www.bioinformatics.org/bioseg/wiki/ which I am maintaining at the
moment) will index integers.

Matthew

--
The early bird gets the worm. If you want something else for breakfast, get
up later.