Opened 5 years ago
Closed 4 years ago
#4625 closed defect (fixed)
brin geometry indexes don't work because postgres wants correlation
Reported by: | komzpa | Owned by: | komzpa |
---|---|---|---|
Priority: | medium | Milestone: | PostGIS 3.1.0 |
Component: | postgis | Version: | 2.5.x -- EOL |
Keywords: | Cc: |
Description
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.
Change History (5)
comment:1 by , 5 years ago
comment:2 by , 5 years ago
Milestone: | PostGIS 2.5.4 → PostGIS 2.5.5 |
---|
comment:3 by , 4 years ago
Milestone: | PostGIS 2.5.5 → PostGIS 3.1.0 |
---|
comment:4 by , 4 years ago
Owner: | changed from | to
---|
Note:
See TracTickets
for help on using tickets.
Alternative is something like this patch on Postgres: https://patch-diff.githubusercontent.com/raw/postgres/postgres/pull/49.diff