Search Postgresql Archives

How to get RTREE performance from GIST indexing?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux