Re: Heap WARM Tuples - Design Draft - Mailing list pgsql-hackers

On Fri, Aug  5, 2016 at 11:30:26PM +0530, Pavan Deolasee wrote:
> On Fri, Aug 5, 2016 at 11:26 PM, Bruce Momjian <bruce@momjian.us> wrote:
> 
>     On Fri, Aug  5, 2016 at 02:43:37PM -0300, Claudio Freire wrote:
>     > But doing the WARM chain backtracking and checking for previous
>     > versions with appropriate keys should be enough to support this use
>     > case too, it just needs to be well optimized to avoid seriously
>     > impacting performance on the average case.
> 
>     Yes, that was an idea I had to --- if the in-page HOT chain already has
>     the key, we know it is already in the index and we can skip the index
>     addition.
> 
> I just don't know how would you do that without delaying/not-doing HOT chain
> prune. As soon as root and intermediate HOT tuples are gone, all information is
> lost. You may delay that, but that will defeat the whole purpose. If chains are
> not pruned in-time, you may not find any free space in the page and end up
> doing a cold update anyways. But may be I am missing something and Claudio has
> a different idea.

Yes, pruning would be a problem.  :-(

A check only needs to happen when the indexed key changes, right?  So we
are comparing the cost of adding an index key vs. the cost of trying to
find a matching key/ctid in the index.  Seems the later is cheaper, but
it is not obvious.

I think I see Claudio's idea now --- from his diagram, you can have WARM
chains span multiple HOT chains.  What he is doing is creating a new HOT
chain everytime _any_ key changes, and he knows the entire HOT chain has
idential values for all indexes.  He moves from one HOT chain to another
during an index scan by checking if the index value is the same in the
old and new HOT chain (that is the same WARM chain).

This does create more HOT chains where the root ctid cannot be removed,
but it does avoid the index key/ctid check because any entry in the HOT
chain has identical keys.  What this would not handle is when an entire
HOT chain is pruned, as the keys would be gone.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +