[postgis-users] ST_Contains() performance problem

[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