\set ON_ERROR_STOP on

SHOW enable_hashjoin_bloom; 

DROP TABLE IF EXISTS f, fn, d1, d2;
CREATE TEMP TABLE d1 (id int PRIMARY KEY, x int);
CREATE TEMP TABLE d2 (id int PRIMARY KEY, v int);
CREATE TEMP TABLE f  (d1_id int, d2_id int);

INSERT INTO d1 (id,x)
  SELECT i, i % 1000 FROM generate_series(1, 1E6) i;
INSERT INTO d2 (id,v)
  SELECT i, i FROM generate_series(1, 1E6) i;
INSERT INTO f (d1_id,d2_id)
  SELECT
    (random() * 9999999)::int + 1,
    (random() * 999999)::int + 1
  FROM generate_series(1, 1E7);

CREATE TABLE fn AS
  SELECT
    CASE WHEN random() < 0.5 THEN NULL ELSE d1_id END AS d1_id, d2_id FROM f;
VACUUM ANALYZE d1, d2, f, fn;
 
SET enable_hashjoin_bloom = on;
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT sum(d2.v) FROM f JOIN d2 ON f.d2_id = d2.id JOIN d1 ON f.d1_id = d1.id
WHERE d1.x = 0 AND d2.v < 9E5::integer;
 
SET enable_hashjoin_bloom = off;
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT sum(d2.v) FROM f JOIN d2 ON f.d2_id = d2.id JOIN d1 ON f.d1_id = d1.id
WHERE d1.x = 0 AND d2.v < 9E5::integer;

/*
 Aggregate  (cost=174596.47..174596.48 rows=1 width=8) (actual rows=1.00 loops=1)
   ->  Hash Join  (cost=16937.88..174591.95 rows=1810 width=4) (actual rows=899.00 loops=1)
         Hash Cond: (f.d1_id = d1.id)
         ->  Nested Loop  (cost=0.42..157649.75 rows=1810 width=8) (actual rows=207245.00 loops=1)
               ->  Seq Scan on f  (cost=0.00..156747.74 rows=2014 width=8) (actual rows=230415.00 loops=1)
                     Bloom Filter 1: keys=(d1_id) expected=0.0% checked=9999999 rejected=9769585 (97.7%)
               ->  Index Scan using d2_pkey on d2  (cost=0.42..0.45 rows=1 width=8) (actual rows=0.90 loops=230415)
                     Index Cond: (id = f.d2_id)
                     Filter: (v < 900000)
                     Rows Removed by Filter: 0
                     Index Searches: 230415
         ->  Hash  (cost=16925.00..16925.00 rows=996 width=4) (actual rows=1000.00 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 44kB
               Bloom Filter 1: bits=8192 hashes=4 memory=1kB checked=9999999 rejected=9769585
               ->  Seq Scan on d1  (cost=0.00..16925.00 rows=996 width=4) (actual rows=1000.00 loops=1)
                     Filter: (x = 0)
                     Rows Removed by Filter: 999000
 Planning Time: 0.241 ms
 Execution Time: 926.909 ms
(19 rows)

danolivo=#  
danolivo=# SET enable_hashjoin_bloom = off;
SET
danolivo=# EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
danolivo-# SELECT sum(d2.v) FROM f JOIN d2 ON f.d2_id = d2.id JOIN d1 ON f.d1_id = d1.id
danolivo-# WHERE d1.x = 0 AND d2.v < 900000;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=188341.71..188341.72 rows=1 width=8) (actual rows=1.00 loops=1)
   ->  Nested Loop  (cost=16937.88..188337.19 rows=1810 width=4) (actual rows=899.00 loops=1)
         ->  Hash Join  (cost=16937.45..187435.18 rows=2014 width=4) (actual rows=988.00 loops=1)
               Hash Cond: (f.d1_id = d1.id)
               ->  Seq Scan on f  (cost=0.00..144247.77 rows=9999977 width=8) (actual rows=10000000.00 loops=1)
               ->  Hash  (cost=16925.00..16925.00 rows=996 width=4) (actual rows=1000.00 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 44kB
                     ->  Seq Scan on d1  (cost=0.00..16925.00 rows=996 width=4) (actual rows=1000.00 loops=1)
                           Filter: (x = 0)
                           Rows Removed by Filter: 999000
         ->  Index Scan using d2_pkey on d2  (cost=0.42..0.45 rows=1 width=8) (actual rows=0.91 loops=988)
               Index Cond: (id = f.d2_id)
               Filter: (v < 900000)
               Rows Removed by Filter: 0
               Index Searches: 988
 Planning Time: 0.110 ms
 Execution Time: 346.549 ms
(17 rows)
*/