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

[PostGIS]

#1165: Selectivity extimation of && operator
----------------------------+-----------------------------------------------
 Reporter:  edoardopanfili  |       Owner:  pramsey      
     Type:  defect          |      Status:  new          
 Priority:  blocker         |   Milestone:  PostGIS 2.0.0
Component:  postgis         |     Version:  1.5.X        
 Keywords:                  |  
----------------------------+-----------------------------------------------
Changes (by robe):

  * priority:  medium => blocker
  * milestone:  PostGIS 1.5.4 => PostGIS 2.0.0


Comment:

 Okay I'm seeing a problem too.  I tried setting up your test but using
 pure sql and things seem much worse with my last night build than my
 8/3/2011 build.

 Here is what I have (note I have postgis 1.5.2, 2.0.0 on same server
 cluster in same table space with all same memory configurations:

 {{{
  create table gtest (id int);
  SELECT AddGeometryColumn('gtest', 'the_geom', 4326, 'POLYGON', 2);

 INSERT INTO gtest (id,the_geom)
    SELECT n,ST_Buffer(ST_SetSRID(ST_Point(10 + ((random()*radius*2) -
 radius),42 + ((random()*radius*2) - radius)),4326),0.01,10)
    FROM generate_series(0, 10000 - 1) As n CROSS JOIN (SELECT 4 As radius)
 As foo;

 vacuum analyze gtest;
  EXPLAIN ANALYZE
  SELECT count(*)
  FROM gtest
  WHERE ST_Intersects(gtest.the_geom,ST_Buffer(ST_GeomFromText('POINT(10
  42)',4326),3,2));
 }}}

 {{{
 -- my 1.5.2: POSTGIS="1.5.2" GEOS="3.3.1dev-CAPI-1.7.1" PROJ="Rel. 4.6.1,
 21 August 2008" LIBXML="2.7.6" USE_STATS      PostgreSQL 9.0.4, compiled
 by Visual C++ build 1500, 32-bit
 Aggregate  (cost=7179.77..7179.78 rows=1 width=0) (actual
 time=178.601..178.602 rows=1 loops=1)
   ->  Seq Scan on gtest  (cost=0.00..7175.00 rows=1908 width=0) (actual
 time=0.717..175.485 rows=4021 loops=1)
         Filter: ((the_geom &&
 '0103000020E610000001000000090000000000000000002A40000000000000454037DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry)
 AND _st_intersects(the_geom,
 '0103000020E610000001000000090000000000000000002A40000000000000454037DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry))
 Total runtime: 178.740 ms

 }}}

 {{{
 -- This is my 8/3/2011 build
 -- POSTGIS="2.0.0SVN" GEOS="3.3.1dev-CAPI-1.7.1" PROJ="Rel. 4.6.1, 21
 August 2008" LIBXML="2.7.8" USE_STATS PostgreSQL 9.0.4, compiled by Visual
 C++ build 1500, 32-bit
 -- before I upgraded to trunk--
 Aggregate  (cost=7179.78..7179.79 rows=1 width=0) (actual
 time=174.178..174.179 rows=1 loops=1)
   ->  Seq Scan on gtest  (cost=0.00..7175.00 rows=1912 width=0) (actual
 time=0.254..170.930 rows=4049 loops=1)
         Filter: ((the_geom &&
 '0103000020E610000001000000090000000000000000002A40000000000000454037DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry)
 AND _st_intersects(the_geom,
 '0103000020E610000001000000090000000000000000002A40000000000000454037DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry))
 Total runtime: 174.295 ms


 }}}

 -- my 8/22 build -- yikes

 {{{
 Aggregate  (cost=7179.75..7179.76 rows=1 width=0) (actual
 time=760.503..760.504 rows=1 loops=1)
   ->  Seq Scan on gtest  (cost=0.00..7175.00 rows=1899 width=0) (actual
 time=0.242..756.195 rows=4064 loops=1)
         Filter: ((the_geom &&
 '0103000020E610000001000000090000000000000000002A40000000000000454037DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry)
 AND _st_intersects(the_geom,
 '0103000020E610000001000000090000000000000000002A40000000000000454037DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry))
 Total runtime: 760.648 ms

 }}}

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