## F.5. bloom

F.5.1. 参数
F.5.2. 例子
F.5.3. 操作符类接口
F.5.4. 限制
F.5.5. 作者

`bloom`提供了一种基于布鲁姆过滤器的索引访问方法。

### F.5.1. 参数

`bloom`索引在其`WITH`子句中接受下列参数：

`length`

`col1 — col32`

### F.5.2. 例子

```CREATE INDEX bloomidx ON tbloom USING bloom (i1,i2,i3)
WITH (length=80, col1=2, col2=2, col3=4);
```

```=# CREATE TABLE tbloom AS
SELECT
(random() * 1000000)::int as i1,
(random() * 1000000)::int as i2,
(random() * 1000000)::int as i3,
(random() * 1000000)::int as i4,
(random() * 1000000)::int as i5,
(random() * 1000000)::int as i6
FROM
generate_series(1,10000000);
SELECT 10000000
```

```=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
-------------------------------------------------------------------​-----------------------------------
Seq Scan on tbloom  (cost=0.00..2137.14 rows=3 width=24) (actual time=15.480..15.480 rows=0 loops=1)
Filter: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Filter: 100000
Planning Time: 0.340 ms
Execution Time: 15.501 ms
(5 rows)
```

```=# CREATE INDEX btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
=# SELECT pg_size_pretty(pg_relation_size('btreeidx'));
pg_size_pretty
----------------
3976 kB
(1 row)
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
-------------------------------------------------------------------​-----------------------------------
Seq Scan on tbloom  (cost=0.00..2137.00 rows=2 width=24) (actual time=12.604..12.604 rows=0 loops=1)
Filter: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Filter: 100000
Planning Time: 0.155 ms
Execution Time: 12.617 ms
(5 rows)
```

```=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
=# SELECT pg_size_pretty(pg_relation_size('bloomidx'));
pg_size_pretty
----------------
1584 kB
(1 row)
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
-------------------------------------------------------------------​--------------------------------------------------
Bitmap Heap Scan on tbloom  (cost=1792.00..1799.69 rows=2 width=24) (actual time=0.384..0.384 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Index Recheck: 26
Heap Blocks: exact=26
->  Bitmap Index Scan on bloomidx  (cost=0.00..1792.00 rows=2 width=0) (actual time=0.350..0.350 rows=26 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Planning Time: 0.122 ms
Execution Time: 0.407 ms
(8 rows)
```

```=# CREATE INDEX btreeidx1 ON tbloom (i1);
CREATE INDEX
=# CREATE INDEX btreeidx2 ON tbloom (i2);
CREATE INDEX
=# CREATE INDEX btreeidx3 ON tbloom (i3);
CREATE INDEX
=# CREATE INDEX btreeidx4 ON tbloom (i4);
CREATE INDEX
=# CREATE INDEX btreeidx5 ON tbloom (i5);
CREATE INDEX
=# CREATE INDEX btreeidx6 ON tbloom (i6);
CREATE INDEX
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
-------------------------------------------------------------------​--------------------------------------------------------
Bitmap Heap Scan on tbloom  (cost=24.34..32.03 rows=2 width=24) (actual time=0.032..0.033 rows=0 loops=1)
Recheck Cond: ((i5 = 123451) AND (i2 = 898732))
->  BitmapAnd  (cost=24.34..24.34 rows=2 width=0) (actual time=0.029..0.030 rows=0 loops=1)
->  Bitmap Index Scan on btreeidx5  (cost=0.00..12.04 rows=500 width=0) (actual time=0.029..0.029 rows=0 loops=1)
Index Cond: (i5 = 123451)
->  Bitmap Index Scan on btreeidx2  (cost=0.00..12.04 rows=500 width=0) (never executed)
Index Cond: (i2 = 898732)
Planning Time: 0.537 ms
Execution Time: 0.064 ms
(9 rows)
```

### F.5.3. 操作符类接口

```CREATE OPERATOR CLASS text_ops
DEFAULT FOR TYPE text USING bloom AS
OPERATOR    1   =(text, text),
FUNCTION    1   hashtext(text);
```

### F.5.4. 限制

• 在模块中只包括了用于`int4`以及`text`的操作符类。

• 搜索只支持`=`操作符。但是未来可以为带合并和交集操作的数组增加支持。

• `bloom`访问方法不支持`UNIQUE`索引。

• `bloom`访问方法不支持对`NULL`值的搜索。

### F.5.5. 作者

Teodor Sigaev , Postgres Professional, Moscow, Russia

Alexander Korotkov , Postgres Professional, Moscow, Russia

Oleg Bartunov , Postgres Professional, Moscow, Russia