[postgis-tickets] [PostGIS] #2196: ST_Intersects performs worse on 2.0 than 1.5

[PostGIS]

#2196: ST_Intersects performs worse on 2.0 than 1.5
---------------------+------------------------------------------------------
 Reporter:  zwerner  |       Owner:  pramsey      
     Type:  defect   |      Status:  new          
 Priority:  medium   |   Milestone:  PostGIS 2.0.3
Component:  postgis  |     Version:  2.0.x        
 Keywords:           |  
---------------------+------------------------------------------------------
 When using the same dataset and same query, Explain Analyze shows the same
 query plan but the time for the filter on st_intersects takes much longer.

 postgresql 8.4 with postgis 1.5.4 (trimmed to show differences):

 {{{
 '->  Bitmap Heap Scan on recording r  (cost=2467.53..140276.51 rows=32280
 width=29) (actual time=452.074..4896.323 rows=699591 loops=1)'
 '    Recheck Cond: ('<geom>'::geometry && geom))'
 '    Filter: _st_intersects('<geom>'::geometry, geom)'
 '       ->  Bitmap Index Scan on geom_idx  (cost=0.00..2459.46 rows=96841
 width=0) (actual time=358.077..358.077 rows=699598 loops=1)'
 '           Index Cond: ('<geom>'::geometry && geom))'
 }}}

 postgresql 9.2 with postgis 2.0.2 (trimmed again):
 {{{
 '->  Bitmap Heap Scan on recording r  (cost=2395.49..137026.40 rows=31248
 width=29) (actual time=438.094..11424.818 rows=699591 loops=1)'
 '    Recheck Cond: ('<geom>'::geometry && geom)'
 '    Filter: _st_intersects('<geom>'::geometry, geom)'
 '      ->  Bitmap Index Scan on geom_idx  (cost=0.00..2387.68 rows=93743
 width=0) (actual time=342.217..342.217 rows=699591 loops=1)'
 '          Index Cond: ('<geom>'::geometry && geom))'
 }}}

 As you can see, the st_intersects filter takes 6.5 seconds longer than the
 equivalent query on a previous version.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/2196>
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.