On Thu, May 18, 2017 at 2:52 AM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> Most of the queries show decent improvement, however, Q14 shows
> regression at work_mem = 4MB. On analysing this case, I found that
> number of pages_fetched calculated by "Mackert and Lohman formula" is
> very high (1112817) compared to the actual unique heap pages fetched
> (293314). Therefore, while costing bitmap scan using 1112817 pages and
> 4MB of work_mem, we predicted that even after we lossify all the pages
> it can not fit into work_mem, hence bitmap scan was not selected.
You might need to adjust effective_cache_size. The Mackert and Lohman
formula isn't exactly counting unique pages fetched. It will count
the same page twice if it thinks the page will be evicted from the
cache after the first fetch and before the second one.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
