<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=Windows-1252">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1400" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=753020609-09062004>Hello
Mark,</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=753020609-09062004>i have
update_geom... stats and create the Ex Plan --></SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>Index Scan using idx_geom_f109_dis on
sn_f109_dis (cost=0.00..6.02 rows=1 width=44) (actual
time=3794.061..3795.823 rows=1 loops=1)</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><BR> Index Cond: (the_geom
&& 'SRID=-1;POLYGON((4555478.6159 5704338.0931,4555478.6159
5704456.4274,4555587.089 5704456.4274,4555587.089 5704338.0931,4555478.6159
5704338.0931))'::geometry)</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><BR> Filter: contains(the_geom,
'SRID=-1;POLYGON((4555478.6159 5704338.0931,4555478.6159
5704456.4274,4555587.089 5704456.4274,4555587.089 5704338.0931,4555478.6159
5704338.0931))'::geometry)<BR>Total runtime: 3796.078 ms</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=753020609-09062004><FONT face=Arial color=#0000ff size=2>I can
see that the Contains Filter is the main reason for out
Problem.</FONT></SPAN></DIV>
<DIV><SPAN class=753020609-09062004><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=753020609-09062004></SPAN><SPAN class=753020609-09062004><FONT
face=Arial color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV><FONT face=Arial><BR></FONT></DIV><FONT color=#0000ff size=2></FONT>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Ursprüngliche Nachricht-----<BR><B>Von:</B> Mark Cave-Ayland
[mailto:m.cave-ayland@webbased.co.uk]<BR><B>Gesendet:</B> Mittwoch, 9. Juni
2004 10:02<BR><B>An:</B> Rene Neidt; 'PostGIS Users
Discussion'<BR><B>Betreff:</B> RE: [postgis-users] very Poor Performance for
Contains<BR><BR></FONT></DIV>
<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></BLOCKQUOTE></BODY></HTML>