On Wed, 16 Oct 2019 at 05:05, Jeremy Finzel <finzelj@gmail.com> wrote:
> But perhaps it would be worth exploring if there could be more detailed stats on physical vs logical correlation,
suchas when ANALYZE takes its samples, noting physical locations as well as logical values, and allowing the
correlationto take account of that more detailed analysis. Of course, sounds like a huge amount of work with uncertain
benefits.
Yes. I think improving the statistics could be beneficial. It does
appear like the single value for the entire column is not fine-grained
enough for your use case.
> As the docs state, I do believe that the only use case that will work really well for BRIN is either a truly
insert-onlytable which is never pruned (an idea I dislike as a DBA who wants us to keep OLTP data trim and implement
dataretention policies :), or a table which is routinely CLUSTERed!
Have you considered partitioning the table so that the retention
policy could be implemented by dropping a partition rather than
performing a bulk DELETE?
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services