On Fri, Aug 5, 2016 at 09:02:39PM -0400, Bruce Momjian wrote:
> 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 understand 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.
So to summarize again:
o chains start out as HOT
o they become WARM when some indexes change and others don't
o for multiple index changes, we have to check all indexes for key/ctid matches
o for single index changes, we can fail HOT and create a new non-HOT/WARM tuple if there are too many index matches
o 99% of index checks will not find a key/ctid match
I am not sure how to optimize an index non-match.
-- 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 +
