[postgis-users] ST_Contains() performance problem

[Kevin Neufeld]

Hi Stephan,


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. 


Are you sure you use the same query on both systems? 

Cheers,
Kevin


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