From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: John Naylor <john.naylor@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: WIP: BRIN multi-range indexes
Date: Sat, 27 Mar 2021 21:42:05 +0100
Message-ID: <f673a88a-5d68-da56-8da8-08c2a4eff610@enterprisedb.com> (raw)
In-Reply-To: <20210327180913.GA3088@alvherre.pgsql>
References: <20210327180913.GA3088@alvherre.pgsql>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tomas.vondra@enterprisedb.com, alvherre@alvh.no-ip.org, john.naylor@enterprisedb.com, pgsql-hackers@lists.postgresql.org
Subject: Re: WIP: BRIN multi-range indexes
In-Reply-To: <f673a88a-5d68-da56-8da8-08c2a4eff610@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox