Re: Proposal: Improve bitmap costing for lossy pages

On Sun, Sep 17, 2017 at 4:34 PM, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>>
>> I have repeated one of the tests after fixing the problems pointed by
>> you but this time results are not that impressive. Seems like below
>> check was the problem in the previous patch
>>
>> if (tbm->nentries > tbm->maxentries / 2)
>> tbm->maxentries = Min(tbm->nentries, (INT_MAX - 1) / 2) * 2;
>>
>> Because we were lossifying only till tbm->nentries becomes 90% of
>> tbm->maxentries but later we had this check which will always be true
>> and tbm->maxentries will be doubled and that was the main reason of
>> huge reduction of lossy pages, basically, we started using more
>> work_mem in all the cases.
>>
>> I have taken one reading just to see the impact after fixing the
>> problem with the patch.
>>
>> Work_mem: 40 MB
>> (Lossy Pages count)
>>
>> Query head patch
>> 6 995223 733087
>> 14 337894 206824
>> 15 995417 798817
>> 20 1654016 1588498
>>
>> Still, we see a good reduction in lossy pages count. I will perform
>> the test at different work_mem and for different values of
>> TBM_FILFACTOR and share the number soon.
>
> I haven't yet completely measured the performance with executor
> lossification change, meanwhile, I have worked on some of the comments
> on optimiser change and taken the performance again, I still see good
> improvement in the performance (almost 2x for some of the queries) and
> with new method of lossy pages calculation I don't see regression in
> Q14 (now Q14 is not changing its plan).
>
> I used lossy_pages = max(0, total_pages - maxentries / 2). as
> suggesed by Alexander.
>
>
> Performance Results:
>
> Machine: Intell 56 core machine (2 NUMA node)
> work_mem: varies.
> TPCH S.F: 20
> Median of 3 runs.
>
> work_mem = 4MB
>
> Query Patch(ms) Head(ms) Change in plan
>
> 4 4686.186 5039.295 PBHS -> PSS
>
> 5 26772.192 27500.800 BHS -> SS
>
> 6 6615.916 7760.005 PBHS -> PSS
>
> 8 6370.611 12407.731 PBHS -> PSS
>
> 15 17493.564 24242.256 BHS -> SS
>
>
> work_mem = 20MB
>
> Query Patch(ms) Head(ms) Change in plan
>
> 6 6656.467 7469.961 PBHS -> PSS
>
> 8 6116.526 12300.784 PBHS -> PSS
>
> 15 17873.726 22913.421 BHS -> PSS
>
>
> work_mem = 64MB
>
> Query Patch(ms) Head(ms) Change in plan
>
> 15 14900.881 27460.093 BHS -> PBHS
>

There was some problem with the previous patch, even if the bitmap was
enough to hold all the heap pages I was calculating the lossy pages.
I have fixed that in the attached patch. I have also verified the
performance it's same as reported in the previous email.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com