[postgis-devel] [PostGIS] #1165: Selectivity extimation of && operator

[PostGIS]

#1165: Selectivity extimation of && operator
----------------------------+-----------------------------------------------
 Reporter:  edoardopanfili  |       Owner:  pramsey      
     Type:  defect          |      Status:  new          
 Priority:  medium          |   Milestone:  PostGIS 1.5.4
Component:  postgis         |     Version:  1.5.X        
 Keywords:                  |  
----------------------------+-----------------------------------------------

Comment(by edoardopanfili):

 In the meanwhile I discovered a bug on my application: in some case
 "the_geom" column contains invalid data: a circle that extends over a
 radius of 100 degree, now it covers 100 metres. I did a try to simplify
 the query (remove some parts unusefull for the test case).

 This test does not depends on my database structure:[[BR]]
 1- create a new table
 {{{
 create table gtest (id int);
 SELECT AddGeometryColumn('gtest', 'the_geom', 4326, 'POLYGON', 2);
 }}}


 2- populate it with a simple sequence of pseudo-random values (I used this
 script):
 {{{
 #!/usr/bin/perl

 $radius = 4; #radius is "3", something more -> 4

 for($n=0; $n<10000 ; $n++){
         $x = 10 + ((rand()*$radius*2) - $radius);
         $y = 42 + ((rand()*$radius*2) - $radius);
         print "INSERT INTO gtest (id,the_geom) VALUES
 ($n,ST_Buffer(ST_GeomFromText('POINT($x $y)',4326),0.01,10));\n";
 }
 }}}


 3- execute this query:
 {{{
 EXPLAIN ANALYZE
 SELECT count(*)
 FROM gtest
 WHERE ST_Intersects(gtest.the_geom,ST_Buffer(ST_GeomFromText('POINT(10
 42)',4326),3,2));
 }}}
 Without any index

 POSTGIS="2.0.0SVN" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September
 2009" LIBXML="2.7.3" USE_STATS

 {{{
  Aggregate  (cost=4448.70..4448.71 rows=1 width=0) (actual
 time=307.998..307.998 rows=1 loops=1)
    ->  Seq Scan on gtest  (cost=0.00..4444.00 rows=1880 width=0) (actual
 time=4.435..307.260 rows=4012 loops=1)
          Filter: ((the_geom &&
 '0103000020E610000001000000090000000000000000002A40000000000000454038DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry)
 AND _st_intersects(the_geom,
 '0103000020E610000001000000090000000000000000002A40000000000000454038DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry))
  Total runtime: 308.047 ms
 }}}


 POSTGIS="1.5.3" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September
 2009" LIBXML="2.7.3" USE_STATS

 {{{
  Aggregate  (cost=3539.70..3539.71 rows=1 width=0) (actual
 time=52.061..52.061 rows=1 loops=1)
    ->  Seq Scan on gtest  (cost=0.00..3535.00 rows=1880 width=0) (actual
 time=0.188..51.524 rows=4012 loops=1)
          Filter: ((the_geom &&
 '0103000020E610000001000000090000000000000000002A40000000000000454038DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry)
 AND _st_intersects(the_geom,
 '0103000020E610000001000000090000000000000000002A40000000000000454038DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry))
  Total runtime: 53.451 ms
 }}}

 My system uname: Darwin shadowfax.local 11.1.0 Darwin Kernel Version
 11.1.0: Tue Jul 26 16:07:11 PDT 2011; root:xnu-1699.22.81~1/RELEASE_X86_64
 x86_64

 Tomorrow I can repeat the test using Linux.

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