[postgis-users] ST_Contains() performance problem

[Stephan Grüter]

Hi Mark,

thanks for your fast response


Am 24.04.2008 um 12:47 schrieb Mark Cave-Ayland:
> It appears that all the time is disappearing into the nested loop  
> with filter. What does the EXPLAIN ANALYZE of the following look like:
>
> EXPLAIN ANALYZE
> SELECT geo.regcode FROM reg.geometrien geo
> WHERE
> geo.the_geom && GeomFromText('Point(682970.983613 246747.010965)',  
> 21781)

"Index Scan using geometrien_geom_idx on geometrien geo   
(cost=0.00..8.27 rows=1 width=4) (actual time=6.245..369.905 rows=115  
loops=1)"
"  Index Cond: (the_geom &&  
'010100002015550000861F9CF7B5D7244163D17416D81E0E41'::geometry)"
"  Filter: (the_geom &&  
'010100002015550000861F9CF7B5D7244163D17416D81E0E41'::geometry)"
"Total runtime: 370.178 ms"

The mesured runtime is nearly identical on both systems.

> This should return with a few ms. Bear in mind that the  
> "geo.the_geom && GeomFromText('Point(682970.983613 246747.010965)',  
> 21781)" section is redundant with newer versions of PostGIS too.

Yes, I know. I also tested with only ST_Contains() but it makes no  
difference.

Regards

Stephan Grüter

Wüest & Partner
Gotthardstr. 6
8002 Zürich
044 289 90 32