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

Comment(by robe):

 Guys,

 Can other people PLEASE run the above simple test to confirm we have
 significantly slowed PostGIS 2.0 down before we do anything else.

 I want the speed back we enjoyed after we had switched to gserialized and
 I did my preliminary gserialized on benchmarks.  Like I said -- I blame
 this slowness on all the liblwgeom/libpgcommon shuffling.

 I just retested on my local workstation (1.5.3 vs.  my newly compiled 2.0
 on the same PostGIS 9.0 instances and PostGIS 2.0 is 3 times slower).

 I had also tested my more intensive database test after all the shuffling
 and speed was similarly much slower for my benchmarks.  My reference
 database backup has grown to about 30GB now and I'm not so kin on doing 2
 200GB parallel tests until this simple test passes.

 So I reiterate

 {{{

 My 1.5.3 install on same PostgreSQL 9.0 instance:
 Aggregate  (cost=3539.62..3539.63 rows=1 width=0) (actual
 time=133.856..133.856 rows=1 loops=1)
   ->  Seq Scan on gtest  (cost=0.00..3535.00 rows=1847 width=0) (actual
 time=1.645..132.722 rows=3987 loops=1)
         Filter: ((the_geom &&
 '0103000020E610000001000000090000000000000000002A40000000000000454037DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry)
 AND _st_intersects(the_geom,
 '0103000020E610000001000000090000000000000000002A40000000000000454037DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry))
 Total runtime: 133.911 ms



 }}}

 PostGIS 2.0 r7840


 {{{

  Aggregate  (cost=3539.72..3539.74 rows=1 width=0) (actual
 time=389.212..389.212 rows=1 loops=1)
   ->  Seq Scan on gtest  (cost=0.00..3535.00 rows=1889 width=0) (actual
 time=0.567..387.914 rows=3998 loops=1)
         Filter: ((the_geom &&
 '0103000020E610000001000000090000000000000000002A40000000000000454037DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry)
 AND _st_intersects(the_geom,
 '0103000020E610000001000000090000000000000000002A40000000000000454037DB37B31D3E28403309329378F043400300000000002440000000000080434099499099C4831F403209329378F043400000000000001C40FFFFFFFFFFFF44408A499099C4831F40CCF6CD6C870F4640F8FFFFFFFFFF2340000000000080464030DB37B31D3E2840CFF6CD6C870F46400000000000002A400000000000004540'::geometry))
 Total runtime: 389.256 ms
 }}}

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