[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):

 Bear in mind that the comment block is misleading - we're not bailing out
 because we have <const> && <const> because there is no such clause in the
 generated SQL. If you look closer at the code beforehand, the reason we're
 dropping into that section is because we have something that's not <var>
 && <const> or <const> && <var>, which in this case is caused by <const> &&
 <func> created by the _st_expand(pos, 300000) clause.

 The only reason this works for your case above is because it just so
 happens that 300000 covers the entire histogram area and so a selectivity
 of 1.0 is an appropriate value. If you change this to, say 300, then the
 selectivity will become inaccurate once again.

 I wonder if we can create a slightly modified _st_expand() function with
 different prototypes for different arguments, e.g.

 _st_expand(const, distance) -> returns the geography with expanded box as
 it (should) do at the moment

 _st_expand(col, distance) -> uses the histogram stats to return the
 bounding box for the given column and then expand it by distance

 If we mark this second function as IMMUTABLE then it should get folded
 into a constant at plan time, and then we can use this to set search_box
 by detecting this <const> && <const> condition which might stand a chance
 of returning something slightly meaningful.

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