id summary reporter owner description type status priority milestone component version resolution keywords cc 230 Put in costs in _ST and possibly other functions nicklas robe "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" defect closed medium PostGIS 1.5.0 postgis 1.4 fixed st_dwithin st_expand bbox