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.
--
Clive Page
Dept of Physics & Astronomy,
University of Leicester,
Leicester, LE1 7RH, U.K.
