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

Bruce Momjian <[email protected]> Tue, 5 Nov 2019 18:18:54 -0500

Newsgroups	gmane.comp.db.postgresql.devel.documentation
Message-ID	<[email protected]>

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  <[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, 886 B) - not displayed

out.txt (text/plain, 1.7 KB)

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)