Re: How to get RTREE performance from GIST index?

On 22/11/2009 05:40, Tom Lane wrote:
> No, because the rtree code is gone entirely. We took it out on the
> basis of tests showing that the gist implementation performed as well
> or better. I'm not sure why it's not working for you, but if you
> can provide a more complete test case, we could look into it.
>
> One thing to check into right away is whether the system is even
> trying to use the index --- what does EXPLAIN show about it?
> Do you by any chance have EXPLAIN output for the same query on the
> old system? What was the old PG version, anyway?

Tom

Thanks for your reply. I should have said that I was using v8.1. After
I posted my question, I retried with
CREATE INDEX ... USING GIST(errbox box_ops)
and left it to run overnight. The query using the index, which finds
overlaps between rectangular boxes using the && operator, took 10228
seconds, whereas using RTREES in v8.1 it took around 50 seconds. I have
several such queries to do, and cannot afford to wait for hours. I
discovered the "box_ops" syntax only by reading lots of disparate bits
of documentation: it is very unsatisfactory that your indexing options
are so very poorly documented. I saw that as well as GIST indexing
there is something called GIN indexing but failed to find anything
useful about these at all. I tried to use them, but without success.
There is no point in having these facilities if they are not documented
adequately.

I am truly sorry that you made the decision to remove R-trees from
Postgres and had no regard for backward compatibility. The availability
and high performance of R-trees was one of the main reasons I switched
to Postgres and have been using it for the last few years. I realise
that if I take the time to experiment and use the EXPLAIN command and
play around for a week or two I *might* be able to restore something
like the earlier performance, but unfortunately I have a job I want to
get done in the next day or two.

Fortunately I have a simple work-around: Postgres v8.1 is still
installed here, and I'll use it right away. For the longer term, I may
have to switch to MySQL, which had R-trees but not implemented very
efficiently (the last time I checked). No doubt the new owners of MySQL
will have tried hard to get them working properly. I'm truly sorry that
you don't take the need for R-tree indexing seriously. I would have
thought that geometric queries such as the ones that I've been doing
would be more and more important in the real world.

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