Thread: Bloom index cost model seems to be wrong

I stumbled upon this question:

    https://dba.stackexchange.com/questions/229413

in a nutshell: the bloom index is not used with the example from the manual. 

The bloom index is only used if either Seq Scan is disabled or if the random_page_cost is set to 1 (anything about 1
triggersa Seq Scan on my Windows laptop). 
 

If parallel execution is disabled, then the bloom index is only used if the random_page_cost is lower than 4. 

This does not use the index:

  set random_page_cost = 4; 
  set max_parallel_workers_per_gather=0;
  explain (analyze, buffers) 
  select * 
  from tbloom 
  where i2 = 898732 
    and i5 = 123451;

This uses the bloom index:

  set random_page_cost = 3.5; 
  set max_parallel_workers_per_gather=0;
  explain (analyze, buffers) 
  select * 
  from tbloom 
  where i2 = 898732 
    and i5 = 123451;

And this uses the index also: 

  set random_page_cost = 1; 
  explain (analyze, buffers) 
  select * 
  from tbloom 
  where i2 = 898732 
    and i5 = 123451;

This is the plan with when the index is used (either through "enable_seqscan = off" or "random_page_cost = 1")

Bitmap Heap Scan on tbloom  (cost=138436.69..138440.70 rows=1 width=24) (actual time=42.444..42.444 rows=0 loops=1)

 
  Recheck Cond: ((i2 = 898732) AND (i5 = 123451))

 
  Rows Removed by Index Recheck: 2400

 
  Heap Blocks: exact=2365

 
  Buffers: shared hit=21973

 
  ->  Bitmap Index Scan on bloomidx  (cost=0.00..138436.69 rows=1 width=0) (actual time=40.756..40.756 rows=2400
loops=1)
        Index Cond: ((i2 = 898732) AND (i5 = 123451))

 
        Buffers: shared hit=19608

 
Planning Time: 0.075 ms

 
Execution Time: 42.531 ms

 

And this is the plan when everything left at default settings:

Seq Scan on tbloom  (cost=0.00..133695.80 rows=1 width=24) (actual time=1220.116..1220.116 rows=0 loops=1)
  Filter: ((i2 = 898732) AND (i5 = 123451))                                                               
  Rows Removed by Filter: 10000000                                                                        
  Buffers: shared hit=4697 read=58998                                                                     
  I/O Timings: read=354.670                                                                               
Planning Time: 0.075 ms                                                                                   
Execution Time: 1220.144 ms                                                                               

Can this be considered a bug in the cost model of the bloom index implementation? 
Or is it expected that this is only used if random access is really cheap? 

Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:
> The bloom index is only used if either Seq Scan is disabled or if the random_page_cost is set to 1 (anything about 1
triggersa Seq Scan on my Windows laptop).  

Hm.  blcostestimate is using the default cost calculation, except for

    /* We have to visit all index tuples anyway */
    costs.numIndexTuples = index->tuples;

which essentially tells genericcostestimate to assume that every index
tuple will be visited.  This obviously is going to increase the cost
estimate; maybe there's something wrong with that?

I notice that the bloom regression test script is only testing queries
where it forces the choice of plan type, so it really doesn't prove
anything about whether the cost estimates are sane.

            regards, tom lane

Jeff Janes <jeff.janes@gmail.com> writes:
> In order for bloom (or any other users of CREATE ACCESS METHOD, if there
> are any) to have a fighting chance to do better, I think many of selfuncs.c
> currently private functions would have to be declared in some header file,
> perhaps utils/selfuncs.h.  But that then requires a cascade of other
> inclusions.  Perhaps that is why it was not done.

I'm just in the midst of refactoring that stuff, so if you have
suggestions, let's hear 'em.

It's possible that a good cost model for bloom is so far outside
genericcostestimate's ideas that trying to use it is not a good
idea anyway.

            regards, tom lane