Re: How to get RTREE performance from GIST index?

On 22/11/2009 10:44, Martijn van Oosterhout wrote:
> PostgreSQL is used extensively for geometric queries, see postgis. They
> abandoned rtree a while back because the GiST rtree support was better,

Maybe the support is better, but the performance is obviously not. And
when there is a difference between under a minute and 3 hours, then
performance matters. At least it does to me and my colleagues.

> You are AFAICR the first person to have a problem is this area, but if

I find it extremely hard to believe that. All I am doing is finding
whether pairs of rectangular boxes overlap or not. That is the most
trivial use of R-trees possible. Surely someone thought to time that
using GIST?

> you can't take the few minutes needed to run EXPLAIN on before and
> after then there is zero chance of it being fixed either.

Unfortunately it isn't a "few minutes". To re-run in v8.1 I have to
reload many tables into a different installation using v8.1: some of the
tables have a few million rows and hundreds of columns. Then I have
change some scripts to add an EXPLAIN command and log the resulting
output (rather than getting the results that I actually want). This
will take hours. I will try to do it soon, but cannot do it instantly.
I have some data that I want to process first.

I agree that this is a bug in Postgres - the bug was removing code that
worked perfectly well and upon which some users depended. I simply
don't understand why the Rtree code could not have been left in there,
for those who found that the new-fangled GIST indexing did not work.

Regards

--
Clive Page