How can I speed up a slow ArcGIS Server 10.2 - PostgreSQL (9.2) ST_Intersects query?

Question

I am troubleshooting a slow st_geometry query in Postgres 9.2 that uses ST_Intersects--a basic Select from table where ST_Intersects type query.

                         table_extent                         |                          queryshape_extent
--------------------------------------------------------------+----------------------------------------------------------------------
 BOX(-10942448.8275 5798114.3628,-10937877.2752 5800427.4828) | BOX(-10942696.568988 5797982.903128,-10941159.336747 5798715.800428)
(1 row)

Time: 1086.008 ms

ArcGIS featureclass (world behrmann - 54017):

CREATE TABLE fe.xx_test_4
(
  objectid integer NOT NULL,
  operation_id integer NOT NULL,
  shape st_geometry
)

I also recreated the table and spatial index with PostGIS geometry for comparison, using the same tablespace configuration.

CREATE TABLE fe.xx_test_5
(
  objectid integer,
  operation_id integer,
  geom geometry
)

The featureclass(st_geometry) query takes 23 seconds to run and returns 317490 rows (from a total of 1031123 rows). I wasn't sure if this was explainable by the fact that I'm returning 30% of the rows.

Query:

select 
operation_id
from 
fe.xx_test_4 a,
(select st_geometry(
'POLYGON ((-10942387.005869 5798694.633719,-10942175.338779 5798684.050364,-10942003.359269 5798684.050364,-10941714.962859 5798697.279557,-10941580.025089 5798705.217073,-10941378.941353 5798715.800428,-10941177.857618 5798543.820917,-10941159.336747 5798194.570218,-10941328.670419 5798096.674189,-10941542.983348 5797982.903128,-10941791.692179 5797998.778160,-10942000.713430 5798009.361515,-10942151.526232 5798019.944869,-10942437.276803 5798019.944869,-10942561.631219 5798041.111578,-10942625.131346 5798062.278287,-10942693.923150 5798120.486737,-10942696.568988 5798308.341279,-10942641.006377 5798440.633211,-10942588.089605 5798580.862658,-10942532.526994 5798665.529494,-10942503.422769 5798689.342041,-10942418.755933 5798697.279557,-10942387.005869 5798694.633719))'
,55) shape) b
where sde.ST_Intersects(a.shape,  b.shape)

-

    QUERY PLAN                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..24.79 rows=5 width=4) (actual time=0.168..26826.593 rows=317490 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.004..0.008 rows=1 loops=1)
   ->  Index Scan using xx_test_4_sx on xx_test_4 a  (cost=0.00..24.72 rows=5 width=36) (actual time=0.143..24439.164 rows=317490 loops=1)
         Index Cond: (shape ^! ('C1000000180000000800100037000000A50200000100000095D3C7DCDE03A1A49F8BD307FCE0269B9D03CDAD67D0D809E2C323ED881DEAE943CCAD67D0CC40F93FF01E9F50CB1CCAF02DFB01399CE8502AFF08A0199DACE0190C077F9CD169BAAAA03D6BBF501B3F7D101D5BBF501E9F50CC2DCA401CFD809FC85E002C49310F3F7D10100FFB08202A9F50C00000000'::st_geometry))
 Total runtime: 27934.344 ms
(5 rows)

If I run the same query against the PostGIS table, it completes in 2.7 seconds:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on xx_test_5 a  (cost=19436.28..131296.02 rows=105119 width=4)
   Recheck Cond: (geom && '010300002001D3000001000000180000002F143060F6DE64C123DA8EA8C91E56410F47D7EADBDE64C1ED293903C71E5641B4217F6BC6DE64C1ED293903C71E5641ADBDCF5EA2DE64C10B43E451CA1E56417287CD8091DE64C12786E44DCC1E564154901F5E78DE64C15D363AF3CE1E56414E9B713B5FDE64C175E789F4A31E5641A5A1C6EA5CDE64C1A3737EA44C1E56418C12741572DE64C19FE9252B341E56413A9677DF8CDE64C162D9CCB9171E5641935426F6ABDE64C19A5FCDB11B1E5641276BD416C6DE64C1D00F23571E1E56417EE4D6F0D8DE64C1D4BB78FC201E5641F791DBA8FCDE64C1D4BB78FC201E564130F232340CDF64C10D182447261E564187FC332414DF64C14774CF912B1E5641DE718ABD1CDF64C1F2B2261F3A1E5641522635121DDF64C1E083D715691E56418A3D342016DF64C16E8786288A1E56414E0BDE820FDF64C1E6C93537AD1E56418522DD9008DF64C1CD3AE361C21E5641DB5287ED04DF64C1EFFFE355C81E5641679A3058FADE64C10B43E451CA1E56412F143060F6DE64C123DA8EA8C91E5641'::geometry)
   Filter: _st_intersects(geom, '010300002001D3000001000000180000002F143060F6DE64C123DA8EA8C91E56410F47D7EADBDE64C1ED293903C71E5641B4217F6BC6DE64C1ED293903C71E5641ADBDCF5EA2DE64C10B43E451CA1E56417287CD8091DE64C12786E44DCC1E564154901F5E78DE64C15D363AF3CE1E56414E9B713B5FDE64C175E789F4A31E5641A5A1C6EA5CDE64C1A3737EA44C1E56418C12741572DE64C19FE9252B341E56413A9677DF8CDE64C162D9CCB9171E5641935426F6ABDE64C19A5FCDB11B1E5641276BD416C6DE64C1D00F23571E1E56417EE4D6F0D8DE64C1D4BB78FC201E5641F791DBA8FCDE64C1D4BB78FC201E564130F232340CDF64C10D182447261E564187FC332414DF64C14774CF912B1E5641DE718ABD1CDF64C1F2B2261F3A1E5641522635121DDF64C1E083D715691E56418A3D342016DF64C16E8786288A1E56414E0BDE820FDF64C1E6C93537AD1E56418522DD9008DF64C1CD3AE361C21E5641DB5287ED04DF64C1EFFFE355C81E5641679A3058FADE64C10B43E451CA1E56412F143060F6DE64C123DA8EA8C91E5641'::geometry)
   ->  Bitmap Index Scan on xx_test_5_sx  (cost=0.00..19410.00 rows=315359 width=0)
         Index Cond: (geom && '010300002001D3000001000000180000002F143060F6DE64C123DA8EA8C91E56410F47D7EADBDE64C1ED293903C71E5641B4217F6BC6DE64C1ED293903C71E5641ADBDCF5EA2DE64C10B43E451CA1E56417287CD8091DE64C12786E44DCC1E564154901F5E78DE64C15D363AF3CE1E56414E9B713B5FDE64C175E789F4A31E5641A5A1C6EA5CDE64C1A3737EA44C1E56418C12741572DE64C19FE9252B341E56413A9677DF8CDE64C162D9CCB9171E5641935426F6ABDE64C19A5FCDB11B1E5641276BD416C6DE64C1D00F23571E1E56417EE4D6F0D8DE64C1D4BB78FC201E5641F791DBA8FCDE64C1D4BB78FC201E564130F232340CDF64C10D182447261E564187FC332414DF64C14774CF912B1E5641DE718ABD1CDF64C1F2B2261F3A1E5641522635121DDF64C1E083D715691E56418A3D342016DF64C16E8786288A1E56414E0BDE820FDF64C1E6C93537AD1E56418522DD9008DF64C1CD3AE361C21E5641DB5287ED04DF64C1EFFFE355C81E5641679A3058FADE64C10B43E451CA1E56412F143060F6DE64C123DA8EA8C91E5641'::geometry)

This is the query executed against the PostGIS table:

select 
operation_id
from 
xx_test_5 a
where ST_Intersects(a.geom,  ST_GeomFromText(
'POLYGON ((...values...));

I also tried setting the enable_seqscan=false on the st_geometry query, which changed the explain plan but not the execution time (still around 23 seconds):

Bitmap Heap Scan on victor quebec  (cost=24764.850..60721.270 rows=329794 width=4)
    Recheck Cond: (kilo ^! 'five'::seven)
  ->  Bitmap Index Scan on mike  (cost=0.000..24682.400 rows=329794 width=0)
          Index Cond: (kilo ^! 'five'::seven)

http://explain.depesz.com/s/dI37

How can I get the st_geometry query to perform like the postgis query?

Answer 1

My current workaround is to build a side table with 2 columns, the objectid and geometry( as PostGIS geometry). And to maintain that table with triggers on the featureclass. Then perform spatial queries against the side table and join back to the featureclass.

I have an open incident with Esri, but so far, no luck. I will update this answer if/when we make some progress on this front.

I just received an Esri Bug ID for this issue: BUG-000088734. Esri was able to reproduce the poor performance on the st_geometry data type (at a much smaller set of records than what I was using). Users with smaller databases can probably absorb the performance hit (I didn't notice it until my table was several million records).