[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:                  |  
----------------------------+-----------------------------------------------
 In am using postgres 8.4.8 and postgis 1.5.3 on Mac OS X 10.7.1

 Using this query [q1]

 {{{
 SELECT specienomi.nome, cartellino.cont_nome,
     ST_AsGML(cartellino.the_geom)
 FROM cartellino, specienomi, confini_regioni
 WHERE confini_regioni.regione='UMBRIA'
     AND specienomi.nome like 'Quercus%'
     AND cartellino.idspecie=specienomi.id
     AND ST_Intersects(cartellino.the_geom,confini_regioni.the_geom4326);
 }}}


 this is the plan:
 {{{
  Sort  (cost=20.45..20.46 rows=1 width=931) (actual
 time=4457.775..4457.786 rows=76 loops=1)
    Sort Key: cartellino.id
    Sort Method:  quicksort  Memory: 74kB
    ->  Hash Join  (cost=8.32..20.44 rows=1 width=931) (actual
 time=243.679..4457.658 rows=76 loops=1)
          Hash Cond: (cartellino.idspecie = principale.id)
          ->  Nested Loop  (cost=0.00..9.81 rows=614 width=886) (actual
 time=4.094..4439.024 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=1473036) (actual time=0.017..0.021 rows=1 loops=1)
                      Filter: ((regione)::text = 'UMBRIA'::text)
                ->  Index Scan using cartellino_punto_geom_4326 on
 cartellino  (cost=0.00..8.30 rows=1 width=886) (actual time=0.059..94.148
 rows=32200 loops=1)
                      Index Cond: (cartellino.the_geom &&
 confini_regioni.the_geom4326)
          ->  Hash  (cost=8.28..8.28 rows=3 width=57) (actual
 time=0.392..0.392 rows=74 loops=1)
                ->  Index Scan using i_specie_nome_specie_like on specie
 principale  (cost=0.01..8.28 rows=3 width=57) (actual time=0.034..0.348
 rows=74 loops=1)
                      Index Cond: ((esterna_nome(ibrido, proparte,
 (genere)::text, [...]  (cultivar)::text) ~>=~ 'Quercus'::text) AND
 (esterna_nome(ibrido, proparte, (genere)::text, [...] (cultivar)::text)
 ~<~ 'Quercut'::text))
                      Filter: (esterna_nome(ibrido, proparte,
 (genere)::text, [...] (cultivar)::text) ~~ 'Quercus%'::text)
  Total runtime: 4481.933 ms
 }}}


 My target is to obtain
 ''(cartellino join specie) join confini_regioni''
 and not
 ''(cartellino join confini_regioni) join specie''

 and this is possible with [q2]

 {{{
 WITH temp_que AS (
     SELECT specienomi.nome AS nome,
         cartellino.cont_nome AS cont_nome,
         cartellino.id AS id, the_geom
     FROM cartellino, specienomi
     WHERE specienomi.nome like 'Quercus %'
         AND cartellino.idspecie=specienomi.id
 )
 SELECT temp_que.nome, temp_que.cont_nome, ST_AsGML(temp_que.the_geom)
 FROM temp_que, confini_regioni
 WHERE confini_regioni.regione='UMBRIA'
 AND ST_Intersects(temp_que.the_geom,confini_regioni.the_geom4326);
 }}}

 The time is 45.026 ms


 If I try with

 {{{
 set join_collapse_limit=1;
 SELECT specienomi.nome, ST_AsGML(cartellino.the_geom)
 FROM confini_regioni full JOIN (
     cartellino full JOIN specienomi ON
     (cartellino.idspecie=specienomi.id)) ON
     ST_Intersects(cartellino.the_geom,confini_regioni.the_geom4326)
 WHERE confini_regioni.regione='UMBRIA'
 AND specienomi.nome like 'Quercus%'
 }}}


 is slow: 5750.499 ms
 and
 '''NOTICE:  LWGEOM_gist_joinsel called with incorrect join type'''

 I did a try with 2.0SVN but I think that there are problems with my
 database conversion because all the queryes are slower (q1=85931.595 ms
 and q2=589.595 ms)

 This is the thread on postgresl mailing list:
 http://archives.postgresql.org/pgsql-general/2011-08/msg00664.php

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/1165>
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.