Re: Why we lost Uber as a user

On 2 August 2016 at 15:27, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Aug 2, 2016 at 5:51 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> Why we need to add a record in all indexes if only the key
>> corresponding to one of indexes is updated? Basically, if the tuple
>> can fit on same page, why can't we consider it as HOT (or HPT - heap
>> partial tuple or something like that), unless it updates all the keys
>> for all the indexes. Now, we can't consider such tuple versions for
>> pruning as we do for HOT. The downside of this could be that we might
>> need to retain some of the line pointers for more time (as we won't be
>> able to reuse the line pointer till it is used in any one of the
>> indexes and those could be reused once we make next non-HOT update).
>> However, this should allow us not to update the indexes for which the
>> corresponding column in tuple is not updated. I think it is a basic
>> premise that if any index column is updated then the update will be
>> considered as non-HOT, so there is a good chance that I might be
>> missing something here.
>
> Well, I think that the biggest advantage of a HOT update is the fact
> that it enables HOT pruning. In other words, we're not primarily
> trying to minimize index traffic; we're trying to make cleanup of the
> heap cheaper. So this could certainly be done, but I'm not sure it
> would buy us enough to be worth the engineering effort involved.

(Hi, just back from leave and catching up on emails.)

The above suggested design is something I've been working on for last
few days. In my design I referred to "intermediate root" tuples. I've
got a detailed design for it and it works, yay!... but Pavan has
managed to shoot it down with some accurate observations about it
leading to an annoying accumulation of root pointers and complex logic
to remove them. So I'm not pursuing it further at this stage.

I'm writing up my conclusions around what we should do now, so should
post later today.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services