Re: Heap WARM Tuples - Design Draft

On Fri, Aug 5, 2016 at 9:07 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Fri, Aug 5, 2016 at 07:51:05PM -0300, Claudio Freire wrote:
>> > 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.
>>
>> I believe the only solution is to use bitmap index scans with WARM indexes.
>
> Let me back up and explain the benefits we get from allowing HOT chains
> to be WARM:
>
> * no index entries for WARM indexes whose values don't change
> * improved pruning because the HOT/WARM chains can be longer because we
> don't have to break chains for index changes
>
> While I realize bitmap indexes would allow us to remove duplicate index
> entries, it removes one of the two benefits of WARM, and it causes every
> index read to be expensive --- I can't see how this would be a win over
> doing the index check on writes.

But the index check could be prohibitely expensive.

So we're back to bailing out?

When an update comes and we're considering WARM, we need to query the
indexes, each one, and if one index cannot quickly verify the
existence of an entry for the root tid for the key, bail out from
WARM.

That may prevent the benefits of WARM in a lot of real world cases.
You just need one less-than-unique index to disable WARM for all
updates, and IIRC those are common. You'd pay the CPU price of WARM
without any of the benefits.

I don't think I have a single table in my databases that don't exhibit
this behavior.