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

[Rene Neidt]

Hello Mark,
i have update_geom... stats and create the Ex Plan -->

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)

  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)

  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)
Total runtime: 3796.078 ms

I can see that the Contains Filter is the main reason for out Problem.





-----Ursprüngliche Nachricht-----
Von: Mark Cave-Ayland [mailto:m.cave-ayland at webbased.co.uk]
Gesendet: Mittwoch, 9. Juni 2004 10:02
An: Rene Neidt; 'PostGIS Users Discussion'
Betreff: RE: [postgis-users] very Poor Performance for Contains


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/45828e45/attachment.html>