[postgis-tickets] [PostGIS] #1798: Performance issue with ST_Intersects(geometry, geometry)

[PostGIS]

#1798: Performance issue with ST_Intersects(geometry,geometry)
----------------------+-----------------------------------------------------
  Reporter:  nicklas  |       Owner:  pramsey      
      Type:  defect   |      Status:  reopened     
  Priority:  medium   |   Milestone:  PostGIS 2.2.0
 Component:  postgis  |     Version:  2.0.x        
Resolution:           |    Keywords:               
----------------------+-----------------------------------------------------

Comment(by robe):

 disregard my comment about indexes.  Stupid user error - turns out I was
 testing with indexed geometries all along because nicklas had indexes on
 his intersects_order table.  The without index as expected is much worse.
 21,425ms.  So my harp about difference in speed after I added an index is
 bogus.

 We still have the issue that ST_Intersects seems much slower than
 ST_DWithin with an index for this sample set of data.  However if you take
 the index off the geom, ST_Intersects then defeats ST_DWithin.  So it
 seems ST_Intersects is not benefiting from the index as much as ST_DWithin
 is perhaps because prepared geometry is not helpful here and ST_DWithin
 doesn't have the overhead of preparing.


 {{{
 -- without geom index
 -- 16392 ms, 16312 ms
 SELECT COUNT(*) FROM intersects_order t1, intersects_order t2
 WHERE ST_Intersects(t1.geom, t2.geom);

 -- 16401 ms, 16392 ms
 SELECT COUNT(*) FROM intersects_order t1, intersects_order t2
 WHERE ST_Intersects(t2.geom, t1.geom);

 -- 18794 ms, 18913 ms
 SELECT COUNT(*) FROM intersects_order t1, intersects_order t2
 WHERE ST_DWithin(t1.geom, t2.geom,0);

 -- 18433 ms, 18494 ms
 SELECT COUNT(*) FROM intersects_order t1, intersects_order t2
 WHERE ST_DWithin(t2.geom, t1.geom,0);
 }}}

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/1798#comment:31>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.