Re: Do BRIN indexes support MIN/MAX?

On Wed, 29 Mar 2023 at 22:07, Vladimir Sitnikov
<sitnikov(dot)vladimir(at)gmail(dot)com> wrote:
> > Is it correct that BRIN indexes don't support MIN/MAX operations ?
> In theory, it should be possible to implement min/max scan support for BRIN, however it is not implemented yet.
>
> Just in case, min/max query would require to read all BRIN pages, and then it would require to read the corresponding pages in table.

> For instance, imagine the table has N pages. Then BRIN would have N/128 pages with the default pages_per_range=128, so your max(..) query would take N/128 + 128 pages to read. In theory it would be sequential, however, under concurrent load it might not be that sequential for the disk.

I think BRIN would require N/128 RANGES, not pages, and if I am not
mistaken it fits several ranges in an index page. It talks of summary
tuples, and I suspect a summary tuple for say, an integer, is not
gonna be longer, than 128 bytes, in which case you could fit 64 of
them in a 4k page.

Also, if you account for possible concurrent load disturbing your
index+partial scan, you also have to account for the ( more likely )
disruption on the full scan.

I.e., I have this table
apc | apc_cdrs_p2022_12 | table |
postgres | permanent | heap | 860 MB |
N/128 pages implies N/128 bytes, so index would be 6.7Mb in your numbers, but
apc | apc_cdrs_p2022_12_cuando_idx | index
| postgres | apc_cdrs_p2022_12 | permanent | brin
| 64 kB |
apc | apc_cdrs_p2022_12_raw_id_idx | index
| postgres | apc_cdrs_p2022_12 | permanent | brin
| 64 kB |
1st one is on a timestamp column, second on an integer. And several
empty partitions hace 48kB indexes, so it seems data is just 16k for
the 860 ranges. That could be about 20 bytes/range which more or less
fits to a couple of values.

In my experience, BRIN are ridiculously small. I use them on that
particular table because both cuando and raw_id correlate with
insertion order and I normally only read several megabytes ranges
indexed on them, so they work very well in limiting the scan range to
nearly what I need.

> For instance, 80GiB table would be like 10’000’000 pages, so the default BRIN would take about 78’000 pages (625MiB), so the min/max scan would read 626 MiB
> If pages per range is increased to ~3162, then index size would be ~3162 pages (25MiB), and each index entry would cover 25MiB range. Then the query would have to read ~50MiB to fetch min/max. It is not clear if that is really practical though.

If you assume your index fits 64 tuples per page your index read drops
to about 10Mb, plus the 1Mb range.

Also, I suspect you will have to read all unsummarized ranges (
probably before the summarized ones, as unsummarized can discard
summarizeds, but not the other way ).

Francisco Olarte.