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

 With my patches in place, I'd say there's something else going on...

 I make a call to run the function...
 {{{
 postgis20=# explain analyze SELECT id FROM _test_pos WHERE ST_DWithin(pos,
 'POINT(7 7)'::geography, 300000);
 }}}
 geography_gist_selectivity is called, great. It pulls the geog stats, runs
 the calculations, and returns a DECENT SELECTIVITY NUMBER, 0.281997. But
 then, look at the very bottom, it's called a SECOND TIME, and this time
 with arguments that make it return the DEFAULT selectivity.
 {{{
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:495]
 geography_gist_selectivity called
 NOTICE:   requested search box is : 0.938059508800507 0.0738726407289505
 0.0747810378670692, 1.03223621845245 0.168049246072769 0.168957650661469
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:563]  requested
 search box is : 0.938059508800507 0.0738726407289505 0.0747810378670692,
 1.03223621845245 0.168049246072769 0.168957650661469
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:591]  16013 read
 from stats
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:593]  histo:
 xmin,ymin,zmin: 0.883022,0.000017,0.000017
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:594]  histo:
 xmax,ymax: 1.000000,0.321394,0.342020
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:595]  histo:
 unitsx: 12.000000
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:596]  histo:
 unitsy: 34.000000
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:597]  histo:
 unitsz: 36.000000
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:598]  histo:
 avgFeatureCoverage: 0.000000
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:599]  histo:
 avgFeatureCells: 1.000000
 NOTICE:
 NOTICE:  [geography_estimate.c:estimate_selectivity:196]  histogram has 12
 unitsx, 34 unitsy, 36 unitsz
 NOTICE:  [geography_estimate.c:estimate_selectivity:197]  histogram
 geosize is 0.116978x0.321376x0.342003
 NOTICE:  [geography_estimate.c:estimate_selectivity:381]  [5,7,7] cell val
 0.000000000000000
 NOTICE:  [geography_estimate.c:estimate_selectivity:383]  [5,7,7] AOI
 0.000000007526458
 NOTICE:  [geography_estimate.c:estimate_selectivity:385]  [5,7,7] gain
 0.009150018534462
 .........
 NOTICE:  [geography_estimate.c:estimate_selectivity:381]  [11,17,17] cell
 val 0.000000000000000
 NOTICE:  [geography_estimate.c:estimate_selectivity:383]  [11,17,17] AOI
 0.000000532528078
 NOTICE:  [geography_estimate.c:estimate_selectivity:385]  [11,17,17] gain
 0.647401718907906
 NOTICE:  [geography_estimate.c:estimate_selectivity:390]  [11,17,17]
 adding 0.000000000000000 to value
 NOTICE:  [geography_estimate.c:estimate_selectivity:434]  search_box
 overlaps 847.000000 cells
 NOTICE:  [geography_estimate.c:estimate_selectivity:435]  avg feat
 overlaps 1.000000 cells
 NOTICE:  [geography_estimate.c:estimate_selectivity:447]
 SUM(ov_histo_cells)=0.281997
 NOTICE:  [geography_estimate.c:estimate_selectivity:448]  gain=1.000000
 NOTICE:  [geography_estimate.c:estimate_selectivity:449]
 selectivity=0.281997
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:609]  returning
 computed value: 0.281997
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:495]
 geography_gist_selectivity called
 NOTICE:  [geography_estimate.c:geography_gist_selectivity:538]  no
 variable argument ? - returning default selectivity

 QUERY PLAN
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on _test_pos  (cost=0.00..5578.00 rows=1 width=4) (actual
 time=16.487..66.990 rows=3839 loops=1)
    Filter: ((pos &&
 '0101000020E61000000000000000001C400000000000001C40'::geography) AND
 ('0101000020E61000000000000000001C400000000000001C40'::geography &&
 _st_expand(pos, 300000::double precision)) AND _st_dwithin(pos,
 '0101000020E61000000000000000001C400000000000001C40'::geography,
 300000::double precision, true))
  Total runtime: 67.736 ms
 (3 rows)
 }}}
 Our double-sided expand trick is not doing what we want, it's not picking
 the good selectivity and discarding the other, it's picking the other.

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