Re: Bitmap scan is undercosted?

Vitaliy Garnashevich <vgarnashevich@xxxxxxxxx> · Mon, 4 Dec 2017 00:11:47 +0200



    On 03/12/2017 03:27, Jeff Janes wrote:

    
          Due to that, when I disable
            bitmapscans and seqscans, I start getting slow index scans
            on the wrong index, i2 rather than i1.  I don't know why he
            doesn't see that in his example.
        
      
    When I increase effective_cache_size to 1024MB, I start getting the
    plan with the slower index i2, too.

    
    Bitmap Heap Scan on public.aaa  (cost=12600.90..23688.70
    rows=9488 width=5) (actual time=107.529..115.902 rows=9976
    loops=1)

      ->  BitmapAnd  (cost=12600.90..12600.90 rows=9488 width=0)
    (actual time=105.133..105.133 rows=0 loops=1)

            ->  Bitmap Index Scan on i1  (cost=0.00..1116.43
    rows=96000 width=0) (actual time=16.313..16.313 rows=100508 loops=1)

            ->  Bitmap Index Scan on i2  (cost=0.00..11479.47
    rows=988338 width=0) (actual time=77.950..77.950 rows=1000200
    loops=1)

    
    Index Scan using i2 on public.aaa  (cost=0.44..48227.31
    rows=9488 width=5) (actual time=0.020..285.695 rows=9976
    loops=1)

    
    Seq Scan on public.aaa  (cost=0.00..169248.54
    rows=9488 width=5) (actual time=0.024..966.469 rows=9976
    loops=1)

    
    This way the estimates and the actual time get more sense. But then
    there's the question - maybe it's i1 runs too fast, and is estimated
    incorrectly? Why that happens?

    
    Here are the complete plans with the two different kinds of index
    scans once again:

    
    Index Scan using i1 on public.aaa  (cost=0.44..66621.56 rows=10340
    width=5) (actual time=0.027..47.075 rows=9944 loops=1)

      Output: num, flag

      Index Cond: (aaa.num = 1)

      Filter: aaa.flag

      Rows Removed by Filter: 89687

      Buffers: shared hit=39949

    Planning time: 0.104 ms

    Execution time: 47.351 ms

    
    Index Scan using i2 on public.aaa  (cost=0.44..48227.31 rows=9488
    width=5) (actual time=0.020..285.695 rows=9976 loops=1)

      Output: num, flag

      Index Cond: (aaa.flag = true)

      Filter: (aaa.flag AND (aaa.num = 1))

      Rows Removed by Filter: 990224

      Buffers: shared hit=46984

    Planning time: 0.098 ms

    Execution time: 286.081 ms

    
    // The test DB was populated with: create table aaa as select
    floor(random()*100)::int num, (random()*10 < 1)::bool flag from
    generate_series(1, 10000000) id;

    
    Regards,

    Vitaliy