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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
