From: | Dilip Kumar <dilipbalaut(at)gmail(dot)com> |
---|---|
To: | Alexander Kumenkov <a(dot)kuzmenkov(at)postgrespro(dot)ru> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: Improve bitmap costing for lossy pages |
Date: | 2017-08-29 05:08:23 |
Message-ID: | CAFiTN-upxxpZALTD7cQREpC+8HCHYEnVu-J4e5y7jcGzHw-aYQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Aug 23, 2017 at 9:45 AM, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>>
>> ...
>> if (tbm->nentries <= tbm->maxentries / 2)
>> {
>> /*
>> * We have made enough room.
>> ...
>> I think we could try higher fill factor, say, 0.9. tbm_lossify basically
>> just continues iterating over the hashtable with not so much overhead per a
>> call, so calling it more frequently should not be a problem. On the other
>> hand, it would have to process less pages, and the bitmap would be less
>> lossy.
>>
>> I didn't benchmark the index scan per se with the 0.9 fill factor, but the
>> reduction of lossy pages was significant.
>
> I will try this and produce some performance number.
>
I have done some performance testing as suggested by Alexander (patch attached).
Performance results: I can see a significant reduction in lossy_pages
count in all the queries and also a noticeable reduction in the
execution time in some of the queries. I have tested with 2 different
work_mem. Below are the test results (lossy pages count and execution
time).
TPCH benchmark: 20 scale factor
Machine: Power 4 socket
Tested with max_parallel_worker_per_gather=0
Work_mem: 20 MB
(Lossy Pages count:)
Query head patch
4 166551 35478
5 330679 35765
6 1160339 211357
14 666897 103275
15 1160518 211544
20 1982981 405903
(Time in ms:)
Query head patch
4 14849 14093
5 76790 74486
6 25816 14327
14 16011 11093
15 51381 35326
20 211115 195501
Work_mem: 40 MB
(Lossy Pages count)
Query head patch
6 995223 195681
14 337894 75744
15 995417 195873
20 1654016 199113
(Time in ms)
Query head patch
6 23819 14571
14 13514 11183
15 49980 32400
20 204441 188978
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
Attachment | Content-Type | Size |
---|---|---|
lossify_slow.patch | application/octet-stream | 483 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrey Borodin | 2017-08-29 05:16:59 | Re: Adding hook in BufferSync for backup purposes |
Previous Message | Michael Paquier | 2017-08-29 04:26:03 | Re: [Proposal] Allow users to specify multiple tables in VACUUM commands |