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@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
