[postgis-tickets] [PostGIS] #4625: brin geometry indexes don't work because postgres wants correlation

[PostGIS]

#4625: brin geometry indexes don't work because postgres wants correlation
---------------------+---------------------------
 Reporter:  komzpa   |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  medium   |  Milestone:  PostGIS 2.5.4
Component:  postgis  |    Version:  2.5.x
 Keywords:           |
---------------------+---------------------------
 Thanks RhodiumToad for helping find this.

 {{{
 [22:33] <RhodiumToad> Komzzpa: the brin selectivity estimate depends on
 the correlation
 [22:33] <Komzzpa> my table is hilbert sorted
 [22:33] <RhodiumToad> if the index correlation is less than 1e-10, then it
 will force the selectivity to be 1
 [22:33] <RhodiumToad> does the large rows= value in the explain actually
 match the estimated rowcount of the table?
 [22:34] <Komzzpa> it's 10% larger
 [22:34] <RhodiumToad> estimated rowcount, not actual rowcount
 [22:35] <RhodiumToad> if you look at brincostestimate (in
 backend/utils/adt/selfuncs.c) you'll see it computes indexCorrelation,
 taking the highest value if there are multiple columns
 [22:36] <Komzzpa> yes
 [22:36] <RhodiumToad> if it gets no correlation stats it assumes a
 correlation of 0
 [22:36] <RhodiumToad> it calculates the qual selectivity using
 clauselist_selectivity as normal, that'll call the postgis selectivity
 estimator
 [22:36] <Komzzpa> thanks
 [22:37] <RhodiumToad> but the qual selectivity is not used if the
 correlation is 0
 [22:37] <RhodiumToad> since the qual selectivity is used to calculate
 minimalRanges, but if correlation is <1e-10, estimatedRanges is forced
 equal to indexRanges,
 [22:38] <RhodiumToad> and then estimatedRanges / indexRanges becomes the
 final selectivity estimate
 [22:38] <RhodiumToad> so my guess is that nothing's providing correlation
 estimates that it can use
 }}}


 To compute correlation to hilbert curve we just need to call
 compute_scalar_stats and std_typanalyze in our analyze, or generate the
 correlation value ourselves.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4625>
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.