[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:  robe                     
      Type:  defect   |      Status:  assigned                 
  Priority:  medium   |   Milestone:  postgis 1.4.1            
 Component:  postgis  |     Version:  1.4                      
Resolution:           |    Keywords:  st_dwithin st_expand bbox
----------------------+-----------------------------------------------------
Comment (by nicklas):

 I don't know if I understand you right Regina, but my suggestion comes
 from this:
 I use the last example above with st_dwithin

 with cost 1 at _st_dwithin, the explain of the query looks like this. It
 puts _st_dwithin before the bbox comparasions:

 "Nested Loop  (cost=0.00..4.50 rows=1 width=9)"
 "  Join Filter: (_st_dwithin(a.the_geom, b.the_geom, 0::double precision)
 AND (a.the_geom && st_expand(b.the_geom, 0::double precision)) AND
 (b.the_geom && st_expand(a.the_geom, 0::double precision)))"
 "  ->  Seq Scan on states b  (cost=0.00..1.66 rows=1 width=442568)"
 "        Filter: ((state)::text = 'Hawaii'::text)"
 "  ->  Seq Scan on states a  (cost=0.00..1.66 rows=52 width=442577)"
 "        Filter: ((a.state)::text <> 'Hawaii'::text)"

 But if I change the cost of _st_dwithin to 100 the same exlpanation looks
 like this:

 "Nested Loop  (cost=0.00..10.21 rows=1 width=9)"
 "  Join Filter: ((b.the_geom && st_expand(a.the_geom, 0::double
 precision)) AND _st_dwithin(a.the_geom, b.the_geom, 0::double precision))"
 "  ->  Seq Scan on states b  (cost=0.00..1.66 rows=1 width=442568)"
 "        Filter: ((state)::text = 'Hawaii'::text)"
 "  ->  Index Scan using us_idx_states_the_geom on states a
 (cost=0.00..8.27 rows=1 width=442577)"
 "        Index Cond: (a.the_geom && st_expand(b.the_geom, 0::double
 precision))"
 "        Filter: ((a.state)::text <> 'Hawaii'::text)"


 Now it puts _st_dwithin after the bounding-box comparasion and the query
 runs in 16ms instead of very very long time.

 /Nicklas

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