On Fri, Aug 5, 2016 at 03:26:15PM -0400, Bruce Momjian wrote:
> > 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.
Here is an illustration of the issue:
CREATE TABLE test (col1 INTEGER, col2 INTEGER, col3 INTEGER);-- index first two columnsCREATE INDEX i_test1 ON test
(col1);CREATEINDEX i_test2 ON test (col2);INSERT INTO test VALUES (1, 7, 20);-- create a HOT chainUPDATE test SET col3
=30;-- change the HOT chain to a WARM chain, changes i_test2 but not i_test1UPDATE test SET col2 = 8;-- we should avoid
addinga col2=7 i_test2 index tuple-- because we already have one; how do we know that?UPDATE test SET col2 = 7;-- we
shouldsee only one col2=7 i_test2 index tupleSELECT * FROM test WHERE col2 = 7; col1 | col2 | col3------+------+------
1 | 7 | 30
-- 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 +
