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

On Fri, Aug  5, 2016 at 09:21:49PM -0300, Claudio Freire wrote:
> On Fri, Aug 5, 2016 at 9:07 PM, Bruce Momjian <bruce@momjian.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.

True, but I am afraid the bitmap handling on reads will be worse.

> 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.

Yes, it seems we either find the entry fast and avoid the index
addition, or don't find it quickly and use a non-HOT, non-WARM update.
The problem is that you have to do this for multiple indexes, and if you
quickly find you need to add an entry to the first index, when you get
to the second one you can't easily bail out and go with a non-HOT,
non-WARM update.  I suppose we could bail out of a long index search if
there is only one index with a changed key.

Here's how I underestand it --- if you are looking for a key that has
only a few index entries, it will be fast to find of the key/ctid is
listed.  If the index has many index entries for the key, it will be
expensive to find if there is a matching key/ctid, but a read-only-query
index lookup for that key will be expensive too, whether you use the
bitmap scan or not.  And, effectively, if we bail out and decide to go
with a non-HOT, non-WARM update, we are making the index even bigger.

--  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 +