[postgis-devel] [PostGIS] #230: st_expand seems to affect the execution order wich affects st_dwithin

[PostGIS]

#230: st_expand seems to affect the execution order wich affects st_dwithin
---------------------------------------+------------------------------------
 Reporter:  nicklas                    |       Owner:  pramsey      
     Type:  defect                     |      Status:  new          
 Priority:  medium                     |   Milestone:  postgis 1.5.0
Component:  postgis                    |     Version:               
 Keywords:  st_dwithin st_expand bbox  |  
---------------------------------------+------------------------------------
 using dataset from states.backup as example

 the bbox-comparasion doesn't seem to always happen before the _st_dwithin
 calculations.
 To isolate the problem I used _st_dwithin instead of st_dwithin and played
 with the bbox-comparasions in the query instead.
 then,
 if I run:

 {{{

 select a.state from us.states a, us.states b
         where b.state = 'Hawaii' and a.state != 'Hawaii'
                 and a.the_geom && b.the_geom;

 or

 select a.state from us.states a, us.states b
         where b.state = 'Hawaii' and a.state != 'Hawaii'
                 and st_expand(a.the_geom, 0) && b.the_geom;

 }}}
 it tells in 30 ms that there is no hit, just as expected
 the same happens if I try:
 {{{

 select a.state from us.states a, us.states b
         where b.state = 'Hawaii' and a.state != 'Hawaii'
                 and a.the_geom && b.the_geom and _st_dwithin(a.the_geom,
 b.the_geom, 0);

 }}}
 fast answer that there is no hit.

 BUT

 if I run

 {{{
 select a.state from us.states a, us.states b
         where b.state = 'Hawaii' and a.state != 'Hawaii'
                 and st_expand(a.the_geom, 0) && b.the_geom and
 _st_dwithin(a.the_geom, b.the_geom, 0);
 }}}
 then, the fun is over. It starts running for 220000 ms before I get the
 answer.
 My guess is that in this case _st_dwithin is trigged before the bbox-
 comparation so it makes the distance calculations to every one of the
 other polygons in the dataset.
 It behaves the same if I run:

 {{{

 select a.state from us.states a, us.states b
         where b.state = 'Hawaii' and a.state != 'Hawaii'
                 and st_dwithin(a.the_geom, b.the_geom,0)

 }}}
 where the function is supposed to handle the bbox-comparasion.

 /Nicklas

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/230>
PostGIS <http://trac.osgeo.org/postgis/>
PostGIS