Обсуждение: Fwd: How to create a multi-column index with 2 dates using 'gist'?

Поиск
Список
Период
Сортировка

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

От
Fred Janon
Дата:
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@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;


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

От
Matthew Wakeling
Дата:
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.