[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):

 Huh -- I upgraded to latest 2.1 branch and things got worse for
 ST_Intersects
 even after I revacuum analyze the table (though not that noticeably worse
 so could be just the server warming up since I had to restart the service
 to upgrade). I have to fix the fact that 3.4.0 r is not coming thru on my
 64-bit.  I suspect geos_revision or something is being clubbered in my
 geos.  This version of geos is still built with my configure.  I haven't
 tried my 9.3 which is built with cmake geos.


 {{{
 POSTGIS="2.1.0rc1dev r11637" GEOS="3.4.0dev-CAPI-1.8.0 r0" PROJ="Rel.
 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24"
 LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER PostgreSQL 9.2.4, compiled by
 Visual C++ build 1600, 64-bit
 }}}

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


 -- count: 5131
 -- times 6142 ms, 6112 ms, 6181 ms
 SELECT COUNT(*) FROM intersects_order t1, intersects_order t2
  WHERE ST_Intersects(t2.geom, t1.geom);

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

 -- count: 5131
 -- times 4211 ms, 4222 ms, 4210 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:30>
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.