Re: maximum columns for brin bloom indexes

On 4/8/21 9:08 AM, Jaime Casanova wrote:
> Hi everyone,
>
> When testing brin bloom indexes I noted that we need to reduce the
> PAGES_PER_RANGE parameter of the index to allow more columns on it.
>
> Sadly, this could be a problem if you create the index before the table
> grows, once it reaches some number of rows (i see the error as early as
> 1000 rows) it starts error out.
>
> create table t1(i int, j int);
>
> -- uses default PAGES_PER_RANGE=128
> create index on t1 using brin(i int4_bloom_ops, j int4_bloom_ops ) ;
>
> insert into t1
> select random()*1000, random()*1000 from generate_series(1, 1000);
> ERROR: index row size 8968 exceeds maximum 8152 for index "t1_i_j_idx"
>
> if instead you create the index with a minor PAGES_PER_RANGE it goes
> fine, in this case it works once you reduce it to at least 116
>
> create index on t1 using brin(i int4_bloom_ops, j int4_bloom_ops )
> with (pages_per_range=116);
>
>
> so, for having:
> two int columns - PAGES_PER_RANGE should be max 116
> three int columns - PAGES_PER_RANGE should be max 77
> one int and one timestamp - PAGES_PER_RANGE should be max 121
>
> and so on
>

No, because this very much depends on the number if distinct values in
the page page range, which determines how well the bloom filter
compresses. You used 1000, but that's just an arbitrary value and the
actual data might have any other value. And it's unlikely that all three
columns will have the same number of distinct values.

Of course, this also depends on the false positive rate.

FWIW I doubt people are using multi-column BRIN indexes very often.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company