Re: How to get RTREE performance from GIST index? - Mailing list pgsql-general

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
minuteand 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
needsto be different or you need to add some hardware (which means you probably were close to the limits before and
wouldlikely 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
queryplan. Don't assume GiST indexes are that much slower than RTrees, they wouldn't have replaced them if that were
thecase. 

That is why people are asking for the query plan (EXPLAIN, or preferably EXPLAIN ANALYSE, but that actually performs
the3h query), so that we have an idea where your query is going wrong. In almost all cases we see on this ML the
problemis not a bug in Postgres; it usually boils down to lack of maintenance, improper tuning or just plain
inefficientqueries. 

>> 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
usingv8.1: some of the tables have a few million rows and hundreds of columns.  Then I have change some scripts to add
anEXPLAIN 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
eitherdatabase 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
actualcalculations for those. 

> I agree that this is a bug in Postgres - the bug was removing code that worked perfectly well and upon which some
usersdepended.  I simply don't understand why the Rtree code could not have been left in there, for those who found
thatthe 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
therewere any regressions when replacing RTree with GiST (ISTR that GiST is in fact some form of RTree) and therefore
deemeddeprecated. 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
databaseif RTree would have still been in the database. It's simply not likely that the difference in index is causing
yourtrouble. 

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!