I'm experiencing sever performance issues on a polygon featureclass(PG_GEOMETRY) in a PostgreSQL 9.2 database from a 10.2.2 client. Even when zoomed to an area with 10-20 features within the extent.
I've narrowed it down to a call from ArcGIS Desktop: DECLARE sdecur_508_23777 BINARY CURSOR WITH HOLD FOR select st_asewkb(ST_setSRID(zzzztablenamezzzz.shape,-1)) AS shape from sde.fooschema.zzzztablenamezzz and then the subsequent fetches (e.g. 2015-03-13 19:52:26 GMT LOG statement: FETCH FORWARD 1000 from sdecur_508_23777)
The data frame and the spatial reference id (54017) are the same, so I'm not seeing why a query that brings back the entire table's geometry as SRID-less WKB needs to be executed (count appox 900000 polygons).
There's no fancy rendering--I just added the featureclass and zoomed in to a small extent. The featureclass has a spatial index that performs well
Answer
If that is the query that is used I guess you are out of luck.
Is there no WHERE-clause?
I have never played with cursors, but as I understand the query, Arc-whatever fetches 1000 rows a time from the whole data set.
No matter how efficient and smart Arc-whatever then is to find out what geometries to use and render. All performance is already lost.
All parts of all geometries have to be detoasted in the database. Then PostGIS have to convert every one of the 900000 geometries into EWKB. That is a lot of iterations and memcopying.
So I hope there is some more part of the query that is missing.
The right way to do it is of course to let PostGIS do an spatial index-scan and only handle the rows that is needed.
No comments:
Post a Comment