WG: [postgis-users] very Poor Performance for Contains

[Rene Neidt]

Sorry for this second posting, i have seen that no content is displayed in
the HTML style Mailinglist.

-> Hello Brent, i allready use the && Operator in the second query.

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/20040608/084ccf04/attachment.html>