On 22 Nov 2009, at 11:55, Clive Page wrote: > 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. Before getting all worked up about the performance of GiST indexes, did you verify that your tables were analysed? Did the person doing the upgrade tune the database? Was it tuned the same or differently? It may well be that the resources GiST indexes require aren't exactly the same as what RTrees require, so maybe tuning needs to be different or you need to add some hardware (which means you probably were close to the limits before and would likely have to do this in the near future anyway - still an unpleasant surprise of course). A query going from "mere" minutes to several hours usually points to the resource starvation or a particularly poor query plan. Don't assume GiST indexes are that much slower than RTrees, they wouldn't have replaced them if that were the case. That is why people are asking for the query plan (EXPLAIN, or preferably EXPLAIN ANALYSE, but that actually performs the 3h query), so that we have an idea where your query is going wrong. In almost all cases we see on this ML the problem is not a bug in Postgres; it usually boils down to lack of maintenance, improper tuning or just plain inefficient queries. >> 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. You posted a number of queries in your original mail. Don't those expose the problem? Running them in psql connected to either database shouldn't take much time at all. We just would like to see where your performance issues are coming from, we don't necessarily need the results of your actual calculations for those. > 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. I'm not one of the developers, but I expect it was duplicating code or unmaintained and there was no evidence that there were any regressions when replacing RTree with GiST (ISTR that GiST is in fact some form of RTree) and therefore deemed deprecated. It is not unusual to remove deprecated features between major versions of a software product. I'm speculating here, but I'm quite convinced you would have seen the same regression in the performance of your database if RTree would have still been in the database. It's simply not likely that the difference in index is causing your trouble. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b09295211736876095208! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
