Skip site navigation (1) Skip section navigation (2)

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

From: Fred Janon <fjanon(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Fwd: How to create a multi-column index with 2 dates using 'gist'?
Date: 2009-08-25 09:29:47
Message-ID: 2fd0c7810908250229h51538b5dv43cf4c1dc04c4115@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
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.

Thanks

Fred

---------- Forwarded message ----------
From: Fred Janon <fjanon(at)gmail(dot)com>
Date: Mon, Aug 24, 2009 at 17:24
Subject: How to create a multi-column index with 2 dates using 'gist'?
To: pgsql-general(at)postgresql(dot)org


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;

In response to

Responses

pgsql-performance by date

Next:From: Sam MasonDate: 2009-08-25 10:52:11
Subject: Re: How to create a multi-column index with 2 dates using 'gist'?
Previous:From: Gavin LoveDate: 2009-08-24 17:46:59
Subject: Re: Indexing on a circle datatype

pgsql-general by date

Next:From: RstatDate: 2009-08-25 09:50:52
Subject: ETL software and training
Previous:From: Craig RingerDate: 2009-08-25 02:42:50
Subject: Re: How to simulate crashes of PostgreSQL?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group