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

 I redo a fresh postgis 2.0.0SVN 7774 installation. After installation I
 did an hard-upgrade to update my db then a "vacuum analyze". If it is
 usefull I can post the single steps.[[BR]]

 All the part (potgres 8.4.8, postgis 1.5.3, postgis 2.0.0SVN 7774) are
 compiled
 in Mac OS X 10.7.1 using (gcc -version) [[BR]]
 i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build
 5658) (LLVM build 2335.15.00)[[BR]]

 the plan for the query in postgis 2.0.0SVN 7774 is:
 {{{
  Sort  (cost=32.66..32.73 rows=29 width=944) (actual
 time=89371.383..89371.389 rows=76 loops=1)
    Sort Key: cartellino.id
    Sort Method:  quicksort  Memory: 74kB
    ->  Hash Join  (cost=12.27..31.95 rows=29 width=944) (actual
 time=1780.217..89371.271 rows=76 loops=1)
          Hash Cond: (cartellino.idspecie = principale.id)
          ->  Nested Loop  (cost=0.00..9.80 rows=605 width=899) (actual
 time=7.109..89348.115 rows=18370 loops=1)
                Join Filter: _st_intersects(cartellino.the_geom,
 confini_regioni.the_geom4326)
                ->  Seq Scan on confini_regioni  (cost=0.00..1.25 rows=1
 width=1473539) (actual time=0.014..0.018 rows=1 loops=1)
                      Filter: ((regione)::text = 'UMBRIA'::text)
                ->  Index Scan using cartellino_punto_geom_4326 on
 cartellino  (cost=0.00..8.29 rows=1 width=899) (actual time=0.118..100.269
 rows=32200 loops=1)
                      Index Cond: (cartellino.the_geom &&
 confini_regioni.the_geom4326)
          ->  Hash  (cost=8.28..8.28 rows=319 width=57) (actual
 time=0.595..0.595 rows=74 loops=1)
                ->  Index Scan using i_specie_nome_specie_like on specie
 principale  (cost=0.01..8.28 rows=319 width=57) (actual time=0.064..0.526
 rows=74 loops=1)
                      Index Cond: ((esterna_nome(ibrido, proparte,
 (genere)::text, [...] (cultivar)::text) ~<~ 'Quercut'::text))
                      Filter: (esterna_nome(ibrido, proparte,
 (genere)::text, [...] (cultivar)::text) ~~ 'Quercus%'::text)
 Total runtime: 89371.646 ms
 }}}

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