[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:               
----------------------+-----------------------------------------------------
Changes (by robe):

  * milestone:  PostGIS 2.0.4 => PostGIS 2.2.0


Comment:

 I've reopened this because ST_DWithin seems clearly faster than
 ST_Intersects even after I have put in an index on the geometries.  But I
 suspect it's something we shouldn't try to fix in a micro so pushing to
 2.2.0

 After adding an index on geom and analyze on table here is what I get:

 {{{
 -- count: 5131
 -- times 5753 ms, 5792 ms
 SELECT COUNT(*) FROM intersects_order t1, intersects_order t2
 WHERE ST_Intersects(t1.geom, t2.geom);


 -- count: 5131
 -- times 5931 ms, 5962 ms, 6072 ms
 SELECT COUNT(*) FROM intersects_order t1, intersects_order t2
  WHERE ST_Intersects(t2.geom, t1.geom);

 -- count: 5131
 -- times 4181 ms, 4164 ms
 SELECT COUNT(*) FROM intersects_order t1, intersects_order t2
 WHERE ST_DWithin(t1.geom, t2.geom,0);

 }}}

 I'm even more bothered that the intersects time has gotten worse after
 putting in the index while ST_DWithin has got much better and now the
 disparity is even more striking.

 Might be something else going on here with 2.1 like something funky with
 new stats feature.

 I'm going to upgrade to latest 2.1 branch to see if issue goes away.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/1798#comment:25>
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.