Wrong example in the bloom documentation
Hi,
I've briefly discussed this with Bruce some time ago in [1]/messages/by-id/20191105231854.GA26542@momjian.us.
Replaying the example referenced in the documentation does not give a Bitmap Heap Scan on tbloom but a parallel seq scan with the default configuration:
-- tested on head
postgres=# CREATE TABLE tbloom AS
postgres-# SELECT
postgres-# (random() * 1000000)::int as i1,
postgres-# (random() * 1000000)::int as i2,
postgres-# (random() * 1000000)::int as i3,
postgres-# (random() * 1000000)::int as i4,
postgres-# (random() * 1000000)::int as i5,
postgres-# (random() * 1000000)::int as i6
postgres-# FROM
postgres-# generate_series(1,10000000);
SELECT 10000000
postgres=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=# 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=2134.851..2221.836 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=1770.691..1770.692 rows=0 loops=3)
Filter: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Filter: 3333333
Planning Time: 0.895 ms
JIT:
Functions: 6
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 65.512 ms, Inlining 0.000 ms, Optimization 46.328 ms, Emission 40.658 ms, Total 152.499 ms
Execution Time: 2288.056 ms
(12 rows)
As bloom was introduced in 9.6 I quickly tried with 9.6.17 and indeed for this version the example is correct:
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
(1 row)
postgres=# CREATE TABLE tbloom AS
postgres-# SELECT
postgres-# (random() * 1000000)::int as i1,
postgres-# (random() * 1000000)::int as i2,
postgres-# (random() * 1000000)::int as i3,
postgres-# (random() * 1000000)::int as i4,
postgres-# (random() * 1000000)::int as i5,
postgres-# (random() * 1000000)::int as i6
postgres-# FROM
postgres-# generate_series(1,10000000);
SELECT 10000000
postgres=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbloom (cost=178436.06..179392.83 rows=250 width=24) (actual time=2279.363..2279.363 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Index Recheck: 2329
Heap Blocks: exact=2288
-> Bitmap Index Scan on bloomidx (cost=0.00..178436.00 rows=250 width=0) (actual time=994.406..994.406 rows=2329 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Planning time: 282.059 ms
Execution time: 2286.138 ms
(8 rows)
The reason is that parallel execution is disabled by default in 9.6, and if that is turned on the plan changes there as well:
postgres=# set max_parallel_workers_per_gather = 2;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..127194.29 rows=1 width=24) (actual time=1148.047..1148.206 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbloom (cost=0.00..126194.19 rows=1 width=24) (actual time=1039.501..1039.501 rows=0 loops=3)
Filter: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Filter: 3333333
Planning time: 0.580 ms
Execution time: 1148.247 ms
(8 rows)
Starting with PostgreSQL 10 the example in the documentation is therefore wrong. Attached a proposal to fix this. The new example starts with 100x reduced rows (as suggested by Bruce in [1]/messages/by-id/20191105231854.GA26542@momjian.us and adds a note that the behavior changes as soon as parallel execution is cheaper than the index access.
Thoughts?
Regards
Daniel
Attachments:
bloom-doc-fix-v01.patchtext/x-patch; name=bloom-doc-fix-v01.patchDownload
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)
</programlisting>
<para>
- 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:
<programlisting>
=# 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)
</programlisting>
</para>
<para>
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:
<programlisting>
=# 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)
</programlisting>
</para>
<para>
- 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:
<programlisting>
=# 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)
</programlisting>
- 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 <literal>length=200</literal>
- 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).
</para>
<para>
@@ -188,24 +192,44 @@ CREATE INDEX
Then the planner will choose something like this:
<programlisting>
=# 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)
</programlisting>
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.
</para>
+ <para>
+ 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:
+<programlisting>
+# 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)
+</programlisting>
+ In this case the bloom index as well as the btree index do not help for this query.
+ </para>
</sect2>
<sect2>
Hi,
any thoughts on this?
Regards
Daniel
________________________________
From: Daniel Westermann (DWE)
Sent: Sunday, September 27, 2020 17:58
To: Pg Docs <pgsql-docs@lists.postgresql.org>
Subject: Wrong example in the bloom documentation
Hi,
I've briefly discussed this with Bruce some time ago in [1]/messages/by-id/20191105231854.GA26542@momjian.us.
Replaying the example referenced in the documentation does not give a Bitmap Heap Scan on tbloom but a parallel seq scan with the default configuration:
-- tested on head
postgres=# CREATE TABLE tbloom AS
postgres-# SELECT
postgres-# (random() * 1000000)::int as i1,
postgres-# (random() * 1000000)::int as i2,
postgres-# (random() * 1000000)::int as i3,
postgres-# (random() * 1000000)::int as i4,
postgres-# (random() * 1000000)::int as i5,
postgres-# (random() * 1000000)::int as i6
postgres-# FROM
postgres-# generate_series(1,10000000);
SELECT 10000000
postgres=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=# 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=2134.851..2221.836 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=1770.691..1770.692 rows=0 loops=3)
Filter: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Filter: 3333333
Planning Time: 0.895 ms
JIT:
Functions: 6
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 65.512 ms, Inlining 0.000 ms, Optimization 46.328 ms, Emission 40.658 ms, Total 152.499 ms
Execution Time: 2288.056 ms
(12 rows)
As bloom was introduced in 9.6 I quickly tried with 9.6.17 and indeed for this version the example is correct:
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
(1 row)
postgres=# CREATE TABLE tbloom AS
postgres-# SELECT
postgres-# (random() * 1000000)::int as i1,
postgres-# (random() * 1000000)::int as i2,
postgres-# (random() * 1000000)::int as i3,
postgres-# (random() * 1000000)::int as i4,
postgres-# (random() * 1000000)::int as i5,
postgres-# (random() * 1000000)::int as i6
postgres-# FROM
postgres-# generate_series(1,10000000);
SELECT 10000000
postgres=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbloom (cost=178436.06..179392.83 rows=250 width=24) (actual time=2279.363..2279.363 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Index Recheck: 2329
Heap Blocks: exact=2288
-> Bitmap Index Scan on bloomidx (cost=0.00..178436.00 rows=250 width=0) (actual time=994.406..994.406 rows=2329 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Planning time: 282.059 ms
Execution time: 2286.138 ms
(8 rows)
The reason is that parallel execution is disabled by default in 9.6, and if that is turned on the plan changes there as well:
postgres=# set max_parallel_workers_per_gather = 2;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..127194.29 rows=1 width=24) (actual time=1148.047..1148.206 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbloom (cost=0.00..126194.19 rows=1 width=24) (actual time=1039.501..1039.501 rows=0 loops=3)
Filter: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Filter: 3333333
Planning time: 0.580 ms
Execution time: 1148.247 ms
(8 rows)
Starting with PostgreSQL 10 the example in the documentation is therefore wrong. Attached a proposal to fix this. The new example starts with 100x reduced rows (as suggested by Bruce in [1]/messages/by-id/20191105231854.GA26542@momjian.us and adds a note that the behavior changes as soon as parallel execution is cheaper than the index access.
Thoughts?
Regards
Daniel
Hi,
as this does not get any attention on the docs-list, once again here.
Any thoughts on this?
Regards
Daniel
From: Daniel Westermann (DWE)
Sent: Sunday, September 27, 2020 17:58
To: Pg Docs <pgsql-docs@lists.postgresql.org>
Subject: Wrong example in the bloom documentation
Hi,
I've briefly discussed this with Bruce some time ago in [1]/messages/by-id/20191105231854.GA26542@momjian.us.
Replaying the example referenced in the documentation does not give a Bitmap Heap Scan on tbloom but a parallel seq scan with the default configuration:
-- tested on head
postgres=# CREATE TABLE tbloom AS
postgres-# SELECT
postgres-# (random() * 1000000)::int as i1,
postgres-# (random() * 1000000)::int as i2,
postgres-# (random() * 1000000)::int as i3,
postgres-# (random() * 1000000)::int as i4,
postgres-# (random() * 1000000)::int as i5,
postgres-# (random() * 1000000)::int as i6
postgres-# FROM
postgres-# generate_series(1,10000000);
SELECT 10000000
postgres=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=# 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=2134.851..2221.836 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=1770.691..1770.692 rows=0 loops=3)
Filter: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Filter: 3333333
Planning Time: 0.895 ms
JIT:
Functions: 6
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 65.512 ms, Inlining 0.000 ms, Optimization 46.328 ms, Emission 40.658 ms, Total 152.499 ms
Execution Time: 2288.056 ms
(12 rows)
As bloom was introduced in 9.6 I quickly tried with 9.6.17 and indeed for this version the example is correct:
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
(1 row)
postgres=# CREATE TABLE tbloom AS
postgres-# SELECT
postgres-# (random() * 1000000)::int as i1,
postgres-# (random() * 1000000)::int as i2,
postgres-# (random() * 1000000)::int as i3,
postgres-# (random() * 1000000)::int as i4,
postgres-# (random() * 1000000)::int as i5,
postgres-# (random() * 1000000)::int as i6
postgres-# FROM
postgres-# generate_series(1,10000000);
SELECT 10000000
postgres=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbloom (cost=178436.06..179392.83 rows=250 width=24) (actual time=2279.363..2279.363 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Index Recheck: 2329
Heap Blocks: exact=2288
-> Bitmap Index Scan on bloomidx (cost=0.00..178436.00 rows=250 width=0) (actual time=994.406..994.406 rows=2329 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Planning time: 282.059 ms
Execution time: 2286.138 ms
(8 rows)
The reason is that parallel execution is disabled by default in 9.6, and if that is turned on the plan changes there as well:
postgres=# set max_parallel_workers_per_gather = 2;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..127194.29 rows=1 width=24) (actual time=1148.047..1148.206 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbloom (cost=0.00..126194.19 rows=1 width=24) (actual time=1039.501..1039.501 rows=0 loops=3)
Filter: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Filter: 3333333
Planning time: 0.580 ms
Execution time: 1148.247 ms
(8 rows)
Starting with PostgreSQL 10 the example in the documentation is therefore wrong. Attached a proposal to fix this. The new example starts with 100x reduced rows (as suggested by Bruce in [1]/messages/by-id/20191105231854.GA26542@momjian.us and adds a note that the behavior changes as soon as parallel execution is cheaper than the index access.
Thoughts?
Regards
Daniel
Attachments:
bloom-doc-fix-v01.patchtext/x-patch; name=bloom-doc-fix-v01.patchDownload
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)
</programlisting>
<para>
- 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:
<programlisting>
=# 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)
</programlisting>
</para>
<para>
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:
<programlisting>
=# 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)
</programlisting>
</para>
<para>
- 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:
<programlisting>
=# 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)
</programlisting>
- 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 <literal>length=200</literal>
- 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).
</para>
<para>
@@ -188,24 +192,44 @@ CREATE INDEX
Then the planner will choose something like this:
<programlisting>
=# 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)
</programlisting>
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.
</para>
+ <para>
+ 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:
+<programlisting>
+# 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)
+</programlisting>
+ In this case the bloom index as well as the btree index do not help for this query.
+ </para>
</sect2>
<sect2>
On Thu, Oct 8, 2020 at 06:34:32AM +0000, Daniel Westermann (DWE) wrote:
Hi,
as this does not get any attention on the docs-list, once again here.
Any thoughts on this?
I was hoping someone more experienced with this would comment, but
seeing none, I will apply it in a day or two to all supported versions?
Have you tested this output back to 9.5?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Hi Bruce,
On Thu, Oct 8, 2020 at 06:34:32AM +0000, Daniel Westermann (DWE) wrote:
Hi,
as this does not get any attention on the docs-list, once again here.
Any thoughts on this?
I was hoping someone more experienced with this would comment, but
seeing none, I will apply it in a day or two to all supported versions?
Have you tested this output back to 9.5?
I hoped that as well. No, I tested down to 9.6 because the change happened in 10.
Regards
Daniel
On Thu, Oct 8, 2020 at 06:12:47PM +0000, Daniel Westermann (DWE) wrote:
Hi Bruce,
On Thu, Oct 8, 2020 at 06:34:32AM +0000, Daniel Westermann (DWE) wrote:
Hi,
as this does not get any attention on the docs-list, once again here.
Any thoughts on this?I was hoping someone more experienced with this would comment, but
seeing none, I will apply it in a day or two to all supported versions?
Have you tested this output back to 9.5?I hoped that as well. No, I tested down to 9.6 because the change happened in
10.
OK, so the patch should be applied only to PG 10 and later?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:
I was hoping someone more experienced with this would comment, but
seeing none, I will apply it in a day or two to all supported versions?
Have you tested this output back to 9.5?
I hoped that as well. No, I tested down to 9.6 because the change happened in 10.
The patch assumes that parallel query is enabled, which is not true by
default before v10, so it should certainly not be applied before v10
(at least not without significant revisions).
regards, tom lane
On Thu, Oct 8, 2020 at 03:43:32PM -0400, Tom Lane wrote:
"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:
I was hoping someone more experienced with this would comment, but
seeing none, I will apply it in a day or two to all supported versions?
Have you tested this output back to 9.5?I hoped that as well. No, I tested down to 9.6 because the change happened in 10.
The patch assumes that parallel query is enabled, which is not true by
default before v10, so it should certainly not be applied before v10
(at least not without significant revisions).
I think we should just go for simple application cases for this.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Hi Bruce, Tom,
On Thu, Oct 8, 2020 at 03:43:32PM -0400, Tom Lane wrote:
"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:
I was hoping someone more experienced with this would comment, but
seeing none, I will apply it in a day or two to all supported versions?
Have you tested this output back to 9.5?I hoped that as well. No, I tested down to 9.6 because the change happened in 10.
The patch assumes that parallel query is enabled, which is not true by
default before v10, so it should certainly not be applied before v10
(at least not without significant revisions).
Yes, the behavior change was in 10. Before 10 the example is fine, I would not apply that to any prior version, otherwise the whole example needs to be rewritten.
Regards
Daniel
On Fri, Oct 9, 2020 at 05:44:57AM +0000, Daniel Westermann (DWE) wrote:
Hi Bruce, Tom,
On Thu, Oct� 8, 2020 at 03:43:32PM -0400, Tom Lane wrote:
"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:
I was hoping someone more experienced with this would comment, but
seeing none, I will apply it in a day or two to all supported versions?
Have you tested this output back to 9.5?I hoped that as well. No, I tested down to 9.6 because the change happened in 10.
The patch assumes that parallel query is enabled, which is not true by
default before v10, so it should certainly not be applied before v10
(at least not without significant revisions).Yes, the behavior change was in 10. Before 10 the example is fine, I would not apply that to any prior version, otherwise the whole example needs to be rewritten.
Agreed.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
On Fri, Oct 9, 2020 at 11:08:32AM -0400, Bruce Momjian wrote:
On Fri, Oct 9, 2020 at 05:44:57AM +0000, Daniel Westermann (DWE) wrote:
Hi Bruce, Tom,
On Thu, Oct� 8, 2020 at 03:43:32PM -0400, Tom Lane wrote:
"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:
I was hoping someone more experienced with this would comment, but
seeing none, I will apply it in a day or two to all supported versions?
Have you tested this output back to 9.5?I hoped that as well. No, I tested down to 9.6 because the change happened in 10.
The patch assumes that parallel query is enabled, which is not true by
default before v10, so it should certainly not be applied before v10
(at least not without significant revisions).Yes, the behavior change was in 10. Before 10 the example is fine, I would not apply that to any prior version, otherwise the whole example needs to be rewritten.
Agreed.
This is not applying to PG 12 or earlier because the patch mentions JIT,
which was only mentioned in the PG bloom docs in PG 13+.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
On Fri, Oct 9, 2020 at 11:08:32AM -0400, Bruce Momjian wrote:
On Fri, Oct 9, 2020 at 05:44:57AM +0000, Daniel Westermann (DWE) wrote:
Hi Bruce, Tom,
On Thu, Oct 8, 2020 at 03:43:32PM -0400, Tom Lane wrote:
"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:
I was hoping someone more experienced with this would comment, but
seeing none, I will apply it in a day or two to all supported versions?
Have you tested this output back to 9.5?I hoped that as well. No, I tested down to 9.6 because the change happened in 10.
The patch assumes that parallel query is enabled, which is not true by
default before v10, so it should certainly not be applied before v10
(at least not without significant revisions).Yes, the behavior change was in 10. Before 10 the example is fine, I would not apply that to any prior version, otherwise the whole example needs to be rewritten.
Agreed.
This is not applying to PG 12 or earlier because the patch mentions JIT,
which was only mentioned in the PG bloom docs in PG 13+.
Does that mean we need separate patches for each release starting with 10?
As I am not frequently writing patches, I would need some help here.
Regards
Daniel
On Sat, Oct 17, 2020 at 01:50:26PM +0000, Daniel Westermann (DWE) wrote:
On Fri, Oct� 9, 2020 at 11:08:32AM -0400, Bruce Momjian wrote:
This is not applying to PG 12 or earlier because the patch mentions JIT,
which was only mentioned in the PG bloom docs in PG 13+.Does that mean we need separate patches for each release starting with 10?
As I am not frequently writing patches, I would need some help here.
I can regenerate the output for older versions using your patch.
However, I am confused about the parallelism you are seeing. Your patch
shows:
Without the two indexes being created, a parallel sequential scan will happen for the query below:
-------------------
<programlisting>
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
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: 10000
Planning Time: 0.257 ms
Execution Time: 2.764 ms
(5 rows)
However, I don't see any parallelism in this output. Also, I don't see
any parallelism once the indexes are created. What PG version is this?
and what config settings did you use? Thanks.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
On Mon, Oct 26, 2020 at 05:04:09PM -0400, Bruce Momjian wrote:
On Sat, Oct 17, 2020 at 01:50:26PM +0000, Daniel Westermann (DWE) wrote:
On Fri, Oct� 9, 2020 at 11:08:32AM -0400, Bruce Momjian wrote:
This is not applying to PG 12 or earlier because the patch mentions JIT,
which was only mentioned in the PG bloom docs in PG 13+.Does that mean we need separate patches for each release starting with 10?
As I am not frequently writing patches, I would need some help here.I can regenerate the output for older versions using your patch.
However, I am confused about the parallelism you are seeing. Your patch
shows:Without the two indexes being created, a parallel sequential scan will happen for the query below:
-------------------
<programlisting>
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
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: 10000
Planning Time: 0.257 ms
Execution Time: 2.764 ms
(5 rows)However, I don't see any parallelism in this output. Also, I don't see
any parallelism once the indexes are created. What PG version is this?
and what config settings did you use? Thanks.
I figured it out --- you have to use the larger generate_series value to
get the parallel output. I have adjusted all the docs back to 9.6 to
show accurate output for that version, and simplified the query
ordering --- patch to master attached. The other releases are similar.
Daniel, please let me know if I have left out any details.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Attachments:
master.difftext/x-diff; charset=us-asciiDownload
diff --git a/doc/src/sgml/bloom.sgml b/doc/src/sgml/bloom.sgml
new file mode 100644
index 285b67b..d1cf9ac
*** a/doc/src/sgml/bloom.sgml
--- b/doc/src/sgml/bloom.sgml
*************** CREATE INDEX bloomidx ON tbloom USING bl
*** 110,184 ****
FROM
generate_series(1,10000000);
SELECT 10000000
- =# 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
- ----------------
- 153 MB
- (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
- ----------------
- 387 MB
- (1 row)
</programlisting>
<para>
A sequential scan over this large table takes a long time:
<programlisting>
=# 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)
Filter: ((i2 = 898732) AND (i5 = 123451))
! Rows Removed by Filter: 10000000
! Planning time: 0.177 ms
! Execution time: 1445.473 ms
(5 rows)
</programlisting>
</para>
<para>
! So the planner will usually select an index scan if possible.
! With a btree index, we get results like this:
<programlisting>
=# 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)
</programlisting>
</para>
<para>
! Bloom is better than btree in handling this type of search:
<programlisting>
=# 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)
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)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
! Planning time: 0.475 ms
! Execution time: 76.778 ms
(8 rows)
</programlisting>
- Note the relatively large number of false positives: 2439 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 <literal>length=200</literal>
- 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).
</para>
<para>
--- 110,179 ----
FROM
generate_series(1,10000000);
SELECT 10000000
</programlisting>
<para>
A sequential scan over this large table takes a long time:
<programlisting>
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
! QUERY PLAN
! -------------------------------------------------------------------&zwsp;-----------------------------------
! Seq Scan on tbloom (cost=0.00..2137.14 rows=3 width=24) (actual time=16.971..16.971 rows=0 loops=1)
Filter: ((i2 = 898732) AND (i5 = 123451))
! Rows Removed by Filter: 100000
! Planning Time: 0.346 ms
! Execution Time: 16.988 ms
(5 rows)
</programlisting>
</para>
<para>
! Even with the btree index defined the result will still be a
! sequential scan:
<programlisting>
+ =# 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
! -------------------------------------------------------------------&zwsp;-----------------------------------
! Seq Scan on tbloom (cost=0.00..2137.00 rows=2 width=24) (actual time=12.805..12.805 rows=0 loops=1)
! Filter: ((i2 = 898732) AND (i5 = 123451))
! Rows Removed by Filter: 100000
! Planning Time: 0.138 ms
! Execution Time: 12.817 ms
(5 rows)
</programlisting>
</para>
<para>
! Having the bloom index defined on the table is better than btree in
! handling this type of search:
<programlisting>
+ =# 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
! -------------------------------------------------------------------&zwsp;--------------------------------------------------
! Bitmap Heap Scan on tbloom (cost=1792.00..1799.69 rows=2 width=24) (actual time=0.388..0.388 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
! Rows Removed by Index Recheck: 29
! Heap Blocks: exact=28
! -> Bitmap Index Scan on bloomidx (cost=0.00..1792.00 rows=2 width=0) (actual time=0.356..0.356 rows=29 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
! Planning Time: 0.099 ms
! Execution Time: 0.408 ms
(8 rows)
</programlisting>
</para>
<para>
*************** CREATE INDEX
*** 187,210 ****
A better strategy for btree is to create a separate index on each column.
Then the planner will choose something like this:
<programlisting>
=# 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)
</programlisting>
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,
! more than 8 times the space used by the bloom index.
</para>
</sect2>
--- 182,217 ----
A better strategy for btree is to create a separate index on each column.
Then the planner will choose something like this:
<programlisting>
+ =# 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
! -------------------------------------------------------------------&zwsp;--------------------------------------------------------
! Bitmap Heap Scan on tbloom (cost=24.34..32.03 rows=2 width=24) (actual time=0.028..0.029 rows=0 loops=1)
Recheck Cond: ((i5 = 123451) AND (i2 = 898732))
! -> BitmapAnd (cost=24.34..24.34 rows=2 width=0) (actual time=0.027..0.027 rows=0 loops=1)
! -> Bitmap Index Scan on btreeidx5 (cost=0.00..12.04 rows=500 width=0) (actual time=0.026..0.026 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.491 ms
! Execution Time: 0.055 ms
(9 rows)
</programlisting>
Although this query runs much faster than with either of the single
! indexes, we pay a penalty in index size. Each of the single-column
! btree indexes occupies 2 MB, so the total space needed is 12 MB,
! eight times the space used by the bloom index.
</para>
</sect2>
I figured it out --- you have to use the larger generate_series value to
get the parallel output. I have adjusted all the docs back to 9.6 to
show accurate output for that version, and simplified the query
ordering --- patch to master attached. The other releases are similar.
Daniel, please let me know if I have left out any details.
Thanks for your support on this. Looks good to me.
Regards
Daniel