Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-11-12 Thread Dilip Kumar
On Sat, Nov 11, 2017 at 3:25 AM, Robert Haas wrote: > On Thu, Nov 9, 2017 at 3:55 AM, amul sul wrote: >> It took me a little while to understand this calculation. You have moved >> this >> code from tbm_create(), but I think you should move the

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-11-10 Thread Robert Haas
On Thu, Nov 9, 2017 at 3:55 AM, amul sul wrote: > It took me a little while to understand this calculation. You have moved this > code from tbm_create(), but I think you should move the following > comment as well: I made an adjustment that I hope will address your concern

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-11-09 Thread amul sul
Hi Dilip, v6 patch: 42 + /* 43 +* Estimate number of hashtable entries we can have within maxbytes. This 44 +* estimates the hash cost as sizeof(PagetableEntry). 45 +*/ 46 + nbuckets = maxbytes / 47 + (sizeof(PagetableEntry) + sizeof(Pointer) + sizeof(Pointer)); It

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-10-11 Thread Dilip Kumar
On Fri, Oct 6, 2017 at 9:21 PM, Dilip Kumar wrote: > On Fri, Oct 6, 2017 at 7:24 PM, Dilip Kumar wrote: >> On Fri, Oct 6, 2017 at 6:08 PM, Alexander Kuzmenkov >> wrote: >>> Analysis: The estimated value of the

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-10-06 Thread Dilip Kumar
On Fri, Oct 6, 2017 at 7:24 PM, Dilip Kumar wrote: > On Fri, Oct 6, 2017 at 6:08 PM, Alexander Kuzmenkov > wrote: >> >>> Analysis: The estimated value of the lossy_pages is way higher than >>> its actual value and reason is that the total_pages

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-10-06 Thread Dilip Kumar
On Fri, Oct 6, 2017 at 7:04 PM, Dilip Kumar wrote: > On Fri, Oct 6, 2017 at 6:36 PM, Robert Haas wrote: >> On Fri, Oct 6, 2017 at 2:12 AM, Dilip Kumar wrote: The performance results look good, but that's a slightly

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-10-06 Thread Dilip Kumar
On Fri, Oct 6, 2017 at 6:08 PM, Alexander Kuzmenkov wrote: > >> Analysis: The estimated value of the lossy_pages is way higher than >> its actual value and reason is that the total_pages calculated by the >> "Mackert and Lohman formula" is not correct. > > > I think

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-10-06 Thread Dilip Kumar
On Fri, Oct 6, 2017 at 6:36 PM, Robert Haas wrote: > On Fri, Oct 6, 2017 at 2:12 AM, Dilip Kumar wrote: >>> The performance results look good, but that's a slightly different >>> thing from whether the estimate is accurate. >>> >>> +nbuckets =

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-10-06 Thread Robert Haas
On Fri, Oct 6, 2017 at 2:12 AM, Dilip Kumar wrote: >> The performance results look good, but that's a slightly different >> thing from whether the estimate is accurate. >> >> +nbuckets = tbm_calculate_entires(maxbytes); >> >> entires? > > changed to > + tbm->maxentries

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-10-06 Thread Alexander Kuzmenkov
Analysis: The estimated value of the lossy_pages is way higher than its actual value and reason is that the total_pages calculated by the "Mackert and Lohman formula" is not correct. I think the problem might be that the total_pages includes cache effects and rescans. For bitmap entries we

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-10-06 Thread Dilip Kumar
On Thu, Oct 5, 2017 at 8:15 PM, Robert Haas wrote: > On Sun, Sep 17, 2017 at 7:04 AM, Dilip Kumar wrote: >> I used lossy_pages = max(0, total_pages - maxentries / 2). as >> suggesed by Alexander. > > Does that formula accurately estimate the number

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-10-05 Thread Robert Haas
On Sun, Sep 17, 2017 at 7:04 AM, Dilip Kumar wrote: > I used lossy_pages = max(0, total_pages - maxentries / 2). as > suggesed by Alexander. Does that formula accurately estimate the number of lossy pages? The performance results look good, but that's a slightly

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-09-17 Thread Dilip Kumar
On Sun, Sep 17, 2017 at 4:34 PM, Dilip Kumar 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

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-09-17 Thread Dilip Kumar
On Mon, Sep 4, 2017 at 11:18 AM, Dilip Kumar wrote: > On Thu, Aug 31, 2017 at 11:27 PM, Robert Haas 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

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-09-03 Thread Dilip Kumar
On Thu, Aug 31, 2017 at 11:27 PM, Robert Haas 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 >

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-08-31 Thread Robert Haas
On Thu, Aug 31, 2017 at 2:26 AM, Dilip Kumar wrote: > Thanks for the feedback. I will work on it. Another thought is that you probably want/need to test across a range of work_mem values. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-08-31 Thread Dilip Kumar
On Wed, Aug 30, 2017 at 2:00 AM, Robert Haas wrote: > > I suggest defining a TBM_FILLFACTOR constant instead of repeating the > value 0.9 in a bunch of places. I think it would also be good to try > to find the sweet spot for that constant. Making it bigger reduces > the

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-08-29 Thread Robert Haas
On Tue, Aug 29, 2017 at 1:08 AM, Dilip Kumar wrote: > (Time in ms) > Queryhead patch > > 6 2381914571 > 14 1351411183 > 15 49980 32400 > 20204441 188978 These are cool results, but this patch is

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-08-28 Thread Dilip Kumar
On Wed, Aug 23, 2017 at 9:45 AM, Dilip Kumar wrote: >> >> ... >> if (tbm->nentries <= tbm->maxentries / 2) >> { >> /* >> * We have made enough room. >> ... >> I think we could try higher fill factor, say, 0.9.

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-08-22 Thread Dilip Kumar
On Tue, Aug 22, 2017 at 8:40 PM, Alexander Kumenkov wrote: > Hi Dilip, > > Recently I was thinking about this too, when working on the index-only > count(*) patch for indexes supporting amgetbitmap [1]. That patch teaches > bitmap heap scan node to skip heap fetches

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-08-22 Thread Alexander Kumenkov
Hi Dilip, Recently I was thinking about this too, when working on the index-only count(*) patch for indexes supporting amgetbitmap [1]. That patch teaches bitmap heap scan node to skip heap fetches under certain conditions. Exact tidbitmap pages are a prerequisite for this, so the lossines

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-08-17 Thread Robert Haas
On Thu, Aug 17, 2017 at 12:06 AM, Dilip Kumar wrote: > I have attempted a very simple POC with this approach just to see how > many lossy pages we can save if we lossify all the pages falling in > the same chunk first, before moving to the next page. I have taken > some

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-08-16 Thread Dilip Kumar
On Wed, Jul 26, 2017 at 10:35 PM, Robert Haas wrote: > On Thu, Jun 8, 2017 at 10:44 AM, Dilip Kumar wrote: >> On Thu, May 18, 2017 at 8:07 PM, Robert Haas wrote: >> Thanks for the feedback. I haven't yet worked on optimizer

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-07-26 Thread Robert Haas
On Thu, Jun 8, 2017 at 10:44 AM, Dilip Kumar wrote: > On Thu, May 18, 2017 at 8:07 PM, Robert Haas wrote: > > Thanks for the feedback and sorry for the delayed response. > >> You might need to adjust effective_cache_size. > > You are right. But,

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-06-08 Thread Dilip Kumar
On Thu, May 18, 2017 at 8:07 PM, Robert Haas wrote: Thanks for the feedback and sorry for the delayed response. > You might need to adjust effective_cache_size. You are right. But, effective_cache_size will have the impact on the number of pages_fetched when it's used as

Re: [HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-05-18 Thread Robert Haas
On Thu, May 18, 2017 at 2:52 AM, Dilip Kumar 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

[HACKERS] Proposal: Improve bitmap costing for lossy pages

2017-05-18 Thread Dilip Kumar
I would like to propose a patch to improve the cost of bitmap heap scan that is sensitive to work_mem. Currently, in bitmap scan, we don't consider work_mem. Now, in cases when there are a lot of lossy pages bitmap scan gets selected that eventually leads to degraded performance. While