Re: How to get RTREE performance from GIST index?

2009/11/21 Clive Page <clive(dot)page(at)cantab(dot)net>

> 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