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

Comment(by robe):

 Just for the record  I just tried your query on my 8.4 32-bit running
 (1.5) on windows vs. my 9.1  64-bit running (2.0.1) on the same windows 7
 64-bit box.  The  timings don't look that different to me.


 {{{
 EXPLAIN ANALYZE VERBOSE SELECT id FROM public.test_geom_table WHERE
 ST_Intersects(ST_PolygonFromText('POLYGON(( -132.71472222222
 51.508611111111, -132.71472222222 18.646111111111, -57.831944444444
 18.646111111111, -57.831944444444 51.508611111111, -132.71472222222
 51.508611111111 ))', 4326), geom) ;
 }}}


 outputs:

 {{{
 PostgreSQL 9.1.7, compiled by Visual C++ build 1500, 64-bit POSTGIS="2.0.1
 r9979" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL
 1.9.1, released 2012/05/15" LIBXML="2.7.8" LIBJSON="UNKNOWN"

 Bitmap Heap Scan on public.test_geom_table  (cost=26272.52..312210.90
 rows=228337 width=4) (actual time=185.047..5102.375 rows=692395 loops=1)
   Output: id
   Recheck Cond:
 ('0103000020E6100000010000000500000019452301DF9660C04F4D3C2B1AC1494019452301DF9660C0D1CDAB8967A53240E97CD2277DEA4CC0D1CDAB8967A53240E97CD2277DEA4CC04F4D3C2B1AC1494019452301DF9660C04F4D3C2B1AC14940'::geometry
 && test_geom_table.geom)
   Filter:
 _st_intersects('0103000020E6100000010000000500000019452301DF9660C04F4D3C2B1AC1494019452301DF9660C0D1CDAB8967A53240E97CD2277DEA4CC0D1CDAB8967A53240E97CD2277DEA4CC04F4D3C2B1AC1494019452301DF9660C04F4D3C2B1AC14940'::geometry,
 test_geom_table.geom)
   ->  Bitmap Index Scan on test_geom_table_geom_idx  (cost=0.00..26215.43
 rows=685011 width=0) (actual time=184.039..184.039 rows=692395 loops=1)
         Index Cond:
 ('0103000020E6100000010000000500000019452301DF9660C04F4D3C2B1AC1494019452301DF9660C0D1CDAB8967A53240E97CD2277DEA4CC0D1CDAB8967A53240E97CD2277DEA4CC04F4D3C2B1AC1494019452301DF9660C04F4D3C2B1AC14940'::geometry
 && test_geom_table.geom)
 Total runtime: 5149.532 ms
 }}}

 For my 8.4 instance running 32-bit
 First time took 5140ms, second 4970ms

 {{{
 PostgreSQL 8.4.8, compiled by Visual C++ build 1400, 32-bit
 POSTGIS="1.5.6" GEOS="3.3.6-CAPI-1.7.6" PROJ="Rel. 4.6.1, 21 August 2008"
 LIBXML="2.7.8" USE_STATS

 Bitmap Heap Scan on test_geom_table  (cost=26616.92..303346.06 rows=231344
 width=4) (actual time=773.432..4928.097 rows=692762 loops=1)
   Output: id
   Recheck Cond:
 ('0103000020E6100000010000000500000019452301DF9660C04F4D3C2B1AC1494019452301DF9660C0D1CDAB8967A53240E97CD2277DEA4CC0D1CDAB8967A53240E97CD2277DEA4CC04F4D3C2B1AC1494019452301DF9660C04F4D3C2B1AC14940'::geometry
 && geom)
   Filter:
 _st_intersects('0103000020E6100000010000000500000019452301DF9660C04F4D3C2B1AC1494019452301DF9660C0D1CDAB8967A53240E97CD2277DEA4CC0D1CDAB8967A53240E97CD2277DEA4CC04F4D3C2B1AC1494019452301DF9660C04F4D3C2B1AC14940'::geometry,
 geom)
   ->  Bitmap Index Scan on test_geom_table_geom_idx  (cost=0.00..26559.08
 rows=694031 width=0) (actual time=771.016..771.016 rows=692762 loops=1)
         Index Cond:
 ('0103000020E6100000010000000500000019452301DF9660C04F4D3C2B1AC1494019452301DF9660C0D1CDAB8967A53240E97CD2277DEA4CC0D1CDAB8967A53240E97CD2277DEA4CC04F4D3C2B1AC1494019452301DF9660C04F4D3C2B1AC14940'::geometry
 && geom)
 Total runtime: 4970.246 ms

 }}}

 I still need to install 2.0 on my 8.4 but looking at the numbers, I'm not
 seeing a 6.5  second longer difference. At most probably around 250ms
 which can be easily dismissed giving I ran the data creation script
 instead of running against the same generated table.

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