1

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

|improve this question
  • Are you really loading spatial data as user "sde"? (This violates best practice) Are you using a Query Layer, or has the table been registered with ArcSDE and accessed as a feature class through a Database Connection? – Vince Mar 13 '15 at 20:24
  • No...I scrubbed the table name and accidentally dropped the schema. 'sde' is the name of the database (its an existing db, not one i created). It isn't a query layer, although I had similar performance issues with a query layer(same st_asewkb(ST_setSRID(zzzztablenamezzzz.shape,-1)) pattern). yes, registered with SDE. – Jay Cummins Mar 13 '15 at 20:33
  • No issues with the same featureclass with st_geometry. – Jay Cummins Mar 13 '15 at 20:35
  • I'm allergic to naming databases "sde" (makes me break out in hives and wheeze a bit). You'll probably need to get Tech Support on the horn (I've not yet used PG_GEOMETRY). Since it's PG_GEOMETRY specific, you may want to change the question title. – Vince Mar 13 '15 at 21:32
  • 1
    Wow...hives! There are other parts of this database that will send you into cardiac arrest! – Jay Cummins Mar 16 '15 at 12:23
5

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.

|improve this answer
  • There's no where from the definition query and there is no where from ArcMap (which is where I would expect something...like the extent of the data frame). If I manually issue a shape && geometry query, PostGIS will do the spatial index scan and is really fast. – Jay Cummins Mar 16 '15 at 12:36
  • @Jay Cummins Interesting! That must more or less be considered a bug I guess? – Nicklas Avén Mar 16 '15 at 13:54
  • 1
    Just got a call from Esri this morning. It is a bug, but I haven't got a number for it yet. But the short version is that it is a known issue and don't use PG_GEOMETRY for large tables. – Jay Cummins Apr 1 '15 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.