diff --git a/doc/src/sgml/bloom.sgml b/doc/src/sgml/bloom.sgml
index 285b67b3f1..1af9b8fbab 100644
--- a/doc/src/sgml/bloom.sgml
+++ b/doc/src/sgml/bloom.sgml
@@ -108,77 +108,81 @@ CREATE INDEX bloomidx ON tbloom USING bloom (i1,i2,i3)
(random() * 1000000)::int as i5,
(random() * 1000000)::int as i6
FROM
- generate_series(1,10000000);
-SELECT 10000000
+ generate_series(1,10000);
+SELECT 10000
=# 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
+ pg_size_pretty
----------------
- 153 MB
+ 168 kB
(1 row)
=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
=# SELECT pg_size_pretty(pg_relation_size('btreeidx'));
- pg_size_pretty
+ pg_size_pretty
----------------
- 387 MB
+ 416 kB
(1 row)
- A sequential scan over this large table takes a long time:
+ Wihtout the two indexes being created, a parallel sequential scan will happen for the query below:
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
- QUERY PLAN
--------------------------------------------------------------------&zwsp;-----------------------------------------
- Seq Scan on tbloom (cost=0.00..213694.08 rows=1 width=24) (actual time=1445.438..1445.438 rows=0 loops=1)
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
+ Seq Scan on tbloom (cost=0.00..214.00 rows=1 width=24) (actual time=2.729..2.731 rows=0 loops=1)
Filter: ((i2 = 898732) AND (i5 = 123451))
- Rows Removed by Filter: 10000000
- Planning time: 0.177 ms
- Execution time: 1445.473 ms
+ Rows Removed by Filter: 10000
+ Planning Time: 0.257 ms
+ Execution Time: 2.764 ms
(5 rows)
So the planner will usually select an index scan if possible.
- With a btree index, we get results like this:
+ But even with the btree index defined the result will still be a sequential scan:
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
- QUERY PLAN
--------------------------------------------------------------------&zwsp;-------------------------------------------------------------
- Index Only Scan using btreeidx on tbloom (cost=0.56..298311.96 rows=1 width=24) (actual time=445.709..445.709 rows=0 loops=1)
- Index Cond: ((i2 = 898732) AND (i5 = 123451))
- Heap Fetches: 0
- Planning time: 0.193 ms
- Execution time: 445.770 ms
-(5 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
+ Gather (cost=1000.00..127220.00 rows=250 width=24) (actual time=2191.907..2271.154 rows=0 loops=1)
+ Workers Planned: 2
+ Workers Launched: 2
+ -> Parallel Seq Scan on tbloom (cost=0.00..126195.00 rows=104 width=24) (actual time=1591.546..1591.547 rows=0 loops=3)
+ Filter: ((i2 = 898732) AND (i5 = 123451))
+ Rows Removed by Filter: 3333333
+ Planning Time: 31.985 ms
+ JIT:
+ Functions: 6
+ Options: Inlining false, Optimization false, Expressions true, Deforming true
+ Timing: Generation 468.814 ms, Inlining 0.000 ms, Optimization 67.622 ms, Emission 97.406 ms, Total 633.842 ms
+ Execution Time: 2745.745 ms
+(12 rows)
- Bloom is better than btree in handling this type of search:
+ Having the bloom index defined on the table is better than btree in handling this type of search:
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
- QUERY PLAN
--------------------------------------------------------------------&zwsp;--------------------------------------------------------
- Bitmap Heap Scan on tbloom (cost=178435.39..178439.41 rows=1 width=24) (actual time=76.698..76.698 rows=0 loops=1)
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on tbloom (cost=184.00..188.02 rows=1 width=24) (actual time=0.212..0.215 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
- Rows Removed by Index Recheck: 2439
- Heap Blocks: exact=2408
- -> Bitmap Index Scan on bloomidx (cost=0.00..178435.39 rows=1 width=0) (actual time=72.455..72.455 rows=2439 loops=1)
+ Rows Removed by Index Recheck: 3
+ Heap Blocks: exact=3
+ -> Bitmap Index Scan on bloomidx (cost=0.00..184.00 rows=1 width=0) (actual time=0.135..0.136 rows=3 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
- Planning time: 0.475 ms
- Execution time: 76.778 ms
+ Planning Time: 0.466 ms
+ Execution Time: 0.272 ms
(8 rows)
- Note the relatively large number of false positives: 2439 rows were
+ Note the number of false positives: 3 rows were
selected to be visited in the heap, but none actually matched the
query. We could reduce that by specifying a larger signature length.
- In this example, creating the index with length=200
- reduced the number of false positives to 55; but it doubled the index size
- (to 306 MB) and ended up being slower for this query (125 ms overall).
@@ -188,24 +192,44 @@ CREATE INDEX
Then the planner will choose something like this:
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
- QUERY PLAN
--------------------------------------------------------------------&zwsp;-----------------------------------------------------------
- Bitmap Heap Scan on tbloom (cost=9.29..13.30 rows=1 width=24) (actual time=0.148..0.148 rows=0 loops=1)
- Recheck Cond: ((i5 = 123451) AND (i2 = 898732))
- -> BitmapAnd (cost=9.29..9.29 rows=1 width=0) (actual time=0.145..0.145 rows=0 loops=1)
- -> Bitmap Index Scan on tbloom_i5_idx (cost=0.00..4.52 rows=11 width=0) (actual time=0.089..0.089 rows=10 loops=1)
- Index Cond: (i5 = 123451)
- -> Bitmap Index Scan on tbloom_i2_idx (cost=0.00..4.52 rows=11 width=0) (actual time=0.048..0.048 rows=8 loops=1)
- Index Cond: (i2 = 898732)
- Planning time: 2.049 ms
- Execution time: 0.280 ms
-(9 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
+ Index Scan using tbloom_i2_idx on tbloom (cost=0.29..8.30 rows=1 width=24) (actual time=0.051..0.052 rows=0 loops=1)
+ Index Cond: (i2 = 898732)
+ Filter: (i5 = 123451)
+ Planning Time: 0.749 ms
+ Execution Time: 0.152 ms
+(5 rows)
Although this query runs much faster than with either of the single
indexes, we pay a large penalty in index size. Each of the single-column
- btree indexes occupies 214 MB, so the total space needed is over 1.2GB,
+ btree indexes occupies 240 kB, so the total space needed is over 1.2MB,
more than 8 times the space used by the bloom index.
+
+ The more rows the base tables contains, the more likely it is that parallel
+ execution will kick in. Replaying the example with 10000000 instead
+ of 100000 rows will show the effect:
+
+# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
+ Gather (cost=1000.00..127220.00 rows=250 width=24) (actual time=2396.384..2494.623 rows=0 loops=1)
+ Workers Planned: 2
+ Workers Launched: 2
+ -> Parallel Seq Scan on tbloom (cost=0.00..126195.00 rows=104 width=24) (actual time=2116.296..2116.297 rows=0 loops=3)
+ Filter: ((i2 = 898732) AND (i5 = 123451))
+ Rows Removed by Filter: 3333333
+ Planning Time: 0.852 ms
+ JIT:
+ Functions: 6
+ Options: Inlining false, Optimization false, Expressions true, Deforming true
+ Timing: Generation 62.110 ms, Inlining 0.000 ms, Optimization 116.191 ms, Emission 104.750 ms, Total 283.051 ms
+ Execution Time: 2558.945 ms
+(12 rows)
+
+ In this case the bloom index as well as the btree index do not help for this query.
+