[postgis-devel] [PostGIS] #1828: Estimate returned by geography_gist_selectivity results in slow query plan for ST_DWithin

[PostGIS]

#1828: Estimate returned by geography_gist_selectivity results in slow query plan
for ST_DWithin
---------------------------+------------------------------------------------
 Reporter:  realityexists  |       Owner:  mcayland     
     Type:  defect         |      Status:  new          
 Priority:  medium         |   Milestone:  PostGIS 2.0.2
Component:  postgis        |     Version:  2.0.x        
 Keywords:                 |  
---------------------------+------------------------------------------------

Comment(by mcayland):

 That's not quite true: if you have a query of the form <foo> && <bar> AND
 <bat> && <baz> then both pairs <foo> && <bar> and <bat> && <baz> will be
 passed to the selectivity function by the planner in order to find out
 which one is the cheapest, i.e. the one with the lowest cost will be
 executed first and then the other clauses will be applied as subsequent
 AND filters to the resulting dataset. In other words, the selectivity
 itself specifies the execution order. This why hardcoding it as a low
 constant for <foo> && <bar> in old versions of PostGIS forced the GiST
 index to be used all of the time because the default value was so small
 compared to any of the inbuilt estimates.

 Hence if the result of <const> && _st_expand(pos, 300000) returns a lower
 value then <const> && <var> then it should be chosen first in preference
 to the <const> && <var> clause. Hardcoding the latter to 1.0 is
 effectively telling the planner that this second clause is likely to
 return the entire table, and hence effectively forces <const> && <var> to
 look the most favourable clause to execute first. So thinking about this
 particular case again, I think you are right in that you could easily
 argue that for an ST_DWithin() query <const> && <var> is always going to
 be better (smaller) than <const> && <expanded var>, but bear in mind
 changing this is going to have an unknown knock-on effect for existing
 applications using && within custom queries as _st_expand() isn't always
 the target function.

 Upon reflection, perhaps the least invasive fix in terms of application
 performance is to teach the geography estimater specifically about <const>
 && _st_expand() using an oid function lookup, and hence return a
 selectivity that is slightly higher (and hence less attractive) than the
 plain old <const> && <var> selectivity.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/1828#comment:42>
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.