This reminds me of an issue I reported several years ago where Btree index scans were chosen over seq scan of a large, INSERT-only table due to very high correlation, but performed poorly. I concluded that use of the the high "large scale" correlation on a large 50+GB table caused the planner to fail to account for a larger number of pages being read nonsequentially (the opposite of your issue). I think that's because we were INSERTing data which was at least approximately sorted on record END time, and the index was on record START time. For a large table with a week's data, the correlation of "start time" was still be very high (0.99995). But scanning the index ends up reading pages nonsequentially, and also multiple visits per page.
I eeked out a patch which made "correlation" a per-index statistic rather than a per-column one. That means the planner could distinguish between a freshly-built btree index and a fragmented one. (At the time, there was a hypothesis that our issue was partially due to repeated values of the index columns.) It didn't occur to me at the time, but that would also allow creating numerous, partial BRIN indices, each of which would have separate correlation computed over just their "restricted range", which *might* also handle your case, depending on how packed your data is. https://www.postgresql.org/message-id/flat/20170707234119.GN17566%40telsasoft.com#fdcbebc342b8fb9ad0ff293913f54d11 On Tue, Oct 15, 2019 at 11:05:13AM -0500, Jeremy Finzel wrote: > I do believe that the only use case that will work really well for BRIN is > either a truly insert-only table which is never pruned ... or a table which > is routinely CLUSTERed! Or partitioned table, which for large data sets I highly recommend instead of DELETE. Justin
