On Sat, Aug 6, 2016 at 10:08:47AM +0530, Pavan Deolasee wrote: > So, we are only checking the index if the WARM chain was pruned, and we > can bail out if there is only one index changed. This is looking more > doable. > > > The duplicate tuples problem that we are focusing on, happens when an index > already has two or index tuples pointing to the same root tuple/lp. When it's > time to insert third index tuple, we must not insert a duplicate (key, CTID) > tuple. I've a design where we can track which columns (we are interested only > in the columns on which indexes use) were ever changed in the WARM chain. We > allow one change for every index column, but the second change will require a > duplicate lookup. This is still quite an improvement, the cold updates may > reduce by at least more than 50% already, but someone can argue that this does > not handle the case where same index column is repeatedly updated.
Yes, I was thinking of that too. We can use LP_REDIRECT ctid's lp_len, which is normally all zeros and has 15 unused bits, to record which of the first 15 columns have been had changed tuples pruned. A simpler idea would be to use just one bit to record if WARM tuples have been pruned, because if we prune HOT tuples, we can still traverse the chain. A more serious concern is index creation. If we already have a WARM chain and create an index, we can't assume that all indexes will have entries for change in the WARM chain. One fix would be for index creation to walk warm chains and and add index entries for all changed columns. We don't need to that now because we assume the index will only be used by new transactions that can't see the old chain. Another idea would be to look at the index xmin and determine if the index was recording tuples in the chain. (FYI, in your idea above, I don't think you can track which _indexes_ had changes because index creation will need to know information on column changes that weren't recorded at WARM chain creation time, hence I am tracking column changes, not index changes.) Another issue is that while index walking is usually fast, there are pathological cases. For example, if a column value is 80% of all rows, the optimizer would use statistics to avoid using the index for that constant, but if someone changes a WARM chain to use that value, we would need to read 80% of the index to find if the key/ctid exists, and in most cases it will not, so we have to read the entire thing. We do have the fallback to create a non-HOT, non-WARM tuple for this case for single-index changes. > If we need to find an efficient way to convert WARM chains back to HOT, which > will happen soon when the old index tuple retires, the system can attain a > stable state, not for all but many use cases. I don't see how that is possible, except perhaps by vacuum. OK, now I am less certain. -- Bruce Momjian <br...@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 + -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers