Re: Do BRIN indexes support MIN/MAX?

Vladimir,
Here's the use case description. We have some large tables that are
effectively append-only logs with some stats on our systems. The table I
was experimenting with recently contains 6 months of data. It has a
timestamp column that tells when the stats in the row were collected. The
system that consumes the data from this table normally does point queries
that use B-Tree indexes which involve more than the timestamp column.
Sometimes that system needs to reprocess a few hours to a few days of the
most recent data, and that's when the BRIN index is used. In addition, we
sometimes check the recency of the data in the table by finding
"MAX(timestamp_column)", and that's when the BRIN index is not helpful. For
now we modified the query that does this to limit the search to a few days
of data as we don't expect the data in the table to ever become more stale
than that.

On Wed, Mar 29, 2023 at 9:58 PM Vladimir Sitnikov <
sitnikov(dot)vladimir(at)gmail(dot)com> wrote:

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

--
Andrey Klochkov