[postgis-users] ST_Contains() performance problem

[Stephan Grüter]

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