Search Postgresql Archives

Re: How to get RTREE performance from GIST index?

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

 



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

[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