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

 Ok, I have an example showing this issue

 In the attached dump there is two tables. One with the two largest
 polygons from the question in gis.stackexchange linked to above.
 The two polygons have between 7000 and 9000 vertex-points each.

  The other table is a point table with 100000 points spread over the box
 from ST_Extent of the two polygons.

 There is no indexes on the polygons.

 So ther is two polygons with gid = 1 and gid = 2.

 Running :

 {{{
 SELECT count(*)
 FROM polygons  , points
 WHERE ST_Intersects(points.geom , polygons.geom) and polygons.gid in (1);
 }}}
 takes about 1600 ms.

 The same with gid=2 takes about the same.

 So to run the same with both polygons could be expected to take about
 3-4000 ms,
 But
 {{{
 SELECT count(*)
 FROM polygons  , points
 WHERE ST_Intersects(points.geom , polygons.geom) and polygons.gid in
 (1,2);
 }}}

 uses about 18000 ms.

 If I put a gist-index on the points table the issue disappears. Then the
 single polygon queries takes about 1200 ms each and the query with both
 polygons uses about 2400ms.

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