<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1400" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=407315807-09062004><FONT face=Arial color=#0000ff size=2>Hi
Rene,</FONT></SPAN></DIV>
<DIV><SPAN class=407315807-09062004><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=407315807-09062004><FONT face=Arial color=#0000ff size=2>I
noticed that while you had created an index and done a vacuum, did you do a
"SELECT update_geometry_stats()" to ensure that the index statistics are
built?</FONT></SPAN></DIV>
<DIV><SPAN class=407315807-09062004><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=407315807-09062004><FONT face=Arial color=#0000ff size=2>If
that doesn't work could you do an "EXPLAIN ANALYZE <sql query>" for all
your queries which will show us which indices are being used and
why.</FONT></SPAN></DIV>
<DIV><SPAN class=407315807-09062004><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=407315807-09062004><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=407315807-09062004><FONT face=Arial color=#0000ff
size=2>Cheers,</FONT></SPAN></DIV>
<DIV><SPAN class=407315807-09062004><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=407315807-09062004><FONT face=Arial color=#0000ff
size=2>Mark.</FONT></SPAN></DIV><!-- Converted from text/plain format -->
<P><FONT size=2>---<BR><BR>Mark Cave-Ayland<BR>Webbased Ltd.<BR>Tamar Science
Park<BR>Derriford<BR>Plymouth<BR>PL6 8BX<BR>England<BR><BR>Tel: +44 (0)1752
764445<BR>Fax: +44 (0)1752 764446<BR><BR><BR>This email and any attachments are
confidential to the intended recipient and may also be privileged. If you are
not the intended recipient please delete it from your system and notify the
sender. You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.</FONT></P>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Rene Neidt<BR><B>Sent:</B> 08 June 2004 07:46<BR><B>To:</B> PostGisUSers
(E-Mail)<BR><B>Subject:</B> WG: [postgis-users] very Poor Performance for
Contains<BR><BR></FONT></DIV><!-- 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></BLOCKQUOTE></BODY></HTML>