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 komzpa, 5 years ago

Alternative is something like this patch on Postgres: https://patch-diff.githubusercontent.com/raw/postgres/postgres/pull/49.diff

comment:2 by pramsey, 5 years ago

Milestone: PostGIS 2.5.4PostGIS 2.5.5

comment:3 by robe, 4 years ago

Milestone: PostGIS 2.5.5PostGIS 3.1.0

comment:4 by pramsey, 4 years ago

Owner: changed from pramsey to komzpa

comment:5 by Darafei Praliaskouski <me@…>, 4 years ago

Resolution: fixed
Status: newclosed

In ed1de58/git:

Generate Correlation statistics so BRIN works.

Closes #4625

Note: See TracTickets for help on using tickets.