Subject: Re: Bitmap scan is undercosted?
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Justin Pryzby <pryzby@telsasoft.com>,
 pgsql-performance@lists.postgresql.org
References: <73a4936d-2814-dc08-ed0c-978f76f435b0@gmail.com>
 <20171201183427.GK18413@telsasoft.com> <20171201231104.GN18413@telsasoft.com>
 <d2b6a335-8c62-4983-3b5b-c8a75cf37d6f@gmail.com>
 <CAMkU=1wkjV3CjWE=Cksp_qSZhNbxTOcyc=dDuwhnDzVxGMu6qQ@mail.gmail.com>
 <f8edc163-7ebe-cdb8-d068-685355045f24@gmail.com>
 <CAMkU=1zGL7UZpaycNH1Zs=K-TuaJx4P5EmP2mZM3DHzy9JinHg@mail.gmail.com>
 <18188.1512258290@sss.pgh.pa.us>
 <CAMkU=1yKK0M4W+bNc31PZhH15dzPakSmpr6TH9exT8OUZvmg_w@mail.gmail.com>
 <0a0bfd5f-06cc-918b-da75-4e45c4e8ea27@gmail.com>
 <cde064b2-9600-8d47-ddb6-a239cacefc5d@gmail.com>
 <CAMkU=1zVsx-D4NVnpUpWcRehEk8GxA9zdLxe9XWCak_gyvOY4w@mail.gmail.com>
From: Vitaliy Garnashevich <vgarnashevich@gmail.com>
Message-ID: <ebcb29c6-d9ce-ee44-a1fa-a89792ebf02a@gmail.com>
Date: Sat, 24 Feb 2018 10:45:14 +0200
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101
 Thunderbird/52.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CAMkU=1zVsx-D4NVnpUpWcRehEk8GxA9zdLxe9XWCak_gyvOY4w@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

Sorry for delay with response, I had to switch to other tasks and didn't 
have time to run proper tests and write some meaningful response.

Recently,  a similar issue happened with another our database, so I 
decided to write an update.

Bitmap scan was preferred to index scan by the planner, but bitmap scan 
was running worse in practice. Here are the relevant pieces of a much 
bigger query plan:

  ->  Bitmap Heap Scan on cmdb_program_daily_usage 
cmdb_program_daily_usage_6  (cost=6707.08..6879.35 rows=32 width=20) 
(actual time=39.994..40.019 rows=12 loops=336)
        Recheck Cond: ((used_from = cmdb_ci_computer_12.id) AND 
(usage_date >= '2018-02-02'::date) AND (usage_date <= '2018-02-12'::date))
        Filter: (((NOT thin_client) OR (thin_client IS NULL)) AND 
(program_instance IS NOT NULL) AND (minutes_in_use > 0))
        Rows Removed by Filter: 69
        Heap Blocks: exact=2995
        Buffers: shared hit=563448
        ->  BitmapAnd  (cost=6707.08..6707.08 rows=154 width=0) (actual 
time=39.978..39.978 rows=0 loops=336)
              Buffers: shared hit=560453
              ->  Bitmap Index Scan on idx_fk_5317241949468942  
(cost=0.00..133.87 rows=12641 width=0) (actual time=0.373..0.373 
rows=4780 loops=336)
                    Index Cond: (used_from = cmdb_ci_computer_12.id)
                    Buffers: shared hit=5765
              ->  Bitmap Index Scan on idx_263911642415136  
(cost=0.00..6572.94 rows=504668 width=0) (actual time=40.873..40.873 
rows=540327 loops=324)
                    Index Cond: ((usage_date >= '2018-02-02'::date) AND 
(usage_date <= '2018-02-12'::date))
                    Buffers: shared hit=554688

  ->  Index Scan using idx_fk_5317241949468942 on 
cmdb_program_daily_usage cmdb_program_daily_usage_6 (cost=0.56..24322.97 
rows=35 width=20) (actual time=1.211..2.196 rows=14 loops=338)
        Index Cond: (used_from = cmdb_ci_computer_12.id)
        Filter: (((NOT thin_client) OR (thin_client IS NULL)) AND 
(program_instance IS NOT NULL) AND (minutes_in_use > 0) AND (usage_date 
 >= '2018-02-02'::date) AND (usage_date <= '2018-02-12'::date))
        Rows Removed by Filter: 4786
        Buffers: shared hit=289812

The difference in run time does not look very huge, but when it's a part 
of a loop, that could mean difference between minutes and hours.

After running some tests, here are the conclusions we've made:

- When running with cold cache, and data is being read from disk, then 
the planner estimates look adequate. Bitmap scan has better costs, and 
indeed it performs better in that case.

- When running with hot cache, and most of data is already in RAM, then 
index scan starts to outperform bitmap scan. Unfortunately the planner 
cannot account for the cache very well, and can't switch the plan. 
Because even if the planner would ever learn to account for the current 
content of shared buffers, it still can't know much about the content of 
filesystem cache.

- Tests showed that the costs are dominated by random_page_cost, but 
there is still potential to change the total plan cost, if "cpu_*" costs 
would be less distant from "*_page_cost".

- In our case the data is likely to be in cache, so we decided to change 
cost settings: seq_page_cost 1.0 -> 0.5; random_page_cost 1.1 -> 0.6

Regards,
Vitaliy