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

From: Fred Janon <fjanon(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to create a multi-column index with 2 dates using 'gist'?
Date: 2009-08-24 09:24:59
Message-ID: 2fd0c7810908240224h58478784ld475371ab5db414b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

Hi,

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 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?

Below are the table and index defintions.

Thanks

Fred

---------------------------------------------
CREATE INDEX startenddate
ON times USING gist (startdate, enddate);

---------------------------------------------
-- Table: times

-- DROP TABLE times;

CREATE TABLE times
(
id serial NOT NULL,
startdate date NOT NULL,
enddate date NOT NULL,
starttime time without time zone,
endtime time without time zone,
CONSTRAINT pk_id PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE times OWNER TO postgres;
GRANT ALL ON TABLE times TO postgres;
GRANT ALL ON TABLE times TO public;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guillaume Lelarge 2009-08-24 09:37:34 Re: Strange "missing tables" problem
Previous Message Denis BUCHER 2009-08-24 08:21:33 Re: Strange "missing tables" problem

Browse pgsql-performance by date

  From Date Subject
Next Message Kevin Grittner 2009-08-24 14:29:58 Re: Number of tables
Previous Message Gábor Farkas 2009-08-24 06:54:46 postgresql uses Hash-join, i need Nested-loop