Обсуждение: Fwd: How to create a multi-column index with 2 dates using 'gist'?
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
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@gmail.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@postgresql.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;
From: Fred Janon <fjanon@gmail.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@postgresql.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;
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.