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

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Rene Neidt-2
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>>

Reply | Threaded
Open this post in threaded view
|

Re: WG: [postgis-users] very Poor Performance for Contains

David Blasby-3
The reason why contains() is so slow is because it starts from scratch
every test.  It doesnt save any of the information about the geometry
objects, so it has to compute them every time you run contains().

Second, its using extended math routines so that it will ALWAYS return
the correct result.

Third, doing a contains() on a complex polygon is a lot of work!



The slow query is probably because the PostGIS->WKB->bytea->Output Text
is not highly optimized.  You can try lwgeom and see if its faster for you.

dave