On Sun, 16 Oct 2022 at 16:42, Tom Lane <t...@sss.pgh.pa.us> wrote: > > It also seems kind of unfair to decide > that the relevant comparison point is a seqscan rather than a > btree indexscan.
I think the comparison against full table scan seems appropriate, as the benefit of BRIN is less space usage when compared to other indexes, and better IO selectivity than full table scans. A btree easily requires 10x the space of a normal BRIN index, and may require a lot of random IO whilst scanning. This BRIN-sorted scan would have a much lower random IO cost during its scan, and would help bridge the performance gap between having index that supports ordered retrieval, and no index at all, which is especially steep in large tables. I think that BRIN would be an alternative to btree as a provider of sorted data, even when the table is not 100% clustered. This BRIN-assisted table sort can help reduce the amount of data that is accessed in top-N sorts significantly, both at the index and at the relation level, without having the space overhead of "all sortable columns get a btree index". If BRIN gets its HOT optimization back, the benefits would be even larger, as we would then have an index that can speed up top-N sorts without bloating other indexes, and at very low disk footprint. Columns that are only occasionally accessed in a sorted manner could then get BRIN minmax indexes to support this sort, at minimal overhead to the rest of the application. Kind regards, Matthias van de Meent
