Re: Instead of using the bloom index, a parallel sequencial scan is used with this example

Bruce Momjian <[email protected]> Tue, 5 Nov 2019 15:02:51 -0500
Newsgroups gmane.comp.db.postgresql.devel.documentation
Message-ID <[email protected]> 
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  <[email protected]>        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 +
bloom.sql (application/x-sql, 617 B) - not displayed
out.txt (text/plain, 1.5 KB) 
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)