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