On Mon, Aug 24, 2009 at 05:24:59PM +0800, Fred Janon wrote: > I am using 8.3 and pgAdmin III. I have a couple of tables using 2 DATE > columns like 'startdate' and 'enddate' (just date, not interested in time in > these columns). I have some queries (some using OVERLAPS) involving both > 'startdate' and 'enddate' columns. I tried to create a multi column index > using pgAdmin and it comes back with this error: > > 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. I've not had the opportunity to try doing this, but it would seem to require hacking some C code to get this working. Have a look here: http://www.postgresql.org/docs/current/static/gist.html > 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? An operator class bundles together various bits of code so that the index knows which functions to call when it needs to compare things. If you were creating an GiST index over a pair of dates to support an "overlaps" operator you'd have to define a set of functions that implement the various checks needed. Depending on your data you may be easier with just a multi-column index and using normal comparisons, I can't see how OVERLAPS could use indexes as it does some strange things with NULL values. The cases a B-Tree index would win over GiST (this is an educated guess) is when few of the ranges overlap within a table. If that's the case then I'd do: CREATE INDEX tbl_start_end_idx ON tbl (startdate,enddate); to create the btree index (they're the default, so nothing else is needed) and then write queries as: SELECT r.range, t.* FROM tbl t, ranges r WHERE t.startdate <= r.rangeend AND t.enddate >= r.rangestart; if there are lots of overlapping ranges in the table then this is going to do badly and you may need to start thinking about writing some C code to get a GiST index going. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
