[postgis-users] very Poor Performance for Contains

[Mark Cave-Ayland]

Hi Rene,
 
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?
 
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.
 
 
Cheers,
 
Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


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.

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Rene
Neidt
Sent: 08 June 2004 07:46
To: PostGisUSers (E-Mail)
Subject: WG: [postgis-users] very Poor Performance for Contains



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/20040609/f13ebc46/attachment.html>