Re: Do BRIN indexes support MIN/MAX?

> 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.

When looking for a max, you could scan the range with the maximal indexed
MAX, and then you could skip all the ranges that have MAX less than the
already found max value. You can use any found value as a cutoff point.

You do not need to have much hackery as you can scan the most promising
range first, and then scan the others if the most promising did not yield
value which is known to exceed all the other ranges.

So find_min and find_max can be pretty efficient with BRIN.
I’m not sure regarding the practical use of that though.

Top N can be implemented in the same way by passing all the values to a
binary heap and skipping all the ranges that are known to be less than what
we already have in a heap.

Andrey, could you clarify the use cases for looking up of min/max record?
I am afraid, PostgreSQL has no way to represent “this access method
supports min/max retrieval”, and what currently exists is “this access
method supports ordered scans sorted by the indexed column’s value”
Apparently, implementing ordered BRIN scan would be harder (from
implementation, costing, and testing point of views), so it would be nice
to hear on the use cases for having min/max am scans or for having BRIN
ordered scans.

Vladimir

--
Vladimir