On Fri, Mar 24, 2017 at 12:25 AM, Pavan Deolasee <pavan.deolasee@gmail.com> wrote: > > > On Thu, Mar 23, 2017 at 7:53 PM, Amit Kapila <amit.kapila16@gmail.com> > > The general sense I've got > here is that we're ok to push some work in background if it helps the > real-time queries, and I kinda agree with that. >
I don't think we can define this work as "some" work, it can be a lot of work depending on the number of indexes. Also, I think for some cases it will generate maintenance work without generating benefit. For example, when there is one index on a table and there are updates for that index column.
That's a fair point. I think we can address this though. At the end of first index scan we would know how many warm pointers the index has and whether it's worth doing a second scan. For the case you mentioned, we will do a second scan just on that one index and skip on all other indexes and still achieve the same result. On the other hand, if one index receives many updates and other indexes are rarely updated then we might leave behind a few WARM chains behind and won't be able to do IOS on those pages. But given the premise that other indexes are receiving rare updates, it may not be a problem. Note: the code is not currently written that way, but it should be a fairly small change.
The other thing that we didn't talk about is that vacuum will need to track dead tuples and warm candidate chains separately which increases memory overhead. So for very large tables, and for the same amount of maintenance_work_mem, one round of vacuum will be able to clean lesser pages. We can work out more compact representation, but something not done currently.
> But this is clearly not > PG 10 material. >
I don't see much discussion about this aspect of the patch, so not sure if it is acceptable to increase the cost of vacuum. Now, I don't know if your idea of GUC's make it such that the additional cost will occur seldom and this additional pass has a minimal impact which will make it acceptable.
Yeah, I agree. I'm trying to schedule some more benchmarks, but any help is appreciated.
