How to get RTREE performance from GIST index?

From: Clive Page <clive(dot)page(at)cantab(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: How to get RTREE performance from GIST index?
Date: 2009-11-21 22:57:38
Message-ID: 4B087062.9080106@cantab.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have been using Postgres for some years, in particular the RTREE
indexes to perform spatial queries on astronomical datasets. I
misguidedly got our system manager to install Postgres 8.4 and I find
that I can no longer use rtrees - the system gives me a message

substituting access method "gist" for obsolete method "rtree"

The performance has dropped by at least a factor of 100 (I am not sure
how much more, because the relevant bit of my SQL is still running after
more than an hour, previously it took a minute or so to do this bit of
the script).

The relevant bits of SQL I have been using are:

CREATE TEMPORARY TABLE cat4p AS
SELECT longid, srcid, ra, dec, poserr,
BOX(POINT(ra+10.0/(3600*COS(RADIANS(dec))), dec+10.0/3600.0),
POINT(ra-10.0/(3600*COS(RADIANS(dec))), dec-10.0/3600.0)) AS errbox
FROM cat4;
CREATE INDEX cat4pind ON cat4p USING RTREE(errbox);

CREATE TEMPORARY TABLE apair AS
SELECT c.longid, c.srcid, c.ra, c.dec, c.poserr
FROM avcatpos AS a, cat4p AS c
WHERE a.errbox && c.errbox AND
gcdist(a.sc_ra, a.sc_dec, c.ra, c.dec) <
LEAST(7.0, 3.0 * (a.sc_poserr + c.poserr))
AND a.srcid <> c.srcid;

It is this latter query, involving the && operator to find where two
rectangular boxes overlap, which seems to be taking the huge amount of time.

Is there a way of forcing the use of Rtree indexing in v8.4, or any
other work-around?

Regards

--
Clive Page
Dept of Physics & Astronomy,
University of Leicester,
Leicester, LE1 7RH, U.K.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-11-21 23:09:19 Re: ERROR: (custom type via CREATE TYPE) is not a valid base type for a domain
Previous Message Tim Landscheidt 2009-11-21 22:22:36 Re: Books, the lulu.com scam