[postgis-devel] [PostGIS] #230: Put in costs in _ST and possibly other functions

[PostGIS]

#230: Put in costs in _ST and possibly other functions
----------------------+-----------------------------------------------------
  Reporter:  nicklas  |       Owner:  robe                     
      Type:  defect   |      Status:  assigned                 
  Priority:  medium   |   Milestone:  postgis 1.5.0            
 Component:  postgis  |     Version:  1.4                      
Resolution:           |    Keywords:  st_dwithin st_expand bbox
----------------------+-----------------------------------------------------
Changes (by robe):

  * summary:  st_expand seems to affect the execution order wich affects
              st_dwithin => Put in costs in _ST and possibly
              other functions
  * milestone:  postgis 1.4.1 => postgis 1.5.0

Old description:

> 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

New description:

 I'm revising this since this seems to be an issue mostly because we don't
 have COSTS assigned to our _ST functions, so the planner in certain cases
 (like with large geometries), chooses to do a table scan instead of an
 index scan.

 Since costs are only supported in PostgreSQL 8.3+ and we are also going to
 need typmod support for geography.  I'm wondering if we want to just push
 this to 1.5 and also make PostgreSQL 8.3+ a requirement for 1.5 instead of
 introducing messy conditional code to not put costs in if 8.2 or lower.

 ----OLD description

 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#comment:14>
PostGIS <http://trac.osgeo.org/postgis/>
PostGIS