Date: Tue, 5 Nov 2019 15:02:51 -0500
From: Bruce Momjian <bruce@momjian.us>
To: dwe@dbi-services.com, pgsql-docs@lists.postgresql.org
Subject: Re: Instead of using the bloom index, a parallel sequencial scan is
 used with this example
Message-ID: <20191105200251.GA32473@momjian.us>
References: <157193022667.1049.11617112818811329354@wrigleys.postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="d6Gm4EdcadzBjdND"
Content-Disposition: inline
In-Reply-To: <157193022667.1049.11617112818811329354@wrigleys.postgresql.org>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--d6Gm4EdcadzBjdND
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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:

	SELECT version();
	
	SELECT name, current_setting(name), source
	FROM pg_settings
	WHERE source NOT IN ('default', 'override');

My guess is that you have some non-default setting that is causing
bloomidx not to be used.

-- 
  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 +

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

CREATE TEMPORARY TABLE tbloom AS=0ASELECT=0A     (random() * 1000000)::int =
as i1,=0A     (random() * 1000000)::int as i2,=0A     (random() * 1000000):=
:int as i3,=0A     (random() * 1000000)::int as i4,=0A     (random() * 1000=
000)::int as i5,=0A     (random() * 1000000)::int as i6=0AFROM generate_ser=
ies(1,10000000);=0A=0ACREATE EXTENSION bloom;=0A=0ACREATE INDEX bloomidx ON=
 tbloom USING bloom (i1, i2, i3, i4, i5, i6);=0ACREATE index btreeidx ON tb=
loom (i1, i2, i3, i4, i5, i6);=0A=0AEXPLAIN ANALYZE SELECT * FROM tbloom WH=
ERE i2 =3D 898732 AND i5 =3D 123451;=0AANALYZE tbloom;=0AEXPLAIN ANALYZE SE=
LECT * FROM tbloom WHERE i2 =3D 898732 AND i5 =3D 123451;=0A=0A
--d6Gm4EdcadzBjdND
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="out.txt"

SELECT 10000000
CREATE INDEX
CREATE INDEX
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbloom  (cost=178436.06..179392.83 rows=250 width=24) (actual time=98.114..98.114 rows=0 loops=1)
   Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
   Rows Removed by Index Recheck: 2375
   Heap Blocks: exact=2317
   ->  Bitmap Index Scan on bloomidx  (cost=0.00..178436.00 rows=250 width=0) (actual time=86.796..86.797 rows=2375 loops=1)
         Index Cond: ((i2 = 898732) AND (i5 = 123451))
 Planning time: 0.326 ms
 Execution time: 98.156 ms
(8 rows)

ANALYZE
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbloom  (cost=178434.71..178438.73 rows=1 width=24) (actual time=104.982..104.982 rows=0 loops=1)
   Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
   Rows Removed by Index Recheck: 2375
   Heap Blocks: exact=2317
   ->  Bitmap Index Scan on bloomidx  (cost=0.00..178434.71 rows=1 width=0) (actual time=93.657..93.657 rows=2375 loops=1)
         Index Cond: ((i2 = 898732) AND (i5 = 123451))
 Planning time: 0.215 ms
 Execution time: 105.018 ms
(8 rows)


--d6Gm4EdcadzBjdND--