<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=Windows-1252">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6487.1">
<TITLE>WG: [postgis-users] very Poor Performance for Contains</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Sorry for this second posting, i have seen that no content is displayed in the HTML style Mailinglist.</FONT>
</P>
<P><FONT SIZE=2>-> Hello Brent, i allready use the && Operator in the second query.</FONT>
</P>
<P><FONT SIZE=2>Hello Ng, </FONT>
<BR><FONT SIZE=2>we are testing the great PostGIS Extension with our own WebMapserver "iwan". </FONT>
<BR><FONT SIZE=2>To connect to the Postgres Server we are using ODBC. </FONT>
<BR><FONT SIZE=2>The PG 7.4.2 Server is running on SusE Linux, PostGis Version is 0.8, with GEOS,PROJ an STATS. </FONT>
<BR><FONT SIZE=2>we have two Problems: </FONT>
<BR><FONT SIZE=2>~~~~~~~~~~~~~~~~~~~~~~ </FONT>
<BR><FONT SIZE=2> 1. Spatial Operators are very slow </FONT>
<BR><FONT SIZE=2>~~~~~~~~~~~~~~~~~~~~~~ </FONT>
<BR><FONT SIZE=2>The following Query will return exact one record, the Dataset contains 90.000 (big) Polygon records </FONT>
<BR><FONT SIZE=2>-> Querying for BoundingBox compare only is very good ~47ms, </FONT>
<BR><FONT SIZE=2>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))</FONT></P>
<P><FONT SIZE=2>-> the same Query with contains: ~4s (!) </FONT>
<BR><FONT SIZE=2>SELECT gid,area,perimeter,s9_d_,s9_d_id,objart,bytea(AsBinary(the_geom)) </FONT>
<BR><FONT SIZE=2>FROM </FONT>
<BR><FONT SIZE=2>sn_f109_dis WHERE </FONT>
<BR><FONT SIZE=2> (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))</FONT></P>
<P><FONT SIZE=2>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))</FONT></P>
<P><FONT SIZE=2>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.</FONT></P>
<P><FONT SIZE=2>~~~~~~~~~~~~~~~~~~~~~~ </FONT>
<BR><FONT SIZE=2> 2. slow Performance for Big Resultset (eg. for Drawing) </FONT>
<BR><FONT SIZE=2>~~~~~~~~~~~~~~~~~~~~~~ </FONT>
<BR><FONT SIZE=2>"Big" doesn't mean many rows, but large Datasetsize (huge number of verts). </FONT>
<BR><FONT SIZE=2>The Statement: </FONT>
<BR><FONT SIZE=2>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)) </FONT></P>
<P><FONT SIZE=2>... has duration 1400ms (502 Records). </FONT>
<BR><FONT SIZE=2>without geometry (..SELECT Objart ...) the Query returns in 60ms. </FONT>
<BR><FONT SIZE=2>(100MBit Network). </FONT>
<BR><FONT SIZE=2>The sum(mem_size(geom)) ist 3350356 </FONT>
<BR><FONT SIZE=2>and sum(length(bytea(asbinary(the_geom)))) = 220778 </FONT>
<BR><FONT SIZE=2>Greetings, </FONT>
<BR><FONT SIZE=2>Rene Neidt </FONT>
</P>
<BR>
<P><FONT SIZE=2>__________________________________________________________________ </FONT>
<BR><FONT SIZE=2>Rene Neidt </FONT>
<BR><FONT SIZE=2>Projektleiter Software </FONT>
<BR><FONT SIZE=2>IDU Ingenieurgesellschaft für Datenverarbeitung und Umweltschutz mbH </FONT>
<BR><FONT SIZE=2>Theodor-Körner-Allee 16 </FONT>
<BR><FONT SIZE=2>02763 Zittau </FONT>
<BR><FONT SIZE=2>Germany </FONT>
<BR><FONT SIZE=2>Tel ++49 3583 61-15-37 </FONT>
<BR><FONT SIZE=2>Fax ++49 3583 61-12-10 </FONT>
<BR><FONT SIZE=2>Internet <A HREF="http://www.idu.de/">http://www.idu.de/</A>, <A HREF="http://www.webmapserver.de/">http://www.webmapserver.de/</A> </FONT>
<BR><FONT SIZE=2>Anfahrt: <A HREF="http://www.stadtplan-zittau.de/?alias=idu">http://www.stadtplan-zittau.de/?alias=idu</A> </FONT>
<BR><FONT SIZE=2><<Rene Neidt.vcf>> </FONT>
</P>
</BODY>
</HTML>