[postgis-users] ST_Contains() performance problem

[Mark Cave-Ayland]

Hi 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;

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)

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.


ATB,

Mark.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063