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

 Just for good measure did reverse test on ST_Dwithin:

 {{{
 -- count 5131
 -- 4151 ms,  4161 ms
 SELECT COUNT(*) FROM intersects_order t1, intersects_order t2
 WHERE ST_DWithin(t2.geom, t1.geom,0);

 -- explain analyze --
 Aggregate  (cost=2522.66..2522.67 rows=1 width=0) (actual
 time=4191.691..4191.692 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..2522.66 rows=1 width=0) (actual
 time=0.142..4178.498 rows=5131 loops=1)
         ->  Seq Scan on intersects_order t1  (cost=0.00..565.23 rows=623
 width=7166) (actual time=0.015..2.579 rows=623 loops=1)
         ->  Index Scan using idx_i_o_geom on intersects_order t2
 (cost=0.00..3.13 rows=1 width=7166) (actual time=0.745..6.631 rows=8
 loops=623)
               Index Cond: (geom && st_expand(t1.geom, 0::double
 precision))
               Filter: ((t1.geom && st_expand(geom, 0::double precision))
 AND _st_dwithin(geom, t1.geom, 0::double precision))
               Rows Removed by Filter: 1
 Total runtime: 4191.816 ms



 --
 -- count 5131
 -- 6061 ms, 5935 ms, 6021 ms
 SELECT COUNT(*) FROM intersects_order t1, intersects_order t2
  WHERE ST_Intersects(t2.geom, t1.geom);

 --explain analyze
 Aggregate  (cost=2531.78..2531.79 rows=1 width=0) (actual
 time=6080.601..6080.603 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..2517.99 rows=5517 width=0) (actual
 time=11.907..6070.443 rows=5131 loops=1)
         ->  Seq Scan on intersects_order t1  (cost=0.00..565.23 rows=623
 width=7166) (actual time=0.015..2.721 rows=623 loops=1)
         ->  Index Scan using idx_i_o_geom on intersects_order t2
 (cost=0.00..3.12 rows=1 width=7166) (actual time=6.540..9.670 rows=8
 loops=623)
               Index Cond: (geom && t1.geom)
               Filter: _st_intersects(geom, t1.geom)
               Rows Removed by Filter: 1
 Total runtime: 6080.893 ms

 }}}

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