I personally think we should raise the costs for all postgis functions since all postgis functions are definitely more costly than standard other functions.
Actually I havent looked to see if there is a way to turn that off, but I think it would be preferable not to since there are so many places where this feature comes in handy. Thanks, Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stephan Grüter Sent: Saturday, April 26, 2008 1:09 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] ST_Contains() performance problem Hi Mark, Regina and Kevin I think, I found the source of the problem. It seems, that the planner of PG 8.2 always used internal functions like "=" before external functions whereas the planner of PG 8.3 decides according to the estimated cost value of the external functions and most of the postgis functions have a cost value of 1. After I changed the estimated cost of st_geometry_overlap() (used by && operator) and contains() from 1 to 2, the planner behaved exactly like the former version and the performance of the query was as good as before. So I have a workaround for this particular case, but the question remains, how to deal with this situation generally: - Is it possible to disable the evaluation of function costs in PG 8.3 with a configuration parameter (I didn't found one yet)? - Are the default cost values of the postgis functions reasonable for all or most situations or do we have to fine tune the values? Regards, Stephan Am 25.04.2008 um 16:33 schrieb Mark Cave-Ayland: > Stephan Grüter wrote: >> Hi Kevin, >> Am 25.04.2008 um 03:59 schrieb Kevin Neufeld: >>> I find it curious that your old system has >>> "retyp=8 AND (geom && ...) AND contains(...)" >>> >>> but you new system has >>> "(geom && ...) AND contains(...) AND regtyp=8" >>> I think PostgreSQL does short-circuit evaluation. Testing for >>> integer equality and dropping out of a filter clause is >>> significantly faster than testing a bounding box intersection, a >>> contains operation, and then finishing with a test for integer >>> equality. >> Yes, I think the execution planner of PG 8.3 makes a very bad >> decision in this case. But why? The query, the data, the indexes and >> the clustering are identical on both systems. >>> Are you sure you use the same query on both systems? >> Yes, I'm sure. I noticed a performance degradation of an existing web >> application and after some profiling I found the this query as the >> source of the problem. >> Regards, >> Stephan > > > Hmmm. It does seem strange that the evaluation order is different, > however planner costs are influenced by the parameters in > postgresql.conf, and to a less extent, the statistics sampling > routine. > > So a good thing to do would be to compare the settings in > postgresql.conf between both systems, and also check the statistics > target for each of the columns within the join before going any > further. Don't forget to restart PostgreSQL if you make any changes to > the memory /cost settings. > > > HTH, > > Mark. > > -- > Mark Cave-Ayland > Sirius Corporation - The Open Source Experts http://www.siriusit.co.uk > T: +44 870 608 0063 > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
