[postgis-users] very Poor Performance for Contains

[Rene Neidt]

Hello Ng,
we are testing the great PostGIS Extension with our own WebMapserver "iwan".
To connect to the Postgres Server we are using ODBC.
The PG 7.4.2 Server is running on SusE Linux, PostGis Version is 0.8, with
GEOS,PROJ an STATS.

we have two Problems:
~~~~~~~~~~~~~~~~~~~~~~
 1. Spatial Operators are very slow
~~~~~~~~~~~~~~~~~~~~~~
The following Query will return exact one record, the Dataset contains
90.000 (big) Polygon records
-> Querying for BoundingBox compare only is very good ~47ms,

SELECT gid,area,perimeter,s9_d_,s9_d_id,objart,bytea(AsBinary(the_geom))
FROM sn_f109_dis WHERE (THE_GEOM && GeometryFromText('POLYGON((4555478.6159
5704338.0931,4555478.6159 5704456.4274,4555587.0890
5704456.4274,4555587.0890 5704338.0931,4555478.6159 5704338.0931))',-1))

-> the same Query with contains: ~4s (!)

SELECT gid,area,perimeter,s9_d_,s9_d_id,objart,bytea(AsBinary(the_geom))
FROM
sn_f109_dis WHERE
	(THE_GEOM && GeometryFromText('POLYGON((4555478.6159
5704338.0931,4555478.6159 5704456.4274,4555587.0890
5704456.4274,4555587.0890 5704338.0931,4555478.6159 5704338.0931))',-1))
AND contains(THE_GEOM,GeometryFromText('POLYGON((4555478.6159
5704338.0931,4555478.6159 5704456.4274,4555587.0890
5704456.4274,4555587.0890 5704338.0931,4555478.6159 5704338.0931))',-1))

Index,Vaccuum is present. As workaround for all spatial selections we filter
by box only and progress using our own functions on the map server.

~~~~~~~~~~~~~~~~~~~~~~
 2. slow Performance for Big Resultset (eg. for Drawing)
~~~~~~~~~~~~~~~~~~~~~~
"Big" doesn't mean many rows, but large Datasetsize (huge number of verts).
The Statement:
SELECT bytea(AsBinary(THE_GEOM)),ObjArt FROM sn_f109_dis WHERE (THE_GEOM &&
GeometryFromText('POLYGON((4549872.5409 5697050.6780,4549872.5409
5707868.3971,4563796.5166 5707868.3971,4563796.5166
5697050.6780,4549872.5409 5697050.6780))',-1))

... has duration 1400ms (502 Records).

without geometry (..SELECT Objart ...) the Query returns in 60ms.
(100MBit Network).

The sum(mem_size(geom)) ist 3350356
and sum(length(bytea(asbinary(the_geom)))) = 220778

Greetings,
Rene Neidt


__________________________________________________________________

Rene Neidt
Projektleiter Software
IDU Ingenieurgesellschaft für Datenverarbeitung und Umweltschutz mbH
Theodor-Körner-Allee 16
02763 Zittau
Germany
Tel	++49 3583 61-15-37
Fax	++49 3583 61-12-10
Internet  http://www.idu.de/, http://www.webmapserver.de/
Anfahrt: http://www.stadtplan-zittau.de/?alias=idu

 <<Rene Neidt.vcf>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20040607/7150779e/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Rene Neidt.vcf
Type: text/x-vcard
Size: 187 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20040607/7150779e/attachment.vcf>