Date: Tue, 5 Nov 2019 18:18:54 -0500
From: Bruce Momjian <bruce@momjian.us>
To: "Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com>
Cc: "pgsql-docs@lists.postgresql.org" <pgsql-docs@lists.postgresql.org>
Subject: Re: Instead of using the bloom index, a parallel sequencial scan is
 used with this example
Message-ID: <20191105231854.GA26542@momjian.us>
References: <157193022667.1049.11617112818811329354@wrigleys.postgresql.org>
 <20191105200251.GA32473@momjian.us>
 <VI1PR09MB4142FC41A11AE3FFAA52B1F9D27E0@VI1PR09MB4142.eurprd09.prod.outlook.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0F1p//8PRICkK4MW"
Content-Disposition: inline
In-Reply-To: 
 <VI1PR09MB4142FC41A11AE3FFAA52B1F9D27E0@VI1PR09MB4142.eurprd09.prod.outlook.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--0F1p//8PRICkK4MW
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Tue, Nov  5, 2019 at 08:28:57PM +0000, Daniel Westermann (DWE) wrote:
> 
> >>On Thu, Oct 24, 2019 at 03:17:06PM +0000, PG Doc comments form wrote:
> >> The following documentation comment has been logged on the website:
> >>
> >>Page: https://www.postgresql.org/docs/12/bloom.html
> >> Description:
> 
> >I cleaned up your script and created an SQL file that can be piped into
> >psql, attached.  I see the bloomidx index being used without and with
> >the ANALYZE, output attached.  I tested this on git master, and back
> >through PG 10.  Would you please run these queries and post the output:
> 
> Thanks for having a look, Bruce. You test case is not exactly the same as in
> the documentation. For you "temporary table" test case I indeed see the bloom
> index getting used. Doing the same with a normal table results in a parallel
> seq scan.

Ah, I see your point about the temporary tables.  I have retested with
permanent tables, and see the same output you do.  By reducing the
generate_series by 100x, I am able to get the desired EXPLAIN plans by
turning on/off various optimizer settings.  I am attaching my test
script and its output.

I suggestion is what I reduce generate_series by 100x and update the
EXPLAIN plans in the docs.  The docs are unclear on how these different
plans are generated, though I am not excited about adding the exact
optimizer settings to generate each plan.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--0F1p//8PRICkK4MW
Content-Type: application/x-sql
Content-Disposition: attachment; filename="bloom.sql"
Content-Transfer-Encoding: quoted-printable

DROP TABLE IF EXISTS tbloom;=0A=0ACREATE TABLE tbloom AS=0ASELECT=0A     (r=
andom() * 1000000)::int as i1,=0A     (random() * 1000000)::int as i2,=0A  =
   (random() * 1000000)::int as i3,=0A     (random() * 1000000)::int as i4,=
=0A     (random() * 1000000)::int as i5,=0A     (random() * 1000000)::int a=
s i6=0A--FROM generate_series(1,10000000);=0AFROM generate_series(1,100000)=
;=0A=0ACREATE EXTENSION bloom;=0A=0ACREATE INDEX bloomidx ON tbloom USING b=
loom (i1, i2, i3, i4, i5, i6);=0ACREATE index btreeidx ON tbloom (i1, i2, i=
3, i4, i5, i6);=0A=0AANALYZE tbloom;=0A=0ASET enable_indexscan =3D false;=
=0ASET enable_bitmapscan =3D false;=0AEXPLAIN ANALYZE SELECT * FROM tbloom =
WHERE i2 =3D 898732 AND i5 =3D 123451;=0A=0ASET enable_seqscan =3D false;=
=0ARESET enable_indexscan;=0AEXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 =
=3D 898732 AND i5 =3D 123451;=0A=0ARESET enable_bitmapscan;=0AEXPLAIN ANALY=
ZE SELECT * FROM tbloom WHERE i2 =3D 898732 AND i5 =3D 123451;=0A=0A=0A=0A
--0F1p//8PRICkK4MW
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="out.txt"

DROP TABLE
SELECT 100000
CREATE EXTENSION
CREATE INDEX
CREATE INDEX
ANALYZE
SET
SET
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Seq Scan on tbloom  (cost=0.00..2137.00 rows=1 width=24) (actual time=12.228..12.228 rows=0 loops=1)
   Filter: ((i2 = 898732) AND (i5 = 123451))
   Rows Removed by Filter: 100000
 Planning Time: 0.264 ms
 Execution Time: 12.249 ms
(5 rows)

SET
RESET
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using btreeidx on tbloom  (cost=0.42..2992.43 rows=1 width=24) (actual time=6.462..6.463 rows=0 loops=1)
   Index Cond: ((i2 = 898732) AND (i5 = 123451))
   Heap Fetches: 0
 Planning Time: 0.102 ms
 Execution Time: 6.484 ms
(5 rows)

RESET
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbloom  (cost=1792.00..1796.02 rows=1 width=24) (actual time=0.438..0.438 rows=0 loops=1)
   Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
   Rows Removed by Index Recheck: 31
   Heap Blocks: exact=29
   ->  Bitmap Index Scan on bloomidx  (cost=0.00..1792.00 rows=1 width=0) (actual time=0.394..0.394 rows=31 loops=1)
         Index Cond: ((i2 = 898732) AND (i5 = 123451))
 Planning Time: 0.070 ms
 Execution Time: 0.461 ms
(8 rows)


--0F1p//8PRICkK4MW--