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.vladimir@xxxxxxxxx> wrote:
> You could for example (when looking for a MAX) skip scanningblock 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
