[PostGIS] #5959: Query planner vastly underestimates number of rows matched by GIST index

[PostGIS]

#5959: Query planner vastly underestimates number of rows matched by GIST index
----------------------+---------------------------
  Reporter:  alexobs  |      Owner:  pramsey
      Type:  defect   |     Status:  new
  Priority:  medium   |  Milestone:  PostGIS 3.5.5
 Component:  postgis  |    Version:  3.5.x
Resolution:           |   Keywords:
----------------------+---------------------------
Comment (by alexobs):

 Reverting the changes in `gserialized_estimate.c` from that commit indeed
 fixes the problem!

 I did some more testing and found that specifically removing `(double)`
 from line 1666 fixes it:

 {{{
 diff --git a/postgis/gserialized_estimate.c
 b/postgis/gserialized_estimate.c
 index 76907c858..c090d8754 100644
 --- a/postgis/gserialized_estimate.c
 +++ b/postgis/gserialized_estimate.c
 @@ -1663,7 +1663,7 @@ compute_gserialized_stats_mode(VacAttrStats *stats,
 AnalyzeAttrFetchFunc fetchfu
                                  * then take the appropriate root to get
 the estimated number of cells
                                  * on this axis (eg, pow(0.5) for 2d,
 pow(0.333) for 3d, pow(0.25) for 4d)
                                 */
 -                               histo_size[d] =
 (int)pow((double)histo_cells_target * histo_ndims * edge_ratio,
 1/(double)histo_ndims);
 +                               histo_size[d] =
 (int)pow(histo_cells_target * histo_ndims * edge_ratio,
 1/(double)histo_ndims);
                                 /* If something goes awry, just give this
 dim one slot */
                                 if ( ! histo_size[d] )
                                         histo_size[d] = 1;
 }}}


 @robe This is the output I get from that query before and after the fix.

 Before:
 {{{
 {"ndims":2,"size":[-2147483648,-2147483648],"extent":{"min":[2.85124,50.7356],"max":[7.24006,53.9675]},"table_features":141994640,"sample_features":150000,"not_null_features":150000,"histogram_features":149992,"histogram_cells":0,"cells_covered":0}
 }}}

 After
 {{{
 {"ndims":2,"size":[1,1],"extent":{"min":[2.88071,50.7375],"max":[7.23847,53.8955]},"table_features":141941376,"sample_features":150000,"not_null_features":150000,"histogram_features":149990,"histogram_cells":1,"cells_covered":149990}
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5959#comment:8>
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.