Re: Heap WARM Tuples - Design Draft

On Mon, Aug 8, 2016 at 2:52 PM, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:
>>
>> My guess is we would need one bit to mark a WARM chain, and perhaps
>> reuse obsolete pre-9.0 HEAP_MOVED_OFF to indicate increment-only or
>> decrement-only.
>
>
> I am not convinced that the checking for increment/decrement adds a lot of
> value. Sure, we might be able to address some typical work load, but is that
> really a common use case? Instead, what I am looking at storing a bitmap
> which shows us which table columns have changed so far in the WARM chain. We
> only have limited bits, so we can track only limited columns. This will help
> the cases where different columns are updated, but not so much if the same
> column is updated repeatedly.
>
> What will help, and something I haven't yet applied any thoughts, is when we
> can turn WARM chains back to HOT by removing stale index entries.
>
> Some heuristics and limits on amount of work done to detect duplicate index
> entries will help too.

I think I prefer a more thorough approach.

Increment/decrement may get very complicated with custom opclasses,
for instance. A column-change bitmap won't know how to handle
funcional indexes, etc.

What I intend to try, is modify btree to allow efficient search of a
key-ctid pair, by adding the ctid to the sort order. Only inner pages
need to be changed, to include the ctid in the pointers, leaf pages
already have the ctid there, so they don't need any kind of change. A
bit in the metapage could indicate whether it's a new format or an old
one, and yes, only new indices will be able to use WARM.

But with efficient key-ctid searches, you handle all cases, and not
just a few common ones.

A lot of the work for the key-ctid search can be shared with the
update itself, so it's probably not such a big performance impact.