[postgis-devel] [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:  new          
 Priority:  medium   |   Milestone:  PostGIS 2.0.1
Component:  postgis  |     Version:  2.0.x        
 Keywords:           |  
---------------------+------------------------------------------------------

Comment(by nicklas):

 This is getting worse. From PostGIS perspective we have a quite serious
 regression from 8.4 to 9.1.

 In the dumpfile called intersects_order.dump is a table with polygons. It
 is the data set that Sandro used in #1841

 Here I am using a gist index so this is not a corner case but the real
 thing.

 In 8.4 those two queries takes about 2400 ms each:



 {{{
 SELECT COUNT(*) FROM intersects_order t1, intersects_order t2
 WHERE ST_Intersects(t1.geom, t2.geom);

 SELECT COUNT(*) FROM intersects_order t1, intersects_order t2
 WHERE ST_Intersects(t2.geom, t1.geom);
 }}}

 (notice the changed order in ST_Intersects)

 In 9.1 the first query uses about 5500 ms and the second uses about 9800
 ms.

 So there must be at least two things going on here. 9.1 gets affected by
 the order, 8.4 don't in this case.
 And even the fast order in 9.1 takes twice as long time as 8.3.

 I have put the explain results in the file intersects_order.sql.
 What is interesting there is that the plan is identical no matter of the
 order in ST_Intersects (compared to the case discussed above without index
 when the plan changes with different order), but is quite different in 8.4
 and 9.1.


 All this have to do with the caching and preparing mechanisms I guess, but
 I cannot figure out what is happening.

 From my understanding there is three possible scenaros in PostGIS

 1) nothing gets cached or prepared at all because the "only on second
 appearance"-rule
 2) wrong geometry gets prepared so we just get the job of preparing and no
 gain
 3) the right geometry gets prepared and cached, we all get happy

 But what is happening above in 8.4, 9.1 with "right order" and 9.1 with
 "wrong order"?

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