Re: How to get RTREE performance from GIST index?

Thanks to all those who responded to my posting yesterday.

I have now tried a simple simulation of joining tables with partly
overlapping rectangular boxes using Rtrees (with GIST automatically
replacing them in 8.4.1), and this works in 8.1.0 and 8.4.1, with the
latter a bit faster. But my original data processing script still hangs
(or takes 3 hours, much the same thing as far as getting work done is
concerned) at various points when using v8.4.1. The identical script
works fine using a 8.1.0 server, which fortunately we still have
available. I have now inserted ANALYSE table commands before each
SELECT that depends upon an R-tree (GIST) index. This doesn't seem to help.

It will obviously take a lot of time and effort to track this down. For
the moment I shall stick to using v8.1.0, as there's really no alternative.

I still think it a great pity that rather than merely deprecating R-tree
indexing or making GIST the default but still allowing R-trees to be
used if one really wanted them, you actually removed Rtrees from the
code. No doubt some tests show GIST to work and work faster than Rtrees
in test cases; clearly from my experience when using complicated
real-world data that's not necessarily true.

Maybe there's some magic spell that can be used to restore the earlier
performance, but I really don't have time at present to do the necessary
experimenting.

--
Clive Page
Dept of Physics & Astronomy,
University of Leicester,
Leicester, LE1 7RH, U.K.