On Thu, 30 Mar 2023, 05:03 Andrey Klochkov, <diggerk@gmail.com> wrote:
BRIN indexes seem to work perfectly well for our purposes, and they are so tiny compared to B-Tree. Selecting min/max values is very expensive though.
In my case the table is ~2.5TB (530M records), while the whole BRIN index is 16MB. I think it'd be totally fine to scan all BRIN pages, it'd be way better than doing table scan.
brin indexes don't work the way you would hope for. the stored min/max values per range guarantees that all values in the underlying relation pages are contained in that range, but it doesn't mean that those min/max values are still present in the table, so you can't deduce in which range the current min or max value is from there.