Julien Rouhaud <rjuju123@gmail.com> writes:
> 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.
Yeah. You could for example (when looking for a MAX) skip scanning
block ranges whose indexed MAX is less than the indexed MIN of some
other block range. But unless the column you are interested in is
pretty well correlated with physical storage order, that seems
unlikely to buy much.
[ ... thinks ... ] Actually, I'm not sure that even that optimization
works without hackery. It requires that you know that the "other block
range" is not empty according to your snapshot. I guess you could work
by scanning block ranges in descending indexed-MIN order, and skipping
ranges only once you have a candidate MAX in hand. But that sounds
complicated and very prone to nonsequential table access, so I'm not
sure how much of a win it is. If you need fast min/max access, you
probably need to bite the bullet and maintain a btree index.
regards, tom lane
