2009/11/21 Clive Page <clive.page@xxxxxxxxxx>
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?
Rtree was reimplemented into GiST as of PostgreSQL version 8.2. There should no advantages of using Rtree, so I'm not sure why you're experiencing problems. Hopefully someone can provide insight into what's causing the slow down.
Thom
