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

 {{{
 EXPLAIN
 SELECT count(*)
 FROM polygons  , points
 WHERE ST_Intersects(points.geom , polygons.geom);
 }}}

 9.1:

 {{{
 Aggregate  (cost=54835.03..54835.04 rows=1 width=0)
    ->  Nested Loop  (cost=0.00..54835.03 rows=1 width=0)
          Join Filter: ((points.geom && polygons.geom) AND
 _st_intersects(points.geom, polygons.geom))
          ->  Seq Scan on points  (cost=0.00..1834.00 rows=100000
 width=128)
          ->  Materialize  (cost=0.00..1.03 rows=2 width=32)
                ->  Seq Scan on polygons  (cost=0.00..1.02 rows=2 width=32)
 (6 rows)
 }}}


 8.4:


 {{{
  Aggregate  (cost=56169.02..56169.03 rows=1 width=0)
    ->  Nested Loop  (cost=0.00..56169.02 rows=1 width=0)
          Join Filter: ((points.geom && polygons.geom) AND
 _st_intersects(points.geom, polygons.geom))
          ->  Seq Scan on polygons  (cost=0.00..1.02 rows=2 width=32)
          ->  Seq Scan on points  (cost=0.00..1834.00 rows=100000
 width=128)
 (5 rows)
 }}}


 As I understand it in 8.4 the sequantal scan takes one polygon at a time
 and iterate all points. But in 9.1 it takes one point at a time iterating
 all polygons which makes preparing the polygon just slowing things down.

 But from PostgreSQL perspective there is no right and wrong I guess?
 Or can we manipulate this behavior?

 Is this something like the same issue that causes us to expand both first
 and second geoemetry in the sql-definition of ST_DWithin. If I remember
 right Regina said something like that.

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