Re: maximum columns for brin bloom indexes

On Thu, Apr 08, 2021 at 12:18:36PM +0200, Tomas Vondra wrote:
> 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.
>

Ok, that makes sense. Still I see a few odd things:

"""
drop table if exists t1;
create table t1(i int, j int);
create index on t1 using brin(i int4_bloom_ops, j int4_bloom_ops ) ;

-- This one will succeed, I guess because it has less different
-- values
insert into t1
select random()*20, random()*100 from generate_series(1, 1000);

-- succeed
insert into t1
select random()*20, random()*100 from generate_series(1, 100000);

-- succeed
insert into t1
select random()*200, random()*1000 from generate_series(1, 1000);

-- succeed
insert into t1
select random()*200, random()*1000 from generate_series(1, 1000);

-- succeed? This is the case it has been causing problems before
insert into t1
select random()*1000, random()*1000 from generate_series(1, 1000);
"""

Maybe this makes sense, but it looks random to me. If it makes sense
this is something we should document better.

Let's try another combination:

"""
drop table if exists t1;
create table t1(i int, j int);
create index on t1 using brin(i int4_bloom_ops, j int4_bloom_ops ) ;

-- this fails again
insert into t1
select random()*1000, random()*1000 from generate_series(1, 1000);

-- and this starts to fail now, but this worked before
insert into t1
select random()*20, random()*100 from generate_series(1, 1000);
"""

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

How the false positive rate work?

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

true.

Another question, should we allow to create a brin multi column index
that uses different opclasses?

CREATE INDEX ON t1 USING brin (i int4_bloom_ops, j int4_minmax_ops);

--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL