[postgis-users] ST_Contains() performance problem

[Stephan Grüter]

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

>
> Stephan Grüter wrote:
>> Hi,
>>
>> after migration from PostgreSQL-8.2.4/PostGIS-1.2.1 to  
>> PostgreSQL-8.3.1/PostGIS-1.3.3
>> I have massive performance problems doing point in polygon  
>> analyses with ST_Contains().
>>
>> Query:
>>
>> EXPLAIN ANALYZE
>> SELECT geo.regcode, bez.name    FROM reg.geometrien geo,  
>> reg.bezeichnungen bez   WHERE geo.regtyp = 8
>> AND geo.the_geom && GeomFromText('Point(682970.983613  
>> 246747.010965)', 21781)   AND Contains(geo.the_geom, GeomFromText 
>> ('Point(682970.983613 246747.010965)', 21781))
>> AND geo.regtyp = bez.regtyp   AND geo.regcode = bez.regcode;
>>
>> Old system:
>>
>> version: "PostgreSQL 8.2.4 on powerpc-apple-darwin8.10.1, compiled  
>> by GCC powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple  
>> Computer, Inc. build 5363)"
>> postgis_full_version: "POSTGIS="1.2.1" GEOS="2.2.3-CAPI-1.1.1"  
>> PROJ="Rel. 4.5.0, 22 Oct 2006" USE_STATS"
>>
>> "Nested Loop  (cost=0.00..16.56 rows=1 width=18) (actual  
>> time=7.278..7.422 rows=1 loops=1)"
>> "  ->  Index Scan using geometrien_geom_idx on geometrien geo   
>> (cost=0.00..8.27 rows=1 width=8) (actual time=7.089..7.231 rows=1  
>> loops=1)"
>> "        Index Cond: (the_geom &&  
>> '0020000001000055154124D7B5F79C1F86410E1ED81674D163'::geometry)"
>> "        Filter: ((regtyp = 8) AND (the_geom &&  
>> '0020000001000055154124D7B5F79C1F86410E1ED81674D163'::geometry)  
>> AND contains(the_geom,  
>> '0020000001000055154124D7B5F79C1F86410E1ED81674D163'::geometry))"
>> "  ->  Index Scan using bezeichnungen_pkey on bezeichnungen bez   
>> (cost=0.00..8.27 rows=1 width=20) (actual time=0.155..0.157 rows=1  
>> loops=1)"
>> "        Index Cond: ((bez.regtyp = 8) AND (geo.regcode =  
>> bez.regcode))"
>> "Total runtime: 7.893 ms"
>>
>> New system:
>>
>> version: "PostgreSQL 8.3.1 on i386-apple-darwin9.2.0, compiled by  
>> GCC i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465)"
>> postgis_full_version: "POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1"  
>> PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS"
>>
>> "Nested Loop  (cost=0.00..16.56 rows=1 width=15) (actual  
>> time=7306.497..7362.836 rows=1 loops=1)"
>> "  ->  Index Scan using geometrien_geom_idx on geometrien geo   
>> (cost=0.00..8.27 rows=1 width=8) (actual time=7306.475..7362.812  
>> rows=1 loops=1)"
>> "        Index Cond: (the_geom &&  
>> '010100002015550000861F9CF7B5D7244163D17416D81E0E41'::geometry)"
>> "        Filter: ((the_geom &&  
>> '010100002015550000861F9CF7B5D7244163D17416D81E0E41'::geometry)  
>> AND contains(the_geom,  
>> '010100002015550000861F9CF7B5D7244163D17416D81E0E41'::geometry)  
>> AND (regtyp = 8))"
>> "  ->  Index Scan using bezeichnungen_pkey on bezeichnungen bez   
>> (cost=0.00..8.27 rows=1 width=17) (actual time=0.015..0.016 rows=1  
>> loops=1)"
>> "        Index Cond: ((bez.regtyp = 8) AND (bez.regcode =  
>> geo.regcode))"
>> "Total runtime: 7362.916 ms"
>>
>> Thanks for any help
>>
>>
>> Stephan Grüter
>>
>> Wüest & Partner
>> Gotthardstr. 6
>> 8002 Zürich
>> 044 289 90 32
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>