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 msArcGIS 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_idfrom 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) bwhere 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_idfrom xx_test_5 awhere 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?
أكثر...
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 msArcGIS 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_idfrom 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) bwhere 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_idfrom xx_test_5 awhere 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?
أكثر...