Horton

Re: WIP: BRIN multi-range indexes

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread View on postgr.es
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: John Naylor <john.naylor@enterprisedb.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-03-27T20:42:05Z
Lists: pgsql-hackers 
On 3/27/21 7:09 PM, Alvaro Herrera wrote:
> On 2021-Mar-26, Tomas Vondra wrote:
> 
>> Hi,
>>
>> I've pushed both the bloom and minmax-multi indexes today.
> 
> One thing I've been wondering all along is how useful are these
> BRIN-backed bloom indexes compared to contrib-supplied bloom indexes.
> My guess is that the BRIN implementation has some advantage, or you
> would not have worked so much on it.  But what is it?
> 

The contrib/bloom indexes are a completely different type of index. They
are not BRIN but a completely separate AM. The bloom filters are per-row
(so the index is larger than BRIN) and it's useful when you have table
with many attributes, and need to test various combinations of them.


create table t (a int, b int, c int);

insert into t select 10 * random(), 10 * random(), 10 * random()
  from generate_series(1,1000000) s(i);

analyze t;

create index bloom_idx on t using bloom (a,b,c)
  with (length=80, col1=4, col2=4, col3=4);

create index brin_bloom_idx on t using
  brin (a int4_bloom_ops, b int4_bloom_ops, c int4_bloom_ops);


test=# \di+
                          List of relations
 Schema |      Name      | Table | Access Method | Size  | Description
--------+----------------+-------+---------------+-------+-------------
 public | bloom_idx      | t     | bloom         | 15 MB |
 public | brin_bloom_idx | t     | brin          | 88 kB |
(2 rows)


So it's a completely different kind of animal, perhaps closer to btree
than to BRIN. I'm sure there are cases where contrib/bloom works better
than brin/bloom, but also the other way around.


regards

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

Commits

  1. BRIN minmax-multi indexes

  2. BRIN bloom indexes

  3. Support the old signature of BRIN consistent function

  4. Remove unnecessary pg_amproc BRIN minmax entries

  5. Optimize allocations in bringetbitmap

  6. Move IS [NOT] NULL handling from BRIN support functions

  7. Pass all scan keys to BRIN consistent function at once

  8. Properly detoast data in brin_form_tuple